Bug #103218 Comparing datetime with year doesn't result in sensible result nor warning/error
Submitted: 6 Apr 9:02 Modified: 9 Apr 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

[6 Apr 9:02] Zhifeng Hu
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
[6 Apr 13:06] MySQL Verification Team
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.
[9 Apr 12:38] MySQL Verification Team
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.