| Bug #116846 | The logical error of the NULLIF function in the WHERE condition. | ||
|---|---|---|---|
| Submitted: | 2 Dec 2024 14:42 | Modified: | 4 Dec 2024 11:09 |
| Reporter: | wang jack | Email Updates: | |
| Status: | Verified | Impact on me: | |
| Category: | MySQL Server: Data Types | Severity: | S3 (Non-critical) |
| Version: | 8.0 | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
[2 Dec 2024 15:42]
MySQL Verification Team
Hi Mr. jack, Thank you for your bug report. However, it is not a bug. There are several reasons why this is not a bug, but we shall cite only two. First of all, USER() function always return the error. Second, you are mixing values of the different types in a single expression, which is not allowed by SQL Standard. You are mixing CHAR/VARCHAR with BOOLEAN. in your case, those two values have a common denominator as DOUBLE, which can not work as a BOOLEAN value. Not a bug.
[4 Dec 2024 11:09]
MySQL Verification Team
Hi Mr. jack, It turns out that your test case involves data types that we are covering by our conversions. Verified as reported.
Description: A predicate as a WHERE condition, if this query has results, then the value of this predicate should be true. When using the NULLIF function and the USER function, a logical inconsistency was encountered. I think this query result should be 1. mysql> SELECT -> ((NULLIF(USER(), (- (CAST((t0.c0) && (t0.c0) AS SIGNED))))) IS NULL) IS TRUE -> FROM -> t0 -> WHERE -> (NULLIF(USER(), (- (CAST((t0.c0) && (t0.c0) AS SIGNED))))) IS NULL; +------------------------------------------------------------------------------+ | ((NULLIF(USER(), (- (CAST((t0.c0) && (t0.c0) AS SIGNED))))) IS NULL) IS TRUE | +------------------------------------------------------------------------------+ | 0 | +------------------------------------------------------------------------------+ 1 row in set, 4 warnings (0.01 sec) Because the above query is essentially equivalent to the following: mysql> select (NULL IS NULL) is true where NULL IS NULL; +------------------------+ | (NULL IS NULL) is true | +------------------------+ | 1 | +------------------------+ 1 row in set (0.00 sec) How to repeat: execute below sqls drop table if exists t0; CREATE TABLE IF NOT EXISTS t0(c0 MEDIUMINT) ; INSERT INTO t0(c0) VALUES(11111); SELECT ((NULLIF(USER(), (- (CAST((t0.c0) && (t0.c0) AS SIGNED))))) IS NULL) IS TRUE FROM t0 WHERE (NULLIF(USER(), (- (CAST((t0.c0) && (t0.c0) AS SIGNED))))) IS NULL;