| 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: | |
| Category: | MySQL Server: Parser | Severity: | S3 (Non-critical) |
| Version: | 5.5.12 MySQL Community Server | OS: | Windows (x64 vista sp2) |
| Assigned to: | CPU Architecture: | Any | |
[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.
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>