Wrong result with GROUP BY + multipart key + IS (NOT) NULL + InnoDB + 1-row table + index_condition_pushdown

Bug #879871 reported by Philip Stoev on 2011-10-22
6
This bug affects 1 person
Affects Status Importance Assigned to Milestone
MariaDB
Fix Released
High
Igor Babaev

Bug 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

Changed in maria:
assignee: nobody → Oleksandr "Sanja" Byelkin (sanja-byelkin)
milestone: none → 5.3
Philip Stoev (pstoev-askmonty) wrote :

No longer reproducible.

Changed in maria:
status: New → Invalid
Philip Stoev (pstoev-askmonty) wrote :

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: <email address hidden>
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: <email address hidden>
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

summary: Wrong result with GROUP BY + multipart key + IS (NOT) NULL + InnoDB +
- 1-row table
+ 1-row table + index_condition_pushdown
Changed in maria:
status: Invalid → Confirmed
description: updated
Philip Stoev (pstoev-askmonty) wrote :

Based on feedback from Igor, assigning to Sergey P.

Changed in maria:
assignee: Oleksandr "Sanja" Byelkin (sanja-byelkin) → Sergey Petrunia (sergefp)
Changed in maria:
assignee: Sergey Petrunia (sergefp) → Igor Babaev (igorb-seattle)
importance: Undecided → High
Changed in maria:
status: Confirmed → Fix Committed
Changed in maria:
status: Fix Committed → Fix Released
To post a comment you must log in.
This report contains Public information  Edit
Everyone can see this information.

Other bug subscribers