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
both built with "gcc -DSQLITE_THREADSAFE=0 -DSQLITE_OMIT_LOAD_EXTENSION shell.c sqlite3.c" on MacOS, but I guess that should not matter.