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