Changeset: 91bbf711ed23 for MonetDB
URL: https://dev.monetdb.org/hg/MonetDB?cmd=changeset;node=91bbf711ed23
Modified Files:
sql/backends/monet5/sql_upgrades.c
sql/server/rel_schema.c
sql/test/SQLancer/Tests/sqlancer04.stable.err
sql/test/SQLancer/Tests/sqlancer04.stable.out
sql/test/emptydb-upgrade-chain-hge/Tests/upgrade.stable.out.int128
sql/test/emptydb-upgrade-chain-hge/Tests/upgrade.stable.out.ppc64.int128
sql/test/emptydb-upgrade-chain/Tests/upgrade.stable.out
sql/test/emptydb-upgrade-chain/Tests/upgrade.stable.out.32bit
sql/test/emptydb-upgrade-chain/Tests/upgrade.stable.out.int128
sql/test/emptydb-upgrade-chain/Tests/upgrade.stable.out.ppc64
sql/test/emptydb-upgrade-chain/Tests/upgrade.stable.out.ppc64.int128
sql/test/emptydb-upgrade-hge/Tests/upgrade.stable.out.int128
sql/test/emptydb-upgrade/Tests/upgrade.stable.out
sql/test/emptydb-upgrade/Tests/upgrade.stable.out.32bit
sql/test/emptydb-upgrade/Tests/upgrade.stable.out.int128
sql/test/emptydb/Tests/check.stable.out
sql/test/emptydb/Tests/check.stable.out.32bit
sql/test/emptydb/Tests/check.stable.out.int128
sql/test/testdb-upgrade-chain-hge/Tests/upgrade.stable.out.int128
sql/test/testdb-upgrade-chain/Tests/upgrade.stable.out
sql/test/testdb-upgrade-chain/Tests/upgrade.stable.out.32bit
sql/test/testdb-upgrade-chain/Tests/upgrade.stable.out.int128
sql/test/testdb-upgrade-hge/Tests/upgrade.stable.out.int128
sql/test/testdb-upgrade/Tests/upgrade.stable.out
sql/test/testdb-upgrade/Tests/upgrade.stable.out.32bit
sql/test/testdb-upgrade/Tests/upgrade.stable.out.int128
Branch: default
Log Message:
Merge with oscar branch.
diffs (truncated from 153718 to 300 lines):
diff --git a/sql/backends/monet5/sql_upgrades.c
b/sql/backends/monet5/sql_upgrades.c
--- a/sql/backends/monet5/sql_upgrades.c
+++ b/sql/backends/monet5/sql_upgrades.c
@@ -36,6 +36,7 @@ sql_fix_system_tables(Client c, mvc *sql
char *buf = GDKmalloc(bufsize), *err = NULL;
node *n;
sql_schema *s;
+ static const char *boolnames[2] = {"false", "true"};
if (buf == NULL)
throw(SQL, __func__, SQLSTATE(HY013) MAL_MALLOC_FAIL);
@@ -83,12 +84,12 @@ sql_fix_system_tables(Client c, mvc *sql
func->base.id, func->base.name,
func->imp, func->mod, (int) FUNC_LANG_INT,
(int) func->type,
- func->side_effect ? "true" : "false",
- func->varres ? "true" : "false",
- func->vararg ? "true" : "false",
+ boolnames[func->side_effect],
+ boolnames[func->varres],
+ boolnames[func->vararg],
func->s ? func->s->base.id : s->base.id,
- func->system ? "true" : "false",
- func->semantics ? "true" : "false");
+ boolnames[func->system],
+ boolnames[func->semantics]);
if (func->res) {
for (m = func->res->h; m; m = m->next, number++) {
arg = m->data;
@@ -2315,132 +2316,6 @@ sql_update_oscar(Client c, mvc *sql, con
/* Move sys.degrees and sys.radians to sql_types.c
definitions (I did this at the bat_logger) Remove the obsolete entries at
privileges table */
pos += snprintf(buf + pos, bufsize - pos,
"delete from privileges where obj_id in
(select obj_id from privileges left join functions on privileges.obj_id =
functions.id where functions.id is null and privileges.obj_id not in ((SELECT
tables.id from tables), 0));\n");
- pos += snprintf(buf + pos, bufsize - pos,
- "UPDATE sys.functions set semantics = false
WHERE (name, func) IN (VALUES \n"
- "('length', 'nitems'),\n"
- "('octet_length', 'nitems'),\n"
- "('>', '>'),\n"
- "('>=', '>='),\n"
- "('<', '<'),\n"
- "('<=', '<='),\n"
- "('min', 'min'),\n"
- "('max', 'max'),\n"
- "('sql_min', 'min'),\n"
- "('sql_max', 'max'),\n"
- "('least', 'min_no_nil'),\n"
- "('greatest', 'max_no_nil'),\n"
- "('sum', 'sum'),\n"
- "('prod', 'prod'),\n"
- "('mod', '%%'),\n"
- "('and', 'and'),\n"
- "('xor', 'xor'),\n"
- "('not', 'not'),\n"
- "('sql_mul', '*'),\n"
- "('sql_div', '/'),\n"
- "('sql_sub', '-'),\n"
- "('sql_add', '+'),\n"
- "('bit_and', 'and'),\n"
- "('bit_or', 'or'),\n"
- "('bit_xor', 'xor'),\n"
- "('bit_not', 'not'),\n"
- "('left_shift', '<<'),\n"
- "('right_shift', '>>'),\n"
- "('abs', 'abs'),\n"
- "('sign', 'sign'),\n"
- "('scale_up', '*'),\n"
- "('scale_down', 'dec_round'),\n"
- "('round', 'round'),\n"
- "('power', 'pow'),\n"
- "('floor', 'floor'),\n"
- "('ceil', 'ceil'),\n"
- "('ceiling', 'ceil'),\n"
- "('sin', 'sin'),\n"
- "('cos', 'cos'),\n"
- "('tan', 'tan'),\n"
- "('asin', 'asin'),\n"
- "('acos', 'acos'),\n"
- "('atan', 'atan'),\n"
- "('atan', 'atan2'),\n"
- "('sinh', 'sinh'),\n"
- "('cot', 'cot'),\n"
- "('cosh', 'cosh'),\n"
- "('tanh', 'tanh'),\n"
- "('sqrt', 'sqrt'),\n"
- "('exp', 'exp'),\n"
- "('log', 'log'),\n"
- "('ln', 'log'),\n"
- "('log10', 'log10'),\n"
- "('log2', 'log2'),\n"
- "('degrees', 'degrees'),\n"
- "('radians', 'radians'),\n"
- "('pi', 'pi'),\n"
- "('curdate', 'current_date'),\n"
- "('current_date', 'current_date'),\n"
- "('curtime', 'current_time'),\n"
- "('current_time', 'current_time'),\n"
- "('current_timestamp',
'current_timestamp'),\n"
- "('localtime', 'current_time'),\n"
- "('localtimestamp',
'current_timestamp'),\n"
- "('sql_sub', 'diff'),\n"
- "('sql_sub',
'date_sub_msec_interval'),\n"
- "('sql_sub',
'date_sub_month_interval'),\n"
- "('sql_sub',
'time_sub_msec_interval'),\n"
- "('sql_sub',
'timestamp_sub_msec_interval'),\n"
- "('sql_sub',
'timestamp_sub_month_interval'),\n"
- "('sql_add',
'date_add_msec_interval'),\n"
- "('sql_add', 'addmonths'),\n"
- "('sql_add',
'timestamp_add_msec_interval'),\n"
- "('sql_add',
'timestamp_add_month_interval'),\n"
- "('sql_add',
'time_add_msec_interval'),\n"
- "('local_timezone',
'local_timezone'),\n"
- "('century', 'century'),\n"
- "('decade', 'decade'),\n"
- "('year', 'year'),\n"
- "('quarter', 'quarter'),\n"
- "('month', 'month'),\n"
- "('day', 'day'),\n"
- "('dayofyear', 'dayofyear'),\n"
- "('weekofyear', 'weekofyear'),\n"
- "('dayofweek', 'dayofweek'),\n"
- "('dayofmonth', 'day'),\n"
- "('week', 'weekofyear'),\n"
- "('hour', 'hours'),\n"
- "('minute', 'minutes'),\n"
- "('second', 'sql_seconds'),\n"
- "('second', 'seconds'),\n"
- "('strings', 'strings'),\n"
- "('locate', 'locate'),\n"
- "('charindex', 'locate'),\n"
- "('splitpart', 'splitpart'),\n"
- "('substring', 'substring'),\n"
- "('substr', 'substring'),\n"
- "('truncate', 'stringleft'),\n"
- "('concat', '+'),\n"
- "('ascii', 'ascii'),\n"
- "('code', 'unicode'),\n"
- "('length', 'length'),\n"
- "('right', 'stringright'),\n"
- "('left', 'stringleft'),\n"
- "('upper', 'toUpper'),\n"
- "('ucase', 'toUpper'),\n"
- "('lower', 'toLower'),\n"
- "('lcase', 'toLower'),\n"
- "('trim', 'trim'),\n"
- "('ltrim', 'ltrim'),\n"
- "('rtrim', 'rtrim'),\n"
- "('lpad', 'lpad'),\n"
- "('rpad', 'rpad'),\n"
- "('insert', 'insert'),\n"
- "('replace', 'replace'),\n"
- "('repeat', 'repeat'),\n"
- "('space', 'space'),\n"
- "('char_length', 'length'),\n"
- "('character_length', 'length'),\n"
- "('octet_length', 'nbytes'),\n"
- "('soundex', 'soundex'),\n"
- "('qgramnormalize', 'qgramnormalize')\n"
- ") and type <> %d;\n",
- F_ANALYTIC);
pos += snprintf(buf + pos, bufsize - pos, "set schema
\"%s\";\n", prev_schema);
assert(pos < bufsize);
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;
@@ -560,13 +582,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));
_______________________________________________
checkin-list mailing list
checkin-list@monetdb.org
https://www.monetdb.org/mailman/listinfo/checkin-list
