Details
-
Type:
Bug
-
Status: Closed
-
Priority:
Major
-
Resolution: Fixed
-
Affects Version/s: None
-
Fix Version/s: None
-
Component/s: None
-
Labels:
Description
The following query:
SELECT a FROM t1 WHERE c IS NULL AND d IS NOT NULL GROUP BY 1;
returns a row evcen though no row matches the WHERE predicate.
Repeatable in maria-5.3. Not repeatable in maria-5.2,mysql-5.5
offending explain from maria 5.3:
| 1 | SIMPLE | t1 | index | c | PRIMARY | 4 | NULL | 1 | Using where |
correct explain from maria 5.2, mysql 5.5:
| 1 | SIMPLE | t1 | ref | c | c | 4 | const | 1 | Using where; Using filesort |
test case:
DROP TABLE t1;
CREATE TABLE t1 (
a int NOT NULL,
b int,
c varchar(1),
d varchar(1),
PRIMARY KEY (a),
KEY c (c,b)
) ENGINE=InnoDB;
INSERT INTO t1 VALUES (10,8,'g','g');
SET SESSION optimizer_switch='index_condition_pushdown=ON'; # was missing previously from test case
SELECT a FROM t1 WHERE c IS NULL AND d IS NOT NULL GROUP BY 1;
bzr version-info:
revision-id: <email address hidden>
date: 2011-10-22 00:14:27 -0700
build-date: 2011-10-22 14:36:32 +0300
revno: 3246
branch-nick: maria-5.3
Gliffy Diagrams
Attachments
Activity
- All
- Comments
- Work Log
- History
- Activity
- Transitions
Re: Wrong result with GROUP BY + multipart key + IS (NOT) NULL + InnoDB + 1-row table + index_condition_pushdown
It turns out that this bug is still reproducible, it just requires index_condition_pushdown=ON.
Full test case:
--source include/have_innodb.inc
CREATE TABLE t1 (
a int NOT NULL,
b int,
c varchar(1),
d varchar(1),
PRIMARY KEY (a),
KEY c (c,b)
) ENGINE=InnoDB;
INSERT INTO t1 VALUES (10,8,'g','g');
SET SESSION optimizer_switch='index_condition_pushdown=ON';
SELECT a FROM t1 WHERE c IS NULL AND d IS NOT NULL GROUP BY 1;
reproducible on 5.3:
revision-id: psergey@askmonty.org-20111107123902-xzbzx5y5sdeq4ojh
date: 2011-11-07 16:39:02 +0400
build-date: 2011-11-09 09:40:52 +0200
revno: 3273
branch-nick: maria-5.3
reproducible on 5.3-icp:
revision-id: igor@askmonty.org-20111108160448-h2mykla5mhjjdckl
date: 2011-11-08 08:04:48 -0800
build-date: 2011-11-09 09:40:29 +0200
revno: 3272
branch-nick: maria-5.3-icp
Re: Wrong result with GROUP BY + multipart key + IS (NOT) NULL + InnoDB + 1-row table + index_condition_pushdown
Based on feedback from Igor, assigning to Sergey P.
Launchpad bug id: 879871
Re: Wrong result with GROUP BY + multipart key + IS (NOT) NULL + InnoDB + 1-row table
No longer reproducible.