MySQL Bugs: #42019: JOIN + LIMIT + SPECIAL WHERE in query sometimes makes it return wrong resultset

Bug #42019	JOIN + LIMIT + SPECIAL WHERE in query sometimes makes it return wrong resultset
Submitted:	10 Jan 2009 17:20	Modified:	6 Jul 2009 19:21
Reporter:	Pavel Bazanov	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	MySQL Server	Severity:	S1 (Critical)
Version:	6.0.8, 6.0.6	OS:	Windows
Assigned to:		CPU Architecture:	Any
Tags:	join, limit, where

Description:
Hello,
I have just installed a new MySQL Server v6.0.8 and immediately found a bug.

Imaging such query:

    SELECT p.ID AS PID, TS
    FROM parts p 
	INNER JOIN manufacturers m ON (p.ManufacturerID = m.ShortName) 
    WHERE p.TS > '2009-01-06 19:40:10' AND p.TS <= '2009-01-10 18:38:12' AND p.ID > 504
    ORDER BY p.ID 
    LIMIT 500;

Note, that there is "p.ID > 504" in WHERE clause.
But when I run this query it returns me also rows with ID <= 504, actually starting from the very first row in the table with ID = 1

When I either
1) remove INNER JOIN statement
or
2) remove LIMIT clause
or
3) remove first part of WHERE clause (p.TS > '2009-01-06 19:40:10')
the query seems to return correct resultset

Then I tried to install v6.0.6 and still the same.
The bug doesn't exist in 6.0.2, so it was introduced in 6.0.3 - 6.0.6 :)

How to repeat:
don't know exactly

Whant to add, that when the query is executed there are also rows with TS values violating WHERE clause, so it seems WHERE clause is omitted at all.

Thank you for a proble report. Please, send a dump of the tables involved. 

If you can't, please, send at least SHOW CREATE TABLE and SHOW TABKE STATUS results for them, as well as EXPLAIN results for the query.

I will try to give you more information in a couple of days when I have more time.

Ok, here is CREATE TABLE DDL, just don't laugh too much, it was designed many years ago by a student :)

CREATE TABLE `parts` (
  `ID` INTEGER(11) UNSIGNED NOT NULL AUTO_INCREMENT,
  `PartN` VARCHAR(20) COLLATE utf8_general_ci DEFAULT NULL,
  `ManufacturerID` VARCHAR(5) COLLATE utf8_general_ci DEFAULT NULL,
  `Substitution` VARCHAR(100) COLLATE utf8_general_ci DEFAULT NULL,
  `OrderType` VARCHAR(1) COLLATE utf8_general_ci DEFAULT NULL,
  `Description` VARCHAR(250) COLLATE utf8_general_ci DEFAULT NULL,
  `Quantity` SMALLINT(5) DEFAULT '0',
  `Available` INTEGER(10) DEFAULT '0',
  `Price` DOUBLE(7,2) DEFAULT '0.00',
  `FactPrice` DOUBLE(15,5) DEFAULT NULL,
  `Remarks` VARCHAR(250) COLLATE utf8_general_ci DEFAULT NULL,
  `OrderDate` DATETIME DEFAULT NULL,
  `Customer` VARCHAR(50) COLLATE utf8_general_ci DEFAULT NULL,
  `CustomerOrderN` SMALLINT(5) DEFAULT '0',
  `SupInvoicesID` INTEGER(10) DEFAULT '0',
  `Supplier1` INTEGER(10) DEFAULT '0',
  `Supplier2` INTEGER(10) DEFAULT '0',
  `Supplier3` INTEGER(10) DEFAULT '0',
  `Supplier4` INTEGER(10) DEFAULT '0',
  `Supplier5` INTEGER(10) DEFAULT '0',
  `SupplierOrderN1` INTEGER(10) DEFAULT '0',
  `SupplierOrderN2` INTEGER(10) DEFAULT '0',
  `SupplierOrderN3` INTEGER(10) DEFAULT '0',
  `SupplierOrderN4` INTEGER(10) DEFAULT '0',
  `SupplierOrderN5` INTEGER(10) DEFAULT '0',
  `SupplierOrderDate1` DATETIME DEFAULT NULL,
  `SupplierOrderDate2` DATETIME DEFAULT NULL,
  `SupplierOrderDate3` DATETIME DEFAULT NULL,
  `SupplierOrderDate4` DATETIME DEFAULT NULL,
  `SupplierOrderDate5` DATETIME DEFAULT NULL,
  `OrderSent` TINYINT(1) NOT NULL DEFAULT '0',
  `OrderDebited` TINYINT(1) NOT NULL DEFAULT '0',
  `OrderInvoiced` TINYINT(1) NOT NULL DEFAULT '0',
  `SubstitutionPrice` TINYINT(1) DEFAULT '0',
  `TS` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `CustomerInvoiceN` INTEGER(10) DEFAULT '0',
  `OrderClosed` TINYINT(1) NOT NULL DEFAULT '0',
  `ShipmentType` VARCHAR(1) COLLATE utf8_general_ci NOT NULL DEFAULT '',
  `IsScanned` TINYINT(1) DEFAULT '0',
  `IsPacked` TINYINT(1) DEFAULT '0',
  `TSPacked` TIMESTAMP NOT NULL DEFAULT '0000-00-00 00:00:00',
  `BoxN` VARCHAR(20) COLLATE utf8_general_ci DEFAULT NULL,
  `InternalBoxN` INTEGER(11) DEFAULT '0',
  `StickerPrinted` TINYINT(4) DEFAULT '0',
  `Comments` VARCHAR(250) COLLATE utf8_general_ci DEFAULT NULL,
  `TSPrinted` TIMESTAMP NOT NULL DEFAULT '0000-00-00 00:00:00',
  `TSScanned` TIMESTAMP NOT NULL DEFAULT '0000-00-00 00:00:00',
  `TSDebited` TIMESTAMP NOT NULL DEFAULT '0000-00-00 00:00:00',
  `TSClosed` TIMESTAMP NOT NULL DEFAULT '0000-00-00 00:00:00',
  `TSInvoiced` TIMESTAMP NOT NULL DEFAULT '0000-00-00 00:00:00',
  `Problem` VARCHAR(20) COLLATE utf8_general_ci DEFAULT NULL,
  `RealPrice` DOUBLE(15,3) DEFAULT NULL,
  `TSStatusUpdated` TIMESTAMP NOT NULL DEFAULT '0000-00-00 00:00:00',
  `PositionConstraint` VARCHAR(25) COLLATE utf8_general_ci DEFAULT '',
  `CustomerChosenSupplierID` INTEGER(10) UNSIGNED DEFAULT NULL,
  `ExpectedPrice` DOUBLE(15,3) DEFAULT NULL,
  PRIMARY KEY (`ID`),
  KEY `PartN` (`PartN`, `ManufacturerID`, `Customer`),
  KEY `OrderDebited` (`OrderDebited`),
  KEY `OrderInvoiced` (`OrderInvoiced`),
  KEY `IsPacked` (`IsPacked`),
  KEY `IsScanned` (`IsScanned`),
  KEY `BoxN` (`BoxN`),
  KEY `ManufacturerID` (`ManufacturerID`),
  KEY `InternalBoxN` (`InternalBoxN`),
  KEY `ForBalance` (`OrderDebited`, `OrderInvoiced`, `Customer`),
  KEY `TSPrinted` (`TSPrinted`),
  KEY `TSScanned` (`TSScanned`),
  KEY `TSPacked` (`TSPacked`),
  KEY `Supplier1` (`Supplier1`, `SupplierOrderN1`),
  KEY `Supplier2` (`Supplier2`, `SupplierOrderN2`),
  KEY `Supplier3` (`Supplier3`, `SupplierOrderN3`),
  KEY `Supplier4` (`Supplier4`, `SupplierOrderN4`),
  KEY `Supplier5` (`Supplier5`, `SupplierOrderN5`),
  KEY `TS` (`TS`),
  KEY `Customer` (`Customer`, `CustomerOrderN`),
  KEY `ForNotDebitedSumm` (`Customer`, `OrderDebited`, `OrderClosed`),
  KEY `FK_parts_CustomerChosenSupplierID_to_suppliers_ID` (`CustomerChosenSupplierID`),
  CONSTRAINT `FK_parts_CustomerChosenSupplierID_to_suppliers_ID` FOREIGN KEY (`CustomerChosenSupplierID`) REFERENCES `suppliers` (`ID`) ON DELETE SET NULL ON UPDATE CASCADE
)ENGINE=InnoDB
AUTO_INCREMENT=455553 ROW_FORMAT=DYNAMIC CHARACTER SET 'utf8' COLLATE 'utf8_general_ci';

CREATE TABLE `manufacturers` (
  `ShortName` VARCHAR(5) COLLATE utf8_general_ci DEFAULT NULL,
  `FullName` VARCHAR(50) COLLATE utf8_general_ci DEFAULT NULL,
  `ID` INTEGER(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  `RemindOfAutoNavar` TINYINT(1) DEFAULT '1',
  `DefaultCountryID` INTEGER(11) DEFAULT NULL,
  PRIMARY KEY (`ID`),
  UNIQUE KEY `ShortName` (`ShortName`),
  UNIQUE KEY `FullName` (`FullName`),
  KEY `DefaultCountryID` (`DefaultCountryID`),
  CONSTRAINT `manufacturers_fk` FOREIGN KEY (`DefaultCountryID`) REFERENCES `mancountries` (`ManCountryID`) ON DELETE SET NULL ON UPDATE CASCADE
)ENGINE=InnoDB
AUTO_INCREMENT=56 CHARACTER SET 'utf8' COLLATE 'utf8_general_ci'

SHOW TABLE STATUS:

| Name                | Engine | Version | Row_format | Rows    | Avg_row_length
 | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | C
reate_time         | Update_time         | Check_time | Collation       | Checks
um | Create_options     | Comment                |

| manufacturers       | InnoDB |      10 | Compact    |      37 |            442
 |       16384 |               0 |        49152 |  10485760 |             56 | 2
009-01-14 03:40:37 | NULL                | NULL       | utf8_general_ci |     NU
LL |                    | InnoDB free: 35840 kB  |

| parts               | InnoDB |      10 | Compact    |  465196 |            253
 |   118145024 |               0 |    220151808 |  10485760 |         455552 | 2
009-01-14 03:40:38 | NULL                | NULL       | utf8_general_ci |     NU
LL | row_format=DYNAMIC |                        |

EXPLAIN:

mysql> EXPLAIN     SELECT p.ID AS PID, TS
    ->     FROM parts p
    ->  INNER JOIN manufacturers m ON (p.ManufacturerID = m.ShortName)
    ->     WHERE p.TS > '2009-01-06 19:40:10' AND p.TS <= '2009-01-10 18:38:12'
AND p.ID > 504
    ->     ORDER BY p.ID
    ->     LIMIT 500;
+----+-------------+-------+-------+---------------------------+-----------+----
-----+-------------------------+--------+-------------+
| id | select_type | table | type  | possible_keys             | key       | key
_len | ref                     | rows   | Extra       |
+----+-------------+-------+-------+---------------------------+-----------+----
-----+-------------------------+--------+-------------+
|  1 | SIMPLE      | p     | index | PRIMARY,ManufacturerID,TS | PRIMARY   | 4
     | NULL                    | 270403 |             |
|  1 | SIMPLE      | m     | ref   | ShortName                 | ShortName | 23
     | rabase.p.ManufacturerID |      1 | Using index |
+----+-------------+-------+-------+---------------------------+-----------+----
-----+-------------------------+--------+-------------+

I think it is interesting that in 6.0.2 I have another EXPLAIN results:

id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	p	range	PRIMARY,ManufacturerID,TS	TS	4	NULL	879	Using where; Using filesort
1	SIMPLE	m	ref	ShortName	ShortName	18	rabase.p.ManufacturerID	1	Using where; Using index

Thank you for the feedback.

I can not repeat described behavior with test data.

Please provide dump of data from affected tables. Probably data which satisfy WHERE clause and row with parts.ID=504 would be enough

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".

The problem doesn't appear in 5.1.36.

Thank you for the feedback.

Set to "Can't repeat".