MySQL Bugs: #118857: Bug Summary: MySQL Returns Incorrect Results for Complex Query with Set Operations

Bug #118857	Bug Summary: MySQL Returns Incorrect Results for Complex Query with Set Operations
Submitted:	20 Aug 8:39	Modified:	20 Aug 10:19
Reporter:	策吕	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S2 (Serious)
Version:	9.2.0	OS:	Linux
Assigned to:		CPU Architecture:	Any

Description:
A bug has been identified in MySQL where two logically equivalent SQL queries produce inconsistent results. A complex query designed to find the intersection of two joins incorrectly returns an empty set (1 rows), while its simpler, equivalent INNER JOIN counterpart correctly returns the expected data (13 rows). This points to a flaw in the query optimizer or execution engine.

We also tested the same SQL script in MariaDB, TiDB, and OceanBase, and we found that for both SELECT query statements returned 0 rows.

** In MySQL **
mysql> (SELECT DISTINCT t1.c0 AS ref0, t0.c0 AS ref1 FROM t0 LEFT JOIN t1 ON (t1.c0) IN (IFNULL('0.3792086453751513', "qR\r")) UNION SELECT DISTINCT t1.c0 AS ref0, t0.c0 AS ref1 FROM t0 RIGHT JOIN t1 ON (t1.c0) IN (IFNULL('0.3792086453751513', "qR\r"))) EXCEPT ((SELECT DISTINCT t1.c0 AS ref0, t0.c0 AS ref1 FROM t0 LEFT JOIN t1 ON (t1.c0) IN (IFNULL('0.3792086453751513', "qR\r")) EXCEPT SELECT DISTINCT t1.c0 AS ref0, t0.c0 AS ref1 FROM t0 RIGHT JOIN t1 ON (t1.c0) IN (IFNULL('0.3792086453751513', "qR\r"))) UNION (SELECT DISTINCT t1.c0 AS ref0, t0.c0 AS ref1 FROM t0 RIGHT JOIN t1 ON (t1.c0) IN (IFNULL('0.3792086453751513', "qR\r")) EXCEPT SELECT DISTINCT t1.c0 AS ref0, t0.c0 AS ref1 FROM t0 LEFT JOIN t1 ON (t1.c0) IN (IFNULL('0.3792086453751513', "qR\r"))));
+------+-------------+
| ref0 | ref1        |
+------+-------------+
|    0 | -1496060000 |
+------+-------------+
1 row in set (0.01 sec)

mysql> -- cardinality: 13
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT DISTINCT t1.c0 AS ref0, t0.c0 AS ref1 FROM t0 INNER JOIN t1 ON (t1.c0) IN (IFNULL('0.3792086453751513', "qR\r"));
+-------------------------------------+-------------+
| ref0                                | ref1        |
+-------------------------------------+-------------+
| 00000000000000000000000000000000000 |  -539730000 |
| 00000000000000000000000000000000000 |           0 |
| 00000000000000000000000000000000000 |  1500280000 |
| 00000000000000000000000000000000000 |    0.112448 |
| 00000000000000000000000000000000000 | -1822370000 |
| 00000000000000000000000000000000000 |    0.861014 |
| 00000000000000000000000000000000000 |  -767464000 |
| 00000000000000000000000000000000000 |  1280360000 |
| 00000000000000000000000000000000000 |   991116000 |
| 00000000000000000000000000000000000 |    0.383531 |
| 00000000000000000000000000000000000 |   767095000 |
| 00000000000000000000000000000000000 |    0.673761 |
| 00000000000000000000000000000000000 | -1496060000 |
+-------------------------------------+-------------+
13 rows in set (0.00 sec)

mysql> select version();
+-----------+
| version() |
+-----------+
| 9.2.0     |
+-----------+
1 row in set (0.00 sec)

** In MariaDB **
MariaDB [database41]> -- cardinality: 1
MariaDB [database41]> (SELECT DISTINCT t1.c0 AS ref0, t0.c0 AS ref1 FROM t0 LEFT JOIN t1 ON (t1.c0) IN (IFNULL('0.3792086453751513', "qR\r")) UNION SELECT DISTINCT t1.c0 AS ref0, t0.c0 AS ref1 FROM t0 RIGHT JOIN t1 ON (t1.c0) IN (IFNULL('0.3792086453751513', "qR\r"))) EXCEPT ((SELECT DISTINCT t1.c0 AS ref0, t0.c0 AS ref1 FROM t0 LEFT JOIN t1 ON (t1.c0) IN (IFNULL('0.3792086453751513', "qR\r")) EXCEPT SELECT DISTINCT t1.c0 AS ref0, t0.c0 AS ref1 FROM t0 RIGHT JOIN t1 ON (t1.c0) IN (IFNULL('0.3792086453751513', "qR\r"))) UNION (SELECT DISTINCT t1.c0 AS ref0, t0.c0 AS ref1 FROM t0 RIGHT JOIN t1 ON (t1.c0) IN (IFNULL('0.3792086453751513', "qR\r")) EXCEPT SELECT DISTINCT t1.c0 AS ref0, t0.c0 AS ref1 FROM t0 LEFT JOIN t1 ON (t1.c0) IN (IFNULL('0.3792086453751513', "qR\r"))));
Empty set (0.002 sec)

MariaDB [database41]> -- cardinality: 13
MariaDB [database41]> SELECT DISTINCT t1.c0 AS ref0, t0.c0 AS ref1 FROM t0 INNER JOIN t1 ON (t1.c0) IN (IFNULL('0.3792086453751513', "qR\r"));
Empty set (0.001 sec)

MariaDB [database41]> select version();
+--------------------------+
| version()                |
+--------------------------+
| 10.11.11-MariaDB-ubu2204 |
+--------------------------+
1 row in set (0.000 sec)

** In TiDB **
mysql> -- cardinality: 1
mysql> (SELECT DISTINCT t1.c0 AS ref0, t0.c0 AS ref1 FROM t0 LEFT JOIN t1 ON (t1.c0) IN (IFNULL('0.3792086453751513', "qR\r")) UNION SELECT DISTINCT t1.c0 AS ref0, t0.c0 AS ref1 FROM t0 RIGHT JOIN t1 ON (t1.c0) IN (IFNULL('0.3792086453751513', "qR\r"))) EXCEPT ((SELECT DISTINCT t1.c0 AS ref0, t0.c0 AS ref1 FROM t0 LEFT JOIN t1 ON (t1.c0) IN (IFNULL('0.3792086453751513', "qR\r")) EXCEPT SELECT DISTINCT t1.c0 AS ref0, t0.c0 AS ref1 FROM t0 RIGHT JOIN t1 ON (t1.c0) IN (IFNULL('0.3792086453751513', "qR\r"))) UNION (SELECT DISTINCT t1.c0 AS ref0, t0.c0 AS ref1 FROM t0 RIGHT JOIN t1 ON (t1.c0) IN (IFNULL('0.3792086453751513', "qR\r")) EXCEPT SELECT DISTINCT t1.c0 AS ref0, t0.c0 AS ref1 FROM t0 LEFT JOIN t1 ON (t1.c0) IN (IFNULL('0.3792086453751513', "qR\r"))));
Empty set (0.01 sec)

mysql> -- cardinality: 13
mysql> SELECT DISTINCT t1.c0 AS ref0, t0.c0 AS ref1 FROM t0 INNER JOIN t1 ON (t1.c0) IN (IFNULL('0.3792086453751513', "qR\r"));
Empty set (0.00 sec)

mysql> select version();
+--------------------------------------------+
| version()                                  |
+--------------------------------------------+
| 8.0.11-TiDB-v9.0.0-beta.2.pre-193-g4852c06 |
+--------------------------------------------+
1 row in set (0.00 sec)

** In OceanBase **
obclient(root@test)[database41]> -- cardinality: 1
Query OK, 0 rows affected (0.000 sec)

obclient(root@test)[database41]> (SELECT DISTINCT t1.c0 AS ref0, t0.c0 AS ref1 FROM t0 LEFT JOIN t1 ON (t1.c0) IN (IFNULL('0.3792086453751513', "qR\r")) UNION SELECT DISTINCT t1.c0 AS ref0, t0.c0 AS ref1 FROM t0 RIGHT JOIN t1 ON (t1.c0) IN (IFNULL('0.3792086453751513', "qR\r"))) EXCEPT ((SELECT DISTINCT t1.0 AS ref0, t0.c0 AS ref1 FROM t0 LEFT JOIN t1 ON (t1.c0) IN (IFNULL('0.3792086453751513', "qR\r")) EXCEPT SELECT DISTINCT t1.c0 AS ref0, t0.c0 AS ref1 FROM t0 RIGHT JOIN t1 ON (t1.c0) IN (IFNULL('0.3792086453751513', "qR\r"))) UNION (SELECT DISTINCT t1.c0 AS ref0, t0.c0 AS ref1 FROM t0 RIGHT JOIN t1 ON (t1.0) IN (IFNULL('0.3792086453751513', "qR\r")) EXCEPT SELECT DISTINCT t1.c0 AS ref0, t0.c0 AS ref1 FROM t0 LEFT JOIN t1 ON (t1.c0) IN (IFNULL('0.3792086453751513', "qR\r"))));
Empty set (0.051 sec)

obclient(root@test)[database41]> -- cardinality: 13
Query OK, 0 rows affected (0.000 sec)

obclient(root@test)[database41]> SELECT DISTINCT t1.c0 AS ref0, t0.c0 AS ref1 FROM t0 INNER JOIN t1 ON (t1.c0) IN (IFNULL('0.3792086453751513', "qR\r"));
Empty set (0.012 sec)

obclient(root@test)[database41]> select version();
+-------------------------------+
| version()                     |
+-------------------------------+
| 5.7.25-OceanBase_CE-v4.2.1.10 |
+-------------------------------+
1 row in set (0.002 sec)

How to repeat:
DROP DATABASE IF EXISTS database41;
CREATE DATABASE database41;
USE database41;
CREATE TABLE t0(c0 FLOAT  COMMENT 'asdf'    NOT NULL) ;
CREATE TABLE t1(c0 SMALLINT(35) ZEROFILL   NOT NULL);
INSERT   INTO t1(c0) VALUES(0.1824230226382254);
INSERT  IGNORE INTO t1(c0) VALUES(NULL);
INSERT  IGNORE INTO t0(c0) VALUES(NULL);
INSERT INTO t0(c0) VALUES(-1669661514);
INSERT INTO t1(c0) VALUES(0.7472309988598856);
DELETE QUICK IGNORE FROM t1 WHERE (((t1.c0) AND (t1.c0)) IN (CAST(1777329468 AS SIGNED))) && (2013524628);
INSERT  IGNORE INTO t1(c0) VALUES(963834148);
REPLACE INTO t0(c0) VALUES(1239037716);
REPLACE INTO t1(c0) VALUES(0.43602613101159104);
INSERT  IGNORE INTO t1(c0) VALUES("'>");
INSERT  IGNORE INTO t1(c0) VALUES(0.9934674085398288);
REPLACE INTO t0(c0) VALUES(0.6740703907035186);
INSERT  IGNORE INTO t1(c0) VALUES(NULL);
REPLACE INTO t0(c0) VALUES(-1.769451152E9);
REPLACE INTO t1(c0) VALUES(0.4749621371179693);
REPLACE INTO t0(c0) VALUES(-30021013);
INSERT IGNORE INTO t0(c0) VALUES(0.24731106863745167), (1778147985), (0.09838507952625908), ('0.4660497943510168'), (0.8122674816254841);
INSERT IGNORE INTO t0(c0) VALUES(-1632610407);
INSERT IGNORE INTO t0(c0) VALUES(NULL);
INSERT INTO t0(c0) VALUES(2081612921);
INSERT  INTO t0(c0) VALUES(0.990338853192909);
# UPDATE t1 SET c0=0;
REPLACE INTO t1(c0) VALUES(0.8431550115185268);
INSERT  IGNORE INTO t0(c0) VALUES(1754857090), (NULL), ("");
INSERT  IGNORE INTO t1(c0) VALUES('i6棘^T');
INSERT INTO t1(c0) VALUES(0.18962959651986877);
INSERT  IGNORE INTO t1(c0) VALUES(549730166);
# ALTER TABLE t1 FORCE, PACK_KEYS 0;
REPLACE INTO t0(c0) VALUES(0.189489284896317), (0.851502970255646), (0.2104462311614259);
REPLACE INTO t0(c0) VALUES(-2.086789819E9);
INSERT  IGNORE INTO t0(c0) VALUES(0.1841282621952196);
REPLACE INTO t0(c0) VALUES(-1130577659);
REPLACE INTO t1(c0) VALUES(0.15226799749882614);
INSERT INTO t0(c0) VALUES(-2054644770);
INSERT  IGNORE INTO t0(c0) VALUES(1440616587);
INSERT  IGNORE INTO t0(c0) VALUES(-85986860), ('Wf}R'), (876191032);
INSERT  IGNORE INTO t0(c0) VALUES(0.27867858994878725);
CREATE INDEX i0 ON t1(c0 DESC);
INSERT  IGNORE INTO t0(c0) VALUES(NULL), (NULL), ('OZDᛁ');
REPLACE INTO t0(c0) VALUES(-1667117131);
REPLACE INTO t0(c0) VALUES('-1197856791');
INSERT  IGNORE INTO t1(c0) VALUES('65651547');
REPLACE INTO t0(c0) VALUES("0.942870114792822");
INSERT IGNORE INTO t1(c0) VALUES(1951500315);
REPLACE INTO t1(c0) VALUES(0.5509397793910037);
INSERT  IGNORE INTO t0(c0) VALUES(NULL);
REPLACE INTO t1(c0) VALUES(0.4770861185062949);
INSERT  IGNORE INTO t0(c0) VALUES(0.5715570769353812);
INSERT  IGNORE INTO t1(c0) VALUES(-1920324395);
REPLACE INTO t1(c0) VALUES(0.025000575724580476);
INSERT  IGNORE INTO t0(c0) VALUES(NULL);
REPLACE INTO t0(c0) VALUES(0.31210399912364317);
INSERT INTO t0(c0) VALUES(1818547475);
REPLACE INTO t0(c0) VALUES(-1979499865);
REPLACE INTO t0(c0) VALUES(0.10764013051980781), (-1766427762), (0.8988106227622641);
INSERT  IGNORE INTO t1(c0) VALUES(NULL);
# CHECKSUM TABLE t0;
INSERT INTO t1(c0) VALUES(0.07064190860033626);
UPDATE t1 SET c0= EXISTS (SELECT 1) WHERE t1.c0;
INSERT INTO t1(c0) VALUES(0.5447848109638285);
INSERT  IGNORE INTO t1(c0) VALUES(0.1560826941734691);
REPLACE INTO t0(c0) VALUES(332832373);
INSERT  IGNORE INTO t0(c0) VALUES(0.28772261646954056);
INSERT  IGNORE INTO t1(c0) VALUES(0.6913662847041423);
INSERT IGNORE INTO t1(c0) VALUES(0.8219073663240016);
UPDATE t0 SET c0='-539730446';
INSERT  IGNORE INTO t0(c0) VALUES('');
DELETE LOW_PRIORITY FROM t1;
INSERT IGNORE INTO t1(c0) VALUES(1197056235);
INSERT  IGNORE INTO t0(c0) VALUES(1500279666);
INSERT  INTO t0(c0) VALUES("0.11244760007773402");
REPLACE INTO t0(c0) VALUES(-1.822372953E9);
INSERT  IGNORE INTO t0(c0) VALUES('');
REPLACE INTO t0(c0) VALUES(0.8610140574800775);
# ALTER TABLE t0 STATS_AUTO_RECALC 1;
INSERT  IGNORE INTO t1(c0) VALUES('&MﲶI');
REPLACE INTO t0(c0) VALUES('-767464133');
INSERT  IGNORE INTO t1(c0) VALUES('[_'), (NULL), (65651547);
REPLACE INTO t0(c0) VALUES(1280356003);
DELETE LOW_PRIORITY QUICK FROM t1;
REPLACE INTO t1(c0) VALUES(0.5705589591860675);
INSERT  IGNORE INTO t0(c0) VALUES('');
REPLACE INTO t0(c0) VALUES(991115748);
REPLACE INTO t1(c0) VALUES(0.7737006902214526), (0.8520837366965166), (0.016662214021319732);
REPLACE INTO t0(c0) VALUES(0.3835309206587264);
INSERT IGNORE INTO t1(c0) VALUES(NULL);
INSERT  IGNORE INTO t0(c0) VALUES(NULL);
REPLACE INTO t1(c0) VALUES(0.8736937294158336);
REPLACE INTO t0(c0) VALUES(767094793);
REPLACE INTO t0(c0) VALUES("0.6737605706328825");
INSERT  IGNORE INTO t0(c0) VALUES('-1496062311');

-- cardinality: 1
(SELECT DISTINCT t1.c0 AS ref0, t0.c0 AS ref1 FROM t0 LEFT JOIN t1 ON (t1.c0) IN (IFNULL('0.3792086453751513', "qR	\r")) UNION SELECT DISTINCT t1.c0 AS ref0, t0.c0 AS ref1 FROM t0 RIGHT JOIN t1 ON (t1.c0) IN (IFNULL('0.3792086453751513', "qR	\r"))) EXCEPT ((SELECT DISTINCT t1.c0 AS ref0, t0.c0 AS ref1 FROM t0 LEFT JOIN t1 ON (t1.c0) IN (IFNULL('0.3792086453751513', "qR	\r")) EXCEPT SELECT DISTINCT t1.c0 AS ref0, t0.c0 AS ref1 FROM t0 RIGHT JOIN t1 ON (t1.c0) IN (IFNULL('0.3792086453751513', "qR	\r"))) UNION (SELECT DISTINCT t1.c0 AS ref0, t0.c0 AS ref1 FROM t0 RIGHT JOIN t1 ON (t1.c0) IN (IFNULL('0.3792086453751513', "qR	\r")) EXCEPT SELECT DISTINCT t1.c0 AS ref0, t0.c0 AS ref1 FROM t0 LEFT JOIN t1 ON (t1.c0) IN (IFNULL('0.3792086453751513', "qR	\r"))));
-- cardinality: 13
SELECT DISTINCT t1.c0 AS ref0, t0.c0 AS ref1 FROM t0 INNER JOIN t1 ON (t1.c0) IN (IFNULL('0.3792086453751513', "qR	\r"));

Hello 策 吕,

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

regards,
Umesh