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
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]