Bug #50096 CONCAT_WS inside procedure returning wrong data

• Submitted: 5 Jan 2010 18:53
• Modified: 12 Mar 2010 16:29
• Reporter: Anca Dogaru
• Status: Closed
• Category: MySQL Server: General
• Severity: S2 (Serious)
• Version: 5.1.41, 6.0.14
• OS: Linux (centos5 64 bit)
• Assigned to: Gleb Shchepa
• CPU Architecture: Any
• Tags: concat, procedures, regression, session variables

[5 Jan 2010 18:53] Anca Dogaru

Description:
When concat_ws is used inside a procedure and the result is stored to a session variable, the result is corrupted.

This bug may be related to 
http://bugs.mysql.com/bug.php?id=46815
because in 5.1.39 was not present.

How to repeat:
DELIMITER //
DROP PROCEDURE IF EXISTS test_concat //
CREATE PROCEDURE test_concat(
	iname varchar(255),
	itimeout tinyint,
	ilifetime mediumint
	)
DETERMINISTIC
BEGIN
	

SELECT iname;
SELECT itimeout;
SELECT ilifetime;

SET @query = CONCAT_WS(",",quote(iname), itimeout, ilifetime);
SELECT @query;
SELECT CONCAT_WS(",",quote(iname), itimeout, ilifetime);
		
END //  

Suggested fix:
mysql> call test_concat( "test1", "8", "1800");
+-------+
| iname |
+-------+
| test1 |
+-------+
1 row in set (0.00 sec)

+----------+
| itimeout |
+----------+
|        8 |
+----------+
1 row in set (0.00 sec)

+-----------+
| ilifetime |
+-----------+
|      1800 |
+-----------+
1 row in set (0.00 sec)

+----------------+
| @query         |
+----------------+
| 'test1',8,'tes |
+----------------+
1 row in set (0.00 sec)

+--------------------------------------------------+
| CONCAT_WS(",",quote(iname), itimeout, ilifetime) |
+--------------------------------------------------+
| 'test1',8,1800                                   |
+--------------------------------------------------+
1 row in set (0.00 sec)

as you can see the result store into variable @query is corrupted, the correct one is displayed in the next statement.

The hack that I have found to avoid this bug is using Convert and cast

SET @query = CONCAT_WS(",",quote(iname), CONVERT(CAST(itimeout AS char) USING ascii), CONVERT(CAST(ilifetime AS char) USING ascii));
SELECT @query;

[6 Jan 2010 4:35] Valeriy Kravchuk

Verified just as described. Problem is repeatable also in 6.0.14:

77-52-1-11:6.0-codebase openxs$ bin/mysql -uroot test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 7
Server version: 6.0.14-alpha-debug Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> DELIMITER //
mysql> DROP PROCEDURE IF EXISTS test_concat //
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> CREATE PROCEDURE test_concat(
    ->   iname varchar(255),
    ->   itimeout tinyint,
    ->   ilifetime mediumint
    -> )
    -> DETERMINISTIC
    -> BEGIN 
    -> SELECT iname;
    -> SELECT itimeout;
    -> SELECT ilifetime;
    -> SET @query = CONCAT_WS(",",quote(iname), itimeout, ilifetime);
    -> SELECT @query;
    -> SELECT CONCAT_WS(",",quote(iname), itimeout, ilifetime);
    -> END //  
Query OK, 0 rows affected (0.36 sec)

mysql> delimiter ;
mysql>  call test_concat( "test1", "8", "1800");
+-------+
| iname |
+-------+
| test1 |
+-------+
1 row in set (0.00 sec)

+----------+
| itimeout |
+----------+
|        8 |
+----------+
1 row in set (0.00 sec)

+-----------+
| ilifetime |
+-----------+
|      1800 |
+-----------+
1 row in set (0.00 sec)

+----------------+
| @query         |
+----------------+
| 'test1',8,'tes |
+----------------+
1 row in set (0.00 sec)

+--------------------------------------------------+
| CONCAT_WS(",",quote(iname), itimeout, ilifetime) |
+--------------------------------------------------+
| 'test1',8,1800                                   |
+--------------------------------------------------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

And it is a regression bug, indeed, as older versions work properly:

77-52-1-11:mysql openxs$ bin/mysql -uroot test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.1.37 MySQL Community Server (GPL)

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> DELIMITER //
mysql> DROP PROCEDURE IF EXISTS test_concat //
Query OK, 0 rows affected, 1 warning (0.37 sec)

mysql> CREATE PROCEDURE test_concat(
    ->   iname varchar(255),
    ->   itimeout tinyint,
    ->   ilifetime mediumint
    -> )
    -> DETERMINISTIC
    -> BEGIN 
    -> SELECT iname;
    -> SELECT itimeout;
    -> SELECT ilifetime;
    -> SET @query = CONCAT_WS(",",quote(iname), itimeout, ilifetime);
    -> SELECT @query;
    -> SELECT CONCAT_WS(",",quote(iname), itimeout, ilifetime);
    -> END //  
Query OK, 0 rows affected (0.01 sec)

mysql>  call test_concat( "test1", "8", "1800");
    -> //
+-------+
| iname |
+-------+
| test1 | 
+-------+
1 row in set (0.00 sec)

+----------+
| itimeout |
+----------+
|        8 | 
+----------+
1 row in set (0.00 sec)

+-----------+
| ilifetime |
+-----------+
|      1800 | 
+-----------+
1 row in set (0.00 sec)

+----------------+
| @query         |
+----------------+
| 'test1',8,1800 | 
+----------------+
1 row in set (0.00 sec)

+--------------------------------------------------+
| CONCAT_WS(",",quote(iname), itimeout, ilifetime) |
+--------------------------------------------------+
| 'test1',8,1800                                   | 
+--------------------------------------------------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

[12 Jan 2010 11: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/96598

3311 Gleb Shchepa	2010-01-12
      Bug #50096: CONCAT_WS inside procedure returning wrong data
      
      Selecting of the CONCAT_WS(...<PS parameter>...) result into
      a user variable may return wrong data.
      
      The Item_func_concat_ws::val_str function has been
      modified to take into account val_str functions
      (such as Item_sp_variable::val_str) that return a 
      pointer to an internal Item member variable that
      may reference to a buffer provided.
     @ mysql-test/r/func_concat.result
        Added test case for bug #50096.
     @ mysql-test/t/func_concat.test
        Added test case for bug #50096.
     @ sql/item_strfunc.cc
        Bug #50096: CONCAT_WS inside procedure returning wrong data
        
        The Item_func_concat_ws::val_str function has been
        modified to take into account val_str functions
        (such as Item_sp_variable::val_str) that return a 
        pointer to an internal Item member variable that
        may reference to a buffer provided.

[12 Jan 2010 11:43] Gleb Shchepa

More detailed explanation of the problem:

Item_func_concat_ws::val_str contains a number of memory
allocation-saving optimization tricks. After the fix
for bug #46815 the control flow has been changed to a
branch that is commented as "This is quite uncommon!":
one of places where we are trying to concatenate
strings inplace. However, that "uncommon" place
didn't care about PS parameters, that have another
trick in Item_sp_variable::val_str(): they use the
intermediate Item_sp_variable::str_value field,
where they may store a reference to an external 
argument's buffer.

[12 Jan 2010 12:04] 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/96601

3311 Gleb Shchepa	2010-01-12
      Bug #50096: CONCAT_WS inside procedure returning wrong data
      
      Selecting of the CONCAT_WS(...<PS parameter>...) result into
      a user variable may return wrong data.
      
      Item_func_concat_ws::val_str contains a number of memory
      allocation-saving optimization tricks. After the fix
      for bug 46815 the control flow has been changed to a
      branch that is commented as "This is quite uncommon!":
      one of places where we are trying to concatenate
      strings inplace. However, that "uncommon" place
      didn't care about PS parameters, that have another
      trick in Item_sp_variable::val_str(): they use the
      intermediate Item_sp_variable::str_value field,
      where they may store a reference to an external 
      
      The Item_func_concat_ws::val_str function has been
      modified to take into account val_str functions
      (such as Item_sp_variable::val_str) that return a 
      pointer to an internal Item member variable that
      may reference to a buffer provided.
     @ mysql-test/r/func_concat.result
        Added test case for bug #50096.
     @ mysql-test/t/func_concat.test
        Added test case for bug #50096.
     @ sql/item_strfunc.cc
        Bug #50096: CONCAT_WS inside procedure returning wrong data
        
        The Item_func_concat_ws::val_str function has been
        modified to take into account val_str functions
        (such as Item_sp_variable::val_str) that return a 
        pointer to an internal Item member variable that
        may reference to a buffer provided.

[13 Jan 2010 4: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/96693

3312 Gleb Shchepa	2010-01-13
      Bug #50096: CONCAT_WS inside procedure returning wrong data
      
      Selecting of the CONCAT_WS(...<PS parameter>...) result into
      a user variable may return wrong data.
      
      Item_func_concat_ws::val_str contains a number of memory
      allocation-saving optimization tricks. After the fix
      for bug 46815 the control flow has been changed to a
      branch that is commented as "This is quite uncommon!":
      one of places where we are trying to concatenate
      strings inplace. However, that "uncommon" place
      didn't care about PS parameters, that have another
      trick in Item_sp_variable::val_str(): they use the
      intermediate Item_sp_variable::str_value field,
      where they may store a reference to an external
      argument's buffer.
      
      The Item_func_concat_ws::val_str function has been
      modified to take into account val_str functions
      (such as Item_sp_variable::val_str) that return a
      pointer to an internal Item member variable that
      may reference to a buffer provided.
     @ mysql-test/r/func_concat.result
        Added test case for bug #50096.
     @ mysql-test/t/func_concat.test
        Added test case for bug #50096.
     @ sql/item_strfunc.cc
        Bug #50096: CONCAT_WS inside procedure returning wrong data
        
        The Item_func_concat_ws::val_str function has been
        modified to take into account val_str functions
        (such as Item_sp_variable::val_str) that return a
        pointer to an internal Item member variable that
        may reference to a buffer provided.

[15 Jan 2010 9:01] Bugs System

Pushed into 5.1.43 (revid:[e-mail]-20100115085139-qkh0i0fpohd9u9p5) (version source revid:[e-mail]-20100113113806-5742ed1swr9rtb4b) (merge vers: 5.1.43) (pib:16)

[22 Jan 2010 1:21] Paul DuBois

Noted in 5.1.43 changelog.

Within a stored routine, selecting the result of CONCAT_WS() with a
routine parameter argument into a user variable could return 
incorrect results.

Setting report to NDI pending push to 5.5.x+.

[1 Feb 2010 17:50] Paul DuBois

Setting report to Need Merge pending push to 5.5.x+.

[5 Feb 2010 11:49] Bugs System

Pushed into mysql-next-mr (revid:[e-mail]-20100204063540-9czpdmpixi3iw2yb) (version source revid:[e-mail]-20100119163614-172adculixyu26j5) (pib:16)

[5 Feb 2010 11:55] Bugs System

Pushed into 6.0.14-alpha (revid:[e-mail]-20100205113942-oqovjy0eoqbarn7i) (version source revid:[e-mail]-20100204064210-ljwanqvrjs83s1gq) (merge vers: 6.0.14-alpha) (pib:16)

[5 Feb 2010 12:01] Bugs System

Pushed into 5.5.2-m2 (revid:[e-mail]-20100203172258-1n5dsotny40yufxw) (version source revid:[e-mail]-20100115112653-e3a24041ag1cv6v3) (merge vers: 5.5.1-m2) (pib:16)

[6 Feb 2010 1:16] Paul DuBois

Noted in 5.5.2, 6.0.14 changelogs.

Setting report to Need Merge pending push to Celosia.

[12 Mar 2010 14:20] Bugs System

Pushed into 5.1.44-ndb-7.0.14 (revid:[e-mail]-20100312135944-t0z8s1da2orvl66x) (version source revid:[e-mail]-20100312115609-woou0te4a6s4ae9y) (merge vers: 5.1.44-ndb-7.0.14) (pib:16)

[12 Mar 2010 14:35] Bugs System

Pushed into 5.1.44-ndb-6.2.19 (revid:[e-mail]-20100312134846-tuqhd9w3tv4xgl3d) (version source revid:[e-mail]-20100312060623-mx6407w2vx76h3by) (merge vers: 5.1.44-ndb-6.2.19) (pib:16)

[12 Mar 2010 14:51] Bugs System

Pushed into 5.1.44-ndb-6.3.33 (revid:[e-mail]-20100312135724-xcw8vw2lu3mijrhn) (version source revid:[e-mail]-20100312103652-snkltsd197l7q2yg) (merge vers: 5.1.44-ndb-6.3.33) (pib:16)

[12 Mar 2010 16:15] Paul DuBois

Setting report to Need Merge pending push to Celosia.

[12 Mar 2010 16:29] Paul DuBois

Fixed in earlier 5.1.x, 5.5.x.

[2 Apr 2010 6:22] Gleb Shchepa

Also see similar CONCAT() bug #40625.