[MDEV-3653] LP:879871 - Wrong result with GROUP BY + multipart key + IS (NOT) NULL + InnoDB + 1-row table + index_condition_pushdown Created: 2011-10-22  Updated: 2015-02-02  Resolved: 2012-10-04

Status: Closed
Project: MariaDB Server
Component/s: None
Affects Version/s: None
Fix Version/s: None

Type: Bug Priority: Major
Reporter: Philip Stoev (Inactive) Assignee: Igor Babaev
Resolution: Fixed Votes: 0
Labels: Launchpad

Attachments: XML File LPexportBug879871.xml    

 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

 Comments   
Comment by Philip Stoev (Inactive) [ 2011-10-26 ]

Re: Wrong result with GROUP BY + multipart key + IS (NOT) NULL + InnoDB + 1-row table
No longer reproducible.

Comment by Philip Stoev (Inactive) [ 2011-11-09 ]

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

Comment by Philip Stoev (Inactive) [ 2011-11-11 ]

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.

Comment by Rasmus Johansson (Inactive) [ 2011-12-13 ]

Launchpad bug id: 879871

Generated at Thu Feb 08 06:50:12 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.