Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-14278

Bad result for UNIX_TIMESTAMP(zero_timestamp_field)

Details

    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

          Bug - A problem which impairs or prevents the functions of the product. MDEV-13995 MAX(timestamp) returns a wrong result near DST change

          • Critical - Crashes, loss of data, severe memory leak.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-14271 Dynamic SQL: TIMESTAMP parameter value is not preserved on timezone change

          • Major - Major loss of function.
          • Open

          Activity

            bar Alexander Barkov added a comment -

            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;
            		 
             }

            bar Alexander Barkov added a comment - 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; }

            People

              bar Alexander Barkov
              bar Alexander Barkov
              Votes:
              0 Vote for this issue
              Watchers:
              4 Start watching this issue

              Dates

                Created:
                Updated:

                Git Integration

                  Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.