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.

Page generated in 0.046 sec. using MySQL 8.4.7-u6-cloud

Timestamp references displayed by the system are UTC.
Content reproduced on this site is the property of the respective copyright holders.
It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.