Changeset: ac51ad5eca08 for MonetDB URL: https://dev.monetdb.org/hg/MonetDB/rev/ac51ad5eca08 Added Files: sql/test/BugConstraints/Tests/3335_check_no_error.test sql/test/BugConstraints/Tests/3568_check_not_enforced.test sql/test/BugConstraints/Tests/check_constraints.test Modified Files: sql/test/BugConstraints/Tests/All Branch: Aug2024 Log Message:
Adding tests for check contraints (for old issues 3335 and 3568) diffs (165 lines): diff --git a/sql/test/BugConstraints/Tests/3335_check_no_error.test b/sql/test/BugConstraints/Tests/3335_check_no_error.test new file mode 100644 --- /dev/null +++ b/sql/test/BugConstraints/Tests/3335_check_no_error.test @@ -0,0 +1,35 @@ +statement ok +create table ary(x integer check(x >0 and x <3)) + +statement ok +insert into ary values(1) + +statement error UPDATE: CHECK constraint violated: ary_x_check +insert into ary values(0) + +statement ok +insert into ary values(2) + +statement error UPDATE: CHECK constraint violated: ary_x_check +insert into ary values(-1) + +statement error UPDATE: CHECK constraint violated: ary_x_check +insert into ary values(3) + +statement error UPDATE: CHECK constraint violated: ary_x_check +update ary set x = x+1 + +statement ok +delete from ary where x = 2 + +statement ok +update ary set x = x+1 + +query I rowsort +select * from ary +---- +2 + +statement ok +drop table ary + diff --git a/sql/test/BugConstraints/Tests/3568_check_not_enforced.test b/sql/test/BugConstraints/Tests/3568_check_not_enforced.test new file mode 100644 --- /dev/null +++ b/sql/test/BugConstraints/Tests/3568_check_not_enforced.test @@ -0,0 +1,52 @@ +statement ok +CREATE TABLE TBLCHECK(nr int CHECK (nr > 10)) + +statement ok +INSERT INTO TBLCHECK VALUES (11) + +statement error 40002!UPDATE: CHECK constraint violated: tblcheck_nr_check +INSERT INTO TBLCHECK VALUES (10) + +statement error 40002!UPDATE: CHECK constraint violated: tblcheck_nr_check +INSERT INTO TBLCHECK VALUES (09) + +statement error 40002!UPDATE: CHECK constraint violated: tblcheck_nr_check +INSERT INTO TBLCHECK VALUES (01) + +statement error 40002!UPDATE: CHECK constraint violated: tblcheck_nr_check +INSERT INTO TBLCHECK VALUES (00) + +statement error 40002!UPDATE: CHECK constraint violated: tblcheck_nr_check +INSERT INTO TBLCHECK VALUES (-01) + +statement error 40002!UPDATE: CHECK constraint violated: tblcheck_nr_check +INSERT INTO TBLCHECK VALUES (-+02) + +statement error 40002!UPDATE: CHECK constraint violated: tblcheck_nr_check +INSERT INTO TBLCHECK VALUES (+-+03) + +statement error 40002!UPDATE: CHECK constraint violated: tblcheck_nr_check +INSERT INTO TBLCHECK VALUES (-+-04) + +statement error 40002!UPDATE: CHECK constraint violated: tblcheck_nr_check +INSERT INTO TBLCHECK VALUES (+-+-05) + +query I rowsort +SELECT * FROM TBLCHECK +---- +11 + +statement error 40002!UPDATE: CHECK constraint violated: tblcheck_nr_check +UPDATE TBLCHECK SET nr = nr -10 + +statement ok +UPDATE TBLCHECK SET nr = nr +10 + +query I rowsort +SELECT * FROM TBLCHECK +---- +21 + +statement ok +DROP TABLE TBLCHECK + diff --git a/sql/test/BugConstraints/Tests/All b/sql/test/BugConstraints/Tests/All --- a/sql/test/BugConstraints/Tests/All +++ b/sql/test/BugConstraints/Tests/All @@ -3,3 +3,6 @@ delete_primarykey_1643845 drop_column_1643542 update_primarykey_1643375 check_constraint.SF-1714829 +3568_check_not_enforced +3335_check_no_error +check_constraints diff --git a/sql/test/BugConstraints/Tests/check_constraints.test b/sql/test/BugConstraints/Tests/check_constraints.test new file mode 100644 --- /dev/null +++ b/sql/test/BugConstraints/Tests/check_constraints.test @@ -0,0 +1,53 @@ +statement ok +create table kw(x integer primary key check(x >= 1), nm varchar(30) check (nm IS NOT NULL AND length(nm) >= 0), constraint kwname_upper check (nm = upper(nm)), constraint kwname_len_1 check (length(nm) > 0) ) + +statement ok +insert into kw values(1, 'FIRST') + +statement error UPDATE: CHECK constraint violated: kw_x_check +insert into kw values(0, 'FIRST') + +statement eror UPDATE: CHECK constraint violated: kwname_upper +insert into kw values(2, 'Second') + +statement ok +insert into kw values(3, 'FREE') + +statement eror UPDATE: CHECK constraint violated: kwname_len_1 +insert into kw values(4, '') + +statement eror UPDATE: CHECK constraint violated: kw_nm_check +insert into kw values(5, NULL) + +query IT rowsort +select * from kw +---- +1 +FIRST +3 +FREE + +statement ok +alter table kw drop constraint kw_x_check + +statement ok +alter table kw drop constraint kw_nm_check + +statement ok +alter table kw drop constraint kwname_upper + +statement ok +alter table kw drop constraint kwname_len_1 + +statement ok +insert into kw values(2, 'Second') + +statement ok +insert into kw values(4, '') + +statement ok +insert into kw values(5, NULL) + +statement ok +drop table kw + _______________________________________________ checkin-list mailing list -- checkin-list@monetdb.org To unsubscribe send an email to checkin-list-le...@monetdb.org
