Bug #119360 Result inconsistency due to sorting in temporary tables

• Submitted: 11 Nov 8:42
• Reporter: Go Yakult
• Status: Open
• Category: MySQL Server: Optimizer
• Severity: S3 (Non-critical)
• Version: 8.0.41
• OS: Any
• CPU Architecture: Any

[11 Nov 8:42] Go Yakult

Description:
Using FORCE INDEX caused incorrect results.

SELECT DISTINCT t0.c9 AS ref0, t0.c5 AS ref1 FROM t0 force index(i3) GROUP BY t0.c9, t0.c5 order by 1, 2;

SELECT DISTINCT t0.c9 AS ref0, t0.c5 AS ref1 FROM t0 GROUP BY t0.c9, t0.c5 order by 1, 2;

How to repeat:
source test2.sql

SELECT DISTINCT t0.c9 AS ref0, t0.c5 AS ref1 FROM t0 force index(i3) GROUP BY t0.c9, t0.c5 order by 1, 2;

SELECT DISTINCT t0.c9 AS ref0, t0.c5 AS ref1 FROM t0 GROUP BY t0.c9, t0.c5 order by 1, 2;