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

INSERT without column list into table with explicit versioning columns produces bad data

    XMLWordPrintable

Details

    Description

      create or replace table t1 (i int, s timestamp(6) as row start, e timestamp(6) as row end, c varchar(8), period for system_time(s,e)) with system versioning;
      insert into t1 values (1,null,null,'foo');
      select * from t1;
      

      Insert works without complaints, the resulting data is wrong (note column c):

      MariaDB [test]> insert into t1 values (1,null,null,'foo');
      Query OK, 1 row affected (0.03 sec)
       
      MariaDB [test]> select * from t1;
      +------+----------------------------+----------------------------+------+
      | i    | s                          | e                          | c    |
      +------+----------------------------+----------------------------+------+
      |    1 | 2017-12-28 20:13:42.655595 | 2038-01-19 05:14:07.999999 | NULL |
      +------+----------------------------+----------------------------+------+
      1 row in set (0.00 sec)
      

      Another variation, if we provide values instead of NULLs – this is especially important, because that's what mysqldump will do:

      MariaDB [test]> create or replace table t1 (i int, s timestamp(6) as row start, e timestamp(6) as row end, c varchar(8), period for system_time(s,e)) with system versioning;
      Query OK, 0 rows affected (0.22 sec)
       
      MariaDB [test]> insert into t1 values (2,'2012-12-12 12:12:12:121212','2012-12-12 12:12:12:121212','bar');
      Query OK, 1 row affected, 1 warning (0.03 sec)
       
      MariaDB [test]> show warnings;
      +---------+------+----------------------------------------+
      | Level   | Code | Message                                |
      +---------+------+----------------------------------------+
      | Warning | 1265 | Data truncated for column 'c' at row 1 |
      +---------+------+----------------------------------------+
      1 row in set (0.00 sec)
       
      MariaDB [test]> select * from t1;
      +------+----------------------------+----------------------------+----------+
      | i    | s                          | e                          | c        |
      +------+----------------------------+----------------------------+----------+
      |    2 | 2017-12-28 20:16:37.635292 | 2038-01-19 05:14:07.999999 | 2012-12- |
      +------+----------------------------+----------------------------+----------+
      1 row in set (0.00 sec)
      

      Now there was a warning, but it's a wrong one anyway. More obvious in strict mode:

      MariaDB [test]> set sql_mode='strict_all_tables';
      Query OK, 0 rows affected (0.00 sec)
       
      MariaDB [test]> insert into t1 values (2,'2012-12-12 12:12:12:121212','2012-12-12 12:12:12:121212','bar');
      ERROR 1406 (22001): Data too long for column 'c' at row 1
      

      It complain about an attempt to insert into a generated column, not about too long value for c.

      Attachments

        Issue Links

          Activity

            People

              midenok Aleksey Midenkov
              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.