Uploaded image for project: 'MariaDB ColumnStore'
  1. MariaDB ColumnStore
  2. MCOL-3693

Table CHECK constraint pretends to work but breaks transactional properties

    XMLWordPrintable

Details

    Description

      drop table if exists t1;
      create table t1 (a int, b int, check(a>b)) engine=Columnstore;
      insert into t1 values (1,0),(2,2);
      select * from t1;
      insert into t1 values (3,0);
      select * from t1;
      

      CREATE TABLE above works without any errors or warnings.
      First INSERT fails ER_CONSTRAINT_FAILED, so it's not ignored:

      MariaDB [db]> insert into t1 values (1,0),(2,2);
      ERROR 4025 (23000): CONSTRAINT `CONSTRAINT_1` failed for `db`.`t1`
      

      Following SELECT expectedly returns an empty result set:

      MariaDB [db]> select * from t1;
      Empty set (0.055 sec)
      

      Next INSERT doesn't break the constraint, so it works.
      But after that, the table contains two rows – the last inserted one and also the first one from the previously failed INSERT:

      MariaDB f93bfb9288d020b190f5c73a31223fff6439687d

      MariaDB [db]> insert into t1 values (3,0);
      Query OK, 1 row affected (0.184 sec)
       
      MariaDB [db]> select * from t1;
      +------+------+
      | a    | b    |
      +------+------+
      |    1 |    0 |
      |    3 |    0 |
      +------+------+
      2 rows in set (0.024 sec)
      

      Attachments

        Issue Links

          Activity

            People

              toddstoffel Todd Stoffel (Inactive)
              elenst Elena Stepanova
              Votes:
              0 Vote for this issue
              Watchers:
              4 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.