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!

Interesting, hope to get an explanation from the developers.