* FROM t0, t1 WHERE t0.c0 = t1.c0; t0.c0 t0.c1 The latest version of MySQL that we tested failed to fetch the row https://bugs.mysql.com/bug.php?id=99122
* FROM t0, t1 WHERE t0.c0 = t1.c0; t0.c0 t0.c1 Challenge: Finding a test oracle that can automatically validate the result set Incorrect result! https://bugs.mysql.com/bug.php?id=99122
“We are unable to use Postgres as an oracle because CockroachDB has slightly different semantics and SQL support, and generating queries that execute identically on both is tricky […].” – Cockroach Labs
t0, t1 WHERE t0.c0=t1.c0 UNION ALL SELECT * FROM t0, t1 WHERE NOT (t0.c0=t1.c0) UNION ALL SELECT * FROM t0, t1 WHERE (t0.c0=t1.c0) IS NULL; SELECT * FROM t0, t1;
t0, t1 WHERE t0.c0=t1.c0 UNION ALL SELECT * FROM t0, t1 WHERE NOT (t0.c0=t1.c0) UNION ALL SELECT * FROM t0, t1 WHERE (t0.c0=t1.c0) IS NULL; SELECT * FROM t0, t1; φ
t0, t1 WHERE t0.c0=t1.c0 UNION ALL SELECT * FROM t0, t1 WHERE NOT (t0.c0=t1.c0) UNION ALL SELECT * FROM t0, t1 WHERE (t0.c0=t1.c0) IS NULL; SELECT * FROM t0, t1; t0.c0 t0.c1 φ
t0, t1 WHERE t0.c0=t1.c0 UNION ALL SELECT * FROM t0, t1 WHERE NOT (t0.c0=t1.c0) UNION ALL SELECT * FROM t0, t1 WHERE (t0.c0=t1.c0) IS NULL; SELECT * FROM t0, t1; t0.c0 t0.c1 ≠ φ
Q’p IS NULL ) SELECT <columns> FROM <tables> [<joins>] SELECT <columns> FROM <tables> [<joins>] WHERE ptern Q′p ⊎ Q′¬p ⊎ Q′p IS NULL The multiset addition can be implemented using UNION ALL
, Q’p IS NULL ) SELECT MAX(<e>) FROM <tables> [<joins>] SELECT MAX(<e>) FROM <tables> [<joins>] WHERE ptern; MAX(Q′p ⊎ Q′¬p ⊎ Q′p IS NULL ) A partition is an intermediate result, rather than a subset of the result set
CREATE TABLE t1(c0 BOOL) INTERLEAVE IN PARENT t0(rowid); INSERT INTO t0(c0) VALUES (0); INSERT INTO t1(rowid, c0) VALUES(0, TRUE); NULL 0 SELECT MAX(aggr) FROM ( SELECT MAX(t1.rowid) as aggr FROM t1 WHERE '+' >= t1.c0 UNION ALL SELECT MAX(t1.rowid) as aggr FROM t1 WHERE NOT('+' >= t1.c0) UNION ALL SELECT MAX(t1.rowid) as aggr FROM t1 WHERE ('+' >= t1.c0) IS NULL ); SELECT MAX(t1.rowid) FROM t1; ≠
, Q’p IS NULL ) SELECT AVG(<e>) FROM <tables> [<joins>]; SELECT SUM(<e>) as s, COUNT(<e>) as s FROM <tables> [<joins>]; SUM(s(Q′p ⊎ Q′¬p ⊎ Q′p IS NULL )) SUM(c(Q′p ⊎ Q′¬p ⊎ Q′p IS NULL ))
us, and I imagine anyone working on a DBMS. Usually these bugs would be slowly found by users over the years, not only negatively affecting the experience of those users but also requiring much more effort to debug and reproduce […].