| Bug #107125 | Weird statement with IS NULL and with IS NOT NULL | ||
|---|---|---|---|
| Submitted: | 26 Apr 2022 7:39 | Modified: | 27 Apr 2022 13:20 |
| Reporter: | John Jove | Email Updates: | |
| Status: | Not a Bug | Impact on me: | |
| Category: | MySQL Server: DML | Severity: | S3 (Non-critical) |
| Version: | 8.0.28 | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
[26 Apr 2022 8:00]
MySQL Verification Team
Hello John Jove, Thank you for the report and feedback. regards, Umesh
[27 Apr 2022 13:20]
Roy Lyseng
Posted by developer:
The behavior may seem peculiar, but it is not a bug.
IS NULL and IS NOT NULL have different, non-symmetric validation functions.
IS NULL checks its argument and sees that it can never be null and delivers FALSE always,
without attempting to evaluate CAST('a12' AS DOUBLE).
IS NOT NULL has a simpler validation function that always causes the argument to be evaluated,
hence it will cause an error because of the invalid CAST function.
This has also consequences for SELECT vs. UPDATE:
SELECT operates in non-strict mode and any error is converted to a warning.
UPDATE operates in strict mode and any error is reported as an error.
It could be reasonable to treat IS NOT NULL the same as IS NULL, though.
Description: WHERE clause with IS NULL is evaluated to false. UPDATE or DELETE statements with IS NULL do not return error or warning. WHERE clause with IS NOT NULL is evaluated to true, however, statements with IS NOT NULL return error. How to repeat: /* init */ DROP TABLE IF EXISTS t; /* init */ CREATE TABLE t(c1 VARCHAR(10)); /* init */ INSERT IGNORE INTO t(c1) VALUES ('try'); mysql> UPDATE t SET c1='test' WHERE (CAST(('a12') AS DOUBLE)) IS NULL; Query OK, 0 rows affected (0.00 sec) Rows matched: 0 Changed: 0 Warnings: 0 mysql> UPDATE t SET c1='test' WHERE (CAST(('a12') AS DOUBLE)) IS NOT NULL; ERROR 1292 (22007): Truncated incorrect DOUBLE value: 'a12' mysql> SELECT (CAST(('a12') AS DOUBLE)) IS NULL; +-----------------------------------+ | (CAST(('a12') AS DOUBLE)) IS NULL | +-----------------------------------+ | 0 | +-----------------------------------+ 1 row in set (0.00 sec) mysql> SELECT (CAST(('a12') AS DOUBLE)) IS NOT NULL; +---------------------------------------+ | (CAST(('a12') AS DOUBLE)) IS NOT NULL | +---------------------------------------+ | 1 | +---------------------------------------+ 1 row in set, 1 warning (0.00 sec)