Bug #82647 Why this SQL without Error message？update table_name set c1=1 and c2 = 2

• Submitted: 19 Aug 2016 9:34
• Modified: 19 Aug 2016 11:19
• Reporter: Wing Wing
• Status: Not a Bug
• Category: MySQL Server: Parser
• Severity: S3 (Non-critical)
• Version: 5.6.19
• OS: Any
• CPU Architecture: Any
• Tags: SQL

[19 Aug 2016 9:34] Wing Wing

Description:
The correct UPDATE SQL is :
update table_name set col2=value2,col3=values3 where col1 = values1

BUT:
update table_name set col2=value2 and col3=values3 where col1 = values1
this update SQL is error without error message , and the data which updated is error。

How to repeat:
ORIGINAL DATA:
create table t (id int,name varchar(16),num int);
insert into t values(1,"A",1),(2,"B",2),(3,"C",3),(4,"D",4),(5,"E",5);

mysql> select * from t;
+------+------+------+
| id   | name | num  |
+------+------+------+
|    1 | A    |    1 |
|    2 | B    |    2 |
|    3 | C    |    3 |
|    4 | D    |    4 |
|    5 | E    |    5 |
+------+------+------+
5 rows in set (0.00 sec)

THE CORRENT UPDATE SQL :
mysql> select * from t;
+------+------+------+
| id   | name | num  |
+------+------+------+
|    1 | A    |    1 |
|    2 | B    |    2 |
|    3 | C    |    3 |
|    4 | D    |    4 |
|    5 | E    |    5 |
+------+------+------+
5 rows in set (0.00 sec)

mysql> update t set name="AA",num=0 where id=1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from t;
+------+------+------+
| id   | name | num  |
+------+------+------+
|    1 | AA   |    0 |
|    2 | B    |    2 |
|    3 | C    |    3 |
|    4 | D    |    4 |
|    5 | E    |    5 |
+------+------+------+
5 rows in set (0.00 sec)

THE INCORRECT UPDATE SQL :
mysql> select * from t;
+------+------+------+
| id   | name | num  |
+------+------+------+
|    1 | A    |    1 |
|    2 | B    |    2 |
|    3 | C    |    3 |
|    4 | D    |    4 |
|    5 | E    |    5 |
+------+------+------+
5 rows in set (0.00 sec)

mysql> update t set name ="AA" and num =0 where id = 1;
Query OK, 1 row affected, 1 warning (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 1

mysql> select * from t;
+------+------+------+
| id   | name | num  |
+------+------+------+
|    1 | 0    |    1 |
|    2 | B    |    2 |
|    3 | C    |    3 |
|    4 | D    |    4 |
|    5 | E    |    5 |
+------+------+------+
5 rows in set (0.00 sec)

I think this incorrect update SQL should report an error rather than performing the wrong result.

[19 Aug 2016 11:19] MySQL Verification Team

Hello Wing,

Thank you for the bug report.
Imho this is not a bug. Your update query "update table_name set col2=value2 and col3=values3 where col1 = values1" is actually interpreted and executed as "update table_name set col2=(value2 and col3=values3) where col1 = values1"

For example in your test case:

mysql> create table t (id int,name varchar(16),num int);
Query OK, 0 rows affected (0.00 sec)

mysql> insert into t values(1,"A",1),(2,"B",2),(3,"C",3),(4,"D",4),(5,"E",5);
Query OK, 5 rows affected (0.00 sec)
Records: 5  Duplicates: 0  Warnings: 0

mysql> select * from t;
+------+------+------+
| id   | name | num  |
+------+------+------+
|    1 | A    |    1 |
|    2 | B    |    2 |
|    3 | C    |    3 |
|    4 | D    |    4 |
|    5 | E    |    5 |
+------+------+------+
5 rows in set (0.00 sec)

mysql> SELECT ("AA" and 1=0); <--- num=1
+----------------+
| ("AA" and 1=0) |
+----------------+
|              0 |
+----------------+
1 row in set, 1 warning (0.00 sec)

So, when you update t set name ="AA" and num =0 where id = 1;

mysql> update t set name ="AA" and num =0 where id = 1;
Query OK, 1 row affected, 1 warning (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 1

mysql> select * from t;
+------+------+------+
| id   | name | num  |
+------+------+------+
|    1 | 0    |    1 | <--- name is 0
|    2 | B    |    2 |
|    3 | C    |    3 |
|    4 | D    |    4 |
|    5 | E    |    5 |
+------+------+------+
5 rows in set (0.00 sec)

Thanks,
Umesh

[19 Aug 2016 11:25] MySQL Verification Team

Also, see Bug #19398, Bug #7068