Bug #87644 | Order by on 'datetime' column not working with LIMIT <row_count> | ||
---|---|---|---|
Submitted: | 1 Sep 2017 22:19 | Modified: | 25 Apr 2018 13:37 |
Reporter: | KORBULY Francois | Email Updates: | |
Status: | Verified | Impact on me: | |
Category: | MySQL Server: Optimizer | Severity: | S2 (Serious) |
Version: | 5.7.18 | OS: | CentOS (6 (x86_64)) |
Assigned to: | CPU Architecture: | Any | |
Tags: | datetime, limit, no sorting, order by |
[1 Sep 2017 22:19]
KORBULY Francois
[1 Sep 2017 22:42]
MySQL Verification Team
Thank you for the bug report. - Unzip 'fk_testing_db.zip' ? Please attach it here using Files tab. Thanks.
[5 Sep 2017 15:11]
KORBULY Francois
Hi, My file is more than 3 Mb, tried to upload it according to documentation on 'Files' tab. But got this error : sftp -oPort=2021 -oUser=francois.korbuly@mail.mcgill.ca sftp.oracle.com:/support/incoming ssh_dispatch_run_fatal: Connection to 141.146.1.165 port 2021: DH GEX group out of range Couldn't read packet: Connection reset by peer Is there another way to provide it to you ? Thanks, Francois
[6 Sep 2017 13:19]
Chiranjeevi Battula
Hello KORBULY, Thank you for your feedback. If the data you need to attach is more than 3MB, you should create a compressed archive of the data and a README file that describes the data with a filename that includes the bug number (recommended filename: mysql-bug-data-87644.zip) and upload one to sftp.oracle.com. Thanks, Chiranjeevi.
[7 Sep 2017 13:10]
KORBULY Francois
Hi, Eventually, I was able to upload the file on your SFTP. The file contains just a SQL to execute on a server. Here are the two tables structures : CREATE TABLE `batch` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `containerID` int(10) unsigned DEFAULT NULL, `technicianID` int(10) unsigned DEFAULT NULL, `machine_auditID` int(10) unsigned DEFAULT NULL, `protocolType` enum('SEQUENCING','PCR','READ','VECTOR_SCREENING','SUBMISSION') DEFAULT NULL, `experimentDate` datetime DEFAULT NULL, `isOk` tinyint(1) NOT NULL DEFAULT '0', `batchNumber` int(10) unsigned DEFAULT NULL, `commentID` int(10) unsigned DEFAULT NULL, `seq_formamide` char(20) DEFAULT NULL, `serviceType` enum('SEQUENCING','BAC_CLONE','SNP_DISCOVERY') DEFAULT 'SEQUENCING', `thermocycler_machineAuditID` int(10) DEFAULT NULL, `machineID` int(10) unsigned DEFAULT NULL, `time_created` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, `poNumber` char(50) DEFAULT '', `isEmailSent` tinyint(1) NOT NULL DEFAULT '0', `productCodeId` int(11) DEFAULT NULL, `priorityStatus` enum('FAST_TRACK','PRIORITY','REGULAR') DEFAULT 'REGULAR', PRIMARY KEY (`id`), KEY `isOk` (`isOk`), KEY `machineID` (`machine_auditID`), KEY `technicianID` (`technicianID`), KEY `containerID` (`containerID`), KEY `experimentDate` (`experimentDate`), KEY `protocolType` (`protocolType`), KEY `commentID` (`commentID`), KEY `thermocycler_machineAuditID` (`thermocycler_machineAuditID`), KEY `batchNumber` (`batchNumber`), KEY `poNumber` (`poNumber`), KEY `protocolType_isOk` (`protocolType`,`isOk`), KEY `productCodeId` (`productCodeId`), CONSTRAINT `0_118` FOREIGN KEY (`containerID`) REFERENCES `container` (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=202432 DEFAULT CHARSET=utf8 CREATE TABLE `container` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `name` char(50) NOT NULL DEFAULT '', `isGivenByClient` tinyint(1) NOT NULL DEFAULT '0', `clientGivenName` char(50) DEFAULT NULL, `type` enum('PLATE','TUBE','BOX') NOT NULL DEFAULT 'PLATE', `nbColumns` tinyint(3) unsigned NOT NULL DEFAULT '0', `nbRows` tinyint(3) unsigned NOT NULL DEFAULT '0', `freezer` char(15) DEFAULT NULL, `barcode` char(20) DEFAULT NULL, `availableForFullPlateSubmission` bit(1) NOT NULL DEFAULT b'0', PRIMARY KEY (`id`), UNIQUE KEY `name` (`name`), UNIQUE KEY `barcode` (`barcode`), KEY `clientGivenName` (`clientGivenName`) ) ENGINE=InnoDB AUTO_INCREMENT=189446 DEFAULT CHARSET=utf8 Thanks again for your help, Francois
[7 Sep 2017 23:58]
MySQL Verification Team
Hi, With your 2 tables if I load the dummy data (in 5.7.19) I have proper response, the rows are sorted by experimentDate descending. Did you manage to upload inserts also? so I can try with your data? best regards Bogdan
[8 Sep 2017 16:15]
KORBULY Francois
Hi Bogdan, The file provided contained the real data we have and with which we have the sorting issue. It is a script that create a new database(fk_testing), create the 2 tables and make the inserts. As an addition I had attached also 2 screenshots of the results (with or without LIMIT on 5.7.18 server) of what I experience. Francois
[8 Sep 2017 16:17]
KORBULY Francois
Screenshot of issue with limit
Attachment: mysql-bug-87644-with_limit.jpg (image/jpeg, text), 382.03 KiB.
[8 Sep 2017 16:17]
KORBULY Francois
Screenshot of issue without limit
Attachment: mysql-bug-87644-without_limit.jpg (image/jpeg, text), 330.42 KiB.
[11 Sep 2017 21:52]
MySQL Verification Team
Hi, With your data - bug is reproducible. Thanks a lot for the data and submission! all best Bogdan
[11 Sep 2017 21:52]
MySQL Verification Team
mysql [localhost] {msandbox} (fk_testing) > SELECT DISTINCT(c.name), b.experimentDate, c.* FROM container c , batch b WHERE b.protocolType='READ' AND c.id=b.containerID ORDER BY b.experimentDate DESC limit 10; +------------------+---------------------+--------+------------------+-----------------+-----------------+-------+-----------+--------+---------+------------+---------------------------------+ | name | experimentDate | id | name | isGivenByClient | clientGivenName | type | nbColumns | nbRows | freezer | barcode | availableForFullPlateSubmission | +------------------+---------------------+--------+------------------+-----------------+-----------------+-------+-----------+--------+---------+------------+---------------------------------+ | 01Aug13-DRSeq | 2013-08-01 21:08:35 | 138986 | 01Aug13-DRSeq | 0 | | PLATE | 12 | 8 | | 1000066153 | | | 01Dec16-CSeq | 2016-12-02 13:13:41 | 183774 | 01Dec16-CSeq | 0 | | PLATE | 12 | 8 | | 1000020348 | | | 01Dec16-CSeq | 2016-12-02 19:37:37 | 183774 | 01Dec16-CSeq | 0 | | PLATE | 12 | 8 | | 1000020348 | | | 01Feb11ABCSeq | 2011-02-02 01:32:04 | 96437 | 01Feb11ABCSeq | 0 | | PLATE | 12 | 8 | | 1000072491 | | | 01Nov13-ACDRSeq | 2013-11-01 20:23:29 | 142706 | 01Nov13-ACDRSeq | 0 | | PLATE | 12 | 8 | | 1000059565 | | | 01Nov13-CSeq | 2013-11-05 20:33:30 | 142820 | 01Nov13-CSeq | 0 | | PLATE | 12 | 8 | | 1000003479 | | | 02Apr14-ABCSeq | 2014-04-04 20:01:54 | 148471 | 02Apr14-ABCSeq | 0 | | PLATE | 12 | 8 | | 1000059563 | | | 02Jul13-CSeq | 2013-07-04 20:25:12 | 137658 | 02Jul13-CSeq | 0 | | PLATE | 12 | 8 | | 1000066390 | | | 02Jun15-ABCSeq | 2015-06-03 17:54:23 | 165347 | 02Jun15-ABCSeq | 0 | | PLATE | 12 | 8 | | 1000021867 | | | 02May16-ABCDRSeq | 2016-05-02 00:17:50 | 176747 | 02May16-ABCDRSeq | 0 | | PLATE | 12 | 8 | | 1000020928 | | +------------------+---------------------+--------+------------------+-----------------+-----------------+-------+-----------+--------+---------+------------+---------------------------------+ 10 rows in set (0.77 sec) mysql [localhost] {msandbox} (fk_testing) >
[11 Sep 2017 21:53]
MySQL Verification Team
mysql [localhost] {msandbox} (fk_testing) > SELECT DISTINCT(c.name), b.experimentDate, c.* FROM container c , batch b WHERE b.protocolType='READ' AND c.id=b.containerID ORDER BY b.experimentDate DESC; +--------------------------------------------+---------------------+--------+--------------------------------------------+-----------------+-----------------+-------+-----------+--------+---------+-------------------+---------------------------------+ | name | experimentDate | id | name | isGivenByClient | clientGivenName | type | nbColumns | nbRows | freezer | barcode | availableForFullPlateSubmission | +--------------------------------------------+---------------------+--------+--------------------------------------------+-----------------+-----------------+-------+-----------+--------+---------+-------------------+---------------------------------+ | test31.07.2017Seq | 2017-08-01 16:39:45 | 189444 | test31.07.2017Seq | 0 | | PLATE | 12 | 8 | | 1000019755 | | | test31.07.2017Seq | 2017-08-01 11:55:54 | 189444 | test31.07.2017Seq | 0 | | PLATE | 12 | 8 | | 1000019755 | | | P1714127 | 2017-06-02 09:22:22 | 189292 | P1714127 | 0 | | PLATE | 24 | 16 | | 1000019927 | | | 2017-May-26A-D-p384Seq | 2017-06-02 05:11:30 | 189260 | 2017-May-26A-D-p384Seq | 0 | | PLATE | 24 | 16 | | 1000019992 | | | 2017-May-26E-H-p384Seq | 2017-06-02 01:21:24 | 189261 | 2017-May-26E-H-p384Seq | 0 | | PLATE | 24 | 16 | | 1000019993 | | | P1714134 | 2017-06-01 23:13:12 | 189375 | P1714134 | 0 | | PLATE | 12 | 8 | | 1000019926 | | | P1714131 | 2017-06-01 21:15:15 | 189346 | P1714131 | 0 | | PLATE | 12 | 8 | | 1000019929 | | | P1714135 | 2017-06-01 17:41:13 | 189376 | P1714135 | 0 | | PLATE | 12 | 8 | | 1000019983 | | | P1714129 | 2017-06-01 15:43:13 | 189335 | P1714129 | 0 | | PLATE | 12 | 8 | | 1000019982 | | | JolicoeurSeq1juin2017Seq | 2017-06-01 13:43:26 | 189361 | JolicoeurSeq1juin2017Seq | 0 | | PLATE | 12 | 8 | | VAL12403252 | | | P1714124 | 2017-06-01 01:32:40 | 189262 | P1714124 | 0 | | PLATE | 24 | 16 | | 1000019987 | | | P1714126 | 2017-05-31 20:15:26 | 189269 | P1714126 | 0 | | PLATE | 12 | 8 | | 1000019931 | | | P1714116 | 2017-05-31 20:13:30 | 189161 | P1714116 | 0 | | PLATE | 12 | 8 | | 1000019928 | | | P1714117 | 2017-05-31 15:41:29 | 189164 | P1714117 | 0 | | PLATE | 12 | 8 | | 1000019991 | | | DSM-31mai2017Seq | 2017-05-31 13:05:28 | 189281 | DSM-31mai2017Seq | 0 | | PLATE | 12 | 8 | | VAL12403248 | | | P1714121 | 2017-05-31 02:22:39 | 189228 | P1714121 | 0 | | PLATE | 24 | 16 | | 1000019990 | | | P1714123 | 2017-05-30 22:45:25 | 189254 | P1714123 | 0 | | PLATE | 12 | 8 | | 1000019933 | | | P1714122 | 2017-05-30 20:47:51 | 189229 | P1714122 | 0 | | PLATE | 12 | 8 | | 1000019932 | | | P1714120 | 2017-05-30 20:46:33 | 189204 | P1714120 | 0 | | PLATE | 24 | 16 | | 1000019995 | | | JB-LOH2Seq | 2017-05-30 19:39:04 | 189276 | JB-LOH2Seq | 0 | | PLATE | 12 | 8 | | 1000019930 | | ...
[11 Sep 2017 21:58]
MySQL Verification Team
if I remove the , c.* from the query it is ok mysql [localhost] {msandbox} (fk_testing) > SELECT DISTINCT(c.name), b.experimentDate FROM container c , batch b WHERE b.protocolType='READ' AND c.id=b.containerID ORDER BY b.experimentDate DESC limit 10; +--------------------------+---------------------+ | name | experimentDate | +--------------------------+---------------------+ | test31.07.2017Seq | 2017-08-01 16:39:45 | | test31.07.2017Seq | 2017-08-01 11:55:54 | | P1714127 | 2017-06-02 09:22:22 | | 2017-May-26A-D-p384Seq | 2017-06-02 05:11:30 | | 2017-May-26E-H-p384Seq | 2017-06-02 01:21:24 | | P1714134 | 2017-06-01 23:13:12 | | P1714131 | 2017-06-01 21:15:15 | | P1714135 | 2017-06-01 17:41:13 | | P1714129 | 2017-06-01 15:43:13 | | JolicoeurSeq1juin2017Seq | 2017-06-01 13:43:26 | +--------------------------+---------------------+ if I add just few from c.xxx it works but when I add 4 for e.g. it fails again SELECT DISTINCT(c.name), b.experimentDate, c.id, c.name,c.isGivenByClient,c.clientGivenName FROM container c , batch b WHERE b.protocolType='READ' AND c.id=b.containerID ORDER BY b.experimentDate DESC limit 10; +------------------+---------------------+--------+------------------+-----------------+-----------------+ | name | experimentDate | id | name | isGivenByClient | clientGivenName | +------------------+---------------------+--------+------------------+-----------------+-----------------+ | 01Aug13-DRSeq | 2013-08-01 21:08:35 | 138986 | 01Aug13-DRSeq | 0 | | | 01Dec16-CSeq | 2016-12-02 13:13:41 | 183774 | 01Dec16-CSeq | 0 | | | 01Dec16-CSeq | 2016-12-02 19:37:37 | 183774 | 01Dec16-CSeq | 0 | | | 01Feb11ABCSeq | 2011-02-02 01:32:04 | 96437 | 01Feb11ABCSeq | 0 | | | 01Nov13-ACDRSeq | 2013-11-01 20:23:29 | 142706 | 01Nov13-ACDRSeq | 0 | | | 01Nov13-CSeq | 2013-11-05 20:33:30 | 142820 | 01Nov13-CSeq | 0 | | | 02Apr14-ABCSeq | 2014-04-04 20:01:54 | 148471 | 02Apr14-ABCSeq | 0 | | | 02Jul13-CSeq | 2013-07-04 20:25:12 | 137658 | 02Jul13-CSeq | 0 | | | 02Jun15-ABCSeq | 2015-06-03 17:54:23 | 165347 | 02Jun15-ABCSeq | 0 | | | 02May16-ABCDRSeq | 2016-05-02 00:17:50 | 176747 | 02May16-ABCDRSeq | 0 | | +------------------+---------------------+--------+------------------+-----------------+-----------------+ 10 rows in set (0.66 sec) mysql [localhost] {msandbox} (fk_testing) >
[24 Apr 2018 17:05]
KORBULY Francois
Hi, I'm just getting back to this, because since ticket is 'verified', I haven't heard anything else. Just wondering if this will be fixed in some way, and/or if it is just waiting to get into the development pipeline. Or nothing more will be done with it. This issue prevents us to go live with 5.7 on our PROD server. Thanks you in advance kindly for your answer. Best regards, Francois
[25 Apr 2018 0:21]
MySQL Verification Team
Hi, it's all about priorities.. the bug will surely be fixed, we just can't say when. it's in the queue so "waiting to get into development pipeline" as you'd say :) all best Bogdan p.s. we pushed 8 GA recently, have you tested it?
[25 Apr 2018 13:37]
KORBULY Francois
Hi Bogdan, Thanks a lot for your feedback. We will definitely look at MYSQL 8 soon. Best regards, Francois
[6 Sep 2022 8:01]
Tor Didriksen
Posted by developer: Fixed in 8.0 In 5.7 the workaround is to increase max_heap_table_size this worked for me for the test data provided: set @@session.max_heap_table_size = 1024 * 1024 * 1024; You can use optimizer trace to see execution steps: "join_execution": { "select#": 1, "steps": [ { "creating_tmp_table": { "tmp_table_info": { "table": "intermediate_tmp_table", "row_length": 570, "key_length": 573, "unique_constraint": false, "location": "memory (heap)", "row_limit_estimate": 1839 } } }, { "converting_tmp_table_to_ondisk": { "cause": "memory_table_size_exceeded", "tmp_table_info": { "table": "intermediate_tmp_table", "row_length": 570, "key_length": 573, "unique_constraint": false, "location": "disk (InnoDB)", "record_format": "fixed" } } } Evidently there was a bug when we spill heap tables to disk.