Bug #46948 any incorrect update sql is executed strangely

• Submitted: 27 Aug 2009 2:34
• Modified: 27 Aug 2009 3:52
• Reporter: ws lee
• Status: Not a Bug
• Category: MySQL Server
• Severity: S3 (Non-critical)
• Version: 5.1.37 ( maybe all version)
• OS: Any
• 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.