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

              Assignee:
              serg Sergei Golubchik
              Reporter:
              karlsson Anders Karlsson
              Votes:
              0 Vote for this issue
              Watchers:
              3 Start watching this issue

                Dates

                Created:
                Updated:
                Resolved: