Bug #106612 Incorrect evaluation on short circuit expression
Submitted: 1 Mar 2022 17:14 Modified: 3 Mar 2022 2:58
Reporter: John Jove Email Updates:
Status: Duplicate Impact on me:
None 
Category:MySQL Server: DML Severity:S3 (Non-critical)
Version: OS:Ubuntu (20.04LTS)
Assigned to: CPU Architecture:x86

[1 Mar 2022 17:14] John Jove
Description:
I try the following statements in the how to repeat part. The UPDATE statement failed to execute and generated a data truncate error.

mysql> SHOW WARNINGS;
+-------+------+---------------------------------------+
| Level | Code | Message                               |
+-------+------+---------------------------------------+
| Error | 1292 | Truncated incorrect DOUBLE value: 'a' |
+-------+------+---------------------------------------+
1 row in set (0.00 sec)

I think there should be no errors, as the expression 0 AND ((t0.c0 != 'a') IS NULL) is a short circuit expression.

How to repeat:
DROP TABLE IF EXISTS t0;
CREATE TABLE t0 (c0 BIGINT);
INSERT INTO t0 VALUES (1);
UPDATE t0 SET c0 = 2 WHERE 0 AND ((t0.c0 != 'a') IS NULL);
SHOW WARNINGS;
[2 Mar 2022 14:27] MySQL Verification Team
Hi Mr. Jove,

Please, do not create duplicate reports in this forum.

This report is a duplicate of:

https://bugs.mysql.com/bug.php?id=106386

Duplicate.
[3 Mar 2022 2:58] John Jove
I think there is not a duplicate bug. 
In this case, AND is a short circuit operator. The expression 0 AND ((t0.c0 != 'a') IS NULL) can directly get the result from the left operand without computing the right part. And I try the statement: SELECT 0 AND 1/0; MySQL can handle this case properly with no warnings. There may be an optimization problem for the first expression.
[3 Mar 2022 13:54] MySQL Verification Team
SQL can not function like some programming languages , because of the handling of the NULL value. Your other example can not result in NULL. Optimising away complex expressions is not stipulated by the SQL standard.