Strange filter condition with notNull
(1) By MBL (RoboManni) on 2021-07-07 18:36:00 [link] [source]
I realized a difference in filtering by where clause in conjunction with the in
operator and notNull
when I wanted to suppress the Null filled field and it did not happen, looking like the and
was ignored; but the order does NOT make any difference.
SQLite version 3.36.0 2021-06-18 18:36:39
Enter ".help" for usage hints.
sqlite> .mode box
sqlite> select TagGroup,TagName,Op,typeof(TagName) from ExitTagList where Op in ('write','written') and Op notNull;
┌──────────┬─────────────┬─────────┬─────────────────┐
│ TagGroup │ TagName │ Op │ typeof(TagName) │
├──────────┼─────────────┼─────────┼─────────────────┤
│ │ │ written │ null │
│ ExitHsk │ REQUEST │ write │ text │
│ ExitHsk │ SuccessCode │ write │ text │
│ ExitHsk │ VALID │ write │ text │
└──────────┴─────────────┴─────────┴─────────────────┘
sqlite> select TagGroup,TagName,Op,typeof(TagName) from ExitTagList where Op notNull and Op in ('write','written');
┌──────────┬─────────────┬─────────┬─────────────────┐
│ TagGroup │ TagName │ Op │ typeof(TagName) │
├──────────┼─────────────┼─────────┼─────────────────┤
│ │ │ written │ null │
│ ExitHsk │ REQUEST │ write │ text │
│ ExitHsk │ SuccessCode │ write │ text │
│ ExitHsk │ VALID │ write │ text │
└──────────┴─────────────┴─────────┴─────────────────┘
sqlite> select TagGroup,TagName,Op,typeof(TagName) from ExitTagList where Op in ('write','written') and Op notNull and TagGroup = 'ExitHsk';
┌──────────┬─────────────┬───────┬─────────────────┐
│ TagGroup │ TagName │ Op │ typeof(TagName) │
├──────────┼─────────────┼───────┼─────────────────┤
│ ExitHsk │ REQUEST │ write │ text │
│ ExitHsk │ SuccessCode │ write │ text │
│ ExitHsk │ VALID │ write │ text │
└──────────┴─────────────┴───────┴─────────────────┘
sqlite> select * from ExitTagList;
┌──────────┬─────────────┬─────────┬───────┐
│ TagGroup │ TagName │ Op │ Used │
├──────────┼─────────────┼─────────┼───────┤
│ │ │ written │ 19310 │
│ │ │ loaded │ 27420 │
│ ExitHsk │ REQUEST │ write │ 13115 │
│ ExitHsk │ REQUEST │ read │ 13115 │
│ ExitHsk │ SuccessCode │ write │ 9218 │
│ ExitHsk │ SuccessCode │ read │ 9218 │
│ ExitHsk │ VALID │ write │ 4609 │
│ ExitHsk │ VALID │ read │ 4609 │
└──────────┴─────────────┴─────────┴───────┘
sqlite>
What did I miss? Or is this a buggy behaviour?
(2) By Richard Hipp (drh) on 2021-07-07 18:41:07 in reply to 1 [source]
I guess you want "tagname NOT NULL" instead of "op NOT NULL".
(3) By anonymous on 2021-07-07 18:44:09 in reply to 2 [link] [source]
My fault! Thanks, you are Right