Incorrect outcome of SELECT ... from ... where id NOT IN (List of ids)

Questions
1

I’m trying to do a compare between to identical tables structures to launch after some specific kitchen.

I have 2 identical tables structures t1@InMemoryStorageand t2@sqlcontainer

Doing a:

select mem.* 
from   t1@InMemoryStorage mem
where  mem.id 
       NOT IN 
       ( select sql.id 
         from   t2@sqlcontainer sql
       )

give me some issues…

if t2@sqlcontainer is empty then the above query will return 0 result whereas it should return all rows of t1.

If I run:

select mem.* 
from   t1@InMemoryStorage mem
where  mem.id NOT IN (NULL)

then I get all t1 results, which is OK.

When I delete some rows in t2@sqlcontainer from another SQL client (SSMS) I do not get correct results: sometimes records are found, sometimes not.

I tried SET invantive-use-cache false but that does not changes anything.

I found a workaround by doing a left outer join:

select mem.* 
from   t1@InMemoryStorage mem
left 
outer 
join   t2@sql sql 
on     mem.id = sql.id 
where  sql.id is NULL

Am I doing wrong with the WHERE NOT IN clause or is it not supported to have a NOT IN from a select statement ?

2

NOT IN (NULL)

The logic seems to have been wrong in the past, looking at the query with mem.id NOT IN (NULL). The current situation is different on 20.2.84: the following case returns no rows:

create table t1@inmemorystorage as select * from DUAL@DataDictionary

select *
from   T1@InMemoryStorage
where  dummy_int16 not in (null)

Which is correct: null means unknown, so the query is looking for rows that do NOT have an unknown value. “NOT unknown” is also “unknown”, since it can be any value that is unknown, including unknown.

A similar query on Oracle RDBMS returns also no rows (runs only on Oracle RDBMS, not on Invantive SQL):

select *
from   dual
where  dummy not in (null)

NOT IN on table

The following original query has been tested:

select mem.* 
from   t1@InMemoryStorage mem
where  mem.id 
       NOT IN 
       ( select sql.id 
         from   t2@sqlcontainer sql
       )

Case 1

First I run the following query to simulate a table with no rows:

select *
from   DUAL@DataDictionary
where  dummy_int32 not in (select dummy_int32 from EMPTY@DataDictionary)

It returns all rows in DUAL@DataDictionary, which is correct.

Case 2

Next, this query:

select *
from   DUAL@DataDictionary
where  dummy_int32 not in (select null from DUAL@DataDictionary)

This one returns all rows from DUAL@DataDictionary, which is INCORRECT. The outcome should have been to return NO rows.

Case 3

Next, this query:

select *
from   DUAL@DataDictionary
where  dummy_int32 not in (select dummy_int32 from DUAL@DataDictionary)

This one returns no rows from DUAL@DataDictionary, which is correct.

Case 4

Finally, this query:

select *
from   DUAL@DataDictionary
where  dummy_int32 not in (select dummy_int32+1 from DUAL@DataDictionary)

which returns all rows, which is correct.

Summary

The current release 20.2.84 of Invantive SQL handles most cases correctly, but there is one case returning incorrect results.

This bug will be resolved in a future release.

3

This case 2 correctly returns NO rows starting release 20.2.112. This release is available from