MySQL Bugs: #103218: Comparing datetime with year doesn't result in sensible result nor warning/error

Bug #103218	Comparing datetime with year doesn't result in sensible result nor warning/error
Submitted:	6 Apr 2021 9:02	Modified:	9 Apr 2021 12:38
Reporter:	Zhifeng Hu	Email Updates:
Status:	In progress	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:		OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
When comparing datetime with year, for example

CREATE TABLE `t1` (
  `a` timestamp NULL DEFAULT NULL,
  `b` year(4) DEFAULT NULL,
  KEY `a` (`a`),
  KEY `b` (`b`)
);
insert into t1 values("2002-10-03 04:28:53",2000);
insert into t1 values("2002-10-03 04:28:53",2002);
select * from t1 x cross join t1 y on x.a=y.b;
select * from t1 where t1.a = t1.b;

the 2 queries all give empty set without any error. I am expecting:

1. Either mysql correctly converts datetime to year implicitly (just like YEAR() is inserted automatically)
2. Or warning/error is reported that MySQL won't compare datetime with year

How to repeat:
CREATE TABLE `t1` (
  `a` timestamp NULL DEFAULT NULL,
  `b` year(4) DEFAULT NULL,
  KEY `a` (`a`),
  KEY `b` (`b`)
);
insert into t1 values("2002-10-03 04:28:53",2000);
insert into t1 values("2002-10-03 04:28:53",2002);
select * from t1 x cross join t1 y on x.a=y.b;
select * from t1 where t1.a = t1.b;

Suggested fix:
1. Either mysql correctly converts datetime to year implicitly (just like YEAR() is inserted automatically)
2. Or warning/error is reported that MySQL won't compare datetime with year

Hi Mr. Hu,

Thank you for your bug report.

If you run SHOW WARNINGS command immediately after each of the SELECT queries, do you get any report ???

Also, you did not specify which version / release are you using ???

Waiting on your feedback.

Hi Mr. Hu,

This is just to inform you that this comparisons will have improved warnings in one of the next releases of 8.0.