Wrong implementation of the NOT IN predicate in the presence of nulls

lukaseder
SQL-Fighter

I think that the three-valued logic rules are not implemented correctly in the NOT IN predicate. At least, Exasol's implementation doesn't match that of most popular RDBMS, or the SQL standard (with the exception of MS Access and Sybase ASE, as far as I know).

Consider this query for reproduction:

 

SELECT 1 != NULL, 1 NOT IN (NULL), 1 NOT IN (NULL, NULL)

 

It produces

 

|1<>NULL|NOT (1=NULL)|NOT (1 IN(NULL, NULL))|
|-------|------------|----------------------|
|       |            |true                  |

 

All three values should be NULL / UNKNOWN, yet the third one is not. Curiously, the second one got optimised correctly to a "simpler" predicate that does not involve the NOT IN operator. The same transformation should have turned the third one into NOT (1 = NULL) AND NOT (1 = NULL).

The following statement is also wrong:

 

SELECT *
FROM (VALUES (1), (NULL)) AS t (v)
WHERE v NOT IN (SELECT NULL);

 

It should produce an empty set, as can be confirmed in most other RDBMS, but it produces:

 

|V  |
|---|
|1  |
|   |

For the record, I'm using Exasol 7.0.7 on Windows with Docker:

docker run --name EXASOL -p 127.0.0.1:9563:8563 --detach --privileged --stop-timeout 120 exasol/docker-db:latest

 

1 REPLY 1

exa-GeorgD
Team Exasol
Team Exasol

Hi Lukas,
thank you for reporting the issue.
You are right, the <in predicate> in Exasol does not behave in a SQL standard conform way.


We created a bug ticket for it.


As with all behavior breaking changes, this will only be done in a major release of Exasol.
Based on our tight schedule, it is unlikely that this will be part of 7.1.


Best wishes
Georg