MySQL Bugs: #113135: DISTINCT JOIN ORDER BY statements ignore ORDER BY when tmp_table

Bug #113135	DISTINCT JOIN ORDER BY statements ignore ORDER BY when tmp_table_size is small
Submitted:	20 Nov 2023 3:03	Modified:	20 Nov 2023 13:03
Reporter:	Xizhe Zhang (OCA)	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: DML	Severity:	S3 (Non-critical)
Version:	8.0	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	distinct, join, order by, tmp_table_size

Description:
We found that there is a bug in MySQL 5.7.43 and 8.0.34. When tmp_table_size is small, the ORDER BY in the "SELECT DISTINCT ... JOIN ... ORDER BY" statement will become invalid. However, when the tmp_table_size is increased, the ORDER BY will take effect.

How to repeat:
Below is the test case I constructed:

SET tmp_table_size = 1024; # Minimum value that can be set

CREATE TABLE t1 (id INT AUTO_INCREMENT PRIMARY KEY);
CREATE TABLE t2 (id INT AUTO_INCREMENT PRIMARY KEY);

DELIMITER |;
CREATE PROCEDURE foo(num int) # Insert num lines into table t1 and t2
BEGIN
    DECLARE i int;
    SET i=1;
    WHILE(i<=num)DO
        INSERT INTO t1 () VALUES();
        INSERT INTO t2 () VALUES();
        SET i=i+1;
    END WHILE;
END|
DELIMITER ;|

CALL foo(221); # In MySQL 5.7.43, 221 is the critical point, below it ORDER BY will take effect

SELECT DISTINCT t1.id FROM t1 LEFT JOIN t2 ON t1.id = t2.id ORDER BY t1.id DESC LIMIT 10;

The result that should be returned is:
id
221
220
219
218
217
216
215
214
213
212

However, 5.7.43 returns:
id
1
2
3
4
5
6
7
8
9
10

8.0.34 returns:
id
212
213
214
215
216
217
218
219
220
221

But if you set tmp_table_size large enough, everything goes back to normal.

Hi Mr Zhang,

Thank you for your bug report.

Since 5.7 is no longer maintained, we tested your test case only on 8.0 and higher.

We repeated your test results:

id
212
213
214
215
216
217
218
219
220
221

It turns out that tmp_table_size minimum should be significantly increased. Your query worked only with 2 Mb or higher values, hence we think that minimum should be 16M.

Verified as reported.