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