Bug #113320 SELECT DISTINCT .. ORDER BY query yields wrong sort order
Submitted: 4 Dec 2023 9:00 Modified: 4 Dec 2023 11:13
Reporter: Seunguck Lee Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S2 (Serious)
Version:8.0 OS:Any
Assigned to: CPU Architecture:Any (DarwinMatt.local 22.5.0 Darwin Kernel Version 22.5.0: root:xnu-8796.121.2~5/RELEASE_ARM64_T6020 arm6)

[4 Dec 2023 9:00] Seunguck Lee
Description:
Following SELECT yields wrong (inconsistent) sort order result.

SELECT DISTINCT a.id
FROM tab3 a
INNER JOIN tab3 b ON b.id=a.id
ORDER BY a.id DESC 
LIMIT 99990, 10;
+--------+
| id     |
+--------+
|  99991 |
|  99992 |
|  99993 |
|  99994 |
|  99995 |
|  99996 |
|  99997 |
|  99998 |
|  99999 |
| 100000 |
+--------+
10 rows in set (1.97 sec)

This query generate correct result when "LIMIT 0, 10" is used, 
but for "LIMIT 99990, 10" sort order is incorrect.

And one more interesting thing is that this issue only happen when JOIN is employed.

How to repeat:
-- // Create & Insert sample table & data
CREATE TABLE tab3 (
  id bigint NOT NULL AUTO_INCREMENT,
  bid bigint NOT NULL,
  created_at datetime DEFAULT NULL,
  PRIMARY KEY (id)
);

INSERT INTO tab3 
SELECT seq.no, seq.no, now()
FROM JSON_TABLE(CONCAT('[1', REPEAT(',1', 100000 - 1), ']'), "$[*]" COLUMNS(no FOR ORDINALITY)) as seq;

-- Run test query
SELECT DISTINCT a.id
FROM tab3 a
INNER JOIN tab3 b ON b.id=a.id
ORDER BY a.id DESC LIMIT 99990, 10;
+--------+
| id     |
+--------+
|  99991 |
|  99992 |
|  99993 |
|  99994 |
|  99995 |
|  99996 |
|  99997 |
|  99998 |
|  99999 |
| 100000 |
+--------+
10 rows in set (1.97 sec)

Suggested fix:
Yield correct sort order
[4 Dec 2023 9:36] Seunguck Lee
Can avoid this issue when using bigger tmp_table_size configuration parameter.
[4 Dec 2023 11:13] MySQL Verification Team
Hi Mr. Lee,

Thank you for your bug report.

We have been able to repeat your test case. Even more, we wrote much simpler test case and got the wrong test results.

This is the test that we used:

-------------------------------
CREATE TABLE tab3 (  id bigint NOT NULL AUTO_INCREMENT,  bid bigint NOT NULL,  created_at datetime DEFAULT NULL,  PRIMARY KEY (id) );

INSERT INTO tab3 SELECT seq.no, seq.no, now() FROM JSON_TABLE(CONCAT('[1', REPEAT(',1', 100000 - 1), ']'), "$[*]" COLUMNS(no FOR ORDINALITY)) as seq;

SELECT id FROM tab3 ORDER BY id ASC  LIMIT 99990, 10;

SELECT id FROM tab3 ORDER BY id DESC LIMIT 99990, 10;

SELECT MAX(id)  FROM tab3;
-------------------------------

And got the following results:

----------------------------------

id
99991
99992
99993
99994
99995
99996
99997
99998
99999
100000
id
10
9
8
7
6
5
4
3
2
1
MAX(id)
100000
---------------------------------

We have repeated the problem in 8.0 and all higher versions.

Verified as reported.