List:General Discussion« Previous MessageNext Message »
From:Dan Nelson Date:March 16 2005 8:03am
Subject:Re: subquery fails when a NOT IN operator tests a subset with NULL valu
View as plain text  
In the last episode (Mar 16), Giuseppe Maxia said:
> Here is a description of what looks like a serious bug. This is
> related to bugs #7294 and #6247
> 
> Tested against mysql 4.1.9 and 4.1.10.
> 
> >Description:
> 	operator NOT IN fails when a subquery returns one or more NULL 
> 	values.
> >How-To-Repeat:
> 	simple proof of concept:
> mysql>  select 1 in (1,null,3);
> +-----------------+
> | 1 in (1,null,3) |
> +-----------------+
> |               1 |
> +-----------------+
> #OK
> 
> mysql> select 2 not in (1,null,3);
> +---------------------+
> | 2 not in (1,null,3) |
> +---------------------+
> |                NULL |
> +---------------------+
> # NOT OK

This looks okay to me, according to
http://dev.mysql.com/doc/mysql/en/comparison-operators.html#id2940868 :

	To comply with the SQL standard, from MySQL 4.1 on IN returns
	NULL not only if the expression on the left hand side is NULL,
	but also if no match is found in the list and one of the
	expressions in the list is NULL.

2 doesn't match 1, NULL, or 3, and there's a NULL in the list, so the
IN expression must return NULL.  NOT(NULL) is still NULL, so the entire
expression returns NULL.

Subqueries using IN() may not be the same as the IN() expression (I
rarely use subqueries so I don't know); they are documented at
http://dev.mysql.com/doc/mysql/en/any-in-some-subqueries.html and
http://dev.mysql.com/doc/mysql/en/all-subqueries.html .

-- 
	Dan Nelson
	dnelson@stripped
Thread
subquery fails when a NOT IN operator tests a subset with NULL valuGiuseppe Maxia16 Mar
  • Re: subquery fails when a NOT IN operator tests a subset with NULL valuDan Nelson16 Mar
    • Re: subquery fails when a NOT IN operator tests a subset with NULLvaluGiuseppe Maxia16 Mar
  • Re: subquery fails when a NOT IN operator tests a subset with NULL valuHarald Fuchs16 Mar
  • Re: subquery fails when a NOT IN operator tests a subset with NULLvaluPeter Brawley16 Mar
    • Re: subquery fails when a NOT IN operator tests a subset with NULLvaluGiuseppe Maxia16 Mar