MySQL Bugs: #60400: IN and NULLs

Bug #60400	IN and NULLs
Submitted:	8 Mar 2011 21:47	Modified:	20 Jul 2011 14:28
Reporter:	Sergei Golubchik	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Server: General	Severity:	S3 (Non-critical)
Version:	5.1	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	Contribution

Description:
IN does not handle NULLs in the list or values correctly if all elements in the list are of the same type.
It takes care of explicit NULLs but not of NULLs that are results of and expression.

How to repeat:
SELECT 5 IN (1,2); -- that's fine
SELECT 5 IN (1,NULL); -- explicit NULL, that's ok
SELECT 5 IN (1,FIND_IN_SET(NULL, '1,2,3')); -- oops. NULL is treated as 0
SELECT 0 IN (1,FIND_IN_SET(NULL, '1,2,3')); -- oops. NULL is treated as 0
SELECT FIND_IN_SET(NULL, '1,2,3'); -- but it's NULL not 0

Suggested fix:
In Item_func_in::fix_length_and_dec
where it stores values in the array:

  if (!args[i]->null_value)
  {
    array->set(j, args[i]);
    j++;
  }
  else
    have_null= 1;

change array->set() to return 1 if the value is NULL.
change this if() to be

  if (!args[i]->null_value && !array->set(j, args[i]))
    j++;
  else
    have_null= 1;

you can even drop an explicit check for args[i]->null_value. It will be unnecessary.

Verified with current mysql-5.1 from bzr:

macbook-pro:5.1 openxs$ bin/mysql -uroot test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.1.57-debug Source distribution

Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL v2 license

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> SELECT FIND_IN_SET(NULL, '1,2,3'); 
+----------------------------+
| FIND_IN_SET(NULL, '1,2,3') |
+----------------------------+
|                       NULL |
+----------------------------+
1 row in set (0.00 sec)

mysql> SELECT 5 IN (1,NULL);
+---------------+
| 5 IN (1,NULL) |
+---------------+
|          NULL |
+---------------+
1 row in set (0.00 sec)

mysql> SELECT 5 IN (1,FIND_IN_SET(NULL, '1,2,3'));
+-------------------------------------+
| 5 IN (1,FIND_IN_SET(NULL, '1,2,3')) |
+-------------------------------------+
|                                   0 |
+-------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT 0 IN (1,FIND_IN_SET(NULL, '1,2,3'));
+-------------------------------------+
| 0 IN (1,FIND_IN_SET(NULL, '1,2,3')) |
+-------------------------------------+
|                                   1 |
+-------------------------------------+
1 row in set (0.00 sec)

duplicate of bug#59270