| 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: | |
| Category: | MySQL Server: DML | Severity: | S3 (Non-critical) |
| Version: | 5.0+ | OS: | Linux |
| Assigned to: | Gleb Shchepa | CPU Architecture: | Any |
[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.
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.