[MDEV-14278] Bad result for UNIX_TIMESTAMP(zero_timestamp_field) Created: 2017-11-03  Updated: 2023-04-27

Status: Open
Project: MariaDB Server
Component/s: Data types, Temporal Types
Affects Version/s: 10.3
Fix Version/s: 10.4

Type: Bug Priority: Major
Reporter: Alexander Barkov Assignee: Alexander Barkov
Resolution: Unresolved Votes: 0
Labels: None

Issue Links:
Relates
relates to MDEV-13995 MAX(timestamp) returns a wrong result... Closed
relates to MDEV-14271 Dynamic SQL: TIMESTAMP parameter valu... Open

 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                                  |
 
+------------------------+---------------------------------------+



 Comments   
Comment by Alexander Barkov [ 2017-11-23 ]

Unfortunately, the intended patch breaks distribution between partitions, e.g. in partition_datatypes.test.
After the patch the value '0000-00-00 00:00:00' gets into a different partition, which is not desirable for upgrade.

index a0fdd4bb8fc..130fabf71b3 100644
 
--- a/sql/item_timefunc.cc
 
+++ b/sql/item_timefunc.cc
 
@@ -1199,6 +1199,16 @@ bool Item_func_unix_timestamp::get_timestamp_value(my_time_t *seconds,
 
       if ((null_value= field->is_null()))
 
         return 1;
 
       *seconds= ((Field_timestamp*)field)->get_timestamp(second_part);
 
+      if (!*seconds)
 
+      {
 
+        DBUG_ASSERT(!*second_part);
 
+        /*
 
+          Field_timestampXXX uses time_t==0 to store '0000-00-00 00:00:00'
 
+          This is not really a Unix timestamp.
 
+        */
 
+        null_value= true;
 
+        return 1;
 
+      }
 
       return 0;
 
     }
 
   }
 
@@ -1259,7 +1269,7 @@ longlong Item_func_unix_timestamp::val_int_endpoint(bool left_endp, bool *incl_e
 
   /* Leave the incl_endp intact */
 
   ulong unused;
 
   my_time_t ts= field->get_timestamp(&unused);
 
-  null_value= field->is_null();
 
+  null_value= field->is_null() || ts == 0;
 
   return ts;
 
 }

Generated at Thu Feb 08 08:12:18 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.