MySQL Bugs: #105773: Bad caching of converted constants in NULL-safe comparison

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 (OCA)	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

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.

Hello Vicent Marti,

Thank you for the report and feedback.

regards,
Umesh

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.

Bug#106611 Nested Null-Safe Equal gives wrong result

was marked as a duplicate.