Bug #106554 Unexpected behavior in UPDATE statement
Submitted: 24 Feb 2022 7:07 Modified: 1 Mar 2022 8:15
Reporter: John Jove Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server: DML Severity:S2 (Serious)
Version:8.0.28, 5.7 OS:Ubuntu (20.04)
Assigned to: CPU Architecture:x86

[24 Feb 2022 7:07] John Jove
Description:
I try the statements in How to repeat part, where the UPDATE statement failed to update the value and threw a data truncate error. I think the UPDATE statement should succeed, as in the where clause 'IS NULL' is used, thus we only need to check whether the two arguments to the equality are NULL or not, without having to actually evaluate the equality.

How to repeat:
DROP TABLE IF EXISTS t0;
CREATE TABLE IF NOT EXISTS t0(c0 FLOAT);
INSERT INTO t0 VALUES (1);
UPDATE t0 SET c0 = 2  WHERE  (IF(1, 'a', 0) ^ (NULL BETWEEN t0.c0 AND NULL)) IS NULL;
[24 Feb 2022 7:33] MySQL Verification Team
Hello John Jove,

Thank you for the report and feedback.

regards,
Umesh
[1 Mar 2022 8:15] Roy Lyseng
Posted by developer:
 
This is considered not a bug.
MySQL is free to evaluate the arguments to the XOR operator, or evaluate whether the arguments are NULL.
The latter can be considered an optimization, but we have currently no intention of implementing that.