SQLite Forum

Strange filter condition with notNull
Login
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?