MySQL Bugs: #37977: Wrong result returned on GROUP BY + OR + Innodb

Bug #37977	Wrong result returned on GROUP BY + OR + Innodb
Submitted:	8 Jul 2008 20:38	Modified:	16 Nov 2010 3:56
Reporter:	Philip Stoev	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S1 (Critical)
Version:	6.0	OS:	Any
Assigned to:	Sergey Petrunya	CPU Architecture:	Any

Description:
MySQL returns a wrong result on the following query against an Innodb table:

mysql> select pk from C WHERE `int_nokey` IS NULL OR  `varchar_key` > 'kr' group by pk\g
+----+
| pk |
+----+
|  1 |
|  2 |
|  3 |
|  4 |
|  5 |
+----+
5 rows in set (0.00 sec)

In fact, only two rows from the table match the condition so it is impossible for the GROUP BY to return 5 rows:

mysql> select pk from C WHERE `int_nokey` IS NULL OR  `varchar_key` > 'kr';
+----+
| pk |
+----+
|  1 |
|  5 |
+----+
2 rows in set (0.00 sec)

The left-hand side of the OR does not match any rows. If it is removed, the GROUP BY starts returning correct data. MyISAM is not affected.

How to repeat:
Some columns may be unnecessary:

CREATE TABLE `C` (
  `pk` int(11) NOT NULL AUTO_INCREMENT,
  `int_nokey` int(11) NOT NULL,
  `int_key` int(11) NOT NULL,
  `date_key` date NOT NULL,
  `date_nokey` date NOT NULL,
  `time_key` time NOT NULL,
  `time_nokey` time NOT NULL,
  `datetime_key` datetime NOT NULL,
  `datetime_nokey` datetime NOT NULL,
  `varchar_key` varchar(5) DEFAULT NULL,
  `varchar_nokey` varchar(5) DEFAULT NULL,
  PRIMARY KEY (`pk`),
  KEY `int_key` (`int_key`),
  KEY `date_key` (`date_key`),
  KEY `time_key` (`time_key`),
  KEY `datetime_key` (`datetime_key`),
  KEY `varchar_key` (`varchar_key`)
) ENGINE=InnoDB;

INSERT INTO `C` VALUES (1,5,5,'2009-10-16','2009-10-16','09:28:15','09:28:15','2007-09-14 05:34:08','2007-09-14 05:34:08','qk','qk'),
(2,6,6,'0000-00-00','0000-00-00','23:06:39','23:06:39','0000-00-00 00:00:00','0000-00-00 00:00:00','j','j'),
(3,10,10,'2000-12-18','2000-12-18','22:16:19','22:16:19','2006-11-04 15:42:50','2006-11-04 15:42:50','aew','aew'),
(4,0,0,'2001-09-18','2001-09-18','00:00:00','00:00:00','2004-03-23 13:23:35','2004-03-23 13:23:35',NULL,NULL),
(5,6,6,'2007-08-16','2007-08-16','22:13:38','22:13:38','2004-08-19 11:01:28','2004-08-19 11:01:28','qu','qu');

select pk from C WHERE `int_nokey` IS NULL OR  `varchar_key` > 'kr' group by pk;

Suggested fix:
The correct result set for this query should be two rows, two numbers, 1 and 5.

Thank you for the report.

Verified as described.

Results from previous versions:

=====mysql-4.1=====
=====bug37977=====
select version();
version()
4.1.25-debug-log
CREATE TABLE `C` (
`pk` int(11) NOT NULL AUTO_INCREMENT,
`int_nokey` int(11) NOT NULL,
`int_key` int(11) NOT NULL,
`date_key` date NOT NULL,
`date_nokey` date NOT NULL,
`time_key` time NOT NULL,
`time_nokey` time NOT NULL,
`datetime_key` datetime NOT NULL,
`datetime_nokey` datetime NOT NULL,
`varchar_key` varchar(5) DEFAULT NULL,
`varchar_nokey` varchar(5) DEFAULT NULL,
PRIMARY KEY (`pk`),
KEY `int_key` (`int_key`),
KEY `date_key` (`date_key`),
KEY `time_key` (`time_key`),
KEY `datetime_key` (`datetime_key`),
KEY `varchar_key` (`varchar_key`)
) ENGINE=InnoDB;
INSERT INTO `C` VALUES (1,5,5,'2009-10-16','2009-10-16','09:28:15','09:28:15','2007-09-14
05:34:08','2007-09-14 05:34:08','qk','qk'),
(2,6,6,'0000-00-00','0000-00-00','23:06:39','23:06:39','0000-00-00 00:00:00','0000-00-00
00:00:00','j','j'),
(3,10,10,'2000-12-18','2000-12-18','22:16:19','22:16:19','2006-11-04 15:42:50','2006-11-04
15:42:50','aew','aew'),
(4,0,0,'2001-09-18','2001-09-18','00:00:00','00:00:00','2004-03-23 13:23:35','2004-03-23
13:23:35',NULL,NULL),
(5,6,6,'2007-08-16','2007-08-16','22:13:38','22:13:38','2004-08-19 11:01:28','2004-08-19
11:01:28','qu','qu');
select pk from C WHERE `int_nokey` IS NULL OR  `varchar_key` > 'kr' group by pk;
pk
1
5
select pk from C WHERE `int_nokey` IS NULL OR  `varchar_key` > 'kr';
pk
1
5
alter table C engine=MyISAM;
select pk from C WHERE `int_nokey` IS NULL OR  `varchar_key` > 'kr' group by pk;
pk
1
5
select pk from C WHERE `int_nokey` IS NULL OR  `varchar_key` > 'kr';
pk
1
5

=====mysql-5.0=====
=====bug37977=====
select version();
version()
5.0.68-debug-log
CREATE TABLE `C` (
`pk` int(11) NOT NULL AUTO_INCREMENT,
`int_nokey` int(11) NOT NULL,
`int_key` int(11) NOT NULL,
`date_key` date NOT NULL,
`date_nokey` date NOT NULL,
`time_key` time NOT NULL,
`time_nokey` time NOT NULL,
`datetime_key` datetime NOT NULL,
`datetime_nokey` datetime NOT NULL,
`varchar_key` varchar(5) DEFAULT NULL,
`varchar_nokey` varchar(5) DEFAULT NULL,
PRIMARY KEY (`pk`),
KEY `int_key` (`int_key`),
KEY `date_key` (`date_key`),
KEY `time_key` (`time_key`),
KEY `datetime_key` (`datetime_key`),
KEY `varchar_key` (`varchar_key`)
) ENGINE=InnoDB;
INSERT INTO `C` VALUES (1,5,5,'2009-10-16','2009-10-16','09:28:15','09:28:15','2007-09-14
05:34:08','2007-09-14 05:34:08','qk','qk'),
(2,6,6,'0000-00-00','0000-00-00','23:06:39','23:06:39','0000-00-00 00:00:00','0000-00-00
00:00:00','j','j'),
(3,10,10,'2000-12-18','2000-12-18','22:16:19','22:16:19','2006-11-04 15:42:50','2006-11-04
15:42:50','aew','aew'),
(4,0,0,'2001-09-18','2001-09-18','00:00:00','00:00:00','2004-03-23 13:23:35','2004-03-23
13:23:35',NULL,NULL),
(5,6,6,'2007-08-16','2007-08-16','22:13:38','22:13:38','2004-08-19 11:01:28','2004-08-19
11:01:28','qu','qu');
select pk from C WHERE `int_nokey` IS NULL OR  `varchar_key` > 'kr' group by pk;
pk
1
5
select pk from C WHERE `int_nokey` IS NULL OR  `varchar_key` > 'kr';
pk
1
5
alter table C engine=MyISAM;
select pk from C WHERE `int_nokey` IS NULL OR  `varchar_key` > 'kr' group by pk;
pk
1
5
select pk from C WHERE `int_nokey` IS NULL OR  `varchar_key` > 'kr';
pk
1
5

=====mysql-5.1=====
=====bug37977=====
select version();
version()
5.1.28-debug-log
CREATE TABLE `C` (
`pk` int(11) NOT NULL AUTO_INCREMENT,
`int_nokey` int(11) NOT NULL,
`int_key` int(11) NOT NULL,
`date_key` date NOT NULL,
`date_nokey` date NOT NULL,
`time_key` time NOT NULL,
`time_nokey` time NOT NULL,
`datetime_key` datetime NOT NULL,
`datetime_nokey` datetime NOT NULL,
`varchar_key` varchar(5) DEFAULT NULL,
`varchar_nokey` varchar(5) DEFAULT NULL,
PRIMARY KEY (`pk`),
KEY `int_key` (`int_key`),
KEY `date_key` (`date_key`),
KEY `time_key` (`time_key`),
KEY `datetime_key` (`datetime_key`),
KEY `varchar_key` (`varchar_key`)
) ENGINE=InnoDB;
INSERT INTO `C` VALUES (1,5,5,'2009-10-16','2009-10-16','09:28:15','09:28:15','2007-09-14
05:34:08','2007-09-14 05:34:08','qk','qk'),
(2,6,6,'0000-00-00','0000-00-00','23:06:39','23:06:39','0000-00-00 00:00:00','0000-00-00
00:00:00','j','j'),
(3,10,10,'2000-12-18','2000-12-18','22:16:19','22:16:19','2006-11-04 15:42:50','2006-11-04
15:42:50','aew','aew'),
(4,0,0,'2001-09-18','2001-09-18','00:00:00','00:00:00','2004-03-23 13:23:35','2004-03-23
13:23:35',NULL,NULL),
(5,6,6,'2007-08-16','2007-08-16','22:13:38','22:13:38','2004-08-19 11:01:28','2004-08-19
11:01:28','qu','qu');
select pk from C WHERE `int_nokey` IS NULL OR  `varchar_key` > 'kr' group by pk;
pk
1
5
select pk from C WHERE `int_nokey` IS NULL OR  `varchar_key` > 'kr';
pk
1
5
alter table C engine=MyISAM;
select pk from C WHERE `int_nokey` IS NULL OR  `varchar_key` > 'kr' group by pk;
pk
1
5
select pk from C WHERE `int_nokey` IS NULL OR  `varchar_key` > 'kr';
pk
1
5

Results from version 6.0:

=====mysql-6.0=====
=====bug37977=====
select version();
version()
6.0.6-alpha-debug-log
CREATE TABLE `C` (
`pk` int(11) NOT NULL AUTO_INCREMENT,
`int_nokey` int(11) NOT NULL,
`int_key` int(11) NOT NULL,
`date_key` date NOT NULL,
`date_nokey` date NOT NULL,
`time_key` time NOT NULL,
`time_nokey` time NOT NULL,
`datetime_key` datetime NOT NULL,
`datetime_nokey` datetime NOT NULL,
`varchar_key` varchar(5) DEFAULT NULL,
`varchar_nokey` varchar(5) DEFAULT NULL,
PRIMARY KEY (`pk`),
KEY `int_key` (`int_key`),
KEY `date_key` (`date_key`),
KEY `time_key` (`time_key`),
KEY `datetime_key` (`datetime_key`),
KEY `varchar_key` (`varchar_key`)
) ENGINE=InnoDB;
INSERT INTO `C` VALUES (1,5,5,'2009-10-16','2009-10-16','09:28:15','09:28:15','2007-09-14
05:34:08','2007-09-14 05:34:08','qk','qk'),
(2,6,6,'0000-00-00','0000-00-00','23:06:39','23:06:39','0000-00-00 00:00:00','0000-00-00
00:00:00','j','j'),
(3,10,10,'2000-12-18','2000-12-18','22:16:19','22:16:19','2006-11-04 15:42:50','2006-11-04
15:42:50','aew','aew'),
(4,0,0,'2001-09-18','2001-09-18','00:00:00','00:00:00','2004-03-23 13:23:35','2004-03-23
13:23:35',NULL,NULL),
(5,6,6,'2007-08-16','2007-08-16','22:13:38','22:13:38','2004-08-19 11:01:28','2004-08-19
11:01:28','qu','qu');
select pk from C WHERE `int_nokey` IS NULL OR  `varchar_key` > 'kr' group by pk;
pk
1
2
3
4
5
select pk from C WHERE `int_nokey` IS NULL OR  `varchar_key` > 'kr';
pk
1
5
alter table C engine=MyISAM;
select pk from C WHERE `int_nokey` IS NULL OR  `varchar_key` > 'kr' group by pk;
pk
1
5
select pk from C WHERE `int_nokey` IS NULL OR  `varchar_key` > 'kr';
pk
1
5

A meaningful summary of the above copy-pastes:

4.1, 5.0, 5.1 all produce the same result. mysql-6.0 produces different result for one query:

-- 5.1.txt
++ 6.0.txt
 select pk from C WHERE `int_nokey` IS NULL OR  `varchar_key` > 'kr' group by pk;
 pk
 1
+2
+3
+4
 5

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/53452

2686 Sergey Petrunia	2008-09-05
      BUG#37977: Wrong result returned on GROUP BY + OR + Innodb
      - Make test_if_skip_sort_order() restore the where condition if it 
        decides not to use the index that make_join_readinfo() has previously 
        decided to use. We need to do this because part of the condition may 
        have been pushed down to storage engine with push_index_cond()

BUG#38013 has been marked as duplicate of this one

Pushed into 6.0.7-alpha  (revid:sergefp@mysql.com-20080905143637-2l2p380itqelych5) (version source revid:sergefp@mysql.com-20080905143637-2l2p380itqelych5) (pib:5)

Noted in 6.0.7 changelog.

The server returned incorrect results for WHERE ... OR ... GROUP BY
queries against InnoDB tables.

Pushed into mysql-next-mr (revid:alik@sun.com-20100816062819-bluwgdq8q4xysmlg) (version source revid:alik@sun.com-20100816062612-enatdwnv809iw3s9) (pib:20)

Pushed into mysql-trunk 5.6.99-m5 (revid:alexander.nozdrin@oracle.com-20101113155825-czmva9kg4n31anmu) (version source revid:vasil.dimov@oracle.com-20100629074804-359l9m9gniauxr94) (merge vers: 5.6.99-m4) (pib:21)

Noted in 5.6.1 changelog.