Hi,

I found this on SELECT-SQL command:

if you use FilterCondition in the form of

FieldName NOT IN (Subquery)
or
FieldName <> ALL(Subquery),
and
FieldName can to be NULL

the FilterCondition return true for all records where FieldName is NULL.

Correct is:
-FilterCondition return null(unknown)

repro code

CLEAR
CREATE CURSOR _TABLE (F1 INT NULL)

INSERT INTO _TABLE VALUES (NULL)

* this is like a correct reference, and return a 0 records cursor
SELECT 1 FROM _TABLE T1 WHERE T1.F1 NOT IN (NULL)

* BUG: this return 1 record, correct is 0 record
SELECT 2 FROM _TABLE T1 WHERE T1.F1 NOT IN (SELECT F1 FROM _TABLE T2)

* BUG: this return 1 record, correct is 0 record
SELECT 3 FROM _TABLE T1 WHERE T1.F1 <> ALL (SELECT F1 FROM _TABLE T2)

This problem particularly is felt in the query of type:

SELECT ... FROM TABLE1 LEFT JOIN TABLE2 ON joinCondition
WHERE ... AND TABLE2.FieldName NOT IN (Subquery2)

or

SELECT ... FROM TABLE1 RIGHT JOIN TABLE2 ON joinCondition
WHERE ... AND TABLE1.FieldName NOT IN (Subquery1)

or

SELECT ... FROM TABLE1 FULL JOIN TABLE2 ON joinCondition
WHERE ... 
AND TABLE1.FieldName NOT IN (Subquery1)
AND TABLE2.FieldName NOT IN (Subquery2)

because the FilterCondition not remove the join rows without outer join partner.

The workaround is simple, add a null check condition:

at
... AND (FieldName IS NOT NULL AND FieldName NOT IN (Subquery)) ...
or
... AND (FieldName IS NOT NULL AND FieldName <> ALL (Subquery)) ...

Fabio