Re: in(NULL)

> -----Original Message-----
> From: pgsql-bugs-owner@postgresql.org
> [mailto:pgsql-bugs-owner@postgresql.org]On Behalf Of jose
> Sent: Wednesday, May 22, 2002 9:38 AM
> To: pgsql-bugs@postgresql.org
> Cc: jose@sferacarta.com
> Subject: [BUGS] in(NULL)
>
>
> Hi all,
>
> I think I found a bug using the IN operator.
>
>
> I have a table t populated as follow:
> a  |  b
> ---+----
> 1  | 11
> 2  | 22
> 3  | 33
> 4  | NULL
> (4 rows)
> select * from t where b in (11,22,NULL);
> a  |  b
> ---+----
> 1  | 11
> 2  | 22
> (2 rows)
> select * from t where b not in (11,22,NULL);
> a  |  b
> ---+----
> (0 rows)
> -----------------------------------------------
> I tried the same quesry in mysql and it give me
> a different result. Who are right?
> select * from t where b not in (11,22,NULL);
> +---+---+
> | a | b |
> +---+---+
> | 3 | 33|
> +---+---+
> 1 row in set (0.00 sec)

As usual, we are right, of course. How can they be sure that any column
isn't in (11,22,NULL), when they can't specific what the NULL value is?

(More specifically: the logical result of 'a IN (11,22,NULL)' for a=10 is
unknown, rather than false or true. Therefore, the WHERE clause fails
(unknown values fail). For a=11 or 22, it is true. So, it works as expected
for IN. For NOT IN, however, the result of 'a NOT IN (11,22,NULL) for a=11
is false. For a=10, it is unknown -- we don't know if the NULL is 10,
therefore we can't say that it isn't. Therefore, for a NOT IN (xx,yy,NULL),
all evaluations are either false or unknown).

SQL specs are clear on this point, and its covered well in Celko's
_SQL_for_Smarties_: NOT IN ( xx, yy, NULL) returns nothing.

At least MySQL has a cute logo. And Monty's not bad, either.

- J.

Joel BURTON | joel@joelburton.com | joelburton.com | aim: wjoelburton
Knowledge Management & Technology Consultant