Bug #111397 表达式进行<=>比较并将结果进行与操作，执行结果错误

• Submitted: 13 Jun 2023 13:32
• Modified: 13 Jun 2023 13:58
• Reporter: yijun xie
• Status: Verified
• Category: MySQL Server: Optimizer
• Severity: S3 (Non-critical)
• Version: 8.0
• OS: Any
• CPU Architecture: Any
• Tags: <=>执行结果错误

[13 Jun 2023 13:32] yijun xie

Description:
表达式进行<=>比较并将结果进行与操作，执行结果错误

How to repeat:
create table tb(date_col date);
insert into tb values('1998-11-03'),('1980-11-03'),('2008-08-08'),('2023-06-13');
SELECT date_col FROM tb GROUP BY 1 HAVING date_col <=> CAST('1998-02-03' AS YEAR) ORDER BY 1;
SELECT date_col FROM tb GROUP BY 1 HAVING date_col <=> CAST('1998-02-03' AS YEAR) and date_col <=> '1998-11-03' ORDER BY 1;

预期结果：第二条sql执行结果为空
实际结果：第二条sql执行结果非空

[13 Jun 2023 13:58] MySQL Verification Team

Hi Mr. xie,

Thank you for your bug report.

This is indeed a bug.

This is a result we have got:

+--------------------+
| ================== |
+--------------------+
| ================== |
+--------------------+
+------------+
| date_col   |
+------------+
| 1998-11-03 |
+------------+

That means that it is a bug , because HAVING condition has two comparisons, which are evaluated by logical AND expression. The result, however, is returned as if it was OR condition.

Usage of '<=>' comparator is irrelevant, since there are no NULLs in the values.

Verified as reported.

[14 Jun 2023 8:30] Tor Didriksen

Posted by developer:
 
google translate says:
The expression is compared with <=> and the result is ANDed, and the execution result is wrong