Details
-
Bug
-
Status: Open (View Workflow)
-
Major
-
Resolution: Unresolved
-
10.0(EOL), 10.1(EOL), 10.2(EOL), 10.3(EOL), 10.4(EOL)
-
None
Description
Conversion to DATETIME for comparison from other data types works differently, both in terms of the result and in terms of warnings generated.
Summary:
- Implicit conversion never returns warnings
- Explicit conversion always returns a warning
- Implicit conversion always returns zero datetime
- Explicit conversion mostly returns zero datetime, but TIME-to-DATETIME returns NULL
Implicit conversion from TIME to DATETIME - zero datetime without warnings
SET sql_mode='NO_ZERO_IN_DATE,NO_ZERO_DATE'; |
SET old_mode=ZERO_DATE_TIME_CAST; |
SELECT TIMESTAMP'2001-01-01 00:00:00' > TIME'00:00:00'; |
+-------------------------------------------------+
|
| TIMESTAMP'2001-01-01 00:00:00' > TIME'00:00:00' |
|
+-------------------------------------------------+
|
| 1 |
|
+-------------------------------------------------+
|
1 row in set (0.00 sec)
|
Implicit conversion from VARCHAR to DATETIME - zero datetime without warnings
SET sql_mode='NO_ZERO_IN_DATE,NO_ZERO_DATE'; |
SET old_mode=ZERO_DATE_TIME_CAST; |
SELECT TIMESTAMP'2001-01-01 00:00:00' > '00:00:00'; |
+---------------------------------------------+
|
| TIMESTAMP'2001-01-01 00:00:00' > '00:00:00' |
|
+---------------------------------------------+
|
| 1 |
|
+---------------------------------------------+
|
1 row in set (0.00 sec)
|
Implicit conversion from INT to DATETIME - zero datetime without warnings
SET sql_mode='NO_ZERO_IN_DATE,NO_ZERO_DATE'; |
SELECT TIMESTAMP'2001-01-01 00:00:00' > 0; |
+------------------------------------+
|
| TIMESTAMP'2001-01-01 00:00:00' > 0 |
|
+------------------------------------+
|
| 1 |
|
+------------------------------------+
|
1 row in set (0.00 sec)
|
Explicit conversion from TIME to DATETIME - NULL with a warning
SET sql_mode='NO_ZERO_IN_DATE,NO_ZERO_DATE'; |
SET old_mode=ZERO_DATE_TIME_CAST; |
SELECT TIMESTAMP'2001-01-01 00:00:00' > CAST(TIME'00:00:00' AS DATETIME); |
+-------------------------------------------------------------------+
|
| TIMESTAMP'2001-01-01 00:00:00' > CAST(TIME'00:00:00' AS DATETIME) |
|
+-------------------------------------------------------------------+
|
| NULL |
|
+-------------------------------------------------------------------+
|
1 row in set, 1 warning (0.00 sec)
|
Explicit conversion from VARCHAR to DATETIME - zero datetime with a warning
SET sql_mode='NO_ZERO_IN_DATE,NO_ZERO_DATE'; |
SET old_mode=ZERO_DATE_TIME_CAST; |
SELECT TIMESTAMP'2001-01-01 00:00:00' > CAST('00:00:00' AS DATETIME); |
+---------------------------------------------------------------+
|
| TIMESTAMP'2001-01-01 00:00:00' > CAST('00:00:00' AS DATETIME) |
|
+---------------------------------------------------------------+
|
| 1 |
|
+---------------------------------------------------------------+
|
1 row in set, 1 warning (0.00 sec)
|
Explicit conversion from INT to DATETIME - zero datetime with a warning
SET sql_mode='NO_ZERO_IN_DATE,NO_ZERO_DATE'; |
SELECT TIMESTAMP'2001-01-01 00:00:00' > CAST(0 AS DATETIME); |
+------------------------------------------------------+
|
| TIMESTAMP'2001-01-01 00:00:00' > CAST(0 AS DATETIME) |
|
+------------------------------------------------------+
|
| 1 |
|
+------------------------------------------------------+
|
1 row in set, 1 warning (0.00 sec)
|
Attachments
Issue Links
- relates to
-
-
- Closed
-
-
-
- Closed
-
-
-
- Closed
-