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:
None 
Category:MySQL Server: Parser Severity:S2 (Serious)
Version:5.6.25 OS:CentOS
Assigned to: CPU Architecture:Any

[25 Jan 2017 7:58] Csaba Aranyi
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.
[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.