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

Check constraints on virtual columns fails on INSERT when column not specified

    XMLWordPrintable

Details

    Description

      When having a check constraint on a virtual column, the column in question must be present in any insert statement and have a value that fulfills the check constraint, despite this value being overwritten by the virtual column formula later, like this:

      CREATE TABLE tv1(c1 int, c2 int as (c1 + 1), CHECK (c2 > 2));
      

      The following fails, which is correct (c2 is 2 which is wrong):

      INSERT INTO tv1(c1) VALUES(1);
      

      But the following also fails:

      INSERT INTO tv1(c1) VALUES(2);
      

      The following also fails (again as c2 is 2):

      INSERT INTO tv1 VALUES(1,NULL);
      

      But the following works:

      INSERT INTO tv1 VALUES(2,NULL);
      

      Which you would think is the same as:

      INSERT INTO tv1(c1) VALUES(2);
      

      But as shown above it is not

      Attachments

        Issue Links

          Activity

            People

              serg Sergei Golubchik
              karlsson Anders Karlsson
              Votes:
              0 Vote for this issue
              Watchers:
              3 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.