SQLite Forum


3 forum posts by user MarkusWinand

11:48 Post: 3.36.0 changed visibility of CTEs, intentionally? (artifact: f3bc922a14 user: MarkusWinand)


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.

12:08 Reply: 3.25 math functions vs. extension-functions.c (artifact: 6e243a2068 user: MarkusWinand)

I'm indeed referring to the extension-functions.c file available from the SQLite homepage here:


I'm aware that it is not supported. I've also fixed the problem on my own, but my main question remains how to copy with problems found in files hosted on the SQLite website? I just generally like to push my fixes "upstream".

I just searched a little more and found this: https://gitlab.com/liamh/extension-functions

I'll try to reach out to the original author and see how that goes.

07:21 Post: 3.25 math functions vs. extension-functions.c (artifact: 02061aac66 user: MarkusWinand)

Starting with 3.25.0 I get the errors shown below when compiling SQLite with the extension-functions.c added.

It seems to be a rather trivial problem that some of the functions provided by extension-functions.c are now in the core distribution (in particular: sign). I just manually removed parts referring to "signFunc" from extension-functions.c. That get is compiling. I didn't check yet if other functions provided by extension-functions.c are now also available in the core distributation.

My main question is: What is the proper way to cope with issues in "Contributed Files"?

ps.: This problem happens when building the JDBC driver for 3.35.x .


target/sqlite-3.35.0-Mac-x86_64/sqlite3.c:234703:13: error: redefinition of 'signFunc'
static void signFunc(sqlite3_context *context, int argc, sqlite3_value **argv){
target/sqlite-3.35.0-Mac-x86_64/sqlite3.c:120439:13: note: previous definition is here
static void signFunc(
target/sqlite-3.35.0-Mac-x86_64/sqlite3.c:235909:20: error: invalid application of 'sizeof' to an incomplete type 'const struct FuncDef []'
  for(i=0; i<sizeof(aFuncs)/sizeof(aFuncs[0]); i++){
2 errors generated.