Bug 3568 - CHECK constraint is not enforced during insert or update
Summary: CHECK constraint is not enforced during insert or update
Status: RESOLVED FIXED
Alias: None
Product: SQL
Classification: Unclassified
Component: all (show other bugs)
Version: -- development
Hardware: x86_64 (amd64/em64t) Linux
: Normal enhancement
Assignee: SQL devs
URL:
Keywords:
: 3969 (view as bug list)
Depends on:
Blocks:
 
Reported: 2014-09-12 18:24 CEST by Martin van Dinther
Modified: 2019-11-29 16:56 CET (History)
3 users (show)



Attachments

Note You need to log in before you can comment on or make changes to this bug.
Description Martin van Dinther cwiconfidential 2014-09-12 18:24:37 CEST
User-Agent:       Mozilla/5.0 (X11; Linux x86_64; rv:32.0) Gecko/20100101 Firefox/32.0
Build Identifier: 

A table can be created with a CHECK constraints such as:
 CREATE TABLE TBLCHECK(nr int CHECK (nr > 10));
It is accepted without any error or warning.
However when inserting (in this case 0 or negative integer) values the CHECK constraint is not enforced. All integer values are accepted without error or warning. That should not be possible.

Reproducible: Always

Steps to Reproduce:
1. Start MonetDB 5 server v11.17.21 "Jan2014-SP3"
2. Start mclient
3. Execute SQLs:
CREATE TABLE TBLCHECK(nr int CHECK (nr > 10));
INSERT INTO TBLCHECK VALUES (11);
INSERT INTO TBLCHECK VALUES (10);
INSERT INTO TBLCHECK VALUES (09);
INSERT INTO TBLCHECK VALUES (01);
INSERT INTO TBLCHECK VALUES (00);
INSERT INTO TBLCHECK VALUES (-01);
INSERT INTO TBLCHECK VALUES (-+02);
INSERT INTO TBLCHECK VALUES (+-+03);
INSERT INTO TBLCHECK VALUES (-+-04);
INSERT INTO TBLCHECK VALUES (+-+-05);
SELECT * FROM TBLCHECK;
UPDATE TBLCHECK SET nr = nr -10;
SELECT * FROM TBLCHECK;

Actual Results:  
sql>CREATE TABLE TBLCHECK(nr int CHECK (nr > 10));
operation successful (3.579ms)
sql>INSERT INTO TBLCHECK VALUES (11);
1 affected rows (71.511ms)
sql>INSERT INTO TBLCHECK VALUES (10);
1 affected rows (1.853ms)
sql>INSERT INTO TBLCHECK VALUES (09);
1 affected rows (15.824ms)
sql>INSERT INTO TBLCHECK VALUES (01);
1 affected rows (2.319ms)
sql>INSERT INTO TBLCHECK VALUES (00);
1 affected rows (1.702ms)
sql>INSERT INTO TBLCHECK VALUES (-01);
1 affected rows (2.556ms)
sql>INSERT INTO TBLCHECK VALUES (-+02);
1 affected rows (30.416ms)
sql>INSERT INTO TBLCHECK VALUES (+-+03);
1 affected rows (51.113ms)
sql>INSERT INTO TBLCHECK VALUES (-+-04);
1 affected rows (3.132ms)
sql>INSERT INTO TBLCHECK VALUES (+-+-05);
1 affected rows (2.108ms)
sql>SELECT * FROM TBLCHECK;
+------+
| nr   |
+======+
|   11 |
|   10 |
|    9 |
|    1 |
|    0 |
|   -1 |
|   -2 |
|   -3 |
|    4 |
|    5 |
+------+
10 tuples (1.047ms)
sql>
sql>UPDATE TBLCHECK SET nr = nr -10;
10 affected row (29.627ms)
sql>SELECT * FROM TBLCHECK;
+------+
| nr   |
+======+
|    1 |
|    0 |
|   -1 |
|   -9 |
|  -10 |
|  -11 |
|  -12 |
|  -13 |
|   -6 |
|   -5 |
+------+
10 tuples (1.744ms)


Expected Results:  
Return an error when CHECK constraint is violated and no record should be inserted into the table.
Comment 1 Niels Nes cwiconfidential 2014-09-15 12:28:38 CEST
this is on the feature extension list.
Comment 2 Martin van Dinther cwiconfidential 2016-04-21 12:31:48 CEST
*** Bug 3969 has been marked as a duplicate of this bug. ***
Comment 4 Pedro Ferreira 2019-11-29 16:56:12 CET
I disabled the compilation of CHECK constraints, so this no longer occurs.