Description:
CREATE TABLE IF NOT EXISTS t0(c0 FLOAT ZEROFILL) ENGINE = HEAP;
CREATE TABLE t1(c0 FLOAT);
CREATE INDEX i0 USING BTREE ON t0(c0);
DROP INDEX c0 ON t1 LOCK=NONE;
INSERT INTO t1(c0) VALUES('-1');
INSERT DELAYED INTO t1(c0) VALUES(1);
REPLACE INTO t0(c0) VALUES(2);
INSERT INTO t0(c0) VALUES(3);
SELECT ALL t0.c0 FROM t0 WHERE NOT EXISTS (SELECT 1 FROM t1 WHERE t0.c0 > t1.c0);
Empty set
SELECT ALL t0.c0 FROM t0 WHERE EXISTS (SELECT 1 FROM t1 WHERE t0.c0 > t1.c0);
+--------------+
| c0 |
+--------------+
| 000000000002 |
| 000000000003 |
| 000000000002 |
| 000000000003 |
+--------------+
SELECT ALL t0.c0 FROM t0;
+--------------+
| c0 |
+--------------+
| 000000000002 |
| 000000000003 |
+--------------+
The output of the semi-join contains duplicates (two copies of the same record), indicating that the semi-join operation produced incorrect results.
How to repeat:
docker run -it -p 3306:3306 -e MYSQL_ROOT_PASSWORD=root mysql:9.1.0
Then execute the above test case.
Description: CREATE TABLE IF NOT EXISTS t0(c0 FLOAT ZEROFILL) ENGINE = HEAP; CREATE TABLE t1(c0 FLOAT); CREATE INDEX i0 USING BTREE ON t0(c0); DROP INDEX c0 ON t1 LOCK=NONE; INSERT INTO t1(c0) VALUES('-1'); INSERT DELAYED INTO t1(c0) VALUES(1); REPLACE INTO t0(c0) VALUES(2); INSERT INTO t0(c0) VALUES(3); SELECT ALL t0.c0 FROM t0 WHERE NOT EXISTS (SELECT 1 FROM t1 WHERE t0.c0 > t1.c0); Empty set SELECT ALL t0.c0 FROM t0 WHERE EXISTS (SELECT 1 FROM t1 WHERE t0.c0 > t1.c0); +--------------+ | c0 | +--------------+ | 000000000002 | | 000000000003 | | 000000000002 | | 000000000003 | +--------------+ SELECT ALL t0.c0 FROM t0; +--------------+ | c0 | +--------------+ | 000000000002 | | 000000000003 | +--------------+ The output of the semi-join contains duplicates (two copies of the same record), indicating that the semi-join operation produced incorrect results. How to repeat: docker run -it -p 3306:3306 -e MYSQL_ROOT_PASSWORD=root mysql:9.1.0 Then execute the above test case.