Bug #118858 Inconsistency between the results returned by MySQL and other vendors' database systems was found during differentiation

Submitted:	20 Aug 2025 8:51	Modified:	23 Sep 2025 4:03
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:
We find it interesting that on other database systems both query statements should return the empty set, but on MySQL the first query statement returns a row of data.

** In MySQL **
mysql> (SELECT DISTINCT t4.c1 AS ref0 FROM t4 LEFT JOIN t0 ON CAST(((t0.c1) IS FALSE) IN ((- (t0.c1))) AS SIGNED) WHERE ('((') IS NOT TRUE GROUP BY t4.c1 UNION SELECT DISTINCT t4.c1 AS ref0 FROM t4 RIGHT JOIN t0 ON CAST(((t0.c1) IS FALSE) IN ((- (t0.c1))) AS SIGNED) WHERE ('((') IS NOT TRUE GROUP BY t4.c1) EXCEPT ((SELECT DISTINCT t4.c1 AS ref0 FROM t4 LEFT JOIN t0 ON CAST(((t0.c1) IS FALSE) IN ((- (t0.c1))) AS SIGNED) WHERE ('((') IS NOT TRUE GROUP BY t4.c1 EXCEPT SELECT DISTINCT t4.c1 AS ref0 FROM t4 RIGHT JOIN t0 ON CAST(((t0.c1) IS FALSE) IN ((- (t0.c1))) AS SIGNED) WHERE ('((') IS NOT TRUE GROUP BY t4.c1) UNION (SELECT DISTINCT t4.c1 AS ref0 FROM t4 RIGHT JOIN t0 ON CAST(((t0.c1) IS FALSE) IN ((- (t0.c1))) AS SIGNED) WHERE ('((') IS NOT TRUE GROUP BY t4.c1 EXCEPT SELECT DISTINCT t4.c1 AS ref0 FROM t4 LEFT JOIN t0 ON CAST(((t0.c1) IS FALSE) IN ((- (t0.c1))) AS SIGNED) WHERE ('((') IS NOT TRUE GROUP BY t4.c1)); 
+------+
| ref0 |
+------+
|    1 |
+------+
1 row in set, 6 warnings (0.00 sec)

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

mysql> SELECT DISTINCT t4.c1 AS ref0 FROM t4 INNER JOIN t0 ON CAST(((t0.c1) IS FALSE) IN ((- (t0.c1))) AS SIGNED) WHERE ('((') IS NOT TRUE GROUP BY t4.c1;
Empty set, 1 warning (0.00 sec)

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

** In MariaDB **
MariaDB [database44]> -- cardinality: 1
MariaDB [database44]> (SELECT DISTINCT t4.c1 AS ref0 FROM t4 LEFT JOIN t0 ON CAST(((t0.c1) IS FALSE) IN ((- (t0.c1))) AS SIGNED) WHERE ('((') IS NOT TRUE GROUP BY t4.c1 UNION SELECT DISTINCT t4.c1 AS ref0 FROM t4 RIGHT JOIN t0 ON CAST(((t0.c1) IS FALSE) IN ((- (t0.c1))) AS SIGNED) WHERE ('((') IS NOT TRUE GROUP BY t4.c1) EXCEPT ((SELECT DISTINCT t4.c1 AS ref0 FROM t4 LEFT JOIN t0 ON CAST(((t0.c1) IS FALSE) IN ((- (t0.c1))) AS SIGNED) WHERE ('((') IS NOT TRUE GROUP BY t4.c1 EXCEPT SELECT DISTINCT t4.c1 AS ref0 FROM t4 RIGHT JOIN t0 ON CAST(((t0.c1) IS FALSE) IN ((- (t0.c1))) AS SIGNED) WHERE ('((') IS NOT TRUE GROUP BY t4.c1) UNION (SELECT DISTINCT t4.c1 AS ref0 FROM t4 RIGHT JOIN t0 ON CAST(((t0.c1) IS FALSE) IN ((- (t0.c1))) AS SIGNED) WHERE ('((') IS NOT TRUE GROUP BY t4.c1 EXCEPT SELECT DISTINCT t4.c1 AS ref0 FROM t4 LEFT JOIN t0 ON CAST(((t0.c1) IS FALSE) IN ((- (t0.c1))) AS SIGNED) WHERE ('((') IS NOT TRUE GROUP BY t4.c1));
Empty set, 6 warnings (0.003 sec)

MariaDB [database44]> -- cardinality: 0
MariaDB [database44]> SELECT DISTINCT t4.c1 AS ref0 FROM t4 INNER JOIN t0 ON CAST(((t0.c1) IS FALSE) IN ((- (t0.c1))) AS SIGNED) WHERE ('((') IS NOT TRUE GROUP BY t4.c1;
Empty set, 3 warnings (0.001 sec)

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

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

obclient(root@test)[database44]> (SELECT DISTINCT t4.c1 AS ref0 FROM t4 LEFT JOIN t0 ON CAST(((t0.c1) IS FALSE) IN ((- (t0.c1))) AS SIGNED) WHERE ('((') IS NOT TRUE GROUP BY t4.c1 UNION SELECT DISTINCT t4.c1 AS ref0 FROM t4 RIGHT JOIN t0 ON CAST(((t0.c1) IS FALSE) IN ((- (t0.c1))) AS SIGNED) WHERE ('((') ISNOT TRUE GROUP BY t4.c1) EXCEPT ((SELECT DISTINCT t4.c1 AS ref0 FROM t4 LEFT JOIN t0 ON CAST(((t0.c1) IS FALSE) IN ((- (t0.c1))) AS SIGNED) WHERE ('((') IS NOT TRUE GROUP BY t4.c1 EXCEPT SELECT DISTINCT t4.c1 AS ref0 FROM t4 RIGHT JOIN t0 ON CAST(((t0.c1) IS FALSE) IN ((- (t0.c1))) AS SIGNED) WHERE ('((') I NOT TRUE GROUP BY t4.c1) UNION (SELECT DISTINCT t4.c1 AS ref0 FROM t4 RIGHT JOIN t0 ON CAST(((t0.c1) IS FALSE) IN ((- (t0.c1))) AS SIGNED) WHERE ('((') IS NOT TRUE GROUP BY t4.c1 EXCEPT SELECT DISTINCT t4.c1 AS ref0 FROM t4 LEFT JOIN t0 ON CAST(((t0.c1) IS FALSE) IN ((- (t0.c1))) AS SIGNED) WHERE ('((') ISNOT TRUE GROUP BY t4.c1));
Empty set (0.037 sec)

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

obclient(root@test)[database44]> SELECT DISTINCT t4.c1 AS ref0 FROM t4 INNER JOIN t0 ON CAST(((t0.c1) IS FALSE) IN ((- (t0.c1))) AS SIGNED) WHERE ('((') IS NOT TRUE GROUP BY t4.c1;
Empty set (0.016 sec)

How to repeat:
DROP DATABASE IF EXISTS database44;
CREATE DATABASE database44;
USE database44;
CREATE TABLE t0(c0 TEXT  COMMENT 'asdf'    NOT NULL, c1 DECIMAL ZEROFILL   COMMENT 'asdf'   PRIMARY KEY UNIQUE KEY) ;
CREATE TABLE t1 LIKE t0;
INSERT INTO t0(c1, c0) VALUES(2028312614, 2028312614);
TRUNCATE TABLE t0;
INSERT INTO t1(c0, c1) VALUES(1541418133, '0.8327317795302001');
INSERT INTO t0(c0, c1) VALUES(-1126678769, "2028312614");

ALTER TABLE t1 RENAME AS t4;

-- cardinality: 1
(SELECT DISTINCT t4.c1 AS ref0 FROM t4 LEFT JOIN t0 ON CAST(((t0.c1) IS FALSE) IN ((- (t0.c1))) AS SIGNED) WHERE ('((') IS NOT TRUE GROUP BY t4.c1 UNION SELECT DISTINCT t4.c1 AS ref0 FROM t4 RIGHT JOIN t0 ON CAST(((t0.c1) IS FALSE) IN ((- (t0.c1))) AS SIGNED) WHERE ('((') IS NOT TRUE GROUP BY t4.c1) EXCEPT ((SELECT DISTINCT t4.c1 AS ref0 FROM t4 LEFT JOIN t0 ON CAST(((t0.c1) IS FALSE) IN ((- (t0.c1))) AS SIGNED) WHERE ('((') IS NOT TRUE GROUP BY t4.c1 EXCEPT SELECT DISTINCT t4.c1 AS ref0 FROM t4 RIGHT JOIN t0 ON CAST(((t0.c1) IS FALSE) IN ((- (t0.c1))) AS SIGNED) WHERE ('((') IS NOT TRUE GROUP BY t4.c1) UNION (SELECT DISTINCT t4.c1 AS ref0 FROM t4 RIGHT JOIN t0 ON CAST(((t0.c1) IS FALSE) IN ((- (t0.c1))) AS SIGNED) WHERE ('((') IS NOT TRUE GROUP BY t4.c1 EXCEPT SELECT DISTINCT t4.c1 AS ref0 FROM t4 LEFT JOIN t0 ON CAST(((t0.c1) IS FALSE) IN ((- (t0.c1))) AS SIGNED) WHERE ('((') IS NOT TRUE GROUP BY t4.c1));
-- cardinality: 0
SELECT DISTINCT t4.c1 AS ref0 FROM t4 INNER JOIN t0 ON CAST(((t0.c1) IS FALSE) IN ((- (t0.c1))) AS SIGNED) WHERE ('((') IS NOT TRUE GROUP BY t4.c1;

Hello 策 吕,

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

regards,
Umesh

Hi， developers

Does the Severity rating for this bug need to be changed to s2?

Hi， developers

I think we can change the Severity level of this bug to S2.

Page generated in 0.061 sec. using MySQL 8.4.7-u6-cloud

Timestamp references displayed by the system are UTC.
Content reproduced on this site is the property of the respective copyright holders.
It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.