MySQL Bugs: #117648: Equivalent straight-join queries with different join orders return different results.

Bug #117648	Equivalent straight-join queries with different join orders return different results.
Submitted:	9 Mar 14:15	Modified:	10 Mar 7:17
Reporter:	jinhui lai	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S2 (Serious)
Version:	9.2.0, 9.1.0, 8.0.41, 8.4.4,	OS:	Ubuntu (22.04)
Assigned to:		CPU Architecture:	Any

Description:
Equivalent two straight-join queries with different join orders return different results. 

How to repeat:
docker run -it --name mysql-9.2.0 -p 3306:3306 -e MYSQL_ROOT_PASSWORD=root mysql:9.2.0
docker exec -it mysql-9.2.0 mysql -uroot -p
create database test;
use test;

CREATE TABLE t0(c0 FLOAT ZEROFILL ) ENGINE = HEAP;
CREATE TABLE IF NOT EXISTS t1 LIKE t0;
INSERT INTO t0(c0) VALUES(0),(-0);
CREATE INDEX i1 ON t0(c0);
INSERT INTO t1(c0) VALUES('-0');

SELECT ALL t0.c0  FROM t0 STRAIGHT_JOIN t1 ON t0.c0 = t1.c0; 
+--------------+
| c0           |
+--------------+
| 000000000000 |
| 000000000000 |
+--------------+
SELECT ALL t0.c0  FROM t1 STRAIGHT_JOIN t0 ON t0.c0 = t1.c0;
Empty set (0.00 sec)

Hello jinhui lai,

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

regards,
Umesh