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: | |
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
[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".