Bug #40625 Concat fails on DOUBLE values in a Stored Procedure, while DECIMAL works
Submitted: 10 Nov 2008 23:07 Modified: 21 Jun 2010 1:03
Reporter: Jason Garrett Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Data Types Severity:S2 (Serious)
Version:5.0.67-community-nt, 5.0.70, 5.1.29 OS:Any
Assigned to: Gleb Shchepa CPU Architecture:Any
Tags: concat

[10 Nov 2008 23:07] Jason Garrett
Description:
In a stored procedure, when performing a GROUP BY using the aggregate function SUM on a DOUBLE column, and storing the result via a cursor into a DOUBLE declared variable, and CONCATenating into a user variable @s, the answer given appears corrupted.  Using DECIMAL instead appears to work around the problem, but the issue is not seen for small data sets. ie < 39 rows.

ie.
SET @s = CONCAT('UPDATE locations SET totalPacketCount = ' , tPieces , ', totalVolume = ', tVolume1, ', totalWeight = ', tWeight, ' WHERE locationID = ', lid);	

Where:
  tVolume1 is a DOUBLE
  tVolume2 is a DOUBLE(14,3)
  tVolume3 is a DECIMAL(14,3)

Testing each value, replacing tVolume1 with tVolume2, etc, results in:
@s                                                                                                         "UPDATE locations SET totalPacketCount = 40, totalVolume = 101.36, totalWeight = 0.000 WHERE locationID = 1"
@s                                                                                                          "UPDATE locations SET totalPacketCount = 40, totalVolume = UPDATE , totalWeight = 0.000 WHERE locationID = 1"
@s                                                                                                           "UPDATE locations SET totalPacketCount = 40, totalVolume = 101.360, totalWeight = 0.000 WHERE locationID = 1"

Note the 2nd @s value has 'UPDATE ' appearing where the DOUBLE(14,3) value should be.

How to repeat:
Script attached

Suggested fix:
My only option, if I wish to stay with the GA version of MySQL, is to not use DOUBLE anywhere in my schema, and replace with DECIMAL, to avoid having this issue occur elsewhere.
[10 Nov 2008 23:13] Jason Garrett
Database Create and Populate Script, with Example Stored Procedure to demonstrate error

Attachment: 20081111 MySQL DOUBLE Error.txt (text/plain), 3.79 KiB.

[11 Nov 2008 5:07] Valeriy Kravchuk
Thank you for a bug report. Verified just as described wiht 5.0.70:

C:\Program Files\MySQL\MySQL Server 5.0\bin>mysql -uroot -proot -P3308
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 15
Server version: 5.0.70-enterprise-gpl-nt-log MySQL Enterprise Server (GPL)

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> CREATE DATABASE example;
Query OK, 1 row affected (0.00 sec)

mysql> USE example;
Database changed
mysql>
mysql> CREATE TABLE locations (
    ->     locationID              INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY,

    ->
    ->     locationName            VARCHAR(50) NOT NULL,
    ->
    ->  totalPacketCount                INTEGER UNSIGNED NOT NULL DEFAULT 0,
    ->     totalVolume             DOUBLE(14,3) NOT NULL DEFAULT 0,
    ->     totalWeight             DOUBLE(14,3) NOT NULL DEFAULT 0,
    ->
    ->  UNIQUE INDEX idx_locations_name(locationName(50))
    -> ) ENGINE = InnoDB;
Query OK, 0 rows affected (0.41 sec)

mysql>
mysql> CREATE TABLE packets (
    ->  packetID                        INTEGER NOT NULL AUTO_INCREMENT PRIMARY
KEY,
    ->     packetNumber        VARCHAR(50) NOT NULL,
    ->     locationID          INTEGER UNSIGNED NOT NULL,
    ->     pieceCount                   INTEGER UNSIGNED NOT NULL DEFAULT 0,
    ->     volume              DOUBLE(14,3) NOT NULL DEFAULT 0,
    ->     weight              DOUBLE(14,3) NOT NULL DEFAULT 0,
    ->
    ->  UNIQUE INDEX key_packets_packetNumber(packetNumber(50)),
    ->  INDEX idx_packets_locationID(locationID)
    -> ) ENGINE = InnoDB;
Query OK, 0 rows affected (0.63 sec)

mysql>
mysql> INSERT INTO locations VALUES (1, 'Location1', 0, 0, 0);
Query OK, 1 row affected (0.06 sec)

mysql> -- 486 rows of sample data
mysql> INSERT INTO `packets` VALUES (1,'PPG0001174',1,132,2.534,0.000),(2,'PPG00
00674',1,132,2.534,0.000),(3,'PPG0000628',1,132,2.534,0.000),(4,'PPG0000626',1,1
32,2.534,0.000),(5,'PPG0000613',1,132,2.534,0.000),(6,'PPG0099107',1,132,2.534,0
.000),(7,'PPG0099085',1,132,2.534,0.000),(8,'PPG0000529',1,132,2.534,0.000),(9,'
PPG0000566',1,132,2.534,0.000),(10,'PPG0000554',1,132,2.534,0.000),(11,'PPG00005
52',1,132,2.534,0.000),(12,'PPG0001000',1,132,2.534,0.000),(13,'PPG0000989',1,13
2,2.534,0.000),(14,'PPG0001018',1,132,2.534,0.000),(15,'PPG0001005',1,132,2.534,
0.000),(16,'PPG0001552',1,132,2.534,0.000),(17,'PPG0001542',1,132,2.534,0.000),(
18,'PPG0001535',1,132,2.534,0.000),(19,'PPG0001568',1,132,2.534,0.000),(20,'PPG0
001567',1,132,2.534,0.000),(21,'PP20309228',1,132,2.534,0.000),(22,'PPG2031547',
1,132,2.534,0.000),(23,'PPG0000987',1,132,2.534,0.000),(24,'PPG0001047',1,132,2.
534,0.000),(25,'PPG0001036',1,132,2.534,0.000),(26,'PPG0001041',1,132,2.534,0.00
0),(27,'PPG0001058',1,132,2.534,0.000),(28,'PPG0000112',1,132,2.534,0.000),(29,'
PPG0000400',1,132,2.534,0.000),(30,'PPG0000661',1,132,2.534,0.000),(31,'PPG00006
87',1,132,2.534,0.000),(32,'PPG0000653',1,132,2.534,0.000),(33,'PPG0000643',1,13
2,2.534,0.000),(34,'PPG0000541',1,132,2.534,0.000),(35,'PPG0000818',1,132,2.534,
0.000),(36,'PPG0000754',1,132,2.534,0.000),(37,'PPG0000705',1,132,2.534,0.000),(
38,'PPG0000741',1,132,2.534,0.000),(39,'PPG0000530',1,132,2.534,0.000),(40,'PPG0
000853',1,132,2.534,0.000);
Query OK, 40 rows affected (0.14 sec)
Records: 40  Duplicates: 0  Warnings: 0

mysql>
mysql> DELIMITER //
mysql> DROP PROCEDURE IF EXISTS spExample1;
    -> CREATE PROCEDURE spExample1() SQL SECURITY INVOKER
    -> BEGIN
    ->  DECLARE done INT DEFAULT 0;
    ->  DECLARE lid INTEGER;
    ->  DECLARE tPieces INTEGER;
    ->  DECLARE tVolume1 DOUBLE;
    ->  DECLARE tVolume2 DOUBLE(14,3);
    ->  DECLARE tVolume3 DECIMAL(14,3);
    ->  DECLARE tWeight DOUBLE(14,3);
    ->  DECLARE cur1 CURSOR FOR
    ->          SELECT locationID AS x, COUNT(*), SUM(volume), SUM(volume), SUM(
volume), SUM(weight)
    ->          FROM packets
    ->          GROUP BY locationID;
    ->  DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
    ->
    ->  OPEN cur1;
    ->
    ->  REPEAT
    ->          FETCH cur1 INTO lid, tPieces, tVolume1, tVolume2, tVolume3, tWei
ght;
    ->          IF NOT done THEN
    ->                  SELECT lid, tPieces, tVolume1, tVolume2, tVolume3, tWeig
ht;
    ->                  SET @s = CONCAT('UPDATE locations SET totalPacketCount =
 ' , tPieces , ', totalVolume = ', tVolume1, ', totalWeight = ', tWeight, ' WHER
E locationID = ', lid);
    ->                  SELECT @s;
    ->                  SET @s = CONCAT('UPDATE locations SET totalPacketCount =
 ' , tPieces , ', totalVolume = ', tVolume2, ', totalWeight = ', tWeight, ' WHER
E locationID = ', lid);
    ->                  SELECT @s;
    ->                  SET @s = CONCAT('UPDATE locations SET totalPacketCount =
 ' , tPieces , ', totalVolume = ', tVolume3, ', totalWeight = ', tWeight, ' WHER
E locationID = ', lid);
    ->                  SELECT @s;
    ->                  -- PREPARE stmt FROM @s;
    ->                  -- EXECUTE stmt;
    ->          END IF;
    ->  UNTIL done END REPEAT;
    -> END;
    -> //
Query OK, 0 rows affected, 1 warning (0.13 sec)

Query OK, 0 rows affected (0.17 sec)

mysql> DELIMITER ;
mysql>
mysql> CALL spExample1();
+------+---------+----------+----------+----------+---------+
| lid  | tPieces | tVolume1 | tVolume2 | tVolume3 | tWeight |
+------+---------+----------+----------+----------+---------+
|    1 |      40 |   101.36 |  101.360 |  101.360 |   0.000 |
+------+---------+----------+----------+----------+---------+
1 row in set (0.05 sec)

+-------------------------------------------------------------------------------
-----------------------------+
| @s
                             |
+-------------------------------------------------------------------------------
-----------------------------+
| UPDATE locations SET totalPacketCount = 40, totalVolume = 101.36, totalWeight
= 0.000 WHERE locationID = 1 |
+-------------------------------------------------------------------------------
-----------------------------+
1 row in set (0.05 sec)

+-------------------------------------------------------------------------------
------------------------------+
| @s
                              |
+-------------------------------------------------------------------------------
------------------------------+
| UPDATE locations SET totalPacketCount = 40, totalVolume = UPDATE , totalWeight
 = 0.000 WHERE locationID = 1 |
+-------------------------------------------------------------------------------
------------------------------+
1 row in set (0.06 sec)

+-------------------------------------------------------------------------------
------------------------------+
| @s
                              |
+-------------------------------------------------------------------------------
------------------------------+
| UPDATE locations SET totalPacketCount = 40, totalVolume = 101.360, totalWeight
 = 0.000 WHERE locationID = 1 |
+-------------------------------------------------------------------------------
------------------------------+
1 row in set (0.08 sec)

Query OK, 0 rows affected, 1 warning (0.09 sec)
[11 Nov 2008 5:08] Valeriy Kravchuk
Same result with 5.1.29.
[23 Jul 2009 15:18] Vasile Ceteras
Verified with 5.1.36-log 64bit on kubuntu 9.04 64bit

I noticed when I run the stored procedure for the second time, it returns the correct result.
[24 Jul 2009 8:31] Vasile Ceteras
I've tested this on 5.0.24, 5.0.45, 5.1.36, and for all these versions, the bug is reproduced only for the first call to the stored procedure after it has been created.
The next calls return correct results, and if I drop the procedure and create it again, it will return bad results only for the first call.

My suggested workaround is to call the stored procedure once after it's created.
[2 Apr 2010 6:21] Gleb Shchepa
Also see similar CONCAT_WS bug #50096.

Simplified test case:

DELIMITER //
CREATE PROCEDURE s1()
BEGIN
        DECLARE v1 DOUBLE(10,3);
        SET v1= 100;

        SET @s = CONCAT('########################################' , 40 , v1);
        SELECT @s;
END;//
DELIMITER ;

CALL s1();
CALL s1();
[2 Apr 2010 7:30] 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/104954

3441 Gleb Shchepa	2010-04-02
      Bug #40625: Concat fails on DOUBLE values in a Stored
                  Procedure, while DECIMAL works
      
      Selecting of the CONCAT(...<SP variable>...) result into
      a user variable may return wrong data.
      
      
      Item_func_concat::val_str contains a number of memory
      allocation-saving tricks. One of them concatenates
      strings inplace inserting the value of one string
      at the beginning of the other string. However,
      this trick didn't care about strings those points
      to the same data buffer: this is possible when
      a CONCAT() parameter is a stored procedure variable -
      Item_sp_variable::val_str() uses the intermediate
      Item_sp_variable::str_value field, where it may
      store a reference to an external buffer.
      
      
      The Item_func_concat::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
        Test case for the bug #40625.
     @ mysql-test/t/func_concat.test
        Test case for the bug #40625.
     @ sql/item_strfunc.cc
        Bug #40625: Concat fails on DOUBLE values in a Stored
                    Procedure, while DECIMAL works
        
        The Item_func_concat::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.
[2 Apr 2010 21:08] 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/104993

3442 Gleb Shchepa	2010-04-03
      Bug #40625: Concat fails on DOUBLE values in a Stored
                  Procedure, while DECIMAL works
      
      Selecting of the CONCAT(...<SP variable>...) result into
      a user variable may return wrong data.
      
      
      Item_func_concat::val_str contains a number of memory
      allocation-saving tricks. One of them concatenates
      strings inplace inserting the value of one string
      at the beginning of the other string. However,
      this trick didn't care about strings those points
      to the same data buffer: this is possible when
      a CONCAT() parameter is a stored procedure variable -
      Item_sp_variable::val_str() uses the intermediate
      Item_sp_variable::str_value field, where it may
      store a reference to an external buffer.
      
      
      The Item_func_concat::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
        Test case for the bug #40625.
     @ mysql-test/t/func_concat.test
        Test case for the bug #40625.
     @ sql/item_strfunc.cc
        Bug #40625: Concat fails on DOUBLE values in a Stored
                    Procedure, while DECIMAL works
        
        The Item_func_concat::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.
[6 Apr 2010 7:57] Bugs System
Pushed into 5.1.46 (revid:sergey.glukhov@sun.com-20100405111026-7kz1p8qlzglqgfmu) (version source revid:gshchepa@mysql.com-20100402203022-np3uklrf7bxb6i97) (merge vers: 5.1.46) (pib:16)
[15 Apr 2010 23:19] Paul Dubois
Noted in 5.1.46 changelog.

If the arguments to a CONCAT() call included a local routine
variable, selecting the return value into a user variable could
produce an incorrect result. 

Setting report to Need Merge pending push to Celosia.
[28 May 2010 6:06] Bugs System
Pushed into mysql-next-mr (revid:alik@sun.com-20100524190136-egaq7e8zgkwb9aqi) (version source revid:alik@sun.com-20100422150750-vp0n37kp9ywq5ghf) (pib:16)
[28 May 2010 6:34] Bugs System
Pushed into 6.0.14-alpha (revid:alik@sun.com-20100524190941-nuudpx60if25wsvx) (version source revid:alik@sun.com-20100422150658-fkhgnwwkyugtxrmu) (merge vers: 6.0.14-alpha) (pib:16)
[28 May 2010 7:02] Bugs System
Pushed into 5.5.5-m3 (revid:alik@sun.com-20100524185725-c8k5q7v60i5nix3t) (version source revid:alexey.kopytov@sun.com-20100411071742-a2o2anlcrj2bq14q) (merge vers: 5.5.4-m3) (pib:16)
[29 May 2010 15:05] Paul Dubois
Noted in 5.5.5, 6.0.14 changelogs.
[17 Jun 2010 12:11] Bugs System
Pushed into 5.1.47-ndb-7.0.16 (revid:martin.skold@mysql.com-20100617114014-bva0dy24yyd67697) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)
[17 Jun 2010 12:59] Bugs System
Pushed into 5.1.47-ndb-6.2.19 (revid:martin.skold@mysql.com-20100617115448-idrbic6gbki37h1c) (version source revid:martin.skold@mysql.com-20100609211156-tsac5qhw951miwtt) (merge vers: 5.1.46-ndb-6.2.19) (pib:16)
[17 Jun 2010 13:39] Bugs System
Pushed into 5.1.47-ndb-6.3.35 (revid:martin.skold@mysql.com-20100617114611-61aqbb52j752y116) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)