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