Bug #111408 select returns incorrect values ​​when using both operator <=> and operator and
Submitted: 14 Jun 2023 1:57 Modified: 14 Jun 2023 3:09
Reporter: yijun xie Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Operator Severity:S3 (Non-critical)
Version:8.0.22and8.0.32 OS:Any
Assigned to: CPU Architecture:Any
Tags: <=>

[14 Jun 2023 1:57] yijun xie 
Description:
select returns incorrect values ​​when using both operator <=> and operator and

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;

expected result:
The second select sql execution result is empty.

actual results:
The second select sql execution result is not empty.
+------------+
|  date_col  | 
+------------+
| 1998-11-03 |
+------------+
[14 Jun 2023 3:09] yijun xie 
this problem is same as Bug #111397.
a repeat problem.