| Bug #84659 | Truncated incorrect DOUBLE value: | ||
|---|---|---|---|
| Submitted: | 25 Jan 2017 7:58 | Modified: | 25 Jan 2017 11:17 |
| Reporter: | Csaba Aranyi | Email Updates: | |
| Status: | Not a Bug | Impact on me: | |
| Category: | MySQL Server: Parser | Severity: | S2 (Serious) |
| Version: | 5.6.25 | OS: | CentOS |
| Assigned to: | CPU Architecture: | Any | |
[25 Jan 2017 8:11]
MySQL Verification Team
Hello Csaba Aranyi,
Thank you for the bug report.
Imho this is not a bug. Your update query "update test set a='1,2' and b='ccc' where id = 1" is actually interpreted and executed as "update test set a=('1,2' and b='ccc') where id = 1.
In your case
root@localhost [db1]> select ('1,2' and 'aaa'='ccc');
+-------------------------+
| ('1,2' and 'aaa'='ccc') |
+-------------------------+
| 0 |
+-------------------------+
1 row in set, 1 warning (0.00 sec)
root@localhost [db1]> update test set a='1,2' and b='ccc' where id = 1;
Query OK, 1 row affected, 1 warning (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 1
root@localhost [db1]> select * from test;
+------+-------+------+
| id | a | b |
+------+-------+------+
| 1 | 0 | aaa |
| 2 | 3,2,1 | bbb |
+------+-------+------+
2 rows in set (0.00 sec)
Also, see Bug #82647, Bug #19398, Bug #7068
Thanks,
Umesh
[25 Jan 2017 11:17]
Csaba Aranyi
I totally understand your explanation, but I'm not sure that is a right approach. Form my point of view, the query is ambiguous and shouldn't be executed. I have checked against Oracle 11.2.0.4 and the query doesn't executed.
Description: I'm trying to run an invalid update command and I receive a warning messages and all data in question are truncated by Mysql. In this way, is easy to destroy all data. The warning message is: Truncated incorrect DOUBLE value: (Warning Code : 1292) How to repeat: - Create a table CREATE TABLE `test` ( `id` bigint(20) DEFAULT NULL, `a` varchar(255) DEFAULT NULL, `b` varchar(255) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1 Put the following data in: insert into `test` (`id`, `a`, `b`) values('1','1,2,3','aaa'); insert into `test` (`id`, `a`, `b`) values('2','3,2,1','bbb'); The run a wrong update syntax: update test set a='1,2' and b='ccc' where id = 1 The outcome is a warning message and the data is truncated by Mysql. Output: Query OK, 1 row affected, 1 warning (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 1 mysql> select * from test; +------+-------+------+ | id | a | b | +------+-------+------+ | 1 | 0 | aaa | | 2 | 3,2,1 | bbb | +------+-------+------+ 2 rows in set (0.00 sec) ---------------------------------------------------------------------------- Full output: mysql> CREATE TABLE `test` ( -> `id` bigint(20) DEFAULT NULL, -> `a` varchar(255) DEFAULT NULL, -> `b` varchar(255) DEFAULT NULL -> ) ENGINE=InnoDB DEFAULT CHARSET=latin1; Query OK, 0 rows affected (0.01 sec) mysql> mysql> insert into `test` (`id`, `a`, `b`) values('1','1,2,3','aaa'); Query OK, 1 row affected (0.01 sec) mysql> insert into `test` (`id`, `a`, `b`) values('2','3,2,1','bbb'); Query OK, 1 row affected (0.01 sec) mysql> select * from test; +------+-------+------+ | id | a | b | +------+-------+------+ | 1 | 1,2,3 | aaa | | 2 | 3,2,1 | bbb | +------+-------+------+ 2 rows in set (0.00 sec) mysql> update test set a='1,2' and b='ccc' where id = 1 -> ; Query OK, 1 row affected, 1 warning (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 1 mysql> select * from test; +------+-------+------+ | id | a | b | +------+-------+------+ | 1 | 0 | aaa | | 2 | 3,2,1 | bbb | +------+-------+------+ 2 rows in set (0.00 sec) Suggested fix: Data shouldn't be affected in this case.