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