Test case:
SQL> select count(*) from rdb$relations where rdb$relation_id not in (100, 200);
COUNT
=====================
67
SQL> select count(*) from rdb$relations where rdb$relation_id not in (100, 200, null);
COUNT
=====================
66 For the second query the expected result is zero.
The new implementation of IN <list> removes NULLs from the lookup list to optimize things and it works perfectly for IN, but it plays a bad joke for NOT IN which should be aware of the NULLs being involved in comparisons.
Test case:
For the second query the expected result is zero.
The new implementation of
IN <list>removes NULLs from the lookup list to optimize things and it works perfectly forIN, but it plays a bad joke forNOT INwhich should be aware of the NULLs being involved in comparisons.