Posted to tcl by sebres at Wed Mar 20 11:56:32 GMT 2019view raw
- sqlite3 db :memory:
- set sub "WITH _temp_ids(id) AS (select [join {1 2 3 4 5} { union all select }])
- , _temp_sub(id) AS (select [join {NULL 2 3 4} { union all select }])"
- ## == 0) show values in both "tables":
- % db eval "$sub select id from _temp_ids"
- 1 2 3 4 5
- % db eval "$sub select id from _temp_sub"
- {} 2 3 4
- ## == 1) check IN (null is in sub-statement):
- % db eval "$sub select id from _temp_ids where id IN (select id from _temp_sub)"
- 2 3 4
- ## == 2.1) check NOT IN (null is in sub-statement):
- % db eval "$sub select id from _temp_ids where id NOT IN (select id from _temp_sub)"
- <no result>
- ## == 2.2) check NOT IN (null is filtered in sub-statement):
- % db eval "$sub select id from _temp_ids where id NOT IN (select id from _temp_sub where id is not null)"
- 1 5
- % db eval "SET ANSI_NULLS OFF"
- near "SET": syntax error
- ???
- does sqlite3 (and tclsqlite) have some option like ANSI_NULLS to avoid this "error-prone" behavior
- (so to force select 2.1 to work correctly without necessary filtering of null's like 2.2)?
Comments
Posted by sebres at Wed Mar 20 12:45:52 GMT 2019 [text] [code]
## Another example illustrating a working scenario which stop to work by first NULL inserted later to second table: sqlite3 db :memory: db eval "create table _temp_ids(id int); create table _temp_sub(id int null);" db eval "insert into _temp_ids select [join {1 2 3 4 5} { union all select }]" db eval "insert into _temp_sub select [join {2 3 4} { union all select }]" proc test {} { puts in:[db eval "select id as 'IN' from _temp_ids where id IN (select id from _temp_sub)"] puts ni:[db eval "select id as 'NOT IN' from _temp_ids where id NOT IN (select id from _temp_sub)"] } % test in:2 3 4 ni:1 5 # let insert one null into second table (used as sub-select) % db eval "insert into _temp_sub values (NULL)" % test in:2 3 4 ni: weird.