Posted to tcl by sebres at Wed Mar 20 11:56:32 GMT 2019view pretty
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.