| 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: | |
| 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 | ||
[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
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 )