Bug #46992 Using IN operator has some problems

• Submitted: 29 Aug 2009 9:30
• Modified: 1 Sep 2009 5:55
• Reporter: boobalan m
• Status: Not a Bug
• Category: MySQL Server: Stored Routines
• Severity: S5 (Performance)
• Version: 5.0
• OS: Any
• CPU Architecture: Any
• Tags: IN operator

[29 Aug 2009 9:30] boobalan m

Description:
field1 varchar , value = '1,2,4,101'

SELECT * FROM `tab_name` WHERE `field1` IN (1,5,3,14);

this returns a record set.

But when i use a query

SELECT * FROM `tab_name` WHERE `field1` IN (2,4,3,14);

i can't able to get records set.

How to repeat:
Through mail

[29 Aug 2009 9:42] Valeriy Kravchuk

This is NOT a bug. 

Hint: When your string '1,2,3,4' is converted to number, you get 1 as a result, like this:

mysql> select cast('1,2,3,4' as unsigned);
+-----------------------------+
| cast('1,2,3,4' as unsigned) |
+-----------------------------+
|                           1 |
+-----------------------------+
1 row in set, 1 warning (0.00 sec)

mysql> select cast('2,1,3,4' as unsigned);
+-----------------------------+
| cast('2,1,3,4' as unsigned) |
+-----------------------------+
|                           2 |
+-----------------------------+
1 row in set, 1 warning (0.00 sec)

Number 1 is present in the IN (...) list of values, while number 2 is NOT. Hence the result you get.

[1 Sep 2009 5:55] boobalan m

I am not satisfied with this solution because i need any of the occurrences of a number or list. Means
`field1` 
1,2,3
2,3
7,8,6,4
5,4,2
2,3,4

But when i use a query

SELECT * FROM `tab_name` WHERE `field1` IN ('2,4,3,14');

i can't able to get records set.