Changeset: bb55c549fd57 for MonetDB
URL: https://dev.monetdb.org/hg/MonetDB/rev/bb55c549fd57
Modified Files:
sql/backends/monet5/rel_bin.c
sql/test/2024/Tests/check.test
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
Branch: Aug2024
Log Message:
When check constraint is violated at insert, report INSERT INTO instead of
UPDATE in the error msg.
diffs (225 lines):
diff --git a/sql/backends/monet5/rel_bin.c b/sql/backends/monet5/rel_bin.c
--- a/sql/backends/monet5/rel_bin.c
+++ b/sql/backends/monet5/rel_bin.c
@@ -5189,7 +5189,7 @@ sql_insert_check(backend *be, sql_key *k
sql_subfunc *cnt = sql_bind_func(sql, "sys", "count",
sql_bind_localtype("void"), NULL, F_AGGR, true, true);
s = stmt_uselect(be, column(be, s), stmt_bool(be, 0), cmp_equal, NULL,
0, 1);
s = stmt_aggr(be, s, NULL, NULL, cnt, 1, 0, 1);
- char *msg = sa_message(sql->sa, SQLSTATE(40002) "UPDATE: CHECK
constraint violated: %s", key->base.name);
+ char *msg = sa_message(sql->sa, SQLSTATE(40002) "INSERT INTO: CHECK
constraint violated: %s", key->base.name);
(void)stmt_exception(be, s, msg, 00001);
}
diff --git a/sql/test/2024/Tests/check.test b/sql/test/2024/Tests/check.test
--- a/sql/test/2024/Tests/check.test
+++ b/sql/test/2024/Tests/check.test
@@ -4,40 +4,46 @@ create table foo (i int CHECK (i > 0), j
statement ok
insert into foo values (1,10), (2, 20), (3, 30)
-statement error
+statement error 40002!UPDATE: CHECK constraint violated: foo_j_check
update foo set j = -30 where i = 3
-statement error
+statement error 40002!UPDATE: CHECK constraint violated: foo_j_check
update foo set j = -j
-statement error
+statement error 40002!INSERT INTO: CHECK constraint violated: foo_j_check
insert into foo values (4, -40)
statement ok
insert into foo select * from foo
-statement error
+statement error 40002!INSERT INTO: CHECK constraint violated: foo_j_check
insert into foo select 4, -40
statement ok
+drop table foo
+
+statement ok
create table bar (i int, j int, constraint check_i_j CHECK (i < j))
-statement error
+statement error 40002!INSERT INTO: CHECK constraint violated: check_i_j
insert into bar values (30, 20)
statement ok
insert into bar values (10,20), (20, 30), (30, 40)
-statement error
+statement error 40002!UPDATE: CHECK constraint violated: check_i_j
update bar set i = 50 where i = 30
-statement error
+statement error 40002!UPDATE: CHECK constraint violated: check_i_j
update bar set i = 50, j = 40 where i = 30
-statement error
+statement ok
+drop table bar
+
+statement error 42000!SELECT: subquery not allowed
create table baz(j int check (j > (select max(i) from foo)))
-statement error
+statement error 42000!SELECT: subquery not allowed
create table baz(j int check (j in (select i from foo)))
-- exported from check-constraint.Bug-3335
@@ -48,15 +54,18 @@ create table t3335(x integer check(x > 0
statement ok
insert into t3335 values(1)
-statement error
+statement error 40002!INSERT INTO: CHECK constraint violated: t3335_x_check
insert into t3335 values(0)
-statement error
+statement error 40002!INSERT INTO: CHECK constraint violated: t3335_x_check
insert into t3335 values(2)
-statement error
+statement error 40002!INSERT INTO: CHECK constraint violated: t3335_x_check
insert into t3335 values(-1)
-statement error
+statement error 40002!INSERT INTO: CHECK constraint violated: t3335_x_check
insert into t3335 values(3)
+statement ok
+drop table t3335
+
diff --git a/sql/test/BugConstraints/Tests/3335_check_no_error.test
b/sql/test/BugConstraints/Tests/3335_check_no_error.test
--- a/sql/test/BugConstraints/Tests/3335_check_no_error.test
+++ b/sql/test/BugConstraints/Tests/3335_check_no_error.test
@@ -4,19 +4,19 @@ create table ary(x integer check(x >0 an
statement ok
insert into ary values(1)
-statement error UPDATE: CHECK constraint violated: ary_x_check
+statement error 40002!INSERT INTO: 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
+statement error 40002!INSERT INTO: CHECK constraint violated: ary_x_check
insert into ary values(-1)
-statement error UPDATE: CHECK constraint violated: ary_x_check
+statement error 40002!INSERT INTO: CHECK constraint violated: ary_x_check
insert into ary values(3)
-statement error UPDATE: CHECK constraint violated: ary_x_check
+statement error 40002!UPDATE: CHECK constraint violated: ary_x_check
update ary set x = x+1
statement ok
diff --git a/sql/test/BugConstraints/Tests/3568_check_not_enforced.test
b/sql/test/BugConstraints/Tests/3568_check_not_enforced.test
--- a/sql/test/BugConstraints/Tests/3568_check_not_enforced.test
+++ b/sql/test/BugConstraints/Tests/3568_check_not_enforced.test
@@ -4,31 +4,31 @@ CREATE TABLE TBLCHECK(nr int CHECK (nr >
statement ok
INSERT INTO TBLCHECK VALUES (11)
-statement error 40002!UPDATE: CHECK constraint violated: tblcheck_nr_check
+statement error 40002!INSERT INTO: CHECK constraint violated: tblcheck_nr_check
INSERT INTO TBLCHECK VALUES (10)
-statement error 40002!UPDATE: CHECK constraint violated: tblcheck_nr_check
+statement error 40002!INSERT INTO: CHECK constraint violated: tblcheck_nr_check
INSERT INTO TBLCHECK VALUES (09)
-statement error 40002!UPDATE: CHECK constraint violated: tblcheck_nr_check
+statement error 40002!INSERT INTO: CHECK constraint violated: tblcheck_nr_check
INSERT INTO TBLCHECK VALUES (01)
-statement error 40002!UPDATE: CHECK constraint violated: tblcheck_nr_check
+statement error 40002!INSERT INTO: CHECK constraint violated: tblcheck_nr_check
INSERT INTO TBLCHECK VALUES (00)
-statement error 40002!UPDATE: CHECK constraint violated: tblcheck_nr_check
+statement error 40002!INSERT INTO: CHECK constraint violated: tblcheck_nr_check
INSERT INTO TBLCHECK VALUES (-01)
-statement error 40002!UPDATE: CHECK constraint violated: tblcheck_nr_check
+statement error 40002!INSERT INTO: CHECK constraint violated: tblcheck_nr_check
INSERT INTO TBLCHECK VALUES (-+02)
-statement error 40002!UPDATE: CHECK constraint violated: tblcheck_nr_check
+statement error 40002!INSERT INTO: CHECK constraint violated: tblcheck_nr_check
INSERT INTO TBLCHECK VALUES (+-+03)
-statement error 40002!UPDATE: CHECK constraint violated: tblcheck_nr_check
+statement error 40002!INSERT INTO: CHECK constraint violated: tblcheck_nr_check
INSERT INTO TBLCHECK VALUES (-+-04)
-statement error 40002!UPDATE: CHECK constraint violated: tblcheck_nr_check
+statement error 40002!INSERT INTO: CHECK constraint violated: tblcheck_nr_check
INSERT INTO TBLCHECK VALUES (+-+-05)
query I rowsort
diff --git a/sql/test/BugConstraints/Tests/check_constraints.test
b/sql/test/BugConstraints/Tests/check_constraints.test
--- a/sql/test/BugConstraints/Tests/check_constraints.test
+++ b/sql/test/BugConstraints/Tests/check_constraints.test
@@ -4,19 +4,19 @@ create table kw(x integer primary key ch
statement ok
insert into kw values(1, 'FIRST')
-statement error UPDATE: CHECK constraint violated: kw_x_check
+statement error 40002!INSERT INTO: CHECK constraint violated: kw_x_check
insert into kw values(0, 'FIRST')
-statement error UPDATE: CHECK constraint violated: kwname_upper
+statement error 40002!INSERT INTO: CHECK constraint violated: kwname_upper
insert into kw values(2, 'Second')
statement ok
insert into kw values(3, 'FREE')
-statement error UPDATE: CHECK constraint violated: kwname_len_1
+statement error 40002!INSERT INTO: CHECK constraint violated: kwname_len_1
insert into kw values(4, '')
-statement error UPDATE: CHECK constraint violated: kw_nm_check
+statement error 40002!INSERT INTO: CHECK constraint violated: kw_nm_check
insert into kw values(5, NULL)
query IT rowsort
@@ -49,5 +49,32 @@ statement ok
insert into kw values(5, NULL)
statement ok
+alter table kw add constraint kw_x_check check(x >= 1)
+
+statement error 40002!UPDATE: CHECK constraint violated: kw_nm_check
+alter table kw add constraint kw_nm_check check (nm IS NOT NULL AND length(nm)
>= 0)
+
+statement error 40002!UPDATE: CHECK constraint violated: kwname_upper
+alter table kw add constraint kwname_upper check (nm = upper(nm))
+
+statement error 40002!UPDATE: CHECK constraint violated: kwname_len_1
+alter table kw add constraint kwname_len_1 check (length(nm) > 0)
+
+statement ok
+delete from kw where nm IS NULL OR length(nm) = 0 or (nm <> upper(nm))
+
+statement ok
+alter table kw add constraint kw_nm_check check (nm IS NOT NULL AND length(nm)
>= 0)
+
+statement ok
+alter table kw add constraint kwname_upper check (nm = upper(nm))
+
+statement ok
+alter table kw add constraint kwname_len_1 check (length(nm) > 0)
+
+statement ok
+insert into kw values(5, 'FIVE')
+
+statement ok
drop table kw
_______________________________________________
checkin-list mailing list -- [email protected]
To unsubscribe send an email to [email protected]
