Bug #112269 Unexpected Results by the hint JOIN_ORDER

• Submitted: 6 Sep 2023 2:51
• Modified: 6 Sep 2023 4:51
• Reporter: JINSHENG BA
• Status: Verified
• Category: MySQL Server: Optimizer
• Severity: S2 (Serious)
• Version: 8.1.0, 8.0.34
• OS: Any
• CPU Architecture: Any

[6 Sep 2023 2:51] JINSHENG BA

Description:
CREATE TABLE t0(c0 DECIMAL UNIQUE);
CREATE TABLE t2(c0 FLOAT, c1 TEXT);
CREATE INDEX i0 ON t2(c1(1));

INSERT INTO t0 VALUES(1);
INSERT INTO t2 VALUES(NULL, '1j');

SELECT * FROM t0, t2 WHERE t2.c1 = t0.c0;  -- {1|NULL|1j}
SELECT /*+ JOIN_ORDER(t2, t0)*/ * FROM t0, t2 WHERE t2.c1 = t0.c0; -- Empty set

Both equivalent queries return different results.

How to repeat:
docker run -it -p 3306:3306 -e MYSQL_ROOT_PASSWORD=root mysql:8.1.0

Then execute the above test case.

[6 Sep 2023 4:51] MySQL Verification Team

Hello Jinsheng Ba,

Thank you for the report and test case.
Verified as described.

regards,
Umesh