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.
