incorrect result with mysql8

[xiongjiwei]

Bug Report

Please answer these questions before submitting your issue. Thanks!

1. Minimal reproduce step (Required)

mysql> select cast('2020-02-02' as datetime) > a from (select 'aa' as a) t;
+------------------------------------+
| cast('2020-02-02' as datetime) > a |
+------------------------------------+
|                                  1 |
+------------------------------------+
1 row in set, 1 warning (0.00 sec)

mysql> show warnings;
+---------+------+------------------------------------------+
| Level   | Code | Message                                  |
+---------+------+------------------------------------------+
| Warning | 1292 | Truncated incorrect datetime value: 'aa' |
+---------+------+------------------------------------------+
1 row in set (0.00 sec)

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

as above

3. What did you see instead (Required)

mysql> select cast('2020-02-02' as datetime) > a from (select 'aa' as a) t;
+------------------------------------+
| cast('2020-02-02' as datetime) > a |
+------------------------------------+
|                               NULL |
+------------------------------------+
1 row in set, 1 warning (0.02 sec)

mysql>  show warnings;
+---------+------+--------------------------------+
| Level   | Code | Message                        |
+---------+------+--------------------------------+
| Warning | 1292 | Incorrect datetime value: 'aa' |
+---------+------+--------------------------------+
1 row in set (0.01 sec)

4. What is your TiDB version? (Required)

[ichn-hu]

I think this is really an edge case, and it is not reasonable why a datetime is larger than a string? Do we really need to fix it?

[ichn-hu]

I wound argue this bug should be a minor bug if not not a bug, according to bug severity standards

[ichn-hu]

/unlabel severity/major

[ichn-hu]

/label severity/minor

[ichn-hu]

@aytrack let me know if you disagree