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

NO_ZERO_DATE affects timestamp values without any warnings

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

          relates to

          Task - A task that needs to be done. MDEV-7635 update defaults and simplify mysqld config parameters

          • Blocker - Blocks development and/or testing work, production could not run.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-12672 Replicated TIMESTAMP fields given wrong value near DST change

          • Major - Major loss of function.
          • Closed

          Activity

            Ascending order - Click to sort in descending order
            elenst Elena Stepanova created issue -
            elenst Elena Stepanova made changes -
            Field Original Value New Value
            bar Alexander Barkov made changes -
            bar Alexander Barkov made changes -
            bar Alexander Barkov made changes -
            bar Alexander Barkov added a comment - - edited

            This problem was fixed earlier, possibly by this change in 10.1.27:

            commit f8a800bec81983910a96a5dc38f3aeb9b7528bce
            		 
            Author: Sergei Golubchik <serg@mariadb.org>
            		 
            Date:   Tue Sep 19 23:50:32 2017 +0200
            		 
             
            		 
                bugfix: copy timestamps correctly in INSERT...SELECT
            		 
                
                don't do it via MYSQL_TIME, that conversion is lossy
            		 
                around DST change dates.

            and is not repeatable in 10.1 - 10.4 any more.

            bar Alexander Barkov added a comment - - edited This problem was fixed earlier, possibly by this change in 10.1.27: commit f8a800bec81983910a96a5dc38f3aeb9b7528bce Author: Sergei Golubchik <serg@mariadb.org> Date: Tue Sep 19 23:50:32 2017 +0200   bugfix: copy timestamps correctly in INSERT...SELECT don't do it via MYSQL_TIME, that conversion is lossy around DST change dates. and is not repeatable in 10.1 - 10.4 any more.
            bar Alexander Barkov made changes -
            Fix Version/s 10.2.9 [ 22611 ]
            Fix Version/s 10.1.27 [ 22609 ]
            Fix Version/s 10.2 [ 14601 ]
            Resolution Fixed [ 1 ]
            Status Open [ 1 ] Closed [ 6 ]
            serg Sergei Golubchik made changes -
            Workflow MariaDB v3 [ 79345 ] MariaDB v4 [ 151596 ]

            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.