Hi Fabio,

Looks like a bug to me.

>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