Bug #46815 CONCAT_WS returning wrong data
Submitted: 19 Aug 2009 20:16 Modified: 18 Dec 2009 23:58
Reporter: Tomasz Pajor Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server Severity:S3 (Non-critical)
Version:5.1.36, 4.1, 5.0, 5.1, next OS:Any
Assigned to: Sergei Glukhov CPU Architecture:Any
Tags: Concat_WS
Triage: Triaged: D2 (Serious)

[19 Aug 2009 20:16] Tomasz Pajor
Description:
I've got a query.

mysql> SELECT IF(t1.name IS NULL, 'test', CONCAT_WS('_', (SELECT name FROM test2 WHERE id_test2 = 1), t1.name)) AS concat_name FROM test1 t1;

Currently it returns:
+--------------+
| concat_name  |
+--------------+
| test         |
| test         |
| test         |
| LOAD_MIN     |
| LOAD_MIN_MAX |
+--------------+

I think this is wrong, and it should return:
+--------------+
| concat_name  |
+--------------+
| test         |
| test         |
| test         |
| LOAD_MIN     |
| LOAD_MAX     |
+--------------+

How to repeat:
Here is database desc with tables and values.

mysql> desc test1; select * from test1; desc test2; select * from test2;
+----------+------------------+------+-----+---------+----------------+
| Field    | Type             | Null | Key | Default | Extra          |
+----------+------------------+------+-----+---------+----------------+
| id_test1 | int(11) unsigned | NO   | PRI | NULL    | auto_increment |
| name     | varchar(20)      | YES  |     | NULL    |                |
+----------+------------------+------+-----+---------+----------------+
2 rows in set (0.00 sec)

+----------+------+
| id_test1 | name |
+----------+------+
|        1 | NULL |
|        2 | NULL |
|        3 | NULL |
|        4 | MIN  |
|        5 | MAX  |
+----------+------+
5 rows in set (0.00 sec)

+----------+------------------+------+-----+---------+----------------+
| Field    | Type             | Null | Key | Default | Extra          |
+----------+------------------+------+-----+---------+----------------+
| id_test2 | int(11) unsigned | NO   | PRI | NULL    | auto_increment |
| name     | varchar(20)      | YES  |     | NULL    |                |
+----------+------------------+------+-----+---------+----------------+
2 rows in set (0.00 sec)

+----------+------+
| id_test2 | name |
+----------+------+
|        1 | LOAD |
+----------+------+
1 row in set (0.00 sec)
[19 Aug 2009 22:41] Sveta Smirnova
Thank you for the report.

Verified as described.
[8 Sep 2009 11:41] 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/82678

2810 Sergey Glukhov	2009-09-08
      Bug#46815 CONCAT_WS returning wrong data
      The problem is that argument buffer can be used as result buffer
      and it leads to argument value change.
      The fix is to use 'old buffer' as result buffer only
      if it is not an argument buffer.
     @ mysql-test/r/func_str.result
        test result
     @ mysql-test/t/func_str.test
        test case
     @ sql/item_strfunc.cc
        The problem is that argument buffer can be used as result buffer
        and it leads to argument value change.
        The fix is to use 'old buffer' as result buffer only
        if it is not an argument buffer.
[8 Sep 2009 12:12] 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/82681

2810 Sergey Glukhov	2009-09-08
      Bug#46815 CONCAT_WS returning wrong data
      The problem is that argument buffer can be used as result buffer
      and it leads to argument value change.
      The fix is to use 'old buffer' as result buffer only
      if first argument is not constant item.
     @ mysql-test/r/func_str.result
        test result
     @ mysql-test/t/func_str.test
        test case
     @ sql/item_strfunc.cc
        The problem is that argument buffer can be used as result buffer
        and it leads to argument value change.
        The fix is to use 'old buffer' as result buffer only
        if first argument is not constant item.
[10 Sep 2009 11:24] 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/82925

2811 Sergey Glukhov	2009-09-10
      Bug#46815 CONCAT_WS returning wrong data
      The problem is that argument buffer can be used as result buffer
      and it leads to argument value change.
      The fix is to use 'old buffer' as result buffer only
      if first argument is not constant item.
     @ mysql-test/r/func_str.result
        test result
     @ mysql-test/t/func_str.test
        test case
     @ sql/item_strfunc.cc
        The problem is that argument buffer can be used as result buffer
        and it leads to argument value change.
        The fix is to use 'old buffer' as result buffer only
        if first argument is not constant item.
[14 Sep 2009 16:05] Bugs System
Pushed into 5.4.4-alpha (revid:alik@sun.com-20090914155317-m1g9wodmndzdj4l1) (version source revid:alik@sun.com-20090914155317-m1g9wodmndzdj4l1) (merge vers: 5.4.4-alpha) (pib:11)
[23 Sep 2009 0:47] Paul Dubois
Noted in 5.4.4 changelog.

CONCAT_WS() could return incorrect results due to an argument buffer
also being used as a result buffer.
[2 Oct 2009 1:40] Paul Dubois
Moved 5.4 changelog entry from 5.4.4 to 5.4.3.
[6 Oct 2009 8:57] Bugs System
Pushed into 5.0.87 (revid:joro@sun.com-20091006073202-rj21ggvo2gw032ks) (version source revid:kristofer.pettersson@sun.com-20090929151855-gvpblm4dnnubypdv) (merge vers: 5.0.87) (pib:11)
[6 Oct 2009 8:59] Bugs System
Pushed into 5.1.40 (revid:joro@sun.com-20091006073316-lea2cpijh9r6on7c) (version source revid:ingo.struewing@sun.com-20090916070128-6053el2ucp5z7pyn) (merge vers: 5.1.39) (pib:11)
[18 Dec 2009 10:32] Bugs System
Pushed into 5.1.41-ndb-7.1.0 (revid:jonas@mysql.com-20091218102229-64tk47xonu3dv6r6) (version source revid:jonas@mysql.com-20091218095730-26gwjidfsdw45dto) (merge vers: 5.1.41-ndb-7.1.0) (pib:15)
[18 Dec 2009 10:48] Bugs System
Pushed into 5.1.41-ndb-6.2.19 (revid:jonas@mysql.com-20091218100224-vtzr0fahhsuhjsmt) (version source revid:jonas@mysql.com-20091217101452-qwzyaig50w74xmye) (merge vers: 5.1.41-ndb-6.2.19) (pib:15)
[18 Dec 2009 11:03] Bugs System
Pushed into 5.1.41-ndb-6.3.31 (revid:jonas@mysql.com-20091218100616-75d9tek96o6ob6k0) (version source revid:jonas@mysql.com-20091217154335-290no45qdins5bwo) (merge vers: 5.1.41-ndb-6.3.31) (pib:15)
[18 Dec 2009 11:17] Bugs System
Pushed into 5.1.41-ndb-7.0.11 (revid:jonas@mysql.com-20091218101303-ga32mrnr15jsa606) (version source revid:jonas@mysql.com-20091218064304-ezreonykd9f4kelk) (merge vers: 5.1.41-ndb-7.0.11) (pib:15)
[18 Dec 2009 23:58] Paul Dubois
Noted in 5.0.87, 5.1.40 changelogs.