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
• Status: Verified
• Category: MySQL Server: DML
• Severity: S2 (Serious)
• Version: 8.0, 5.7
• OS: MacOS
• 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

[21 Oct 2023 9:37] Luuk V

It also does not work on other platforms.