MySQL Bugs: #118544: A potential join operator bug

Bug #118544	A potential join operator bug
Submitted:	27 Jun 14:18	Modified:	30 Jun 6:41
Reporter:	策吕	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S3 (Non-critical)
Version:	9.2.0 , 9.3.0	OS:	Linux
Assigned to:		CPU Architecture:	Any

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);

The two executed queries are logically equivalent. The expected behavior is that both queries must return the same result. The INNER JOIN query correctly returns an empty set, so the INTERSECT ALL query is also expected to return an Empty set.
The inconsistency in results points to a critical bug in the server's handling of INTERSECT and OUTER JOIN operations.

We also tested MySQL on Windows with the same SQL code, but the results returned on Windows were all empty sets as well.
mysql> select version();
+-----------+
| version() |
+-----------+
| 8.0.36    |
+-----------+
1 row in set (0.00 sec)

mysql>
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);
Empty set (0.00 sec)

Hello 策 吕,

Thank you for the report and feedback.
Verified as described.

regards,
Umesh