Description:
The MySQL server returns inconsistent results for two queries that are logically equivalent according to relational algebra. An INNER JOIN query and a query constructed as (LEFT JOIN) INTERSECT ALL (RIGHT JOIN) should always yield the same result set. However, when using a constant FALSE join condition, one query correctly returns an empty set while the other incorrectly returns a non-empty set.
This demonstrates a fundamental bug in how the query execution engine handles set operations, as it violates logical consistency.
Output:
mysql> SELECT DISTINCTROW t0.c0 AS ref0, t1.c0 AS ref1 FROM t1 INNER JOIN t0 ON CAST((CAST(-321952131 AS SIGNED)) LIKE ( EXISTS (SELECT 1 wHERE FALSE)) AS SIGNED) GROUP BY t0.c0, t1.c0;
Empty set (0.00 sec)
mysql>
mysql> (SELECT DISTINCTROW t0.c0 AS ref0, t1.c0 AS ref1 FROM t1 LEFT JOIN t0 ON CAST((CAST(-321952131 AS SIGNED)) LIKE ( EXISTS (SELECT 1 wHERE FALSE)) AS SIGNED) GROUP BY t0.c0, t1.c0) INTERSECT ALL (SELECT DISTINCTROW t0.c0 AS ref0, t1.c0 AS ref1 FROM t1 RIGHT JOIN t0 ON CAST((CAST(-321952131 AS SIGNED)) LIKE ( EXISTS (SELECT 1 wHERE FALSE)) AS SIGNED) GROUP BY t0.c0, t1.c0);
+------+------------+
| ref0 | ref1 |
+------+------------+
| 0 | 1555806081 |
+------+------------+
1 row in set (0.00 sec)
mysql> (SELECT DISTINCTROW t0.c0 AS ref0, t1.c0 AS ref1 FROM t1 LEFT JOIN t0 ON CAST((CAST(-321952131 AS SIGNED)) LIKE ( EXISTS (SELECT 1 wHERE FALSE)) AS SIGNED) GROUP BY t0.c0, t1.c0) ;
+------+---------------------------------------------------------+
| ref0 | ref1 |
+------+---------------------------------------------------------+
| NULL | 0000000000000000000000000000000000000000000000000000001 |
| NULL | 0000000000000000000000000000000000000000000000000000000 |
| NULL | 0000000000000000000000000000000000000000000001326069676 |
| NULL | 0000000000000000000000000000000000000000000001555806081 |
+------+---------------------------------------------------------+
4 rows in set (0.00 sec)
mysql> (SELECT DISTINCTROW t0.c0 AS ref0, t1.c0 AS ref1 FROM t1 RIGHT JOIN t0 ON CAST((CAST(-321952131 AS SIGNED)) LIKE ( EXISTS (SELECT 1 wHERE FALSE)) AS SIGNED) GROUP BY t0.c0, t1.c0);
+---------------------------------------------------------+------+
| ref0 | ref1 |
+---------------------------------------------------------+------+
| 0000000000000000000000000000000000000000000001326069676 | NULL |
| 0000000000000000000000000000000000000000000000000000000 | NULL |
| 0000000000000000000000000000000000000000000001555806081 | NULL |
| 0000000000000000000000000000000000000000000001219342680 | NULL |
+---------------------------------------------------------+------+
4 rows in set (0.00 sec)
mysql> (SELECT DISTINCTROW t0.c0 AS ref0, t1.c0 AS ref1 FROM t1 LEFT JOIN t0 ON CAST((CAST(-321952131 AS SIGNED)) LIKE ( EXISTS (SELECT 1 wHERE FALSE)) AS SIGNED) GROUP BY t0.c0, t1.c0) INTERSECT ALL (SELECT DISTINCTROW t0.c0 AS ref0, t1.c0 AS ref1 FROM t1 RIGHT JOIN t0 ON CAST((CAST(-321952131 AS SIGNED)) LIKE ( EXISTS (SELECT 1 wHERE FALSE)) AS SIGNED) GROUP BY t0.c0, t1.c0);
+------+------------+
| ref0 | ref1 |
+------+------------+
| 0 | 1555806081 |
+------+------------+
1 row in set (0.00 sec)
How to repeat:
mysql> status;
--------------
mysql Ver 9.2.0 for Linux on x86_64 (MySQL Community Server - GPL)
Connection id: 1191
Current database:
Current user: root@localhost
SSL: Not in use
Current pager: stdout
Using outfile: ''
Using delimiter: ;
Server version: 9.2.0 MySQL Community Server - GPL
Protocol version: 10
Connection: Localhost via UNIX socket
Server characterset: utf8mb4
Db characterset: utf8mb4
Client characterset: latin1
Conn. characterset: latin1
UNIX socket: /var/run/mysqld/mysqld.sock
Binary data as: Hexadecimal
Uptime: 6 hours 53 min 21 sec
--------------------------------- SQLcode ---------------------------------
DROP DATABASE IF EXISTS database31;
CREATE DATABASE database31;
USE database31;
CREATE TABLE t0(c0 INT(55) ZEROFILL COMMENT 'asdf' STORAGE MEMORY COLUMN_FORMAT DYNAMIC NOT NULL) ;
CREATE TABLE IF NOT EXISTS t1 LIKE t0;
# CHECK TABLE t1, t0 EXTENDED FOR UPGRADE;
# SET SESSION optimizer_switch = 'mrr=on,semijoin=on,derived_merge=on';
# select TABLE_NAME, ENGINE from information_schema.TABLES where table_schema = 'database31';
TRUNCATE TABLE t1;
INSERT DELAYED INTO t1(c0) VALUES(0.9446300435855938);
select TABLE_NAME, ENGINE from information_schema.TABLES where table_schema = 'database31';
INSERT IGNORE INTO t1(c0) VALUES(0.20320249672485002);
REPLACE DELAYED INTO t0(c0) VALUES(1326069676);
# SET SESSION optimizer_switch = 'batched_key_access=on,skip_scan=off,derived_merge=off,subquery_materialization_cost_based=on,mrr=off,index_merge_union=on,index_merge_sort_union=on';
# SET SESSION unique_checks = ON;
INSERT IGNORE INTO t0(c0) VALUES('');
CHECKSUM TABLE t1;
# ANALYZE NO_WRITE_TO_BINLOG TABLE t0, t1;
# select TABLE_NAME, ENGINE from information_schema.TABLES where table_schema = 'database31';
REPLACE DELAYED INTO t1(c0) VALUES(1326069676);
# select TABLE_NAME, ENGINE from information_schema.TABLES where table_schema = 'database31';
#
# select TABLE_NAME, ENGINE from information_schema.TABLES where table_schema = 'database31';
# SET SESSION read_buffer_size = 1708111923;
# select TABLE_NAME, ENGINE from information_schema.TABLES where table_schema = 'database31';
INSERT IGNORE INTO t1(c0) VALUES(NULL);
INSERT DELAYED IGNORE INTO t0(c0) VALUES(-9.52672243E8);
# select TABLE_NAME, ENGINE from information_schema.TABLES where table_schema = 'database31';
INSERT IGNORE INTO t0(c0) VALUES(1555806081);
REPLACE DELAYED INTO t1(c0) VALUES(1555806081);
INSERT DELAYED IGNORE INTO t0(c0) VALUES(1219342680);
INSERT IGNORE INTO t0(c0) VALUES(NULL);
SELECT DISTINCTROW t0.c0 AS ref0, t1.c0 AS ref1 FROM t1 INNER JOIN t0 ON CAST((CAST(-321952131 AS SIGNED)) LIKE ( EXISTS (SELECT 1 wHERE FALSE)) AS SIGNED) GROUP BY t0.c0, t1.c0;
(SELECT DISTINCTROW t0.c0 AS ref0, t1.c0 AS ref1 FROM t1 LEFT JOIN t0 ON CAST((CAST(-321952131 AS SIGNED)) LIKE ( EXISTS (SELECT 1 wHERE FALSE)) AS SIGNED) GROUP BY t0.c0, t1.c0) INTERSECT ALL (SELECT DISTINCTROW t0.c0 AS ref0, t1.c0 AS ref1 FROM t1 RIGHT JOIN t0 ON CAST((CAST(-321952131 AS SIGNED)) LIKE ( EXISTS (SELECT 1 wHERE FALSE)) AS SIGNED) GROUP BY t0.c0, t1.c0);
Description: The MySQL server returns inconsistent results for two queries that are logically equivalent according to relational algebra. An INNER JOIN query and a query constructed as (LEFT JOIN) INTERSECT ALL (RIGHT JOIN) should always yield the same result set. However, when using a constant FALSE join condition, one query correctly returns an empty set while the other incorrectly returns a non-empty set. This demonstrates a fundamental bug in how the query execution engine handles set operations, as it violates logical consistency. Output: mysql> SELECT DISTINCTROW t0.c0 AS ref0, t1.c0 AS ref1 FROM t1 INNER JOIN t0 ON CAST((CAST(-321952131 AS SIGNED)) LIKE ( EXISTS (SELECT 1 wHERE FALSE)) AS SIGNED) GROUP BY t0.c0, t1.c0; Empty set (0.00 sec) mysql> mysql> (SELECT DISTINCTROW t0.c0 AS ref0, t1.c0 AS ref1 FROM t1 LEFT JOIN t0 ON CAST((CAST(-321952131 AS SIGNED)) LIKE ( EXISTS (SELECT 1 wHERE FALSE)) AS SIGNED) GROUP BY t0.c0, t1.c0) INTERSECT ALL (SELECT DISTINCTROW t0.c0 AS ref0, t1.c0 AS ref1 FROM t1 RIGHT JOIN t0 ON CAST((CAST(-321952131 AS SIGNED)) LIKE ( EXISTS (SELECT 1 wHERE FALSE)) AS SIGNED) GROUP BY t0.c0, t1.c0); +------+------------+ | ref0 | ref1 | +------+------------+ | 0 | 1555806081 | +------+------------+ 1 row in set (0.00 sec) mysql> (SELECT DISTINCTROW t0.c0 AS ref0, t1.c0 AS ref1 FROM t1 LEFT JOIN t0 ON CAST((CAST(-321952131 AS SIGNED)) LIKE ( EXISTS (SELECT 1 wHERE FALSE)) AS SIGNED) GROUP BY t0.c0, t1.c0) ; +------+---------------------------------------------------------+ | ref0 | ref1 | +------+---------------------------------------------------------+ | NULL | 0000000000000000000000000000000000000000000000000000001 | | NULL | 0000000000000000000000000000000000000000000000000000000 | | NULL | 0000000000000000000000000000000000000000000001326069676 | | NULL | 0000000000000000000000000000000000000000000001555806081 | +------+---------------------------------------------------------+ 4 rows in set (0.00 sec) mysql> (SELECT DISTINCTROW t0.c0 AS ref0, t1.c0 AS ref1 FROM t1 RIGHT JOIN t0 ON CAST((CAST(-321952131 AS SIGNED)) LIKE ( EXISTS (SELECT 1 wHERE FALSE)) AS SIGNED) GROUP BY t0.c0, t1.c0); +---------------------------------------------------------+------+ | ref0 | ref1 | +---------------------------------------------------------+------+ | 0000000000000000000000000000000000000000000001326069676 | NULL | | 0000000000000000000000000000000000000000000000000000000 | NULL | | 0000000000000000000000000000000000000000000001555806081 | NULL | | 0000000000000000000000000000000000000000000001219342680 | NULL | +---------------------------------------------------------+------+ 4 rows in set (0.00 sec) mysql> (SELECT DISTINCTROW t0.c0 AS ref0, t1.c0 AS ref1 FROM t1 LEFT JOIN t0 ON CAST((CAST(-321952131 AS SIGNED)) LIKE ( EXISTS (SELECT 1 wHERE FALSE)) AS SIGNED) GROUP BY t0.c0, t1.c0) INTERSECT ALL (SELECT DISTINCTROW t0.c0 AS ref0, t1.c0 AS ref1 FROM t1 RIGHT JOIN t0 ON CAST((CAST(-321952131 AS SIGNED)) LIKE ( EXISTS (SELECT 1 wHERE FALSE)) AS SIGNED) GROUP BY t0.c0, t1.c0); +------+------------+ | ref0 | ref1 | +------+------------+ | 0 | 1555806081 | +------+------------+ 1 row in set (0.00 sec) How to repeat: mysql> status; -------------- mysql Ver 9.2.0 for Linux on x86_64 (MySQL Community Server - GPL) Connection id: 1191 Current database: Current user: root@localhost SSL: Not in use Current pager: stdout Using outfile: '' Using delimiter: ; Server version: 9.2.0 MySQL Community Server - GPL Protocol version: 10 Connection: Localhost via UNIX socket Server characterset: utf8mb4 Db characterset: utf8mb4 Client characterset: latin1 Conn. characterset: latin1 UNIX socket: /var/run/mysqld/mysqld.sock Binary data as: Hexadecimal Uptime: 6 hours 53 min 21 sec --------------------------------- SQLcode --------------------------------- DROP DATABASE IF EXISTS database31; CREATE DATABASE database31; USE database31; CREATE TABLE t0(c0 INT(55) ZEROFILL COMMENT 'asdf' STORAGE MEMORY COLUMN_FORMAT DYNAMIC NOT NULL) ; CREATE TABLE IF NOT EXISTS t1 LIKE t0; # CHECK TABLE t1, t0 EXTENDED FOR UPGRADE; # SET SESSION optimizer_switch = 'mrr=on,semijoin=on,derived_merge=on'; # select TABLE_NAME, ENGINE from information_schema.TABLES where table_schema = 'database31'; TRUNCATE TABLE t1; INSERT DELAYED INTO t1(c0) VALUES(0.9446300435855938); select TABLE_NAME, ENGINE from information_schema.TABLES where table_schema = 'database31'; INSERT IGNORE INTO t1(c0) VALUES(0.20320249672485002); REPLACE DELAYED INTO t0(c0) VALUES(1326069676); # SET SESSION optimizer_switch = 'batched_key_access=on,skip_scan=off,derived_merge=off,subquery_materialization_cost_based=on,mrr=off,index_merge_union=on,index_merge_sort_union=on'; # SET SESSION unique_checks = ON; INSERT IGNORE INTO t0(c0) VALUES(''); CHECKSUM TABLE t1; # ANALYZE NO_WRITE_TO_BINLOG TABLE t0, t1; # select TABLE_NAME, ENGINE from information_schema.TABLES where table_schema = 'database31'; REPLACE DELAYED INTO t1(c0) VALUES(1326069676); # select TABLE_NAME, ENGINE from information_schema.TABLES where table_schema = 'database31'; # # select TABLE_NAME, ENGINE from information_schema.TABLES where table_schema = 'database31'; # SET SESSION read_buffer_size = 1708111923; # select TABLE_NAME, ENGINE from information_schema.TABLES where table_schema = 'database31'; INSERT IGNORE INTO t1(c0) VALUES(NULL); INSERT DELAYED IGNORE INTO t0(c0) VALUES(-9.52672243E8); # select TABLE_NAME, ENGINE from information_schema.TABLES where table_schema = 'database31'; INSERT IGNORE INTO t0(c0) VALUES(1555806081); REPLACE DELAYED INTO t1(c0) VALUES(1555806081); INSERT DELAYED IGNORE INTO t0(c0) VALUES(1219342680); INSERT IGNORE INTO t0(c0) VALUES(NULL); SELECT DISTINCTROW t0.c0 AS ref0, t1.c0 AS ref1 FROM t1 INNER JOIN t0 ON CAST((CAST(-321952131 AS SIGNED)) LIKE ( EXISTS (SELECT 1 wHERE FALSE)) AS SIGNED) GROUP BY t0.c0, t1.c0; (SELECT DISTINCTROW t0.c0 AS ref0, t1.c0 AS ref1 FROM t1 LEFT JOIN t0 ON CAST((CAST(-321952131 AS SIGNED)) LIKE ( EXISTS (SELECT 1 wHERE FALSE)) AS SIGNED) GROUP BY t0.c0, t1.c0) INTERSECT ALL (SELECT DISTINCTROW t0.c0 AS ref0, t1.c0 AS ref1 FROM t1 RIGHT JOIN t0 ON CAST((CAST(-321952131 AS SIGNED)) LIKE ( EXISTS (SELECT 1 wHERE FALSE)) AS SIGNED) GROUP BY t0.c0, t1.c0);