Changeset: 09eb79cdcf5b for MonetDB
URL: https://dev.monetdb.org/hg/MonetDB?cmd=changeset;node=09eb79cdcf5b
Modified Files:
sql/server/rel_schema.c
sql/test/SQLancer/Tests/sqlancer04.sql
sql/test/SQLancer/Tests/sqlancer04.stable.err
sql/test/SQLancer/Tests/sqlancer04.stable.out
sql/test/pg_regress/Tests/alter_table.stable.err
Branch: Jun2020
Log Message:
Making SQLancer happy, we retrict foreign key columns to be of the same type
class, except for the compatability between char and clob (they are mapped to
the same type on the backend)
diffs (179 lines):
diff --git a/sql/server/rel_schema.c b/sql/server/rel_schema.c
--- a/sql/server/rel_schema.c
+++ b/sql/server/rel_schema.c
@@ -319,6 +319,12 @@ column_constraint_name(mvc *sql, symbol
#define COL_NULL 0
#define COL_DEFAULT 1
+static bool
+foreign_key_check_types(sql_subtype *lt, sql_subtype *rt)
+{
+ return lt->type->eclass == rt->type->eclass ||
(EC_VARCHAR(lt->type->eclass) && EC_VARCHAR(rt->type->eclass));
+}
+
static int
column_constraint_type(mvc *sql, const char *name, symbol *s, sql_schema *ss,
sql_table *t, sql_column *cs, int *used)
{
@@ -358,6 +364,7 @@ column_constraint_type(mvc *sql, const c
sql_fkey *fk;
list *cols;
sql_key *rk = NULL;
+ sql_kc *kc;
assert(n->next->next->next->type == type_int);
/*
@@ -398,6 +405,19 @@ column_constraint_type(mvc *sql, const c
(void) sql_error(sql, 02, SQLSTATE(42000) "CONSTRAINT
FOREIGN KEY: not all columns are handled\n");
return res;
}
+ kc = rk->columns->h->data;
+ if (!foreign_key_check_types(&cs->type, &kc->c->type)) {
+ str tp1 = subtype2string(&cs->type), tp2 =
subtype2string(&kc->c->type);
+
+ if (!tp1 || !tp2)
+ (void) sql_error(sql, 02, SQLSTATE(HY013)
MAL_MALLOC_FAIL);
+ else
+ (void) sql_error(sql, 02, SQLSTATE(42000)
"CONSTRAINT FOREIGN KEY: the type of the FOREIGN KEY column '%s' %s is not
compatible with the referenced %s KEY column type %s\n",
+ cs->base.name,
tp1, rk->type == pkey ? "PRIMARY" : "UNIQUE", tp2);
+ _DELETE(tp1);
+ _DELETE(tp2);
+ return res;
+ }
fk = mvc_create_fkey(sql, t, name, fkey, rk, ref_actions & 255,
(ref_actions>>8) & 255);
mvc_create_fkc(sql, fk, cs);
res = SQL_OK;
@@ -566,13 +586,26 @@ table_foreign_key(mvc *sql, char *name,
for (fnms = rk->columns->h; nms && fnms; nms = nms->next, fnms
= fnms->next) {
char *nm = nms->data.sval;
- sql_column *c = mvc_bind_column(sql, t, nm);
+ sql_column *cs = mvc_bind_column(sql, t, nm);
+ sql_kc *kc = fnms->data;
- if (!c) {
+ if (!cs) {
sql_error(sql, 02, SQLSTATE(42S22) "CONSTRAINT
FOREIGN KEY: no such column '%s' in table '%s'\n", nm, t->base.name);
return SQL_ERR;
}
- mvc_create_fkc(sql, fk, c);
+ if (!foreign_key_check_types(&cs->type, &kc->c->type)) {
+ str tp1 = subtype2string(&cs->type), tp2 =
subtype2string(&kc->c->type);
+
+ if (!tp1 || !tp2)
+ (void) sql_error(sql, 02,
SQLSTATE(HY013) MAL_MALLOC_FAIL);
+ else
+ (void) sql_error(sql, 02,
SQLSTATE(42000) "CONSTRAINT FOREIGN KEY: the type of the FOREIGN KEY column
'%s' %s is not compatible with the referenced %s KEY column type %s\n",
+
cs->base.name, tp1, rk->type == pkey ? "PRIMARY" : "UNIQUE", tp2);
+ _DELETE(tp1);
+ _DELETE(tp2);
+ return SQL_ERR;
+ }
+ mvc_create_fkc(sql, fk, cs);
}
if (nms || fnms) {
sql_error(sql, 02, SQLSTATE(42000) "CONSTRAINT FOREIGN
KEY: not all columns are handled\n");
diff --git a/sql/test/SQLancer/Tests/sqlancer04.sql
b/sql/test/SQLancer/Tests/sqlancer04.sql
--- a/sql/test/SQLancer/Tests/sqlancer04.sql
+++ b/sql/test/SQLancer/Tests/sqlancer04.sql
@@ -70,3 +70,13 @@ LEFT OUTER JOIN (SELECT t1.c1, t0.c1, 0.
WHERE ((NOT ((TIME '00:25:07') IN (TIME '07:29:34', TIME '05:21:58', CASE 0.54
WHEN 0.65 THEN TIME '17:23:46' ELSE TIME '05:14:30' END))))
GROUP BY TIMESTAMP '1969-12-08 01:47:58';
ROLLBACK;
+
+CREATE TABLE t0(c0 TIME, UNIQUE(c0));
+CREATE TABLE t1(c0 CHAR(222), FOREIGN KEY (c0) REFERENCES t0(c0) MATCH FULL,
PRIMARY KEY(c0)); --error, foreign key from char to time not allowed
+CREATE TABLE t1(c0 int, FOREIGN KEY (c0) REFERENCES t0(c0) MATCH FULL, PRIMARY
KEY(c0)); --error, foreign key from int to time not allowed
+DROP TABLE t0;
+
+CREATE TABLE t0(c0 INTERVAL SECOND, UNIQUE(c0));
+CREATE TABLE t1(c2 BLOB, FOREIGN KEY (c2) REFERENCES t0(c0) MATCH FULL,
PRIMARY KEY(c2)); --error, foreign key from blob to interval second not allowed
+CREATE TABLE t1(c2 TIME, FOREIGN KEY (c2) REFERENCES t0(c0) MATCH FULL,
PRIMARY KEY(c2)); --error, foreign key from time to interval second not allowed
+DROP TABLE t0;
diff --git a/sql/test/SQLancer/Tests/sqlancer04.stable.err
b/sql/test/SQLancer/Tests/sqlancer04.stable.err
--- a/sql/test/SQLancer/Tests/sqlancer04.stable.err
+++ b/sql/test/SQLancer/Tests/sqlancer04.stable.err
@@ -14,6 +14,22 @@ QUERY = select case covar_samp(all - (co
when interval '-3' month then 0.3 end as interval second) end then
coalesce (abs(0.6),
ERROR = !SELECT: cannot use non GROUP BY column 't0.c0' in query results
without an aggregate function
CODE = 42000
+MAPI = (monetdb) /var/tmp/mtest-32314/.s.monetdb.31414
+QUERY = CREATE TABLE t1(c0 CHAR(222), FOREIGN KEY (c0) REFERENCES t0(c0) MATCH
FULL, PRIMARY KEY(c0)); --error, foreign key from char to time not allowed
+ERROR = !CONSTRAINT FOREIGN KEY: the type of the FOREIGN KEY column 'c0'
char(222) is not compatible with the referenced UNIQUE KEY column type time(1)
+CODE = 42000
+MAPI = (monetdb) /var/tmp/mtest-32314/.s.monetdb.31414
+QUERY = CREATE TABLE t1(c0 int, FOREIGN KEY (c0) REFERENCES t0(c0) MATCH FULL,
PRIMARY KEY(c0)); --error, foreign key from int to time not allowed
+ERROR = !CONSTRAINT FOREIGN KEY: the type of the FOREIGN KEY column 'c0'
int(32) is not compatible with the referenced UNIQUE KEY column type time(1)
+CODE = 42000
+MAPI = (monetdb) /var/tmp/mtest-42538/.s.monetdb.31166
+QUERY = CREATE TABLE t1(c2 BLOB, FOREIGN KEY (c2) REFERENCES t0(c0) MATCH
FULL, PRIMARY KEY(c2)); --error, foreign key from blob to interval second not
allowed
+ERROR = !CONSTRAINT FOREIGN KEY: the type of the FOREIGN KEY column 'c2' blob
is not compatible with the referenced UNIQUE KEY column type sec_interval(13)
+CODE = 42000
+MAPI = (monetdb) /var/tmp/mtest-42538/.s.monetdb.31166
+QUERY = CREATE TABLE t1(c2 TIME, FOREIGN KEY (c2) REFERENCES t0(c0) MATCH
FULL, PRIMARY KEY(c2)); --error, foreign key from time to interval second not
allowed
+ERROR = !CONSTRAINT FOREIGN KEY: the type of the FOREIGN KEY column 'c2'
time(1) is not compatible with the referenced UNIQUE KEY column type
sec_interval(13)
+CODE = 42000
# 09:44:50 >
# 09:44:50 > "Done."
diff --git a/sql/test/SQLancer/Tests/sqlancer04.stable.out
b/sql/test/SQLancer/Tests/sqlancer04.stable.out
--- a/sql/test/SQLancer/Tests/sqlancer04.stable.out
+++ b/sql/test/SQLancer/Tests/sqlancer04.stable.out
@@ -46,6 +46,19 @@ stdout of test 'sqlancer04` in directory
% tinyint # type
% 1 # length
#ROLLBACK;
+#START TRANSACTION;
+#CREATE TABLE "sys"."t0" ("c0" CHARACTER LARGE OBJECT NOT NULL,"c1" BIGINT NOT
NULL,CONSTRAINT "t0_c1_pkey" PRIMARY KEY ("c1"));
+#CREATE TABLE "sys"."t1" ("c0" CHARACTER LARGE OBJECT,"c1" BIGINT);
+#COPY 3 RECORDS INTO "sys"."t1" FROM stdin USING DELIMITERS E'\t',E'\n','"';
+#"-216073164" NULL
+#"-2044926527" NULL
+#NULL 1
+[ 3 ]
+#ROLLBACK;
+#CREATE TABLE t0(c0 TIME, UNIQUE(c0));
+#DROP TABLE t0;
+#CREATE TABLE t0(c0 INTERVAL SECOND, UNIQUE(c0));
+#DROP TABLE t0;
# 09:44:50 >
# 09:44:50 > "Done."
diff --git a/sql/test/pg_regress/Tests/alter_table.stable.err
b/sql/test/pg_regress/Tests/alter_table.stable.err
--- a/sql/test/pg_regress/Tests/alter_table.stable.err
+++ b/sql/test/pg_regress/Tests/alter_table.stable.err
@@ -82,18 +82,22 @@ MAPI = (monetdb) /var/tmp/mtest-30274/.
QUERY = ALTER TABLE FKTABLE ADD FOREIGN KEY(ftest1) references pktable(ptest1);
ERROR = !ALTER TABLE: not supported on TEMPORARY table 'fktable'
CODE = 42S02
-MAPI = (monetdb) /var/tmp/mtest-30274/.s.monetdb.37685
+MAPI = (monetdb) /var/tmp/mtest-56887/.s.monetdb.37895
+QUERY = ALTER TABLE FKTABLE ADD FOREIGN KEY(ftest1) references pktable;
+ERROR = !CONSTRAINT FOREIGN KEY: the type of the FOREIGN KEY column 'ftest1'
varchar(10) is not compatible with the referenced PRIMARY KEY column type
int(32)
+CODE = 42000
+MAPI = (monetdb) /var/tmp/mtest-56887/.s.monetdb.37895
QUERY = ALTER TABLE FKTABLE ADD FOREIGN KEY(ftest1) references pktable(ptest1);
-ERROR = !Create Key failed, key 'fktable_ftest1_fkey' already exists
+ERROR = !CONSTRAINT FOREIGN KEY: the type of the FOREIGN KEY column 'ftest1'
varchar(10) is not compatible with the referenced PRIMARY KEY column type
int(32)
CODE = 42000
MAPI = (monetdb) /var/tmp/mtest-30274/.s.monetdb.37685
QUERY = ALTER TABLE FKTABLE ADD FOREIGN KEY(ftest1, ftest2) references pktable;
-ERROR = !types int(32,0) and inet(0,0) are not equal for column 'ptest1'
+ERROR = !CONSTRAINT FOREIGN KEY: the type of the FOREIGN KEY column 'ftest1'
inet is not compatible with the referenced PRIMARY KEY column type int(32)
CODE = 42000
MAPI = (monetdb) /var/tmp/mtest-30274/.s.monetdb.37685
QUERY = ALTER TABLE FKTABLE ADD FOREIGN KEY(ftest1, ftest2)
references pktable(ptest1, ptest2);
-ERROR = !types int(32,0) and inet(0,0) are not equal for column 'ptest1'
+ERROR = !CONSTRAINT FOREIGN KEY: the type of the FOREIGN KEY column 'ftest1'
inet is not compatible with the referenced PRIMARY KEY column type int(32)
CODE = 42000
MAPI = (monetdb) /var/tmp/mtest-30274/.s.monetdb.37685
QUERY = ALTER TABLE FKTABLE ADD FOREIGN KEY(ftest1, ftest2)
@@ -103,7 +107,7 @@ CODE = 42000
MAPI = (monetdb) /var/tmp/mtest-30274/.s.monetdb.37685
QUERY = ALTER TABLE FKTABLE ADD FOREIGN KEY(ftest2, ftest1)
references pktable(ptest1, ptest2);
-ERROR = !types int(32,0) and inet(0,0) are not equal for column 'ptest1'
+ERROR = !CONSTRAINT FOREIGN KEY: the type of the FOREIGN KEY column 'ftest2'
inet is not compatible with the referenced PRIMARY KEY column type int(32)
CODE = 42000
MAPI = (monetdb) /var/tmp/mtest-30274/.s.monetdb.37685
QUERY = alter table atacc1 add constraint atacc_test1 check (test>3);
_______________________________________________
checkin-list mailing list
checkin-list@monetdb.org
https://www.monetdb.org/mailman/listinfo/checkin-list
