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

CREATE ... SELECT system_versioned_table causes invalid defaults

Details

    • Bug
    • Status: Closed (View Workflow)
    • Critical
    • Resolution: Fixed
    • 10.3(EOL), 10.4(EOL), 10.5, 10.6, 10.7(EOL), 10.8(EOL), 10.9(EOL), 10.10(EOL)
    • 10.11.1
    • Versioned Tables
    • None

    Description

      Fields AS ROW START and AS ROW END appear to have a default value of 0.
      It matters when one creates a new table out of system versioned table using
      CREATE ... SELECT. But the default value avoids validation, these fields are created with zero defaults even if sql_mode=NO_ZERO_DATE. Examples are in versioning.create test which succeeds even in the traditional mode.

      Attachments

        Issue Links

          Activity

            in bb-10.11-serg

            serg Sergei Golubchik added a comment - in bb-10.11-serg

            I haven't got anything that would not allow it to be pushed. Please go ahead.

            The behavior is still weird in some ways, e.g. it is strange when you can do insert (f) values (default), but select default(f) causes an error, as in

            MariaDB [test]> create or replace table t (a int, s bigint unsigned as row start, e bigint unsigned as row end, period for system_time(s,e)) with system versioning;
            Query OK, 0 rows affected (0.074 sec)
             
            MariaDB [test]> insert into t values (1,default,default);
            Query OK, 1 row affected (0.010 sec)
             
            MariaDB [test]> select default(a), default(s), default(e) from t;
            ERROR 1364 (HY000): Field 's' doesn't have a default value
            

            But it's not weirder than it was before (and still is with "regular" virtual columns), when select default(f) returns something completely different from what insert (default) inserts, so it can't be an obstacle.

            elenst Elena Stepanova added a comment - I haven't got anything that would not allow it to be pushed. Please go ahead. The behavior is still weird in some ways, e.g. it is strange when you can do insert (f) values (default) , but select default(f) causes an error, as in MariaDB [test]> create or replace table t (a int , s bigint unsigned as row start, e bigint unsigned as row end , period for system_time(s,e)) with system versioning; Query OK, 0 rows affected (0.074 sec)   MariaDB [test]> insert into t values (1, default , default ); Query OK, 1 row affected (0.010 sec)   MariaDB [test]> select default (a), default (s), default (e) from t; ERROR 1364 (HY000): Field 's' doesn't have a default value But it's not weirder than it was before (and still is with "regular" virtual columns), when select default(f) returns something completely different from what insert (default) inserts, so it can't be an obstacle.

            People

              serg Sergei Golubchik
              serg Sergei Golubchik
              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.