MySQL Bugs: #91892: Update Statement with syntax errors executing updating all rows in table

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:	None
Category:	MySQL Server: Parser	Severity:	S1 (Critical)
Version:	latest	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
select * from <table> IN (1,2,3) is giving a syntex error to execute the statement

but the same thing for Update

Update <table> set <columnName>=<value> in (1,2,3) executing without any errors and update all values in table

is this is not a Bug?

Also, SQL newline char is not considering for the below scenarios:

select * from <table>
-- Update <table> set <columnName>=<value>
-- select * from <table>
in (1,2,3)

How to repeat:
follow the steps mentioned in Description of the problem.

Suggested fix:
update statement should throw an error when if a syntax error in the statement instead of update all rows.

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

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

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