[SQLancer] Result mismatch detected by NoREC
Non-optimizing Reference Engine Construction detected a case where the result of the optimized and unoptimized query does not match. The optimized query SELECT t0.c0, t0.c1, t1.c0 FROM t0, t1 WHERE ((t0.c1)<=(""));
computes a result that contains 8 rows, while the unoptimized query SELECT COUNT(*) FROM (SELECT ((t0.c1)<=("")) AS flag FROM t0, t1) AS res WHERE flag=TRUE;
computes 32.
Database state:
CREATE DATABASE database5;
USE database5;
CREATE TABLE t0 (c0 FLOAT PRIMARY KEY, c1 VARCHAR(137));
CREATE TABLE t1 (c0 CHAR(975) PRIMARY KEY NOT NULL);
INSERT INTO t1 VALUES ("BA]A=");
INSERT INTO t1 VALUES ("]xUF&n~z*");
INSERT INTO t1 VALUES ("=i#f={Q)");
INSERT INTO t0 VALUES (DEFAULT, NULL);
INSERT INTO t0 VALUES (0.42557452732568, "=F"), (NULL, "diCX-");
INSERT INTO t1 VALUES ("kgmGWC");
INSERT INTO t0 VALUES (0.041431000954554764, "jDwde+(LmU");
INSERT INTO t0 VALUES (0.041431000954554764, "J^w.&"), (0.5047047142689786, "mJ}j}%(.GTG"), (0.16148984776985809, "");
INSERT INTO t1 VALUES ("UVv~HOM[SUev^Ui"), (DEFAULT);
INSERT INTO t0 VALUES (DEFAULT, "U");
INSERT INTO t1 VALUES ("BApem.EAunp&"), ("yVi#rs]eWnn");
INSERT INTO t0 VALUES (0.6296801440445668, NULL);
INSERT INTO t0 VALUES (0.3655584931156993, "}HFY)QolDw§R#w");
INSERT INTO t0 VALUES (NULL, "+v"), (0.9929167163042155, NULL);
optimizedQuery: SELECT t0.c0, t0.c1, t1.c0 FROM t0, t1 WHERE ((t0.c1)<=(""));
unoptimizedQuery: SELECT COUNT(*) FROM (SELECT ((t0.c1)<=("")) AS flag FROM t0, t1) AS res WHERE flag=TRUE;
Command to reprodruce:
echo 'CREATE DATABASE database5; USE database5; CREATE TABLE t0 (c0 FLOAT PRIMARY KEY, c1 VARCHAR(137)); CREATE TABLE t1 (c0 CHAR(975) PRIMARY KEY NOT NULL); INSERT INTO t1 VALUES ("BA]A="); INSERT INTO t1 VALUES ("]xUF&n~z*"); INSERT INTO t1 VALUES ("=i#f={Q)"); INSERT INTO t0 VALUES (DEFAULT, NULL); INSERT INTO t0 VALUES (0.42557452732568, "=F"), (NULL, "diCX-"); INSERT INTO t1 VALUES ("kgmGWC"); INSERT INTO t0 VALUES (0.041431000954554764, "jDwde+(LmU"); INSERT INTO t0 VALUES (0.041431000954554764, "J^w.&"), (0.5047047142689786, "mJ}j}%(.GTG"), (0.16148984776985809, ""); INSERT INTO t1 VALUES ("UVv~HOM[SUev^Ui"), (DEFAULT); INSERT INTO t0 VALUES (DEFAULT, "U"); INSERT INTO t1 VALUES ("BApem.EAunp&"), ("yVi#rs]eWnn"); INSERT INTO t0 VALUES (0.6296801440445668, NULL); INSERT INTO t0 VALUES (0.3655584931156993, "}HFY)QolDw§R#w"); INSERT INTO t0 VALUES (NULL, "+v"), (0.9929167163042155, NULL); SELECT t0.c0, t0.c1, t1.c0 FROM t0, t1 WHERE ((t0.c1)<=("")); SELECT COUNT(*) FROM (SELECT ((t0.c1)<=("")) AS flag FROM t0, t1) AS res WHERE flag=TRUE;' | build/debug_shared/bin/shell --plan-enumerator PEall
Edited by Tobias Kopp