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

[10 Jan 2009 17:20] Pavel Bazanov
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
[10 Jan 2009 17:22] Pavel Bazanov
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.
[10 Jan 2009 17:57] Valeriy Kravchuk
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.
[10 Jan 2009 18:15] Pavel Bazanov
I will try to give you more information in a couple of days when I have more time.
[14 Jan 2009 0:06] Pavel Bazanov
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
[14 Jan 2009 8:06] Sveta Smirnova
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
[15 Feb 2009 0:00] Bugs System
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".
[6 Jul 2009 13:40] Pavel Bazanov
The problem doesn't appear in 5.1.36.
[6 Jul 2009 19:21] Sveta Smirnova
Thank you for the feedback.

Set to "Can't repeat".