| 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: | |
| 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: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)
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.