Bug #42240 Query returns non-matching rows with Innodb + IN + ORDER BY pk
Submitted: 21 Jan 2009 14:36 Modified: 1 Sep 2022 7:08
Reporter: Philip Stoev Email Updates:
Status: Can't repeat Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S2 (Serious)
Version:5.1-bzr, 6.0-bzr OS:Any
Assigned to: Assigned Account CPU Architecture:Any
Tags: regression

[21 Jan 2009 14:36] Philip Stoev 
Description:
The following query:

SELECT * FROM `D` WHERE `date_key` IN ( '2008' , '2008-03-02' ) ORDER BY `pk`;

returns rows where date_key = 0000-00-00 even though such rows are not returned without the ORDER BY.

The following items must be present for the bug to trigger:

* Table must be Innodb, MyISAM is not affected
* The IN predicate must contain both a valid and an invalid value.
* ORDER BY primary key must be present
* The table needs to contain both indexed and unindexed columns, even though the unindexed column does not participate in the query

How to repeat:
--source include/have_innodb.inc

SET SQL_MODE='NO_ENGINE_SUBSTITUTION';

CREATE TABLE `D` (
  `pk` int(11) NOT NULL AUTO_INCREMENT,
  `date_key` date NOT NULL,
  `date_nokey` date DEFAULT NULL,
  PRIMARY KEY (`pk`),
  KEY `date_key` (`date_key`)
) ENGINE=InnoDB;

INSERT INTO `D` VALUES (1,'2005-09-22',NULL),(2,'2003-02-18',NULL),(6,'0000-00-00',NULL),(9,'0000-00-00',NULL),(10,'0000-00-00',NULL),(11,'2008-10-20',NULL),(12,'2000-11-15',NULL),(13,'2009-09-23',NULL),(14,'2008-05-20',NULL),(15,'2004-07-04',NULL),(16,'0000-00-00',NULL),(17,'0000-00-00',NULL),(18,'0000-00-00',NULL),(19,'0000-00-00',NULL),(20,'0000-00-00',NULL),(21,'2009-07-12',NULL),(22,'2001-11-17',NULL),(23,'2004-10-15',NULL),(24,'0000-00-00',NULL),(25,'2003-10-14',NULL),(26,'0000-00-00',NULL),(27,'0000-00-00',NULL),(28,'2004-09-23',NULL),(29,'2005-10-11',NULL),(30,'2007-01-06',NULL),(31,'0000-00-00',NULL),(32,'0000-00-00',NULL),(33,'2009-10-10',NULL),(34,'2002-03-21',NULL),(35,'2000-12-23',NULL),(36,'0000-00-00',NULL),(37,'0000-00-00',NULL),(38,'2007-03-15',NULL),(39,'2004-06-13',NULL),(40,'2004-10-13',NULL),(41,'2004-01-19',NULL),(42,'2006-09-24',NULL),(43,'2008-10-18',NULL),(44,'2008-12-27',NULL),(45,'2007-04-12',NULL),(46,'2004-08-15',NULL),(47,'2005-04-03',NULL),(48,'2009-09-11',NULL),(49,'2004-09-21',NULL),(50,'2001-03-16',NULL),(51,'0000-00-00',NULL),(52,'0000-00-00',NULL),(53,'2004-12-09',NULL),(54,'2007-03-19',NULL),(55,'0000-00-00',NULL),(56,'0000-00-00',NULL),(57,'2001-11-19',NULL),(58,'2006-11-20',NULL),(59,'0000-00-00',NULL),(60,'0000-00-00',NULL),(61,'0000-00-00',NULL),(62,'2001-04-08',NULL),(63,'2001-07-22',NULL),(64,'0000-00-00',NULL),(65,'2006-11-19',NULL),(66,'0000-00-00',NULL),(67,'2004-06-25',NULL),(68,'0000-00-00',NULL),(69,'0000-00-00',NULL),(70,'2002-12-25',NULL),(71,'0000-00-00',NULL),(72,'2006-11-27',NULL),(73,'2000-06-10',NULL),(74,'0000-00-00',NULL),(75,'2002-07-24',NULL),(76,'2007-06-23',NULL),(77,'2001-12-22',NULL),(78,'0000-00-00',NULL),(79,'0000-00-00',NULL),(80,'0000-00-00',NULL),(81,'0000-00-00',NULL),(82,'0000-00-00',NULL),(83,'2009-02-06',NULL),(84,'2004-09-28',NULL),(85,'0000-00-00',NULL),(86,'2002-01-25',NULL),(87,'2006-10-10',NULL),(88,'2000-01-09',NULL),(89,'0000-00-00',NULL),(90,'0000-00-00',NULL),(91,'0000-00-00',NULL),(92,'2000-01-28',NULL),(93,'0000-00-00',NULL),(94,'2006-09-06',NULL),(95,'2007-12-03',NULL),(96,'2000-12-03',NULL),(97,'2005-09-23',NULL),(98,'0000-00-00',NULL),(99,'2007-05-17',NULL),(100,'2009-12-02',NULL),(101,'0000-00-00',NULL);

SELECT * FROM `D` WHERE `date_key` IN ( '2008' , '2008-03-02' );
SELECT * FROM `D` WHERE `date_key` IN ( '2008' , '2008-03-02' ) ORDER BY `pk`;
[21 Jan 2009 14:48] Philip Stoev 
Here is another example, involving varchar keys and BETWEEN

CREATE TABLE `BB` (
  `pk` int(11) NOT NULL AUTO_INCREMENT,
  `varchar_key` varchar(1) NOT NULL,
  `varchar_nokey` varchar(1) NOT NULL,
  PRIMARY KEY (`pk`),
  KEY `varchar_key` (`varchar_key`)
) ENGINE=InnoDB;

INSERT INTO `BB` VALUES (10,'a',''),(11,'b',''),(13,'z','');

# This returns all rows
SELECT * FROM `BB` WHERE `varchar_key` BETWEEN 'm' AND 1 ORDER BY `pk`;

# This returns only one row
SELECT * FROM `BB` WHERE `varchar_key` BETWEEN 'm' AND 1
[21 Jan 2009 15:08] Valeriy Kravchuk 
5.0.76 is NOT saffected, so this is a regression:

openxs@suse:/home2/openxs/dbs/5.0/mysql-test> ./mysql-test-run.pl 42240
Logging: ./mysql-test-run.pl 42240
MySQL Version 5.0.76
Using ndbcluster when necessary, mysqld supports it
Setting mysqld to support SSL connections
Binaries are debug compiled
mysql-test-run: WARNING: Could not find all required ndb binaries, all ndb tests will fail, use --skip-ndbcluster to skip testing it.
Using MTR_BUILD_THREAD      = 0
Using MASTER_MYPORT         = 9306
Using MASTER_MYPORT1        = 9307
Using SLAVE_MYPORT          = 9308
Using SLAVE_MYPORT1         = 9309
Using SLAVE_MYPORT2         = 9310
Using NDBCLUSTER_PORT       = 9311
Using IM_PORT               = 9313
Using IM_MYSQLD1_PORT       = 9314
Using IM_MYSQLD2_PORT       = 9315
Killing Possible Leftover Processes
Removing Stale Files
Creating Directories
Installing Master Database
=======================================================
Starting Tests in the 'main' suite

TEST                           RESULT         TIME (ms)
-------------------------------------------------------

42240                          [ fail ]

mysqltest: The specified result file does not exist: '/home2/openxs/dbs/5.0/mysql-test/r/42240.result'

The result from queries just before the failure was:
< snip >
(1,'2005-09-22',NULL),(2,'2003-02-18',NULL),(6,'0000-00-00',NULL),(9,'0000-00-00',NULL),(10,'0000-00-00',NULL),(11,'2008-10-20',NULL),(12,'2000-11-15',NULL),(13,'2009-09-23',NULL),
(14,'2008-05-20',NULL),(15,'2004-07-04',NULL),(16,'0000-00-00',NULL),(17,'0000-00-00',NULL
),(18,'0000-00-00',NULL),(19,'0000-00-00',NULL),(20,'0000-00-00',NULL),(21,'2009-07-12',NULL),(22,'2001-11-17',NULL),(23,'2004-10-15',NULL),(24,'0000-00-00',NULL),(25,'2003-10-14',
NULL),(26,'0000-00-00',NULL),(27,'0000-00-00',NULL),(28,'2004-09-23',NULL),(29,'2005-10-11',NULL),(30,'2007-01-06',NULL),(31,'0000-00-00',NULL),(32,'0000-00-00',NULL),(33,'2009-10-10',NULL),(34,'2002-03-21',NULL),(35,'2000-12-23',NULL),(36,'0000-00-00',NULL),(37,'0000-00-00',NULL),(38,'2007-03-15',NULL),(39,'2004-06-13',NULL),(40,'2004-10-13',NULL),(41,'2004-01-19',NULL),(42,'2006-09-24',NULL),(43,'2008-10-18',NULL),
(44,'2008-12-27',NULL),(45,'2007-04-12',NULL),(46,'2004-08-15',NULL),(47,'2005-04-03',NULL),(48,'2009-09-11',NULL),(49,'2004-09-21',NULL),(50,'2001-03-16',NULL),(51,'0000-00-00',NULL),(52,'0000-00-00',NULL),(53
,'2004-12-09',NULL),(54,'2007-03-19',NULL),(55,'0000-00-00',NULL),(56,'0000-00-00',NULL),(
57,'2001-11-19',NULL),(58,'2006-11-20',NULL),(59,'0000-00-00',NULL),(60,'0000-00-00',NULL)
,(61,'0000-00-00',NULL),(62,'2001-04-08',NULL),(63,'2001-07-22',NULL),(64,'0000-00-00',NULL),(65,'2006-11-19',NULL),(66,'0000-00-00',NULL),(67,'2004-06-25',NULL),(68,'0000-00-00',NULL),(69,'0000-00-00',NULL),(70,'2002-12-25',NULL),(71,'0000-00-00',NULL),(72,'2006-11-27'
,NULL),(73,'2000-06-10',NULL),(74,'0000-00-00',NULL),(75,'2002-07-24',NULL),(76,'2007-06-23',NULL),(77,'2001-12-22',NULL),(78,'0000-00-00',NULL),(79,'0000-00-00',NULL),(80,'0000-00-00',NULL),(81,'0000-00-00',NULL),(82,'0000-00-00',NULL),(83,'2009-02-06',NULL),(84,'2004-09-28',NULL),(85,'0000-00-00',NULL),(86,'2002-01-25',NULL),(87,'2006-10-10',NULL),(88,'2000-01-09',NULL),(89,'0000-00-00',NULL),(90,'0000-00-00',NULL),(91,'0000-00-00',NULL),(92,'2000-01-28',NULL),(93,'0000-00-00',NULL),(94,'2006-09-06',NULL),(95,'2007-12-03',NULL),(96,
'2000-12-03',NULL),(97,'2005-09-23',NULL),(98,'0000-00-00',NULL),(99,'2007-05-17',NULL),(100,'2009-12-02',NULL),(101,'0000-00-00',NULL);
SELECT * FROM `D` WHERE `date_key` IN ( '2008' , '2008-03-02' );
pk      date_key        date_nokey
Warnings:
Warning 1292    Incorrect date value: '2008' for column 'date_key' at row 1
Warning 1292    Incorrect date value: '2008' for column 'date_key' at row 1
SELECT * FROM `D` WHERE `date_key` IN ( '2008' , '2008-03-02' ) ORDER BY `pk`;
pk      date_key        date_nokey
Warnings:
Warning 1292    Incorrect date value: '2008' for column 'date_key' at row 1
Warning 1292    Incorrect date value: '2008' for column 'date_key' at row 1

More results from queries before failure can be found in /home2/openxs/dbs/5.0/mysql-test/var/log/42240.log

Warnings from just before the error:
Warning 1292 Incorrect date value: '2008' for column 'date_key' at row 1

Aborting: 42240 failed in default mode.
To continue, re-run with '--force'.
Stopping All Servers
skipped 9 bytes from file: socket (3)
[22 Jan 2009 17:43] Valeriy Kravchuk 
5.1.30 is also affected, so this is NOT a recent regression.
[1 Sep 2022 7:08] Roy Lyseng 
Posted by developer:
 
Not reproducible in 8.0.