Re: Can't use NULL in IN conditional?

pgsql-bugs(at)postgresql(dot)org writes:
> -- This works
> SELECT type_id, code FROM product_types WHERE code = '0A' OR code = NULL;
> -- This doesn't
> SELECT type_id, code FROM product_types WHERE code IN ('OA', NULL);

"code = NULL" is not legal SQL --- or at least, the standard's
interpretation of it is not what you appear to expect. According to the
spec the result must always be NULL, which is effectively FALSE in this
context.

Since certain Microsoft products misinterpret "var = NULL" as "var IS NULL",
we've inserted a hack into our parser to convert a comparison against a
literal NULL to an IS NULL clause. However, that only works for the
specific cases of "var = NULL" and "var <> NULL", not for any other
contexts where a null might be compared against something else.

Personally I regard this hack as a bad idea, and would prefer to take it
out. I'd certainly resist extending it to the IN operator...

regards, tom lane