SQLite Forum

Error when disable SQLITE_SimplifyJoin

Error when disable SQLITE_SimplifyJoin

(1) By Wang Ke (krking) on 2021-05-31 05:52:56 [link] [source]

Hello everyone,

I noticed that there is an annotation in the source code related to the function sqlite3_test_control, which I quoted here:

sqlite3_test_control(SQLITE_TESTCTRL_OPTIMIZATIONS, sqlite3 *db, int N)

The idea is that a test program (like the SQL Logic Test or SLT test module) can run the same SQL multiple times with various optimizations disabled to verify that the same answer is obtained in every case.

Then I found a test case, which may result in error when an optimization is disabled:

CREATE TABLE t0(c0, c1);
CREATE TABLE t1(c0, c1);
CREATE TABLE t2(c0, c1);

SELECT * FROM t0 LEFT JOIN t2 ON t0.c0=t1.c1 JOIN t1 ON t1.c0>t2.c0; -- empty
.testctrl optimizations 0x00002000;
-- disable SQLITE_SimplifyJoin
SELECT * FROM t0 LEFT JOIN t2 ON t0.c0=t1.c1 JOIN t1 ON t1.c0>t2.c0; -- Error: ON clause references tables to its right

I guess the reason for the error may be that, when processing an expression like "a JOIN b ON c.x JOIN c ON y", the columns of table "c" is allowed to be used by the previous "a JOIN b", but LEFT JOIN does not allow this. So if this optimization is on, LEFT JOIN is optimized to be JOIN, so it won't report an error message, but it will report an error message if this optimization is not on.

Although I can understand the reason for the error, I still have questions about it. Is there a description of similar cases in SQLite, such as whether syntax is specified, so that it is consistent in terms of whether the query can be done with or without error when optimization is on and off.

Looking forward to your early reply!

(2) By anonymous on 2021-05-31 18:49:07 in reply to 1 [link] [source]

Interesting, hope to get an explanation from the developers.

(3) By Gunter Hick (gunter_hick) on 2021-06-01 06:22:50 in reply to 1 [link] [source]

SQLite has become picky about the terms in the ON clause of joins some time ago.

The fragment "t0 LEFT JOIN t2 ON t0.c0=t1.c1" does not actually constrain t2 at all, but references an as yet unknown table. I would consider this a semantic error and SQLite is quite right for telling you off on it.

I suspect with optimization turned on, the statement is interpreted as

t0 LEFT JOIN (t2 JOIN t1 ON t1.c0>t2.c0) ON t0.c0=t1.c1

which "shifts" the offending ON clause to a position where it can be checked

(4) By Wang Ke (krking) on 2021-06-01 07:57:02 in reply to 3 [source]

Yes, I agree with you.

This is indeed a semantically problematic query, which is transformed into a semantically correct query by the optimization of SQLITE_SimplifyJoin.

I'm amazed at the fact that optimizations may affect whether a query can be executed to the end. It's kind of confusing.