Different behave between MySQL and TiDB in query

[blacktear23]

Bug Report

1. Minimal reproduce step (Required)

create table t (id varchar(20) primary key);
insert into t values('asdf'), ('1asdf');

mysql> select * from t where id = timestamp'0000-00-00 00:00:00';
Empty set, 2 warnings (0.00 sec)

mysql> select * from t where id = date'0000-00-00';
ERROR 1292 (22007): Incorrect datetime value: '0000-00-00'

2. What did you expect to see? (Required)

In MySQL：

mysql> select * from t where id = timestamp'0000-00-00 00:00:00';
+-------+
| id    |
+-------+
| 1asdf |
| asdf  |
+-------+
2 rows in set, 2 warnings (0.00 sec)

mysql> select * from t where id = date'0000-00-00';
+-------+
| id    |
+-------+
| 1asdf |
| asdf  |
+-------+
2 rows in set, 2 warnings (0.00 sec)

3. What did you see instead (Required)

See section 1.

4. What is your TiDB version? (Required)

Release Version: v4.0.0-beta.2-1588-ge33271784-dirty
Edition: Community
Git Commit Hash: e332717
Git Branch: issue-21123
UTC Build Time: 2020-11-19 03:08:05
GoVersion: go1.15.2
Race Enabled: false
TiKV Min Version: v3.0.0-60965b006877ca7234adaced7890d7b029ed1306
Check Table Before Drop: false

[blacktear23]

@bb7133 @wjhuang2016 PTAL

[blacktear23]

I think this is related to ZERO value type convert and compare is not fully compatible with MySQL.

[ghost]

Yeah, I think it might be related to #10485