Hi, SQLite developers,

To improve performance, SQLite will short-circuit (i.e., returning result quickly) some empty-table queries. For example, empty-table joins other non-empty-tables. However, SQLite fails to short-circuit for some empty tables queries. You can reproduce these performance issues on the latest SQLite (Version 3.50.1) as follows:

1. Create tables and insert rows.

.timer ON
CREATE TABLE t0(c0 INTEGER); -- keep t0 empty
CREATE TABLE t1(c1 INTEGER);
CREATE TABLE t2(c2 INTEGER);
INSERT INTO t1 SELECT value FROM generate_series(1, 10000000);
INSERT INTO t2 SELECT value FROM generate_series(1, 100000000);

2. There are some positive cases.

Since t0 is empty, all of these positive cases can short-circuit and return empty set result quickly (i.e., consumes 0.000 s).

SELECT * FROM t0 CROSS JOIN t1 CROSS JOIN t2;
Run Time: real 0.000 user 0.000067 sys 0.000003

SELECT * FROM t2 CROSS JOIN t1 CROSS JOIN t0;
Run Time: real 0.000 user 0.000079 sys 0.000000

SELECT * FROM t0 CROSS JOIN t1 WHERE c0 = (SELECT AVG(c2) FROM t2);
Run Time: real 0.000 user 0.000067 sys 0.000000

SELECT * FROM t0 CROSS JOIN t1 ON c0 = (SELECT AVG(c2) FROM t2);
Run Time: real 0.001 user 0.000171 sys 0.000002

SELECT * FROM t0 NATURAL JOIN t1 NATURAL JOIN t2;
Run Time: real 0.000 user 0.000138 sys 0.000008

SELECT * FROM t2 NATURAL JOIN t1 NATURAL JOIN t0; 
Run Time: real 0.000 user 0.000070 sys 0.000000

SELECT * FROM t0 LEFT JOIN t1 ON c0 = c1 LEFT JOIN t2 ON c1 = c2;
Run Time: real 0.000 user 0.000000 sys 0.000106

SELECT * FROM t0 LEFT JOIN t1 ON c0 = c1 LEFT JOIN t2 ON c0 = (SELECT AVG(c2) FROM t2);
Run Time: real 0.000 user 0.000090 sys 0.000000

SELECT * FROM t0 LEFT JOIN t1 ON c0 = c1 WHERE c0 = (SELECT AVG(c2) FROM t2);
Run Time: real 0.000 user 0.000093 sys 0.000000

SELECT * FROM t0 INNER JOIN t1 ON c0 = c1 INNER JOIN t2 ON c1 = c2;
Run Time: real 0.000 user 0.000000 sys 0.000182

SELECT * FROM t0 INNER JOIN t1 ON c0 = c1 WHERE c0 = (SELECT AVG(c2) FROM t2);
Run Time: real 0.000 user 0.000097 sys 0.000000

SELECT * FROM t0 WHERE EXISTS (SELECT 1 FROM t2 WHERE c0 = c2);
Run Time: real 0.000 user 0.000109 sys 0.000000

SELECT * FROM t0 WHERE NOT EXISTS (SELECT 1 FROM t2 WHERE c0 = c2);
Run Time: real 0.000 user 0.000065 sys 0.000000

SELECT * FROM t0 WHERE c0 = (SELECT AVG(c2) FROM t2);
Run Time: real 0.000 user 0.000071 sys 0.000000

3. There are some other negetive cases.

Since the short-circuiting optimization is not perfect, SQLite fails to short-circuit for some empty-table queries. For example, changing the order or conditions of joins or adding a WHERE/HAVING clause with subquery may result in a short circuit failure.

SELECT COUNT(*) FROM t0 CROSS JOIN t1 HAVING c0 = (SELECT AVG(c2) FROM t2);
Run Time: real 3.424 user 3.423092 sys 0.000000

SELECT * FROM t0 NATURAL JOIN t1 WHERE c0 = (SELECT AVG(c2) FROM t2);
Run Time: real 7.379 user 7.378735 sys 0.000000

SELECT COUNT(*) FROM t0 NATURAL JOIN t1 HAVING c0 = (SELECT AVG(c2) FROM t2);
Run Time: real 3.465 user 3.465309 sys 0.000000

SELECT * FROM t2 RIGHT JOIN t0 ON c0=c2;
Run Time: real 1.265 user 1.264280 sys 0.000000

SELECT COUNT(*) FROM t0 LEFT JOIN t1 ON c0 = c1 HAVING c0 = (SELECT AVG(c2) FROM t2);
Run Time: real 3.501 user 3.501514 sys 0.000001

SELECT * FROM t2 INNER JOIN t1 ON c2 = c1 INNER JOIN t0 ON c1 = c0;
Run Time: real 21.286 user 21.080132 sys 0.205787

SELECT * FROM t0 INNER JOIN t2 ON c0 = (SELECT AVG(c2) FROM t2);
Run Time: real 6.636 user 6.635655 sys 0.000002

SELECT COUNT(*) FROM t0 INNER JOIN t1 ON c0 = c1 HAVING c0 = (SELECT AVG(c2) FROM t2);
Run Time: real 3.494 user 3.493708 sys 0.000000

SELECT * FROM t2 WHERE EXISTS (SELECT 1 FROM t0 WHERE c0 = c2);
Run Time: real 5.296 user 5.296476 sys 0.000000

SELECT * FROM t0 INTERSECT SELECT * FROM t2;
Run Time: real 14.052 user 12.509542 sys 1.539806

SELECT * FROM t0 EXCEPT SELECT * FROM t2;
Run Time: real 3.671 user 3.671752 sys 0.000000

SELECT COUNT(*) FROM t0 HAVING c0 = (SELECT AVG(c2) FROM t2);
Run Time: real 3.452 user 3.451532 sys 0.000000

Best regard,

Jinhui Lai