Posted to tcl by sebres at Wed Mar 20 11:56:32 GMT 2019view raw

  1. sqlite3 db :memory:
  2.  
  3. set sub "WITH _temp_ids(id) AS (select [join {1 2 3 4 5} { union all select }])
  4. , _temp_sub(id) AS (select [join {NULL 2 3 4} { union all select }])"
  5.  
  6. ## == 0) show values in both "tables":
  7. % db eval "$sub select id from _temp_ids"
  8. 1 2 3 4 5
  9. % db eval "$sub select id from _temp_sub"
  10. {} 2 3 4
  11.  
  12. ## == 1) check IN (null is in sub-statement):
  13. % db eval "$sub select id from _temp_ids where id IN (select id from _temp_sub)"
  14. 2 3 4
  15.  
  16. ## == 2.1) check NOT IN (null is in sub-statement):
  17. % db eval "$sub select id from _temp_ids where id NOT IN (select id from _temp_sub)"
  18. <no result>
  19.  
  20. ## == 2.2) check NOT IN (null is filtered in sub-statement):
  21. % db eval "$sub select id from _temp_ids where id NOT IN (select id from _temp_sub where id is not null)"
  22. 1 5
  23.  
  24. % db eval "SET ANSI_NULLS OFF"
  25. near "SET": syntax error
  26.  
  27. ???
  28.  
  29. does sqlite3 (and tclsqlite) have some option like ANSI_NULLS to avoid this "error-prone" behavior
  30. (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.