Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Incomplete
-
10.9.4
-
None
-
None
-
Ubuntu 18.04.6 LTS (GNU/Linux 5.4.0-144-generic x86_64)
Description
Here is our table creation statement：
CREATE TABLE IF NOT EXISTS t0(c0 LONGTEXT) ; |
CREATE TABLE IF NOT EXISTS t1 LIKE t0; |
CREATE TABLE IF NOT EXISTS t2(c0 DECIMAL) ; |
REPLACE INTO t0(c0) VALUES('j_{(~PAZ)'); |
INSERT INTO t0(c0) VALUES(NULL); |
REPLACE INTO t0(c0) VALUES(NULL); |
INSERT IGNORE INTO t0(c0) VALUES(''); |
REPLACE INTO t0(c0) VALUES(1373444939); |
DELETE LOW_PRIORITY IGNORE FROM t0 WHERE t0.c0; |
INSERT INTO t1(c0) VALUES(1257022825), (365137223), (NULL); |
REPLACE LOW_PRIORITY INTO t1(c0) VALUES(-1836894879); |
REPLACE INTO t0(c0) VALUES(NULL); |
REPLACE INTO t1(c0) VALUES(NULL); |
REPLACE INTO t2(c0) VALUES(960364164); |
REPLACE LOW_PRIORITY INTO t1(c0) VALUES(""); |
INSERT IGNORE INTO t0(c0) VALUES(1318946640); |
DELETE QUICK IGNORE FROM t0; |
REPLACE LOW_PRIORITY INTO t1(c0) VALUES(NULL); |
INSERT IGNORE INTO t0(c0) VALUES(NULL); |
INSERT LOW_PRIORITY IGNORE INTO t0(c0) VALUES(2066070913); |
This is the statement where we found inconsistencies：
SELECT ALL t2.c0 AS ref0 FROM t1, t2 RIGHT OUTER JOIN t0 ON EXISTS (SELECT 1 wHERE FALSE) WHERE ((t2.c0 IS FALSE) NOT IN (BIT_COUNT(t1.c0))); |
--expected:[null,null,null,null,null,null] |
--actual:empty set |
Because the values of t2.c0 are all null, we replace t2.c0 with null in the inconsistent statement. Theoretically, the semantics are the same, but this statement can return a data list containing six nulls;
SELECT ALL t2.c0 AS ref0 FROM t1, t2 RIGHT OUTER JOIN t0 ON EXISTS (SELECT 1 wHERE FALSE) WHERE ((NULL IS FALSE) NOT IN (BIT_COUNT(t1.c0))); |
--expected:[null,null,null,null,null,null]
|
--actual:[null,null,null,null,null,null] |
Subsequently, we used SQLancer's TLP method for validation and found that the two queries that were supposed to be equivalent had inconsistent results：
--SELECT ALL t2.c0 AS ref0 FROM t1, t2 RIGHT OUTER JOIN t0 ON EXISTS (SELECT 1 wHERE FALSE);
|
--actual:[null,null,null,null,null,null,null,null,null,null,null,null,null,null] |
|
|
SELECT ALL t2.c0 AS ref0 FROM t1, t2 RIGHT OUTER JOIN t0 ON EXISTS (SELECT 1 wHERE FALSE) WHERE ((t2.c0 IS FALSE) NOT IN (BIT_COUNT(t1.c0))) UNION ALL SELECT ALL t2.c0 AS ref0 FROM t1, t2 RIGHT OUTER JOIN t0 ON EXISTS(SELECT 1 wHERE FALSE) WHERE (NOT ((t2.c0 IS FALSE) NOT IN (BIT_COUNT(t1.c0)))) UNION ALL SELECT ALL t2.c0 AS ref0 FROM t1, t2 RIGHT OUTER JOIN t0 ON EXISTS(SELECT 1 wHERE FALSE) WHERE ((t2.c0 IS FALSE) NOT IN (BIT_COUNT(t1.c0))) is null; |
--actual:[null,null,null,null,null,null] |
The results of the above two queries should be the same. The second query is that we added a condition to the first query and performed a UNION ALL operation on the true, false, and null results of this condition.