Description:
Queries generated with logical partitioning (IN & NOT IN) and UNION operation may return incorrect results, inconsistent with their equivalent queries.
How to repeat:
docker run -it --name mysql-latest -p 3306:3306 -e MYSQL_ROOT_PASSWORD=123456 mysql:latest
DROP TABLE IF EXISTS t0,t1;
CREATE TABLE t0(c0 FLOAT PRIMARY KEY) ENGINE = HEAP;
CREATE TABLE IF NOT EXISTS t1 LIKE t0;
INSERT INTO t1(c0) VALUES("-0.0"),(0.0);
INSERT INTO t0(c0) VALUES(0);
-- For the above two tables, i expect the following semantically equivalent queries should return the same results:
SELECT DISTINCT * FROM t0 INNER JOIN t1 ON t0.c0 IN (t1.c0) OR t0.c0 NOT IN (t1.c0);
+----+----+
| c0 | c0 |
+----+----+
| 0 | -0 |
| 0 | 0 |
+----+----+
SELECT DISTINCT * FROM t0 INNER JOIN t1 ON t0.c0 IN (t1.c0)
UNION ALL
SELECT DISTINCT * FROM t0 INNER JOIN t1 ON t0.c0 NOT IN (t1.c0);
+----+----+
| c0 | c0 |
+----+----+
| 0 | 0 |
+----+----+
SELECT DISTINCT * FROM t0 INNER JOIN t1 ON t0.c0 IN (t1.c0)
UNION
SELECT DISTINCT * FROM t0 INNER JOIN t1 ON t0.c0 NOT IN (t1.c0);
+----+----+
| c0 | c0 |
+----+----+
| 0 | 0 |
+----+----+
SELECT DISTINCT * FROM t0 INNER JOIN t1;
+----+----+
| c0 | c0 |
+----+----+
| 0 | -0 |
| 0 | 0 |
+----+----+
However, the actual results are inconsistent. Maybe the UNION operation return wrong result.
Description: Queries generated with logical partitioning (IN & NOT IN) and UNION operation may return incorrect results, inconsistent with their equivalent queries. How to repeat: docker run -it --name mysql-latest -p 3306:3306 -e MYSQL_ROOT_PASSWORD=123456 mysql:latest DROP TABLE IF EXISTS t0,t1; CREATE TABLE t0(c0 FLOAT PRIMARY KEY) ENGINE = HEAP; CREATE TABLE IF NOT EXISTS t1 LIKE t0; INSERT INTO t1(c0) VALUES("-0.0"),(0.0); INSERT INTO t0(c0) VALUES(0); -- For the above two tables, i expect the following semantically equivalent queries should return the same results: SELECT DISTINCT * FROM t0 INNER JOIN t1 ON t0.c0 IN (t1.c0) OR t0.c0 NOT IN (t1.c0); +----+----+ | c0 | c0 | +----+----+ | 0 | -0 | | 0 | 0 | +----+----+ SELECT DISTINCT * FROM t0 INNER JOIN t1 ON t0.c0 IN (t1.c0) UNION ALL SELECT DISTINCT * FROM t0 INNER JOIN t1 ON t0.c0 NOT IN (t1.c0); +----+----+ | c0 | c0 | +----+----+ | 0 | 0 | +----+----+ SELECT DISTINCT * FROM t0 INNER JOIN t1 ON t0.c0 IN (t1.c0) UNION SELECT DISTINCT * FROM t0 INNER JOIN t1 ON t0.c0 NOT IN (t1.c0); +----+----+ | c0 | c0 | +----+----+ | 0 | 0 | +----+----+ SELECT DISTINCT * FROM t0 INNER JOIN t1; +----+----+ | c0 | c0 | +----+----+ | 0 | -0 | | 0 | 0 | +----+----+ However, the actual results are inconsistent. Maybe the UNION operation return wrong result.