List:General Discussion« Previous MessageNext Message »
From:Giuseppe Maxia Date:March 16 2005 8:34am
Subject:Re: subquery fails when a NOT IN operator tests a subset with NULL
valu
View as plain text  
Dan Nelson wrote:
> 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.
> 

Thanks for your comment.
I knew about that page, and probably I was a bit too quick when I used this example as
proof of concept.
Actually, the real problem happens only with subqueries, as I reported in the rest of my
previous message.


> 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 .

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);


BTW, I posted to this list by mistake.
I re-posted an amended version of the same bug report to the bugs list.

Giuseppe Maxia

-- 
Giuseppe Maxia
CTO
http://www.StarData.it
MySQL Certified Professional
        __              __     __
   ___ / /____ ________/ /__ _/ /____ _
  (_-</ __/ _ `/ __/ _  / _ `/ __/ _ `/
/___/\__/\_,_/_/  \_,_/\_,_/\__/\_,_/
        Database is our business
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