Bug #60607 UPDATE Incorrect syntax indicates success but fails

• Submitted: 23 Mar 2011 20:04
• Modified: 23 Mar 2011 20:37
• Reporter: Tobin Cataldo
• Status: Duplicate
• Category: MySQL Server: DML
• Severity: S3 (Non-critical)
• Version: 5.5.10
• OS: Any
• CPU Architecture: Any
• Tags: multiple updates, UPDATE

[23 Mar 2011 20:04] Tobin Cataldo

Description:
An UPDATE statement with an incorrect syntax indicates success but is a failure.

UPDATE `t`
SET `c1`= 1 AND `c2` = 2 
WHERE ... 

Returns "Query OK" 

mysql> 
mysql> UPDATE `test` SET `id` = 3 AND `id2` = 4 WHERE `id` = 1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> 
mysql> SELECT * FROM `test`;
+------+------+
| id   | id2  |
+------+------+
|    0 |    2 |
+------+------+
1 row in set (0.00 sec)

How to repeat:
DROP TABLE `test`;

CREATE TABLE `test` (
 `id` int(11),
 `id2` int(11)
) ENGINE=InnoDB;

INSERT INTO `test` (`id`,`id2`) VALUES (1,2);
SELECT * FROM `test`;
UPDATE `test` SET `id2` = 4 WHERE `id`= 1;
UPDATE `test` SET `id` = 3 WHERE `id` = 1;
SELECT * FROM `test`;
UPDATE `test` SET `id` = 1, `id2` = 2 WHERE `id` = 3;
SELECT * FROM `test`;
UPDATE `test` SET `id` = 3 AND `id2` = 4 WHERE `id` = 1;
SELECT * FROM `test`;

Suggested fix:
The documentation is good. But should this statement fire an exception?

[23 Mar 2011 20:37] MySQL Verification Team

Thank you for the bug report. Duplicate of bug: http://bugs.mysql.com/bug.php?id=6583.