Bug #105773 Bad caching of converted constants in NULL-safe comparison
Submitted: 2 Dec 2021 12:45 Modified: 21 Dec 2021 22:13
Reporter: Vicent Marti Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: DML Severity:S2 (Serious)
Version:8.0.27 OS:Any
Assigned to: CPU Architecture:Any
Tags: regression

[2 Dec 2021 12:45] Vicent Marti
Description:
In MySQL versions 8.0+, the following query returns a wrong result:

mysql> SELECT NULL <=> (0 <=> NULL);
+-----------------------+
| NULL <=> (0 <=> NULL) |
+-----------------------+
|                     1 |
+-----------------------+

The right side of the expression, (0 <=> NULL), should evaluate to 0. Then NULL <=> 0 should evaluate to 0 again. This does not happen because of converted constant caching. For the comparator in the higher level comparison, the one that compares `NULL` against `(0 <=> NULL)`, the right-side of the expression is cached in `Arg_comparator::set_cmp_func`, item_cmpfunc.cc:1233.

The call to `cache_converted_constant` for the right side of the comparison is supposed to resolve and cache the result of `(0 <=> NULL)`, but the returned `Item` is actually NULL (it has m_nullable=true and null_value=true). This cannot be the right cached result because the result of a comparison with the NULL-safe equality operator can never be NULL.

Since the cached result for the right-hand expression is NULL, the final evaluation of `NULL <=> NULL` returns true/1.

Attempting the same query in MySQL 5.7 returns the right result (false/0).

How to repeat:
Run this query in a MySQL 8.0+ instance:

mysql> SELECT NULL <=> (0 <=> NULL);
+-----------------------+
| NULL <=> (0 <=> NULL) |
+-----------------------+
|                     1 |
+-----------------------+

This is an issue with the evaluation engine and reproduces regardless of the connector/client being used.

Suggested fix:
I do not fully understand the behavior of `cache_converted_constant`, but it is clearly not returning the right cached value for the sub-expression in the right-hand side.
[2 Dec 2021 13:02] MySQL Verification Team
Hello Vicent Marti,

Thank you for the report and feedback.

regards,
Umesh
[21 Dec 2021 22:13] Christine Cole
Posted by developer:
 
Fixed as of the upcoming MySQL 8.0.29 release, and here's the proposed changelog entry from the documentation team:

A NULL-safe comparison between 0 (FALSE) and NULL could return TRUE, even
though the implementation responsible for evaluating an object of this
type can never result in NULL and should always return FALSE.

Thank you for the bug report.
[2 Mar 2022 11:27] Erlend Dahl
Bug#106611 Nested Null-Safe Equal gives wrong result

was marked as a duplicate.