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

Bad result for UNIX_TIMESTAMP(zero_timestamp_field)

    XMLWordPrintable

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

              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.