Bug #91892 | Update Statement with syntax errors executing updating all rows in table | ||
---|---|---|---|
Submitted: | 3 Aug 2018 14:23 | Modified: | 4 Aug 2018 11:47 |
Reporter: | Prasad Anu | Email Updates: | |
Status: | Not a Bug | Impact on me: | |
Category: | MySQL Server: Parser | Severity: | S1 (Critical) |
Version: | latest | OS: | Any |
Assigned to: | CPU Architecture: | Any |
[3 Aug 2018 14:23]
Prasad Anu
[4 Aug 2018 10:35]
MySQL Verification Team
Hello Prasad, Thank you for the report. Imho this is not a bug. Your update query "update <table> set <columnName>=<value> in (1,2,3) is actually interpreted and executed as "update <table> set <columnName>=(<value> in (1,2,3) )" Thanks to my senior colleague Shane, who provided a test case to demonstrate this: mysql> drop table if exists t; Query OK, 0 rows affected (0.06 sec) mysql> create table t(a int)engine=innodb; Query OK, 0 rows affected (0.00 sec) mysql> insert into t values(0),(1),(2),(3),(4),(5); Query OK, 6 rows affected (0.00 sec) Records: 6 Duplicates: 0 Warnings: 0 mysql> select * from t; +------+ | a | +------+ | 0 | | 1 | | 2 | | 3 | | 4 | | 5 | +------+ 6 rows in set (0.00 sec) mysql> select 1 in(1,2) from t; +-----------+ | 1 in(1,2) | +-----------+ | 1 | | 1 | | 1 | | 1 | | 1 | | 1 | +-----------+ 6 rows in set (0.00 sec) mysql> update t set a=(1 in(1,2)); Query OK, 5 rows affected (0.00 sec) Rows matched: 6 Changed: 5 Warnings: 0 mysql> select * from t; +------+ | a | +------+ | 1 | | 1 | | 1 | | 1 | | 1 | | 1 | +------+ 6 rows in set (0.00 sec) thanks, Umesh
[4 Aug 2018 11:47]
Prasad Anu
Umesh, Thanks for looking at this issue. How could an SQL statement will be interpreted to wrong way and update the value in DB. according to SQL standards this DML should look at appropriate syntax and execute based on valid statement or stop execution and throw error. and it did the same when i'm using select statement as per your example please try to run the below command and look at what happend: select * from t in (1,2); -- this is giving a syntax errors update t set a=2 in (1,2); -- but this update all value in table to 2 without giving an syntax errors. i did verified the same in MsSQL and its throwing erros. I can demonstrate the same from my end if you need. please setup a call to look at this together. Please do verify and let me know if you need any further information Thanks Prasad
[6 Aug 2018 9:34]
MySQL Verification Team
Imho syntactically the expression is correct i.e <columnName>=<value> in (1,2,3), and hence no parsing error for update <table> set <columnName>=<value> in (1,2,3). Also, please see Bug #82647 , Bug #7068 , Bug #19398 , Bug #26405