SQLite Forum

3.36.0 changed visibility of CTEs, intentionally?

3.36.0 changed visibility of CTEs, intentionally?

(1) By Markus Winand (MarkusWinand) on 2021-06-21 11:48:53 [link] [source]


I noticed that SQLite 3.36.0 changed the visibility of CTEs (WITH clause). Nothing in the release notes seems to indicate that, so I wonder if it was intentional — especially because the new behavior is in line the SQL standard and (as far as I know) all other SQL implementation.

The changed behavior can be exposed by these statements:

create table t (id integer);
insert into t values (1);

create table t2 (id integer);
insert into t2 values (2);

create view v as select id from t;

with t as (select id from t2) select * from v;

The question is whether or not the CTE is visible inside the view when the final query runs.

Previously (up to 3.35.5) SQLite was giving "2" as result, meaning that the view v was seeing the CTE t defined in the final query and thus actually accessing the table t2 instead of t.

With 3.36.0 I get "1", as mandated by the SQL Standard and other implementations (e.g., PostgreSQL).

My question is if this an unintentional side effect of another change or the new behavior of SQLite?

NB: Being a "Standard SQL" guy I would like it to stay that way, but I must admit that I also like the possibility have "polymorphic views" in that way — i.e., the possibility to re-use the code of a view on other source tables.

I observed the difference between

sqlite-amalgamation-3350500 sqlite-amalgamation-3360000

both built with "gcc -DSQLITE_THREADSAFE=0 -DSQLITE_OMIT_LOAD_EXTENSION shell.c sqlite3.c" on MacOS, but I guess that should not matter.

(2) By Richard Hipp (drh) on 2021-06-21 12:10:35 in reply to 1 [source]

This was a bug fix. We don't list bug fixes in the change log unless they seem like they are important. As this problem was found internally and has never been reported by a user, it is not considered to be important.