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

Zero datetime reinterprets as '1970-01-01 00:00:00' on field_datetime=field_timestamp

    XMLWordPrintable

Details

    Description

      Zero datetime reinterprets as '1970-01-01 00:00:00' on field_datetime=field_timestamp

      SET sql_mode='';
      SET time_zone='+00:00';
      CREATE OR REPLACE TABLE t1 (a TIMESTAMP);
      INSERT INTO t1 VALUES ('0000-00-00 00:00:00');
      SELECT * FROM t1;
      

      +---------------------+
      | a                   |
      +---------------------+
      | 0000-00-00 00:00:00 |
      +---------------------+
      

      CREATE OR REPLACE TABLE t2 (a datetime);
      INSERT INTO t2 SELECT a FROM t1;
      SELECT * FROM t2;
      

      +---------------------+
      | a                   |
      +---------------------+
      | 1970-01-01 00:00:00 |
      +---------------------+
      

      Looks wrong. The zero datetime was erroneously re-interpreted as '1970-01-01 00:00:00'.

      Note, if I wrap the column t2.a into COALESCE(), it works OK:

      INSERT INTO t2 SELECT COALESCE(a) FROM t1;
      SELECT * FROM t2;
      

      +---------------------+
      | a                   |
      +---------------------+
      | 1970-01-01 00:00:00 |
      | 0000-00-00 00:00:00 |
      +---------------------+
      

      The problem resides in this code:

      int Field_timestamp::save_in_field(Field *to)
      {
        ulong sec_part;
        my_time_t ts= get_timestamp(&sec_part);
        return to->store_timestamp_dec(Timeval(ts, sec_part), decimals());
      }
      

      It should not call store_timestamp_dec() for zero datetime, as this method knows nothing about zero datetime. It can store only real timestamp values.

      A proposed fix:

      int Field_timestamp::save_in_field(Field *to)
      {
        ulong sec_part;
        my_time_t ts= get_timestamp(&sec_part);
        if (!ts && ! sec_part)
          return to->store_time_dec(Datetime::zero().get_mysql_time(), decimals());
        return to->store_timestamp_dec(Timeval(ts, sec_part), decimals());
      }
      

      Attachments

        Issue Links

          Activity

            People

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

              Dates

                Created:
                Updated:
                Resolved:

                Git Integration

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