Bug #46948 any incorrect update sql is executed strangely
Submitted: 27 Aug 2009 2:34 Modified: 27 Aug 2009 3:52
Reporter: ws lee Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server Severity:S3 (Non-critical)
Version:5.1.37 ( maybe all version) OS:Any
Assigned to: CPU Architecture:Any

[27 Aug 2009 2:34] ws lee
Description:
any incorrect update sql is executed strangely

How to repeat:
1. firstly, i have set.
[MYSQLD]
default_storage_engine=innodb
sql-mode="STRICT_TRANS_TABLES"

2.
mysql> create table test(id int, a int, b int);
Query OK, 0 rows affected (0.63 sec)

mysql> insert into test values(1,100,0);
Query OK, 1 row affected (0.03 sec)

mysql> select * from test;
+------+------+------+
| id   | a    | b    |
+------+------+------+
|    1 |  100 |    0 | 
+------+------+------+
1 row in set (0.00 sec)

mysql> update test set a=101 and b=1 where id=1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from test;
+------+------+------+
| id   | a    | b    |
+------+------+------+
|    1 |    0 |    0 | 
+------+------+------+
1 row in set (0.00 sec)

I have set sql-mode="STRICT_TRANS_TABLES".
Although this SQL "update test set a=101 and b=1 where id=1;" is inncorrect,
it was executed nothing warning.

and "a" column is updated by not expected value.
[27 Aug 2009 3:52] Valeriy Kravchuk
This is NOT a bug. Your 

update test set a=101 and b=1 where id=1

is interpreted as

update test set a=(101 and b=1) where id=1

Check http://dev.mysql.com/doc/refman/5.1/en/logical-operators.html and UPDATE syntax.