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

CHECK Constraints not working for UPDATE

Details

    • New Feature
    • Status: Closed (View Workflow)
    • Minor
    • Resolution: Won't Do
    • 1.1.0
    • Icebox
    • MariaDB Server
    • None
    • CentOS 6.4

    Description

      CHECK constrains on a ColumnStore table works for INSERTs, but not for UPDATEs. UPDATEs that violates CHECK constraints are executed anyway.

      DROP TABLE IF EXISTS columnstore1;
      		 
      CREATE TABLE columnstore1(c1 INTEGER NOT NULL, CHECK(c1 > 10)) Engine=ColumnStore;
      		 
      INSERT INTO columnstore1 VALUES(10);
      		 
      INSERT INTO columnstore1 VALUES(11);
      		 
      UPDATE columnstore1 SET c1 = 10;
      		 
      SELECT c1 FROM columnstore1;

      In the above example, the first INSERT fails, the second succeeds and the update is also successful, meaning that we end up with invalid data in the table.

      Attachments

        Issue Links

          relates to

          New Feature - A new feature of the product, which has yet to be developed. MCOL-3693 Table CHECK constraint pretends to work but breaks transactional properties

          • Major - Major loss of function.
          • Closed

          Activity

            Ascending order - Click to sort in descending order
            LinuxJedi Andrew Hutchings (Inactive) added a comment -

            CHECK() is not yet a supported feature for ColumnStore. Please see the create table syntax:

            https://mariadb.com/kb/en/library/columnstore-create-table/

            I'll modify this to be a feature request accordingly.

            LinuxJedi Andrew Hutchings (Inactive) added a comment - CHECK() is not yet a supported feature for ColumnStore. Please see the create table syntax: https://mariadb.com/kb/en/library/columnstore-create-table/ I'll modify this to be a feature request accordingly.
            karlsson Anders Karlsson added a comment -

            Then it is odd that the syntax is supported, the table gets created, I see it in the SHOW CREATE TABLE (in 10.0 / MariaDB 10.1 check constrints were silently ignored) and they work for INSERT statements. The Column Store CREATE TABLE page you referenmce also has an error in the example where the data type is VAR-CHAR which obviously should be VARCHAR. Anyway, so be it.

            karlsson Anders Karlsson added a comment - Then it is odd that the syntax is supported, the table gets created, I see it in the SHOW CREATE TABLE (in 10.0 / MariaDB 10.1 check constrints were silently ignored) and they work for INSERT statements. The Column Store CREATE TABLE page you referenmce also has an error in the example where the data type is VAR-CHAR which obviously should be VARCHAR. Anyway, so be it.
            toddstoffel Todd Stoffel (Inactive) added a comment -

            This item is being closed because it was well passed the expiration date with no activity. If you suspect this was done in error please create a new ticket.

            toddstoffel Todd Stoffel (Inactive) added a comment - This item is being closed because it was well passed the expiration date with no activity. If you suspect this was done in error please create a new ticket.

            People

              toddstoffel Todd Stoffel (Inactive)
              karlsson Anders Karlsson
              Votes:
              1 Vote for this issue
              Watchers:
              5 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.