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

NO_ZERO_IN_DATE: Incorrect generated column value

    XMLWordPrintable

    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: