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:
None 
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
Description:
Since upgrade to 5.7, from 5.6.33, on our QC environment to validate possibility to have it on our PROD servers, we came upon this behavior regarding sorting on a datetime column with a LIMIT <row_count>.

The resultset is not ordered at all by the datetime column. Even worse, dates are all mixed up.
If we remove the LIMIT <row_count>, behavior is as expected, rows are ordered by date, but then we get the whole dataset.

On 5.6.33, everything is fine, we get ordered 50 rows by date on the same dataset.

Schemas between servers are the same.

The 'datetime' column (experimentDate) is a index on the table containing it.

Query : 

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 0,50;

I provided a test database, 'fk_testing' in file.

How to repeat:
- Unzip 'fk_testing_db.zip'
- Execute the script on a test MySQL 5.7 server
- Execute the script on a test MySQL 5.6 server
- Run the query on both servers, with and without the 'LIMIT' to see the problem

On 5.6, with or without LIMIT, rows are ordered by date descending as expected.
On 5.7, with LIMIT, no order at all, mix of rows on the datetime column.
On 5.7, without LIMIT, rows are ordered by date descending as expected.
[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.