Bug #82990 Multiple UPDATE query separated by 'AND' not detected Syntax Error
Submitted: 14 Sep 2016 12:29 Modified: 14 Sep 2016 13:44
Reporter: Bruno Borghi ciborghi Email Updates:
Status: Duplicate Impact on me:
None 
Category:MySQL Server: Parser Severity:S3 (Non-critical)
Version: OS:Any
Assigned to: CPU Architecture:Any
Tags: Error not detected, syntax error, UPDATE, UPDATE multiple columns

[14 Sep 2016 12:29] Bruno Borghi ciborghi
Description:
Hi guys,

I have a table with this strutucture:

----------------------------------------------------------------------
CREATE TABLE `tb_product_price` (
  `id_product_price` int(11) NOT NULL AUTO_INCREMENT,
  `mn_price_1` decimal(10,2) NOT NULL,
  `mn_price_2` decimal(10,2) NOT NULL,
  `mn_price_3` decimal(10,2) NOT NULL,
  PRIMARY KEY (`id_product_price`),
  KEY `mn_price_1` (`mn_price_1`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=latin1;
----------------------------------------------------------------------

And i execute this query to multiple update three columns:

----------------------------------------------------------------------
UPDATE tb_product_price SET mn_price_1 = 'X' AND mn_price_2 = 'Y' AND mn_price_3 = 'Z' WHERE id_product_price = N
----------------------------------------------------------------------

This query was wrong (why multiple columns update are separated by comma), but the MYSQL not detected this syntax error and update mn_price_1 with '1.00' value, why this expression "AND mn_price_2 = 'Y' AND mn_price_3 = 'Z'" return TRUE, because the values ​​for these fields are the same as registered in the table (otherwise it returns FALSE, and the return value would be zero).

Both the query via terminal (using mysql commands), as the query via MySQL Workbench not return errors, and I believed that the update was done correctly.

I tested this in:

- OS Linux Ubuntu 14.04LTS with MySQL Server 5.5.50-0ubuntu0.14.04.1 (local)

- OS Linux Debian 6 64bits with MySQL Server 5.6.30-76.3-56-log Percona XtraDB Cluster (GPL), Release rel76.3, Revision aa929cb, WSREP version 25.16, wsrep_25.16 (production)

How to repeat:
For example to repeat this "error" :

--------------------------------------------------------------------------

CREATE TABLE `tb_product_price` (
  `id_product_price` int(11) NOT NULL AUTO_INCREMENT,
  `mn_price_1` decimal(10,2) NOT NULL,
  `mn_price_2` decimal(10,2) NOT NULL,
  `mn_price_3` decimal(10,2) NOT NULL,
  PRIMARY KEY (`id_product_price`),
  KEY `mn_price_1` (`mn_price_1`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=latin1;

--------------------------------------------------------------------------

INSERT INTO tb_product_price (id_product_price, mn_price_1, mn_price_2, mn_price_3) VALUES (1, 10, 20, 30);

--------------------------------------------------------------------------

UPDATE tb_product_price SET mn_price_1 = '15' AND mn_price_2 = '20' AND mn_price_3 = '30' WHERE id_product_price = 1

--------------------------------------------------------------------------

And looks the new mn_price_1 value... It's a 1.00, because mn_price_2 = '20' AND mn_price_3 = '30' are TRUE. And not detected syntax error.

Suggested fix:
Block the multiple column UPDATE query using a 'AND' separator with Syntax Error.
[14 Sep 2016 13:44] MySQL Verification Team
Thank you for the bug report. Duplicate of bug: http://bugs.mysql.com/bug.php?id=6583.