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: | |
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
[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.