Bug #105783 `datetime <cmp> int` returns different result in different SQLs
Submitted: 3 Dec 2021 3:18 Modified: 3 Dec 2021 6:02
Reporter: Huaiyu Xu Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: DML Severity:S2 (Serious)
Version:8.0, 5.7 OS:Mac OS X
Assigned to: CPU Architecture:x86

[3 Dec 2021 3:18] Huaiyu Xu
Description:
mysql> select cast("2001-08-14 00:00:00" as datetime)>20021020,cast("2001-08-15 00:00:00" as datetime)>20021020,cast("2001-08-16 00:00:00" as datetime)>20021020,cast("2003-09-15 01:20:30" as datetime)>20021020;
+--------------------------------------------------+--------------------------------------------------+--------------------------------------------------+--------------------------------------------------+
| cast("2001-08-14 00:00:00" as datetime)>20021020 | cast("2001-08-15 00:00:00" as datetime)>20021020 | cast("2001-08-16 00:00:00" as datetime)>20021020 | cast("2003-09-15 01:20:30" as datetime)>20021020 |
+--------------------------------------------------+--------------------------------------------------+--------------------------------------------------+--------------------------------------------------+
|                                                1 |                                                1 |                                                1 |                                                1 |
+--------------------------------------------------+--------------------------------------------------+--------------------------------------------------+--------------------------------------------------+

mysql> drop table if exists t1;
Query OK, 0 rows affected (0.02 sec)

mysql> create table t1 ( dt datetime);
Query OK, 0 rows affected (0.03 sec)

mysql> insert into t1 values ("2001-08-14 00:00:00"),("2001-08-15 00:00:00"),("2001-08-16 00:00:00"),("2003-09-15 01:20:30");
Query OK, 4 rows affected (0.01 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> select * from t1 where dt > 20021020;
+---------------------+
| dt                  |
+---------------------+
| 2003-09-15 01:20:30 |
+---------------------+
1 row in set (0.00 sec)

mysql> select dt>20021020 from t1;
+-------------+
| dt>20021020 |
+-------------+
|           0 |
|           0 |
|           0 |
|           1 |
+-------------+
4 rows in set (0.00 sec)

How to repeat:
case1:

select cast("2001-08-14 00:00:00" as datetime)>20021020,cast("2001-08-15 00:00:00" as datetime)>20021020,cast("2001-08-16 00:00:00" as datetime)>20021020,cast("2003-09-15 01:20:30" as datetime)>20021020;

case2:
drop table if exists t1;
create table t1 ( dt datetime);
insert into t1 values ("2001-08-14 00:00:00"),("2001-08-15 00:00:00"),("2001-08-16 00:00:00"),("2003-09-15 01:20:30");
select * from t1 where dt > 20021020;
[3 Dec 2021 3:19] Huaiyu Xu
Expected result:
Both of the 2 cases should return `1` for all the tuples
[3 Dec 2021 6:02] MySQL Verification Team
Hello Huaiyu Xu,

Thank you for the report and test case.

regards,
Umesh