Description:
A query that uses a combination of set operators to compute a logical intersection yields an inconsistent result set compared to a simple and logically equivalent INNER JOIN query.
How to repeat:
DROP DATABASE IF EXISTS database12;
CREATE DATABASE database12;
USE database12;
CREATE TABLE IF NOT EXISTS t0(c0 FLOAT NOT NULL, c1 DOUBLE COMMENT 'asdf' NOT NULL, c2 FLOAT ZEROFILL NOT NULL) ;
CREATE TABLE t1(c0 FLOAT NOT NULL) DELAY_KEY_WRITE = 0, AVG_ROW_LENGTH = 3354068541;
DELETE FROM t1 WHERE "";
INSERT IGNORE INTO t0(c0, c2) VALUES(0.7458289479989914, NULL);
INSERT IGNORE INTO t0(c1, c0, c2) VALUES(NULL, "0.7458289479989914", "[3L<%䏌27");
REPLACE INTO t1(c0) VALUES(0.7458289479989914);
Query results against 8.4.6:
----------------------------
ysql [localhost:8407] {msandbox} (database12) > (SELECT DISTINCT t1.c0 AS ref0 FROM t1 LEFT JOIN t0 ON ((CAST(t0.c0 AS SIGNED)) >= ((CASE t0.c0 WHEN NULL THEN NULL ELSE 0.7572921407216231 END))) > ( EXISTS (SELECT 1 wHERE FALSE)) WHERE GREATEST((1244366786) NOT IN (t0.c0, 0.6802236352500778, t1.c0), CAST(t0.c1 AS SIGNED)) GROUP BY t1.c0 UNION SELECT DISTINCT t1.c0 AS ref0 FROM t1 RIGHT JOIN t0 ON ((CAST(t0.c0 AS SIGNED)) >= ((CASE t0.c0 WHEN NULL THEN NULL ELSE 0.7572921407216231 END))) > ( EXISTS (SELECT 1 wHERE FALSE)) WHERE GREATEST((1244366786) NOT IN (t0.c0, 0.6802236352500778, t1.c0), CAST(t0.c1 AS SIGNED)) GROUP BY t1.c0) EXCEPT ((SELECT DISTINCT t1.c0 AS ref0 FROM t1 LEFT JOIN t0 ON ((CAST(t0.c0 AS SIGNED)) >= ((CASE t0.c0 WHEN NULL THEN NULL ELSE 0.7572921407216231 END))) > ( EXISTS (SELECT 1 wHERE FALSE)) WHERE GREATEST((1244366786) NOT IN (t0.c0, 0.6802236352500778, t1.c0), CAST(t0.c1 AS SIGNED)) GROUP BY t1.c0 EXCEPT SELECT DISTINCT t1.c0 AS ref0 FROM t1 RIGHT JOIN t0 ON ((CAST(t0.c0 AS SIGNED)) >= ((CASE t0.c0 WHEN NULL THEN NULL ELSE 0.7572921407216231 END))) > ( EXISTS (SELECT 1 wHERE FALSE)) WHERE GREATEST((1244366786) NOT IN (t0.c0, 0.6802236352500778, t1.c0), CAST(t0.c1 AS SIGNED)) GROUP BY t1.c0) UNION (SELECT DISTINCT t1.c0 AS ref0 FROM t1 RIGHT JOIN t0 ON ((CAST(t0.c0 AS SIGNED)) >= ((CASE t0.c0 WHEN NULL THEN NULL ELSE 0.7572921407216231 END))) > ( EXISTS (SELECT 1 wHERE FALSE)) WHERE GREATEST((1244366786) NOT IN (t0.c0, 0.6802236352500778, t1.c0), CAST(t0.c1 AS SIGNED)) GROUP BY t1.c0 EXCEPT SELECT DISTINCT t1.c0 AS ref0 FROM t1 LEFT JOIN t0 ON ((CAST(t0.c0 AS SIGNED)) >= ((CASE t0.c0 WHEN NULL THEN NULL ELSE 0.7572921407216231 END))) > ( EXISTS (SELECT 1 wHERE FALSE)) WHERE GREATEST((1244366786) NOT IN (t0.c0, 0.6802236352500778, t1.c0), CAST(t0.c1 AS SIGNED)) GROUP BY t1.c0));
+-------------+
| ref0 |
+-------------+
| 0.745829 |
| 710528000 |
| -1032030000 |
| 0.97447 |
| 1776820000 |
| 961388000 |
| -101773000 |
| 0.820946 |
| 1244370000 |
| 0.843708 |
| 0.530019 |
| -675523000 |
| -252511000 |
| -1325650000 |
+-------------+
14 rows in set (0.01 sec)
Query results from MySQL 8.0.43:
mysql [localhost:8045] {msandbox} (database12) > (SELECT DISTINCT t1.c0 AS ref0 FROM t1 LEFT JOIN t0 ON ((CAST(t0.c0 AS SIGNED)) >= ((CASE t0.c0 WHEN NULL THEN NULL ELSE 0.7572921407216231 END))) > ( EXISTS (SELECT 1 wHERE FALSE)) WHERE GREATEST((1244366786) NOT IN (t0.c0, 0.6802236352500778, t1.c0), CAST(t0.c1 AS SIGNED)) GROUP BY t1.c0 UNION SELECT DISTINCT t1.c0 AS ref0 FROM t1 RIGHT JOIN t0 ON ((CAST(t0.c0 AS SIGNED)) >= ((CASE t0.c0 WHEN NULL THEN NULL ELSE 0.7572921407216231 END))) > ( EXISTS (SELECT 1 wHERE FALSE)) WHERE GREATEST((1244366786) NOT IN (t0.c0, 0.6802236352500778, t1.c0), CAST(t0.c1 AS SIGNED)) GROUP BY t1.c0) EXCEPT ((SELECT DISTINCT t1.c0 AS ref0 FROM t1 LEFT JOIN t0 ON ((CAST(t0.c0 AS SIGNED)) >= ((CASE t0.c0 WHEN NULL THEN NULL ELSE 0.7572921407216231 END))) > ( EXISTS (SELECT 1 wHERE FALSE)) WHERE GREATEST((1244366786) NOT IN (t0.c0, 0.6802236352500778, t1.c0), CAST(t0.c1 AS SIGNED)) GROUP BY t1.c0 EXCEPT SELECT DISTINCT t1.c0 AS ref0 FROM t1 RIGHT JOIN t0 ON ((CAST(t0.c0 AS SIGNED)) >= ((CASE t0.c0 WHEN NULL THEN NULL ELSE 0.7572921407216231 END))) > ( EXISTS (SELECT 1 wHERE FALSE)) WHERE GREATEST((1244366786) NOT IN (t0.c0, 0.6802236352500778, t1.c0), CAST(t0.c1 AS SIGNED)) GROUP BY t1.c0) UNION (SELECT DISTINCT t1.c0 AS ref0 FROM t1 RIGHT JOIN t0 ON ((CAST(t0.c0 AS SIGNED)) >= ((CASE t0.c0 WHEN NULL THEN NULL ELSE 0.7572921407216231 END))) > ( EXISTS (SELECT 1 wHERE FALSE)) WHERE GREATEST((1244366786) NOT IN (t0.c0, 0.6802236352500778, t1.c0), CAST(t0.c1 AS SIGNED)) GROUP BY t1.c0 EXCEPT SELECT DISTINCT t1.c0 AS ref0 FROM t1 LEFT JOIN t0 ON ((CAST(t0.c0 AS SIGNED)) >= ((CASE t0.c0 WHEN NULL THEN NULL ELSE 0.7572921407216231 END))) > ( EXISTS (SELECT 1 wHERE FALSE)) WHERE GREATEST((1244366786) NOT IN (t0.c0, 0.6802236352500778, t1.c0), CAST(t0.c1 AS SIGNED)) GROUP BY t1.c0));
+-------------+
| ref0 |
+-------------+
| 0.745829 |
| 710528000 |
| 0 |
| -1032030000 |
| 0.97447 |
| 1776820000 |
| 961388000 |
| -101773000 |
| 0.820946 |
| -0 |
| 1244370000 |
| 0.843708 |
| 0.530019 |
| -675523000 |
| -252511000 |
| -1325650000 |
+-------------+
16 rows in set (0.00 sec)
The results clearly show '0' & '-0'is missing
REPLACE INTO t1(c0) VALUES(7.10528103E8);
INSERT IGNORE INTO t1(c0) VALUES(NULL);
INSERT IGNORE INTO t0(c2, c1, c0) VALUES(681012499, -1.032025305E9, NULL);
INSERT IGNORE INTO t0(c1, c2) VALUES(NULL, 'OFOᆭSe⩅');
REPLACE INTO t1(c0) VALUES(-1032025305);
REPLACE INTO t1(c0) VALUES(0.9744703957441182);
UPDATE t0 SET c1=-1325651387;
REPLACE INTO t1(c0) VALUES(1776815991);
INSERT INTO t1(c0) VALUES(-1032025305);
REPLACE INTO t1(c0) VALUES(961387551);
INSERT INTO t1(c0) VALUES(-1.01772883E8);
REPLACE INTO t0(c1, c0, c2) VALUES(0.7458289479989914, -1032025305, 0.5751652403379044);
INSERT INTO t1(c0) VALUES(0.8209458724081099);
REPLACE INTO t1(c0) VALUES("-0.0");
REPLACE INTO t1(c0) VALUES(1244366786);
REPLACE INTO t1(c0) VALUES(0.8437080345332381);
INSERT IGNORE INTO t1(c0) VALUES(1244366786), (0.5300191996031577), ("710528103");
REPLACE INTO t1(c0) VALUES(-675523347);
REPLACE INTO t1(c0) VALUES(-252510652);
INSERT IGNORE INTO t0(c0, c2) VALUES(0.656037835378751, "-252510652");
REPLACE INTO t1(c0) VALUES(-1.325651387E9);
Description: A query that uses a combination of set operators to compute a logical intersection yields an inconsistent result set compared to a simple and logically equivalent INNER JOIN query. How to repeat: DROP DATABASE IF EXISTS database12; CREATE DATABASE database12; USE database12; CREATE TABLE IF NOT EXISTS t0(c0 FLOAT NOT NULL, c1 DOUBLE COMMENT 'asdf' NOT NULL, c2 FLOAT ZEROFILL NOT NULL) ; CREATE TABLE t1(c0 FLOAT NOT NULL) DELAY_KEY_WRITE = 0, AVG_ROW_LENGTH = 3354068541; DELETE FROM t1 WHERE ""; INSERT IGNORE INTO t0(c0, c2) VALUES(0.7458289479989914, NULL); INSERT IGNORE INTO t0(c1, c0, c2) VALUES(NULL, "0.7458289479989914", "[3L<%䏌27"); REPLACE INTO t1(c0) VALUES(0.7458289479989914); Query results against 8.4.6: ---------------------------- ysql [localhost:8407] {msandbox} (database12) > (SELECT DISTINCT t1.c0 AS ref0 FROM t1 LEFT JOIN t0 ON ((CAST(t0.c0 AS SIGNED)) >= ((CASE t0.c0 WHEN NULL THEN NULL ELSE 0.7572921407216231 END))) > ( EXISTS (SELECT 1 wHERE FALSE)) WHERE GREATEST((1244366786) NOT IN (t0.c0, 0.6802236352500778, t1.c0), CAST(t0.c1 AS SIGNED)) GROUP BY t1.c0 UNION SELECT DISTINCT t1.c0 AS ref0 FROM t1 RIGHT JOIN t0 ON ((CAST(t0.c0 AS SIGNED)) >= ((CASE t0.c0 WHEN NULL THEN NULL ELSE 0.7572921407216231 END))) > ( EXISTS (SELECT 1 wHERE FALSE)) WHERE GREATEST((1244366786) NOT IN (t0.c0, 0.6802236352500778, t1.c0), CAST(t0.c1 AS SIGNED)) GROUP BY t1.c0) EXCEPT ((SELECT DISTINCT t1.c0 AS ref0 FROM t1 LEFT JOIN t0 ON ((CAST(t0.c0 AS SIGNED)) >= ((CASE t0.c0 WHEN NULL THEN NULL ELSE 0.7572921407216231 END))) > ( EXISTS (SELECT 1 wHERE FALSE)) WHERE GREATEST((1244366786) NOT IN (t0.c0, 0.6802236352500778, t1.c0), CAST(t0.c1 AS SIGNED)) GROUP BY t1.c0 EXCEPT SELECT DISTINCT t1.c0 AS ref0 FROM t1 RIGHT JOIN t0 ON ((CAST(t0.c0 AS SIGNED)) >= ((CASE t0.c0 WHEN NULL THEN NULL ELSE 0.7572921407216231 END))) > ( EXISTS (SELECT 1 wHERE FALSE)) WHERE GREATEST((1244366786) NOT IN (t0.c0, 0.6802236352500778, t1.c0), CAST(t0.c1 AS SIGNED)) GROUP BY t1.c0) UNION (SELECT DISTINCT t1.c0 AS ref0 FROM t1 RIGHT JOIN t0 ON ((CAST(t0.c0 AS SIGNED)) >= ((CASE t0.c0 WHEN NULL THEN NULL ELSE 0.7572921407216231 END))) > ( EXISTS (SELECT 1 wHERE FALSE)) WHERE GREATEST((1244366786) NOT IN (t0.c0, 0.6802236352500778, t1.c0), CAST(t0.c1 AS SIGNED)) GROUP BY t1.c0 EXCEPT SELECT DISTINCT t1.c0 AS ref0 FROM t1 LEFT JOIN t0 ON ((CAST(t0.c0 AS SIGNED)) >= ((CASE t0.c0 WHEN NULL THEN NULL ELSE 0.7572921407216231 END))) > ( EXISTS (SELECT 1 wHERE FALSE)) WHERE GREATEST((1244366786) NOT IN (t0.c0, 0.6802236352500778, t1.c0), CAST(t0.c1 AS SIGNED)) GROUP BY t1.c0)); +-------------+ | ref0 | +-------------+ | 0.745829 | | 710528000 | | -1032030000 | | 0.97447 | | 1776820000 | | 961388000 | | -101773000 | | 0.820946 | | 1244370000 | | 0.843708 | | 0.530019 | | -675523000 | | -252511000 | | -1325650000 | +-------------+ 14 rows in set (0.01 sec) Query results from MySQL 8.0.43: mysql [localhost:8045] {msandbox} (database12) > (SELECT DISTINCT t1.c0 AS ref0 FROM t1 LEFT JOIN t0 ON ((CAST(t0.c0 AS SIGNED)) >= ((CASE t0.c0 WHEN NULL THEN NULL ELSE 0.7572921407216231 END))) > ( EXISTS (SELECT 1 wHERE FALSE)) WHERE GREATEST((1244366786) NOT IN (t0.c0, 0.6802236352500778, t1.c0), CAST(t0.c1 AS SIGNED)) GROUP BY t1.c0 UNION SELECT DISTINCT t1.c0 AS ref0 FROM t1 RIGHT JOIN t0 ON ((CAST(t0.c0 AS SIGNED)) >= ((CASE t0.c0 WHEN NULL THEN NULL ELSE 0.7572921407216231 END))) > ( EXISTS (SELECT 1 wHERE FALSE)) WHERE GREATEST((1244366786) NOT IN (t0.c0, 0.6802236352500778, t1.c0), CAST(t0.c1 AS SIGNED)) GROUP BY t1.c0) EXCEPT ((SELECT DISTINCT t1.c0 AS ref0 FROM t1 LEFT JOIN t0 ON ((CAST(t0.c0 AS SIGNED)) >= ((CASE t0.c0 WHEN NULL THEN NULL ELSE 0.7572921407216231 END))) > ( EXISTS (SELECT 1 wHERE FALSE)) WHERE GREATEST((1244366786) NOT IN (t0.c0, 0.6802236352500778, t1.c0), CAST(t0.c1 AS SIGNED)) GROUP BY t1.c0 EXCEPT SELECT DISTINCT t1.c0 AS ref0 FROM t1 RIGHT JOIN t0 ON ((CAST(t0.c0 AS SIGNED)) >= ((CASE t0.c0 WHEN NULL THEN NULL ELSE 0.7572921407216231 END))) > ( EXISTS (SELECT 1 wHERE FALSE)) WHERE GREATEST((1244366786) NOT IN (t0.c0, 0.6802236352500778, t1.c0), CAST(t0.c1 AS SIGNED)) GROUP BY t1.c0) UNION (SELECT DISTINCT t1.c0 AS ref0 FROM t1 RIGHT JOIN t0 ON ((CAST(t0.c0 AS SIGNED)) >= ((CASE t0.c0 WHEN NULL THEN NULL ELSE 0.7572921407216231 END))) > ( EXISTS (SELECT 1 wHERE FALSE)) WHERE GREATEST((1244366786) NOT IN (t0.c0, 0.6802236352500778, t1.c0), CAST(t0.c1 AS SIGNED)) GROUP BY t1.c0 EXCEPT SELECT DISTINCT t1.c0 AS ref0 FROM t1 LEFT JOIN t0 ON ((CAST(t0.c0 AS SIGNED)) >= ((CASE t0.c0 WHEN NULL THEN NULL ELSE 0.7572921407216231 END))) > ( EXISTS (SELECT 1 wHERE FALSE)) WHERE GREATEST((1244366786) NOT IN (t0.c0, 0.6802236352500778, t1.c0), CAST(t0.c1 AS SIGNED)) GROUP BY t1.c0)); +-------------+ | ref0 | +-------------+ | 0.745829 | | 710528000 | | 0 | | -1032030000 | | 0.97447 | | 1776820000 | | 961388000 | | -101773000 | | 0.820946 | | -0 | | 1244370000 | | 0.843708 | | 0.530019 | | -675523000 | | -252511000 | | -1325650000 | +-------------+ 16 rows in set (0.00 sec) The results clearly show '0' & '-0'is missing REPLACE INTO t1(c0) VALUES(7.10528103E8); INSERT IGNORE INTO t1(c0) VALUES(NULL); INSERT IGNORE INTO t0(c2, c1, c0) VALUES(681012499, -1.032025305E9, NULL); INSERT IGNORE INTO t0(c1, c2) VALUES(NULL, 'OFOᆭSe⩅'); REPLACE INTO t1(c0) VALUES(-1032025305); REPLACE INTO t1(c0) VALUES(0.9744703957441182); UPDATE t0 SET c1=-1325651387; REPLACE INTO t1(c0) VALUES(1776815991); INSERT INTO t1(c0) VALUES(-1032025305); REPLACE INTO t1(c0) VALUES(961387551); INSERT INTO t1(c0) VALUES(-1.01772883E8); REPLACE INTO t0(c1, c0, c2) VALUES(0.7458289479989914, -1032025305, 0.5751652403379044); INSERT INTO t1(c0) VALUES(0.8209458724081099); REPLACE INTO t1(c0) VALUES("-0.0"); REPLACE INTO t1(c0) VALUES(1244366786); REPLACE INTO t1(c0) VALUES(0.8437080345332381); INSERT IGNORE INTO t1(c0) VALUES(1244366786), (0.5300191996031577), ("710528103"); REPLACE INTO t1(c0) VALUES(-675523347); REPLACE INTO t1(c0) VALUES(-252510652); INSERT IGNORE INTO t0(c0, c2) VALUES(0.656037835378751, "-252510652"); REPLACE INTO t1(c0) VALUES(-1.325651387E9);