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

            Activity

              People

              • Assignee:
                bar Alexander Barkov
                Reporter:
                nirbhay_c Nirbhay Choubey (Inactive)
              • Votes:
                0 Vote for this issue
                Watchers:
                4 Start watching this issue

                Dates

                • Created:
                  Updated:
                  Resolved: