| 2011-11-09 07:43:03 |
Philip Stoev |
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');
SELECT a FROM t1 WHERE c IS NULL AND d IS NOT NULL GROUP BY 1;
bzr version-info:
revision-id: igor@askmonty.org-20111022071427-lvbk4la7nuiqy1vr
date: 2011-10-22 00:14:27 -0700
build-date: 2011-10-22 14:36:32 +0300
revno: 3246
branch-nick: maria-5.3 |
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: igor@askmonty.org-20111022071427-lvbk4la7nuiqy1vr
date: 2011-10-22 00:14:27 -0700
build-date: 2011-10-22 14:36:32 +0300
revno: 3246
branch-nick: maria-5.3 |
|
