| Bug #106611 | Nested Null-Safe Equal gives wrong result | ||
|---|---|---|---|
| Submitted: | 1 Mar 2022 13:20 | Modified: | 2 Mar 2022 11:26 |
| Reporter: | Shanshan Ying | Email Updates: | |
| Status: | Duplicate | Impact on me: | |
| Category: | MySQL Server: Optimizer | Severity: | S2 (Serious) |
| Version: | 8.0.27, 8.0.28 | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
[1 Mar 2022 13:23]
Shanshan Ying
Item_equal --> Item_func_equal
[1 Mar 2022 13:25]
MySQL Verification Team
Hello Shanshan Ying, Thank you for the report and test case. regards, Umesh
[2 Mar 2022 8:21]
Shanshan Ying
Here I give a simplified example how I noticed this bug.
While visiting the same table in differnt order (e.g., using differnt index), we the find the result differs. To make it easy to repeat, I create two tables of the same schema, but insert value `NULL` in differnt ordering.
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
example starts here:
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
mysql [localhost:8027] {msandbox} (test) > show create table t1;
+-------+------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+------------------------------------------------------------------------------------------------------------------------+
| t1 | CREATE TABLE `t1` (
`a` varchar(100) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |
+-------+------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql [localhost:8027] {msandbox} (test) > show create table t2;
+-------+------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+------------------------------------------------------------------------------------------------------------------------+
| t2 | CREATE TABLE `t2` (
`a` varchar(100) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |
+-------+------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql [localhost:8027] {msandbox} (test) > select * from t1;
+------+
| a |
+------+
| NULL |
| |
+------+
2 rows in set (0.00 sec)
mysql [localhost:8027] {msandbox} (test) > select * from t2;
+------+
| a |
+------+
| |
| NULL |
+------+
2 rows in set (0.00 sec)
mysql [localhost:8027] {msandbox} (test) > select a, ((NULLIF(a, "abc"))<=>(0<=>NULL)) from t1;
+------+-----------------------------------+
| a | ((NULLIF(a, "abc"))<=>(0<=>NULL)) |
+------+-----------------------------------+
| NULL | 1 |
| | 1 |
+------+-----------------------------------+
2 rows in set (0.00 sec)
mysql [localhost:8027] {msandbox} (test) > select a, ((NULLIF(a, "abc"))<=>(0<=>NULL)) from t2;
+------+-----------------------------------+
| a | ((NULLIF(a, "abc"))<=>(0<=>NULL)) |
+------+-----------------------------------+
| | 1 |
| NULL | 0 |
+------+-----------------------------------+
2 rows in set (0.00 sec)
[2 Mar 2022 11:26]
Erlend Dahl
Duplicate of Bug#105773 Bad caching of converted constants in NULL-safe comparison which has been fixed in the upcoming 8.0.29.
Description: It is clear that `0 <=> NULL` returns 0 (or false), and vice versa. It is expected that `(NULL <=> (0 <=> NULL))` gives 0 (or false) How to repeat: connect o mysql 8027, and execute: mysql [localhost:8027] {msandbox} ((none)) > select (NULL <=> (0 <=> NULL)); +-------------------------+ | (NULL <=> (0 <=> NULL)) | +-------------------------+ | 1 | +-------------------------+ 1 row in set (0.00 sec) Execute this simple query on some earlier version，e.g. use 5.7.34 here mysql [localhost:5734] {msandbox} ((none)) > select (NULL <=> (0 <=> NULL)); +-------------------------+ | (NULL <=> (0 <=> NULL)) | +-------------------------+ | 0 | +-------------------------+ 1 row in set (0.00 sec) Suggested fix: Item_equal extends Item_func_comparison. But, `Item_equal` is never nullable. do not use `Item_func_comparison::is_null()` to check if some Item_equal instance is nullable.