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

            Activity

              People

              • Assignee:
                bar Alexander Barkov
                Reporter:
                bar Alexander Barkov
              • Votes:
                0 Vote for this issue
                Watchers:
                3 Start watching this issue

                Dates

                • Created:
                  Updated: