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

NO_ZERO_IN_DATE: Incorrect generated column value

Details

    Description

      MariaDB [test]> set sql_mode = '';
      		 
      Query OK, 0 rows affected (0.00 sec)
      		 
       
      		 
      MariaDB [test]> select timediff('2008-12-31 23:59:59.000001','2008-12-30 01:01:01.000002');
      		 
      +---------------------------------------------------------------------+
      		 
      | timediff('2008-12-31 23:59:59.000001','2008-12-30 01:01:01.000002') |
      		 
      +---------------------------------------------------------------------+
      		 
      | 46:58:57.999999                                                     |
      		 
      +---------------------------------------------------------------------+
      		 
      1 row in set (0.00 sec)
      		 
       
      		 
      MariaDB [test]> drop table t1;
      		 
      Query OK, 0 rows affected (0.00 sec)
      		 
       
      		 
      MariaDB [test]> CREATE TABLE `t1` (`a` datetime DEFAULT NULL, `b` datetime DEFAULT NULL, `c` time GENERATED ALWAYS AS (timediff(`a`,`b`)) VIRTUAL ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
      		 
      Query OK, 0 rows affected (0.01 sec)
      		 
       
      		 
      MariaDB [test]> insert into t1 values ('2008-12-31 23:59:59.000001','2008-12-30 01:01:01.000002',default);
      		 
      Query OK, 1 row affected (0.00 sec)
      		 
       
      		 
      MariaDB [test]> select * from t1;
      		 
      +---------------------+---------------------+----------+
      		 
      | a                   | b                   | c        |
      		 
      +---------------------+---------------------+----------+
      		 
      | 2008-12-31 23:59:59 | 2008-12-30 01:01:01 | 46:58:58 |
      		 
      +---------------------+---------------------+----------+
      		 
      1 row in set (0.00 sec)
      		 
       
      		 
      MariaDB [test]> set sql_mode='NO_ZERO_IN_DATE';
      		 
      Query OK, 0 rows affected (0.00 sec)
      		 
       
      		 
      MariaDB [test]> drop table t1;
      		 
      Query OK, 0 rows affected (0.00 sec)
      		 
       
      		 
      MariaDB [test]> CREATE TABLE `t1` (`a` datetime DEFAULT NULL, `b` datetime DEFAULT NULL, `c` time GENERATED ALWAYS AS (timediff(`a`,`b`)) VIRTUAL ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
      		 
      Query OK, 0 rows affected (0.00 sec)
      		 
       
      		 
      MariaDB [test]> insert into t1 values ('2008-12-31 23:59:59.000001','2008-12-30 01:01:01.000002',default);
      		 
      Query OK, 1 row affected (0.00 sec)
      		 
       
      		 
      MariaDB [test]> select * from t1;
      		 
      +---------------------+---------------------+------+
      		 
      | a                   | b                   | c    |
      		 
      +---------------------+---------------------+------+
      		 
      | 2008-12-31 23:59:59 | 2008-12-30 01:01:01 | NULL | <---- Notice "NULL"
      		 
      +---------------------+---------------------+------+
      		 
      1 row in set (0.00 sec)

      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

          Activity

            bar Alexander Barkov added a comment - - edited

            The same problem is repeatable in this script:

            SET sql_mode='NO_ZERO_IN_DATE';
            		 
            DROP TABLE IF EXISTS t1;
            		 
            CREATE TABLE t1 (a TIME(6));
            		 
            INSERT INTO t1 SELECT timediff(timestamp'2008-12-31 23:59:59.000001',timestamp'2008-12-30 01:01:01.000002');
            		 
            SELECT * FROM t1;


            +------+
            		 
            | a    |
            		 
            +------+
            		 
            | NULL |
            		 
            +------+

            The expected result would be to store '46:58:57.999999' instead of NULL in the column.

            bar Alexander Barkov added a comment - - edited The same problem is repeatable in this script: SET sql_mode= 'NO_ZERO_IN_DATE' ; DROP TABLE IF EXISTS t1; CREATE TABLE t1 (a TIME (6)); INSERT INTO t1 SELECT timediff( timestamp '2008-12-31 23:59:59.000001' , timestamp '2008-12-30 01:01:01.000002' ); SELECT * FROM t1; +------+ | a | +------+ | NULL | +------+ The expected result would be to store '46:58:57.999999' instead of NULL in the column.

            People

              bar Alexander Barkov
              nirbhay_c Nirbhay Choubey (Inactive)
              Votes:
              0 Vote for this issue
              Watchers:
              4 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.