Details
-
Bug
-
Status: Open (View Workflow)
-
Major
-
Resolution: Unresolved
-
10.3(EOL)
-
None
Description
This script erroneously returns 0 for a zero TIMESTAMP field:
SET time_zone='+00:00'; |
CREATE OR REPLACE TABLE t1 (a TIMESTAMP); |
INSERT INTO t1 VALUES ('0000-00-00 00:00:00'); |
SELECT a, UNIX_TIMESTAMP(a), FROM_UNIXTIME(UNIX_TIMESTAMP(a)) FROM t1; |
+---------------------+-------------------+----------------------------------+
|
| a | UNIX_TIMESTAMP(a) | FROM_UNIXTIME(UNIX_TIMESTAMP(a)) |
|
+---------------------+-------------------+----------------------------------+
|
| 0000-00-00 00:00:00 | 0 | 1970-01-01 00:00:00 |
|
+---------------------+-------------------+----------------------------------+
|
This is wrong. The value 0000-00-00 00:00:00 is a special value for zero TIMESTAMPs.
Although it's internally stored as time_t=0, it should not be associated in any ways with the true time_t=0 value, which is 1970-01-01 00:00:00+00.
UNIX_TIMESTAMP in the above example confuses the special zero TIMESTAMP value of 0000-00-00 00:00:00 with a real time_t=0.
The above script should return NULL for the second and the third columns.
Note, if I wrap the field into COALESCE, it starts returning correct results:
SELECT COALESCE(a), UNIX_TIMESTAMP(COALESCE(a)), FROM_UNIXTIME(UNIX_TIMESTAMP(COALESCE(a))) FROM t1; |
+---------------------+-----------------------------+--------------------------------------------+
|
| COALESCE(a) | UNIX_TIMESTAMP(COALESCE(a)) | FROM_UNIXTIME(UNIX_TIMESTAMP(COALESCE(a))) |
|
+---------------------+-----------------------------+--------------------------------------------+
|
| 0000-00-00 00:00:00 | NULL | NULL |
|
+---------------------+-----------------------------+--------------------------------------------+
|
If I wrap the field into MAX, it also correctly returns NULL:
SELECT UNIX_TIMESTAMP(MAX(a)), FROM_UNIXTIME(UNIX_TIMESTAMP(MAX(a))) FROM t1; |
+------------------------+---------------------------------------+
|
| UNIX_TIMESTAMP(MAX(a)) | FROM_UNIXTIME(UNIX_TIMESTAMP(MAX(a))) |
|
+------------------------+---------------------------------------+
|
| NULL | NULL |
|
+------------------------+---------------------------------------+
|
Attachments
Issue Links
- relates to
-
-
- Closed
-
-
-
- Open
-