List:General Discussion« Previous MessageNext Message »
From:Giuseppe Maxia Date:March 16 2005 5:03pm
Subject:Re: subquery fails when a NOT IN operator tests a subset with NULL
valu
View as plain text  
Peter Brawley wrote:
> Giuseppe,
> 
>  >mysql> select 2 not in (1,null,3);
>  >+---------------------+
>  >| 2 not in (1,null,3) |
>  >+---------------------+
>  >|                NULL |
>  >+---------------------+
>  >1 row in set (0.00 sec)
> 
>  ># NOT OK
> 
> Isn't that standard SQL behaviour? 

Yes, it is. As I said before, I was too quick using this example, while
the problem arises only during a subquery.

Now, others have pointed out that even with subqueries this should be
considered standard behaviour, even though severa people in my workplace
agree that it does not look intuitive.



> NULL is not a value. NOT IN compares 
> the values using '=' and correctly returns NULL if any value is NULL ie 
> missing, eg for Oracle see http://builder.com.com/5100-6388_14-5319615.html

I see now that this mechanism is intentional.
Thanks for your link.

Giuseppe



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