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