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

NO_ZERO_DATE affects timestamp values without any warnings

    XMLWordPrintable

Details

    Description

      SET sql_mode = '';
      CREATE TABLE t1 (a TIMESTAMP NULL) ENGINE = MyISAM;
      CREATE TABLE t2 (a TIMESTAMP NULL) ENGINE = MyISAM;
      CREATE TABLE t3 (a TIMESTAMP NULL) ENGINE = MyISAM;
       
      SET @@session.time_zone = 'UTC';
      INSERT INTO t1 VALUES ('2011-10-29 23:00:00');
      INSERT INTO t1 VALUES ('2011-10-29 23:00:01');
      INSERT INTO t1 VALUES ('2011-10-29 23:59:59');
       
      SET @@session.time_zone = 'Europe/Moscow';
       
      SET sql_mode='NO_ZERO_DATE';
      INSERT INTO t2 SELECT * FROM t1;
       
      SET sql_mode='';
      INSERT INTO t3 SELECT * FROM t1;
       
      SELECT UNIX_TIMESTAMP(a), a FROM t2;
      SELECT UNIX_TIMESTAMP(a), a FROM t3;
       
      DROP TABLE t1, t2, t3;
      

      Results on 10.2

      SET sql_mode = '';
      CREATE TABLE t1 (a TIMESTAMP NULL) ENGINE = MyISAM;
      CREATE TABLE t2 (a TIMESTAMP NULL) ENGINE = MyISAM;
      CREATE TABLE t3 (a TIMESTAMP NULL) ENGINE = MyISAM;
      SET @@session.time_zone = 'UTC';
      INSERT INTO t1 VALUES ('2011-10-29 23:00:00');
      INSERT INTO t1 VALUES ('2011-10-29 23:00:01');
      INSERT INTO t1 VALUES ('2011-10-29 23:59:59');
      SET @@session.time_zone = 'Europe/Moscow';
      SET sql_mode='NO_ZERO_DATE';
      INSERT INTO t2 SELECT * FROM t1;
      SET sql_mode='';
      INSERT INTO t3 SELECT * FROM t1;
      SELECT UNIX_TIMESTAMP(a), a FROM t2;
      UNIX_TIMESTAMP(a)	a
      1319925600	2011-10-30 02:00:00
      1319925601	2011-10-30 02:00:01
      1319929199	2011-10-30 02:59:59
      SELECT UNIX_TIMESTAMP(a), a FROM t3;
      UNIX_TIMESTAMP(a)	a
      1319929200	2011-10-30 02:00:00
      1319929201	2011-10-30 02:00:01
      1319932799	2011-10-30 02:59:59
      DROP TABLE t1, t2, t3;
      

      Note different UNIX_TIMESTAMP values in SELECT result sets.

      It started happening in 10.1.
      Earlier versions of MariaDB and MySQL 5.5-5.7 produce the same unix timestamps for both queries.

      Attachments

        Issue Links

          Activity

            People

              bar Alexander Barkov
              elenst Elena Stepanova
              Votes:
              0 Vote for this issue
              Watchers:
              2 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.