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)

please reply to jose(at)sferacarta(dot)com
jose

> -----Original Message-----
> From: pgsql-bugs-owner(at)postgresql(dot)org
> [mailto:pgsql-bugs-owner(at)postgresql(dot)org]On Behalf Of jose
> Sent: Wednesday, May 22, 2002 9:38 AM
> To: pgsql-bugs(at)postgresql(dot)org
> Cc: jose(at)sferacarta(dot)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(at)joelburton(dot)com | joelburton.com | aim: wjoelburton
Knowledge Management & Technology Consultant

jose <jose(at)sferacarta(dot)com> writes:
> I think I found a bug using the IN operator.
> select * from t where b not in (11,22,NULL);

This is not a bug. The behavior of NOT IN with NULLs is not very
intuitive but it is correct according to the SQL standard. The result
can only be FALSE (when b is 11 or 22, IN is definitely TRUE, so NOT IN
is definitely FALSE) or NULL (for anything else, the result is UNKNOWN).
Either way, this query will select no rows.

> I tried the same quesry in mysql and it give me
> a different result. Who are right?

If mysql gets this wrong, then they are not implementing NULLs per SQL
spec.

regards, tom lane

Jose,

First, do not waste your time comparing PG to MySql: MySql IS NOT SQL!
Second, any operation on NULL is NULL.
Third, no set can include NULL.

You want to rewrite your query as:
select * from t where b in (11,22) or b is null;

jose wrote:
>
> 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)
>
> please reply to jose(at)sferacarta(dot)com
> jose
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://archives.postgresql.org