Bug #106742 DateTime Type Shows Inconsistent Behavior In SELECT Statement
Submitted: 16 Mar 2022 8:29 Modified: 17 Mar 2022 7:26
Reporter: Ein Exin Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server: Data Types Severity:S3 (Non-critical)
Version:8.0.22 OS:MacOS
Assigned to: CPU Architecture:x86
Tags: datetime

[16 Mar 2022 8:29] Ein Exin
Description:
When DateTime type compares with Integral in a SELECT statement with a table, the Integral is cast into DateTime. However, when used in a SELECT statement without a table, the Integral is cast to Double.

How to repeat:
CREATE TABLE t1 ( a DATE,KEY ( a ) );
CREATE TABLE t2 ( a DATETIME,KEY ( a ) );
INSERT INTO t1 VALUES ('2009-09-22');
INSERT INTO t1 VALUES ('2009-09-23');
INSERT INTO t2 VALUES ('2009-09-23 00:00:00');
INSERT INTO t2 VALUES ('2009-09-23 12:00:00');

mysql> SELECT * FROM t1 WHERE a = 20090923;
+------------+
| a          |
+------------+
| 2009-09-23 |
+------------+
1 row in set (0.01 sec)

mysql> SELECT * FROM t1 WHERE a = 20090923000000;
+------------+
| a          |
+------------+
| 2009-09-23 |
+------------+
1 row in set (0.00 sec)

mysql> SELECT * FROM t2 WHERE a = 20090923;
+---------------------+
| a                   |
+---------------------+
| 2009-09-23 00:00:00 |
+---------------------+
1 row in set (0.00 sec)

mysql> SELECT * FROM t2 WHERE a = 20090923120000;
+---------------------+
| a                   |
+---------------------+
| 2009-09-23 12:00:00 |
+---------------------+
1 row in set (0.00 sec)

mysql> select cast('2009-09-23' as date) = 20090923;
+---------------------------------------+
| cast('2009-09-23' as date) = 20090923 |
+---------------------------------------+
|                                     1 |
+---------------------------------------+
1 row in set (0.00 sec)

mysql> select cast('2009-09-23' as date) = 20090923000000;
+---------------------------------------------+
| cast('2009-09-23' as date) = 20090923000000 |
+---------------------------------------------+
|                                           0 |
+---------------------------------------------+
1 row in set (0.00 sec)

mysql> select cast('2009-09-23 00:00:00' as datetime) = 20090923000000;
+----------------------------------------------------------+
| cast('2009-09-23 00:00:00' as datetime) = 20090923000000 |
+----------------------------------------------------------+
|                                                        1 |
+----------------------------------------------------------+
1 row in set (0.01 sec)

mysql> select cast('2009-09-23 00:00:00' as datetime) = 20090923;
+----------------------------------------------------+
| cast('2009-09-23 00:00:00' as datetime) = 20090923 |
+----------------------------------------------------+
|                                                  0 |
+----------------------------------------------------+
1 row in set (0.00 sec)

mysql> select cast(cast('2009-09-23 00:00:00' as datetime) as date) = 20090923;
+------------------------------------------------------------------+
| cast(cast('2009-09-23 00:00:00' as datetime) as date) = 20090923 |
+------------------------------------------------------------------+
|                                                                1 |
+------------------------------------------------------------------+
1 row in set (0.00 sec)

Suggested fix:
Either cast the Integral to Double or use DateTime when comparing with DateTime type.
[16 Mar 2022 13:58] MySQL Verification Team
Hi Mr. Exin,

Thank you for your bug report.

First of all, we do not have an integral data type. What you were using is a datetime.

All results that our queries returned are correct ones and this behaviour is in compliance with SQL standard. It is also fully described in our manual.

Any SELECT, which in its list has the comparison X = Y will return a boolean, either 1 for TRUE or 0 for FALSE. Hence, no doubles were used.

In other cases, MySQL does extra work, does not return the error but compares datetime with the integer that is written in similar format. That is a feature that is with us since 1997.

Not a bug.
[16 Mar 2022 16:52] Ein Exin
Hi, could you please tell me why this statement returns a row containing boolean False in MySQL?

mysql> select cast('2009-09-23 00:00:00' as datetime) = 20090923;
+----------------------------------------------------+
| cast('2009-09-23 00:00:00' as datetime) = 20090923 |
+----------------------------------------------------+
|                                                  0 |
+----------------------------------------------------+
1 row in set (0.00 sec)
[17 Mar 2022 7:26] Ein Exin
Is there any explanation for the inconsistent behavior of the following commands?

mysql> SELECT * FROM t2;
+---------------------+
| a                   |
+---------------------+
| 2009-09-23 00:00:00 |
+---------------------+
1 row in set (0.00 sec)

mysql> SELECT a = 20090923 FROM t2;
+--------------+
| a = 20090923 |
+--------------+
|            1 |
+--------------+
1 row in set (0.00 sec)

mysql> SELECT cast('2009-09-23 00:00:00' as datetime) = 20090923;
+----------------------------------------------------+
| cast('2009-09-23 00:00:00' as datetime) = 20090923 |
+----------------------------------------------------+
|                                                  0 |
+----------------------------------------------------+
1 row in set (0.00 sec)
[17 Mar 2022 14:00] MySQL Verification Team
Hi,

Yes, there is a simple explanation for this behaviour and it is described also in our Reference Manual.

If you perform casting, you prohibit conversion to the integer, hence equality comparison fails.

Not a bug.