Changeset: d0ffd8595669 for MonetDB
URL: https://dev.monetdb.org/hg/MonetDB?cmd=changeset;node=d0ffd8595669
Modified Files:
sql/server/rel_schema.c
sql/test/BugTracker-2020/Tests/tpch-cube.Bug-6938.stable.out
sql/test/emptydb-upgrade-chain/Tests/upgrade.stable.out.32bit
sql/test/emptydb-upgrade/Tests/upgrade.stable.out.32bit
sql/test/pg_regress/Tests/alter_table.stable.err
sql/test/testdb-upgrade-chain/Tests/upgrade.stable.out.32bit
sql/test/testdb-upgrade/Tests/upgrade.stable.out.32bit
Branch: oscar
Log Message:
Merge with Jun2020 branch.
diffs (229 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
@@ -315,6 +315,14 @@ 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)
+{
+ if (lt->type->eclass == EC_EXTERNAL && rt->type->eclass == EC_EXTERNAL)
+ return lt->type->localtype == rt->type->localtype;
+ 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, bool isDeclared, int *used)
{
@@ -354,6 +362,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);
/*
@@ -394,6 +403,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;
@@ -562,13 +584,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/BugTracker-2020/Tests/tpch-cube.Bug-6938.sql
b/sql/test/BugTracker-2020/Tests/tpch-cube.Bug-6938.sql
--- a/sql/test/BugTracker-2020/Tests/tpch-cube.Bug-6938.sql
+++ b/sql/test/BugTracker-2020/Tests/tpch-cube.Bug-6938.sql
@@ -132,7 +132,7 @@ SELECT
DM_CustomerNation_N_NAME AS CustomerNation,
DM_CustomerRegion_R_NAME AS CustomerRegion,
avg(DM_Li_L_DISCOUNT) AS L_DISCOUNT_Customer_Nation_Supplier_Nation,
- sum(DM_Li_L_QUANTITY) AS L_QUANTITY_Customer_Nation_Supplier_Nation,
+ CAST(sum(DM_Li_L_QUANTITY) AS DECIMAL(10,5)) AS
L_QUANTITY_Customer_Nation_Supplier_Nation,
O_ORDERDATE_Year,
DM_Nation_N_NAME AS SupplierNation,
DM_Region_R_NAME AS SupplierRegion
diff --git a/sql/test/BugTracker-2020/Tests/tpch-cube.Bug-6938.stable.out
b/sql/test/BugTracker-2020/Tests/tpch-cube.Bug-6938.stable.out
--- a/sql/test/BugTracker-2020/Tests/tpch-cube.Bug-6938.stable.out
+++ b/sql/test/BugTracker-2020/Tests/tpch-cube.Bug-6938.stable.out
@@ -113,7 +113,7 @@ stdout of test 'tpch-cube.Bug-6938` in d
% ., ., ., ., .customerorderdetails, ., . # table_name
% customernation, customerregion,
l_discount_customer_nation_supplier_nation,
l_quantity_customer_nation_supplier_nation, o_orderdate_year,
suppliernation, supplierregion # name
% char, char, decimal, decimal, int, char, char #
type
-% 25, 25, 20, 40, 1, 25, 25 # length
+% 25, 25, 20, 12, 1, 25, 25 # length
[ NULL, NULL, NULL, NULL, NULL, NULL, NULL ]
#ROLLBACK;
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
@@ -46,3 +46,37 @@ START TRANSACTION;
CREATE TABLE "sys"."t0" ("c0" INTERVAL SECOND NOT NULL);
SELECT CASE '5'^3 WHEN COUNT(TRUE) THEN 1 END FROM t0 GROUP BY 2 IN
((CAST(INTERVAL '-2' SECOND AS INT))%2); --error on default: types
sec_interval(13,0) and int(32,0) are not equal
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
+
+SELECT 1 FROM t1 LEFT OUTER JOIN t0 ON TRUE
+LEFT OUTER JOIN (SELECT 1 FROM t0) AS sub0 ON TRUE
+WHERE (TIME '00:25:07') IN (TIME '07:29:34', CASE 2 WHEN 2 THEN TIME
'17:23:46' ELSE TIME '05:14:30' END);
+ -- empty
+
+SELECT 1 FROM t1 LEFT OUTER JOIN t0 ON TRUE
+LEFT OUTER JOIN (SELECT 1 FROM t0) AS sub0 ON TRUE
+WHERE t0.c0 <= t0.c0 AND (TIME '00:25:07') IN (TIME '07:29:34', CASE 2 WHEN 2
THEN TIME '17:23:46' ELSE TIME '05:14:30' END);
+ -- empty
+
+SELECT ALL COUNT(TIME '06:32:50') FROM t1 LEFT OUTER JOIN t0 ON TRUE
+LEFT OUTER JOIN (SELECT t1.c1, t0.c1, 0.43 FROM t0, t1) AS sub0 ON TRUE
+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: can't alter 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
