Bug #63112 Truncated incorrect DOUBLE value
Submitted: 5 Nov 2011 17:17 Modified: 6 Nov 2011 14:07
Reporter: Yurii Korotia Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server: Parser Severity:S3 (Non-critical)
Version: 5.5.12 MySQL Community Server OS:Microsoft Windows (x64 vista sp2)
Assigned to: CPU Architecture:Any

[5 Nov 2011 17:17] Yurii Korotia
Description:
we cannot update records in table. always see this error.
in fact, we update 1 record, after, every try is fail with message

"Truncated incorrect DOUBLE value"

other tries fail, success only with cast

How to repeat:
mysql> create database d;
mysql> use d;
mysql> create table t (id char(36), id2 varchar(36) null);
mysql> insert t values (1,null),(2,null),(3,1),(4,2);

mysql> select * from t;
+------+------+
| id   | id2  |
+------+------+
| 1    | NULL |
| 2    | NULL |
| 3    | 1    |
| 4    | 2    |
+------+------+
4 rows in set (0.00 sec)

=======
#1 1st time success

mysql> set @id = uuid();
mysql> update t set id = @id where id = 1;
mysql> update t set id2 = @id where id2 = 1;

mysql> select * from t;
+--------------------------------------+--------------------------------------+
| id                                   | id2                                  |
+--------------------------------------+--------------------------------------+
| 72cf23f9-07d1-11e1-aa22-001583c688df | NULL                                 |
| 2                                    | NULL                                 |
| 3                                    | 72cf23f9-07d1-11e1-aa22-001583c688df |
| 4                                    | 2                                    |
+--------------------------------------+--------------------------------------+
4 rows in set (0.00 sec)

============
#2 other tries fail
mysql> set @id = uuid();
Query OK, 0 rows affected (0.00 sec)

mysql> update t set id = @id where id = 2
    -> ;
ERROR 1292 (22007): Truncated incorrect DOUBLE value: '72cf23f9-07d1-11e1-aa22-0
01583c688df
   '

================
#3 success only with cast
mysql> update t set id = @id where id = cast(2 as char(36));
Query OK, 1 row affected (0.02 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql>
[6 Nov 2011 8:43] Peter Laursen
You are probably running 'strict mode'?  In non-strict mode I get warnings but the UPDATEs actually do perform.  

I also noticed same behaviour on 5.1 and 5.5 server. With 5.0 there is one more strange observaation. 

Also user variables are not required to reproduce this:

-- simplified test case with no user variable
SET SQL_MODE = '';
CREATE DATABASE d;
USE d;
DROP TABLE IF EXISTS t;
CREATE TABLE t (id CHAR(36), id2 VARCHAR(36) NULL);
INSERT t VALUES (1,NULL),(2,NULL),(3,1),(4,2);
UPDATE t SET id = UUID() WHERE id = 1;
UPDATE t SET id = UUID() WHERE id = 2; -- same with any number replacing 2
SHOW WARNINGS; 
/* returns the warning ONCE on 5.1 and 5.5 but TWICE on 5.0

Warning Code : 1292
Truncated incorrect DOUBLE value: 'e71a018f-0854-11e1-a13f-7d6ca803186e 
*/
SELECT * FROM t; -- see that UPDATE actually did happen
UPDATE t SET id = UUID(); -- success with no WHERE clause

-- test case with one row in column
DROP TABLE IF EXISTS t;
CREATE TABLE t (id CHAR(36) NULL);
INSERT t VALUES (1),(2),(3),(4);
UPDATE t SET id = UUID() WHERE id = 1;
UPDATE t SET id = UUID() WHERE id = 2; 
SHOW WARNINGS; 
/* returns the warning ONCE

Warning Code : 1292
Truncated incorrect DOUBLE value: 'e71a018f-0854-11e1-a13f-7d6ca803186e 
*/
SELECT * FROM t: -- see that UPDATE actually did happen
UPDATE t SET id = UUID(); -- succes

So it is the comparison in the WHERE clause that causes this.  An integer is being compared with a string and both are converted to double for the comparison.  I think this is expected.  But why no error/warning on the first UPDATE? And why *TWO* warnings on the two-column table in MySQL 5.0 as the WHERE condition is on a single column. 

I don't have the answer.  Just added my observations!

Peter
(not a MySQL person)
[6 Nov 2011 9:02] Peter Laursen
UUID() function also not required.  This is probably most simple test case:

SET SQL_MODE = '';
DROP TABLE IF EXISTS t;
CREATE TABLE t (id CHAR(36) NULL);
INSERT t VALUES (1),(2),(3),(4);
UPDATE t SET id = 'a' WHERE id = 1;
SHOW WARNINGS -- empty set
UPDATE t SET id = 'b' WHERE id = 2; 
SHOW WARNINGS; 
/* returns

Warning Code : 1292
Truncated incorrect DOUBLE value: 'a 
*/
SELECT * FROM t;
/* returns

id    
------
a     
b     
3     
4     
*/

Obviously quoting the numbers in WHERE clause a strings will solve this:

SET SQL_MODE = '';
DROP TABLE IF EXISTS t;
CREATE TABLE t (id CHAR(36) NULL);
INSERT t VALUES (1),(2),(3),(4);
UPDATE t SET id = 'a' WHERE id = '1';
SHOW WARNINGS; -- empty set
UPDATE t SET id = 'b' WHERE id = '2'; 
SHOW WARNINGS; -- empty set

Here "WHERE id = '2'" will compare two strings and no internal casting occurs.  But I still do not understand why there is no error/warning on the first update when not quoting.
[6 Nov 2011 9:17] Peter Laursen
Not a bug!

See this:

SET SQL_MODE = '';
DROP TABLE IF EXISTS t;
CREATE TABLE t (id CHAR(36) NULL);
INSERT t VALUES (1),(2),(3),(4);
UPDATE t SET id = 'a' WHERE id = 1;
SHOW WARNINGS; -- empty set
UPDATE t SET id = 'b' WHERE id = 2; 
SHOW WARNINGS; 
/* ONE warning

Warning Code : 1292
Truncated incorrect DOUBLE value: 'a 
*/
UPDATE t SET id = 'c' WHERE id = 3; 
SHOW WARNINGS; 
/* TWO warnings

Warning Code : 1292
Truncated incorrect DOUBLE value: 'a 
*/
SELECT * FROM t;
/* returns

id    
------
a     
b     
3     
4     
*/

The WHERE clause will compare with `id`column for every row in the table.  First UPDATE finds only numbers --> no warning
Second UPDATE finds 3 numbers and the string 'a' --> one warning
Second UPDATE finds 2 numbers and the strings 'a' and 'b' --> two warnings

.. So it seems that with integers 1,2,3,4 stored in a string column the internal cast to DOUBLE does not truncate and thus no warning/error

The correct way to write the WHERE clause is 

WHERE id = 'string'

.. because the column is a string column.
[6 Nov 2011 9:22] Peter Laursen
and one more detail:

INSERT t VALUES (1),(2),(3),(4);

could be written as 

INSERT t VALUES ('1'),('2'),('3'),('4');

.. what would be logically more correct when inserting to a string column.  But MySQL does not care about that. Other RDBMS would!
[6 Nov 2011 10:13] Yurii Korotia
uuid was used as I used it to convert ids to guids, so it was left as it was
when this error appeared.

thanks Peter for answer. I thought mysql should convert number to string and make comparition. As you may see, when we use @some_variable we cannot quote it as string. Or can we? I don't know.

About insert values('1','2') not values(1,2). Mysql has inserted it well both ways.

As for me, it is a bug. But as previous my report was called as mysql specific, this probably will be called same way.

What was expected when we use
UPDATE t SET id = 'c' WHERE id = 3;

1. mysql looks for type of column. sees char(36)
2. converts 3 to '3            ...'. IN FACT, it was converted to '3' which is wrong, or not?
3. compare strings 'c' == '3'
4. update id

As new question appeared, is it correct that char(36) contains only '3' without 35 trailing spaces?
[6 Nov 2011 10:16] Yurii Korotia
3. compare strings 'c' == '3'
compare id == '3'
[6 Nov 2011 12:48] Peter Laursen
SELECT orange > banana;

Is this true or false? Well .. most often a banana is longer than an orange.  So if lenght is the criteria the above statement is false.  But most often an orange is heavier than a banana, so if weight is the criteria the above statement is true.

When comparing different types (orange versus banana, string versus integer) some 'criteria' or 'rule' will have to be used.  When MySQL compares different datatypes they are bost cast to DOUBLE internally before comparison. The integer "1" casts to the double "1" - the string 'a' or 'UUID()' casts to the double "0".

I was confused by your report as your original test case used user variables and the UUID() function - but the issue here is not related to any of those. I had to work out a simplied test case eliminating those to understand the basics of this. 

To avoid casting to DOUBLE internally ensure that the values compared are same datatype. In your case the `id` columns are strings and the value you compare with should also be strings (not 1 but '1' - not 2 but '2' etc.) or use cast()/convert() in your statement as you also figured out yourself.
[6 Nov 2011 13:30] Peter Laursen
http://dev.mysql.com/doc/refman/5.5/en/char.html

"The CHAR and VARCHAR types .. differ .. in whether trailing spaces are retained. When CHAR (not VARCHAR - my comment) values are stored, they are right-padded with spaces to the specified length. When CHAR (not varchar - my comment) values are retrieved, trailing spaces are removed unless the PAD_CHAR_TO_FULL_LENGTH SQL mode is enabled."

SET sql_mode = ''

USE test;
DROP TABLE IF EXISTS trailingblanks;
CREATE TABLE trailingblanks (id INT, str1 CHAR (2), str2 VARCHAR(2));
INSERT INTO trailingblanks VALUES (1, 'a', 'a');
INSERT INTO trailingblanks VALUES (2, CONCAT('a', X'20'), CONCAT('a', X'20')); -- space character
INSERT INTO trailingblanks VALUES (3, CONCAT('a', X'00'), CONCAT('a', X'20')); -- null character

SELECT * FROM trailingblanks;
/* returns

    id  str1    str2  
------  ------  ------
     1  a       a     
     2  a       a     
     3  a       a    
*/

SELECT id, HEX(str1), HEX(str2) FROM trailingblanks;
/* returns

    id  hex(str1)  hex(str2)
------  ---------  ---------
     1  61         61       
     2  61         6120     
     3  6100       6120       
*/

SET sql_mode = 'pad_char_to_full_length';

USE test;
DROP TABLE IF EXISTS trailingblanks;
CREATE TABLE trailingblanks (id INT, str1 CHAR (2), str2 VARCHAR(2));
INSERT INTO trailingblanks VALUES (1, 'a', 'a');
INSERT INTO trailingblanks VALUES (2, CONCAT('a', X'20'), CONCAT('a', X'20')); -- space character
INSERT INTO trailingblanks VALUES (3, CONCAT('a', X'00'), CONCAT('a', X'20')); -- null character

SELECT * FROM trailingblanks;
/* returns

    id  str1    str2  
------  ------  ------
     1  a       a     
     2  a       a     
     3  a       a    
*/

SELECT id, HEX(str1), HEX(str2) FROM trailingblanks;
/* returns

    id  HEX(str1)  HEX(str2)
------  ---------  ---------
     1  6120       61       
     2  6120       6120     
     3  6100       6120          
*/
[6 Nov 2011 14:07] Yurii Korotia
i got it
[6 Nov 2011 14:08] Peter Laursen
something messed up wiht my last post! sorry! :-(

This should be enough 8and also adding TEXT type to the case):

SET sql_mode = '';

USE test;
DROP TABLE IF EXISTS trailingblanks;
CREATE TABLE trailingblanks (id INT, str1 CHAR (2), str2 VARCHAR(2), str3 TEXT);
INSERT INTO trailingblanks VALUES (1, 'a', 'a', 'a');
INSERT INTO trailingblanks VALUES (2, CONCAT('a', X'20'), CONCAT('a', X'20'), CONCAT('a', X'20'));

SELECT id, HEX(str1), HEX(str2), HEX(str3) FROM trailingblanks;
/* returns

    id  hex(str1)  hex(str2)  hex(str3)
------  ---------  ---------  ---------
     1  61         61         61       
     2  61         6120       6120             
*/

SET sql_mode = 'pad_char_to_full_length';

USE test;
DROP TABLE IF EXISTS trailingblanks;
CREATE TABLE trailingblanks (id INT, str1 CHAR (2), str2 VARCHAR(2), str3 TEXT);
INSERT INTO trailingblanks VALUES (1, 'a', 'a', 'a');
INSERT INTO trailingblanks VALUES (2, CONCAT('a', X'20'), CONCAT('a', X'20'), CONCAT('a', X'20'));

SELECT id, HEX(str1), HEX(str2), HEX(str3) FROM trailingblanks;
/* returns

    id  HEX(str1)  HEX(str2)  hex(str3)
------  ---------  ---------  ---------
     1  6120       61         61       
     2  6120       6120       6120          
*/

Note that HEX() on the CHAR column shows that the trailing space is stored if (and only if) the 'pad_char_to_full length is SET - and that is true no matter if you INSERT that trailing space or not.  VARCHAR and TEXT columns will INSERT (only) what trailing spaces you specify.