MySQL Bugs: #116782: Any necessary to restrict update stmt syntax ?

Bug #116782	Any necessary to restrict update stmt syntax ?
Submitted:	26 Nov 2024 9:07	Modified:	28 Nov 2024 11:38
Reporter:	ximin liang (OCA)	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: DML	Severity:	S4 (Feature request)
Version:	8.0	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
Hello MySQL team:
  This is a case about update syntax, see below:

How to repeat:
create table t1(c1 int, c2 int, c3 int);
insert into t1 values (1,1,1),(2,2,2);
update t1 set c2 = 2 and c3 = 3;
select * from t1;
+------+------+------+
| c1   | c2   | c3   |
+------+------+------+
|    1 |    0 |    1 |
|    2 |    0 |    2 |
+------+------+------+

Parser treats c2 = 2 and c3 = 3 as expr, but user may be misunderstanding, this sql update c2 to 2, c3 to 3. I wonder is there any necessary restrict such usage like Oracle ? In oracle, if execute:

SQL> update test set c2 = 100 and c3 = 1000;
update test set c2 = 100 and c3 = 1000
                         *
ERROR at line 1:
ORA-00933: SQL command not properly ended

Thanks.

Hi Mr. liang,

Thank you for your bug report.

However, this is not a bug, since your UPDATE syntax is wrong. All SQL Standards have specified very strict syntax for DMLs and your syntax is simply wrong and unacceptable.

However, this makes a good feature request.

Verified as a feature request, for versions 8.0 and higher.

This looks like a timeless issue, I met it in 2017 and blogged about it.

https://jfg-mysql.blogspot.com/2017/01/oracle-mysql-and-funny-replication-breakage-2017-01...

Posted by developer:
 
This may be a bit confusing, but the expression "100 and c3 = 1000" is valid in MySQL.
In fact, similar expressions are valid in standard SQL, provided that they work on
boolean values. In MySQL, we extend this so that any expressions can be converted
into a boolean expression, by interpreting a zero value as false and any other value as true.

Thank you, Roy .......