Re: subquery fails when a NOT IN operator tests a subset with NULL valu

[Harald Fuchs]

In article <4237EF79.3090906@stripped>,
Giuseppe Maxia <g.maxia@stripped> writes:

> The whole point is actually in subqueries, not when using IN or NOT IN in a normal
> query.
> The bug occurs when a NOT IN is used in a subquery as a LEFT JOIN replacement.

> SELECT something from t1 where column1 NOT IN (SELECT nullable_column from t2);

That's not a bug.  Let's say that the subquery returns 2, NULL, 3.
Thus the NOT IN is a shorthand for

  column1 != 2 AND column1 != NULL AND column1 != 3

Since the second condition is never true, you get an empty result set.