Bug #59405 FIND_IN_SET won't work normaly after upgrade from 5.1 to 5.5
Submitted: 10 Jan 2011 21:34 Modified: 21 Apr 2011 1:10
Reporter: Michael Bourquin Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S1 (Critical)
Version:5.5.8, 5.5.9-bzr OS:Any (Linux, Windows XP, Mac OS X)
Assigned to: Jørgen Løland CPU Architecture:Any
Tags: FIND_IN_SET, regression

[10 Jan 2011 21:34] Michael Bourquin
Description:
Hi,

The function FIND_IN_SET seems to do not work like in the version 5.1.x

I upgraded my server to 5.5.8 and some query using FIND_IN_SET in where clause return no result...

Exemple:
Create a table "test" with a field "days" -> type set('1','2','3','4','5','6','7') and insert a line with ALL "set" selected.

SELECT 1
FROM test
WHERE 
FIND_IN_SET( DAYOFWEEK( CURRENT_DATE( ) ) , days )

This work with MySQL 5.1.x but no more with 5.5.8...?

That work if you use "FIND_IN_SET( 1 , days )" -> static value inside.

Someone can try to duplicate that problem please ?

Thanks,
Michael

How to repeat:
Exemple:
Create a table "test" with a field "days" -> type set('1','2','3','4','5','6','7') and insert a line with ALL "set" selected.

SELECT 1
FROM test
WHERE 
FIND_IN_SET( DAYOFWEEK( CURRENT_DATE( ) ) , days )
[11 Jan 2011 7:30] Valeriy Kravchuk
Verified on Windows XP. This is what we had in 5.1.54:

C:\Program Files\MySQL\MySQL Server 5.5\bin>mysql -uroot -proot -P3310 test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 26
Server version: 5.1.54-community MySQL Community Server (GPL)

Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

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

mysql> drop table test;
Query OK, 0 rows affected (0.09 sec)

mysql> create table test(days set('1','2','3','4','5','6','7'));
Query OK, 0 rows affected (0.13 sec)

mysql> insert into test values('1,2,3,4,5,6,7');
Query OK, 1 row affected (0.05 sec)

mysql> select 1 from test where find_in_set('1',days);
+---+
| 1 |
+---+
| 1 |
+---+
1 row in set (0.01 sec)

mysql> select * from test where find_in_set('1',days);
+---------------+
| days          |
+---------------+
| 1,2,3,4,5,6,7 |
+---------------+
1 row in set (0.00 sec)

mysql> select * from test where find_in_set(dayofweek(current_date()),days);
+---------------+
| days          |
+---------------+
| 1,2,3,4,5,6,7 |
+---------------+
1 row in set (0.02 sec)

mysql> exit
Bye

With 5.5.8 find_in_set() does not work as expected when nested functions call is used:

C:\Program Files\MySQL\MySQL Server 5.5\bin>mysql -uroot -proot -P3312 test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 15
Server version: 5.5.8 MySQL Community Server (GPL)

Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

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

mysql> drop table test;
Query OK, 0 rows affected (0.11 sec)

mysql> create table test(days set('1','2','3','4','5','6','7'));
Query OK, 0 rows affected (0.08 sec)

mysql> insert into test values('1,2,3,4,5,6,7');
Query OK, 1 row affected (0.06 sec)

mysql> select 1 from test where find_in_set('1',days);
+---+
| 1 |
+---+
| 1 |
+---+
1 row in set (0.08 sec)

mysql> select * from test where find_in_set(dayofweek(current_date()),days);
Empty set (0.01 sec)

mysql> explain extended select * from test where find_in_set(dayofweek(current_d
ate()),days);
+----+-------------+-------+------+---------------+------+---------+------+-----
-+----------+-------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows
 | filtered | Extra       |
+----+-------------+-------+------+---------------+------+---------+------+-----
-+----------+-------------+
|  1 | SIMPLE      | test  | ALL  | NULL          | NULL | NULL    | NULL |    1
 |   100.00 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+-----
-+----------+-------------+
1 row in set, 1 warning (0.03 sec)

mysql> show warnings\G
*************************** 1. row ***************************
  Level: Note
   Code: 1003
Message: select `test`.`test`.`days` AS `days` from `test`.`test` where find_in_
set(<cache>(dayofweek(curdate())),`test`.`test`.`days`)
1 row in set (0.00 sec)

mysql> show variables like 'char%';
+--------------------------+----------------------------------------------------
-----+
| Variable_name            | Value
     |
+--------------------------+----------------------------------------------------
-----+
| character_set_client     | utf8
     |
| character_set_connection | utf8
     |
| character_set_database   | utf8
     |
| character_set_filesystem | binary
     |
| character_set_results    | utf8
     |
| character_set_server     | utf8
     |
| character_set_system     | utf8
     |
| character_sets_dir       | C:\Program Files\MySQL\MySQL Server 5.5\share\chars
ets\ |
+--------------------------+----------------------------------------------------
-----+
8 rows in set (0.00 sec)
[12 Jan 2011 13:11] Michael Bourquin
Can i/we hope that this bug will be fix in 5.5.9 ? or do i have to downgrade to 5.1.x ?
[18 Jan 2011 15:45] Sveta Smirnova
Re-verified with mysql-trunk on Mac OSX 10.4

This is not repeatable if use MTR for me, but repeatable if use command line client. I also added SET NAMES utf8 to mimic Valeriy's environment.
[20 Jan 2011 13:46] Jørgen Løland
create table test(days set('1','2','3','4','5','6','7')) engine=innodb;
insert into test values('1,2,3,4,5,6,7');
select * from test where find_in_set(dayofweek(current_date()),days);
drop table test;
[31 Jan 2011 13:39] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/130043

3578 Jorgen Loland	2011-01-31
      BUG#59405: FIND_IN_SET won't work normaly after upgrade 
                 from 5.1 to 5.5
      
      In this bug, args[0] in an Item_func_find_in_set stored an 
      Item_func_weekday that was constant. In 
      Item_func_find_in_set::fix_length_and_dec(), args[0]->val_str() 
      was called. Later, when Item_func_find_in_set::val_int() was 
      called, args[0]->null_value was checked. However, the 
      Item_func_weekday in args[0] had now been replaced with an 
      Item_cache. No val_*() calls had been made to this Item_cache,
      thus null_value was incorrectly 'true', resulting in missing 
      rows in the result set.
      
      The implemented fix is to remember args[0]->null_value as it 
      was right after calling it's val_str() in fix_length_and_dec().
      Note: this only applies if args[0] is constant. 
      
      An alternative fix would be to call args[0]->val_int() inside 
      Item_func_find_in_set::val_int(), but that would have to be 
      done for every record this const value is checked against.
     @ mysql-test/r/func_set.result
        Add test for BUG#59405
     @ mysql-test/t/func_set.test
        Add test for BUG#59405
     @ sql/item_func.cc
        Add variable Item_func_find_in_set::args_0_null_value
     @ sql/item_func.h
        Add variable Item_func_find_in_set::args_0_null_value
[21 Apr 2011 1:10] Paul Dubois
Noted in 5.5.11, 5.6.2 changelogs.

FIND_IN_SET() could work differently in MySQL 5.5 than in 5.1.

CHANGESET - http://lists.mysql.com/commits/132474