Bug #33764 Wrong result with IN(), CONCAT() and implicit type conversion
Submitted: 9 Jan 2008 12:02 Modified: 15 Mar 2008 8:11
Reporter: Kristian Nielsen Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: DML Severity:S3 (Non-critical)
Version:5.0+ OS:Linux
Assigned to: Gleb Shchepa CPU Architecture:Any
Triage: D2 (Serious) / R3 (Medium) / E3 (Medium)

[9 Jan 2008 12:02] Kristian Nielsen
Description:
create table t1 (a varchar(10) not null primary key) engine=myisam;
insert into t1 (a) values ('foo0'), ('bar0'), ('baz0');
select * from t1 where a in (concat('foo', 0), 'bar');

Empty set (0.00 sec)

BUT:

mysql> select * from t1 where a in (concat('foo', 0));
+------+
| a    |
+------+
| foo0 | 
+------+
1 row in set (0.00 sec)

mysql> select * from (select * from t1) st where a in (concat('foo', 0), 'bar');
+------+
| a    |
+------+
| foo0 | 
+------+
1 row in set (0.00 sec)

mysql> select * from t1 where a in (concat('foo', '0'), 'bar');
+------+
| a    |
+------+
| foo0 | 
+------+
1 row in set (0.00 sec)

mysql> select * from t1 where a=concat('foo', 0) or a='bar';
+------+
| a    |
+------+
| foo0 | 
+------+
1 row in set (0.00 sec)

How to repeat:
create table t1 (a varchar(10) not null primary key) engine=myisam;
insert into t1 (a) values ('foo0'), ('bar0'), ('baz0');
select * from t1 where a in (concat('foo', 0), 'bar');

Suggested fix:
The query

    select * from t1 where a in (concat('foo', 0), 'bar');

should return the row 'foo0', just like the other variants given above do.

Since the problem disappears when selecting from a subquery, it may be related to bad index optimization.

It also (in this example at least) only occurs when more than one element in the IN() list, _and_ when there is an implicit number->string type conversion, as seen in working examples.
[9 Jan 2008 12:16] Kristian Nielsen
and this (query works when only one row in table):

mysql> delete from t1 where a != 'foo0';
Query OK, 2 rows affected (0.00 sec)

mysql> select * from t1 where a in (concat('foo', 0), 'bar');
+------+
| a    |
+------+
| foo0 | 
+------+
1 row in set (0.00 sec)
[10 Jan 2008 12:05] Susanne Ebrecht
Verified as described.
[12 Feb 2008 8:09] 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/42091

ChangeSet@1.2602, 2008-02-12 12:09:06+04:00, gshchepa@host.loc +3 -0
  Fixed bug#33764: Wrong result with IN(), CONCAT() and implicit
                   type conversion.
  
  Instead of copying of whole character string from a temporary
  buffer, the server copied a short-living pointer to that string
  into a long-living structure. That has been fixed.
[12 Feb 2008 17:17] 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/42114

ChangeSet@1.2602, 2008-02-12 21:17:05+04:00, gshchepa@host.loc +3 -0
  Fixed bug#33764: Wrong result with IN(), CONCAT() and implicit
                   type conversion.
  
  Instead of copying of whole character string from a temporary
  buffer, the server copied a short-living pointer to that string
  into a long-living structure. That has been fixed.
[12 Feb 2008 20:43] 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/42141

ChangeSet@1.2602, 2008-02-13 00:16:59+04:00, gshchepa@host.loc +3 -0
  Fixed bug#33764: Wrong result with IN(), CONCAT() and implicit
                   type conversion.
  
  Instead of copying of whole character string from a temporary
  buffer, the server copied a short-living pointer to that string
  into a long-living structure. That has been fixed.
[13 Mar 2008 19:27] Bugs System
Pushed into 6.0.5-alpha
[13 Mar 2008 19:35] Bugs System
Pushed into 5.1.24-rc
[13 Mar 2008 19:42] Bugs System
Pushed into 5.0.60
[15 Mar 2008 8:11] Jon Stephens
Bugfix documented in the 5.0.60, 5.1.24, and 6.0.5 changelogs as follows:

        Some queries using a combination of IN, CONCAT(), and an implicit type
        conversion could return an incorrect result.
[2 Apr 2008 17:01] Jon Stephens
Also noted in the 5.1.23-ndb-6.3.11 changelog.