[MCOL-935] CHECK Constraints not working for UPDATE Created: 2017-09-21  Updated: 2022-11-05  Resolved: 2022-11-05

Status: Closed
Project: MariaDB ColumnStore
Component/s: MariaDB Server
Affects Version/s: 1.1.0
Fix Version/s: Icebox

Type: New Feature Priority: Minor
Reporter: Anders Karlsson Assignee: Todd Stoffel (Inactive)
Resolution: Won't Do Votes: 1
Labels: None
Environment:

CentOS 6.4

Issue Links:
Relates
relates to MCOL-3693 Table CHECK constraint pretends to wo... Closed
Epic Link: ColumnStore Compatibility Improvements

 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.

 Comments   
Comment by Andrew Hutchings (Inactive) [ 2017-09-21 ]

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.

Comment by Anders Karlsson [ 2017-09-21 ]

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.

Comment by Todd Stoffel (Inactive) [ 2022-11-05 ]

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.

Generated at Thu Feb 08 02:24:55 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.