Changeset: 1c9d7fbe4bc0 for MonetDB
URL: https://dev.monetdb.org/hg/MonetDB/rev/1c9d7fbe4bc0
Modified Files:
gdk/gdk_calc_convert.c
gdk/gdk_hash.c
monetdb5/optimizer/opt_mergetable.c
sql/backends/monet5/sql_upgrades.c
sql/test/emptydb-previous-upgrade-chain-hge/Tests/upgrade.stable.out.int128
sql/test/emptydb-previous-upgrade-chain/Tests/upgrade.stable.out
sql/test/emptydb-previous-upgrade-chain/Tests/upgrade.stable.out.int128
sql/test/emptydb-previous-upgrade-hge/Tests/upgrade.stable.out.int128
sql/test/emptydb-previous-upgrade/Tests/upgrade.stable.out
sql/test/emptydb-previous-upgrade/Tests/upgrade.stable.out.int128
sql/test/emptydb-upgrade-chain-hge/Tests/upgrade.stable.out.int128
sql/test/emptydb-upgrade-chain/Tests/upgrade.stable.out
sql/test/emptydb-upgrade-chain/Tests/upgrade.stable.out.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.int128
sql/test/emptydb/Tests/check.SQL.py
sql/test/testdb-previous-upgrade-chain-hge/Tests/upgrade.stable.out.int128
sql/test/testdb-previous-upgrade-chain/Tests/upgrade.stable.out
sql/test/testdb-previous-upgrade-chain/Tests/upgrade.stable.out.int128
sql/test/testdb-previous-upgrade-hge/Tests/upgrade.stable.out.int128
sql/test/testdb-previous-upgrade/Tests/upgrade.stable.out
sql/test/testdb-previous-upgrade/Tests/upgrade.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.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.int128
Branch: default
Log Message:
Merge with Dec2023 branch.
diffs (truncated from 650 to 300 lines):
diff --git a/gdk/gdk_calc_convert.c b/gdk/gdk_calc_convert.c
--- a/gdk/gdk_calc_convert.c
+++ b/gdk/gdk_calc_convert.c
@@ -821,6 +821,7 @@ convert_str_fix(BATiter *bi, int tp, voi
return 0;
}
+ int (*atomcmp)(const void *, const void *) = ATOMcompare(tp);
TIMEOUT_LOOP(ci->ncand, qry_ctx) {
oid x = canditer_next(ci) - candoff;
const char *s = BUNtvar(*bi, x);
@@ -834,7 +835,7 @@ convert_str_fix(BATiter *bi, int tp, voi
goto conversion_failed;
}
assert(len == ATOMsize(tp));
- if (ATOMcmp(tp, dst, nil) == 0)
+ if (atomcmp(dst, nil) == 0)
nils++;
}
dst = (void *) ((char *) dst + len);
diff --git a/gdk/gdk_hash.c b/gdk/gdk_hash.c
--- a/gdk/gdk_hash.c
+++ b/gdk/gdk_hash.c
@@ -873,7 +873,8 @@ BAThash_impl(BAT *restrict b, struct can
case TYPE_uuid:
starthash(uuid);
break;
- default:
+ default: {
+ int (*atomcmp)(const void *, const void *) =
ATOMcompare(h->type);
TIMEOUT_LOOP(p, qry_ctx) {
const void *restrict v = BUNtail(bi, o -
b->hseqbase);
c = hash_any(h, v);
@@ -887,8 +888,7 @@ BAThash_impl(BAT *restrict b, struct can
for (hb = hget;
hb != BUN_NONE;
hb = HASHgetlink(h, hb)) {
- if (ATOMcmp(h->type,
- v,
+ if (atomcmp(v,
BUNtail(bi, hb)) ==
0)
break;
}
@@ -902,6 +902,7 @@ BAThash_impl(BAT *restrict b, struct can
GOTO_LABEL_TIMEOUT_HANDLER(bailout,
qry_ctx));
break;
}
+ }
TRC_DEBUG_IF(ACCELERATOR) if (p < cnt1)
TRC_DEBUG_ENDIF(ACCELERATOR,
"%s: abort starthash with "
@@ -949,7 +950,8 @@ BAThash_impl(BAT *restrict b, struct can
case TYPE_uuid:
finishhash(uuid);
break;
- default:
+ default: {
+ int (*atomcmp)(const void *, const void *) =
ATOMcompare(h->type);
TIMEOUT_LOOP(ci->ncand - p, qry_ctx) {
const void *restrict v = BUNtail(bi, o - b->hseqbase);
c = hash_any(h, v);
@@ -959,7 +961,7 @@ BAThash_impl(BAT *restrict b, struct can
for (hb = hget;
hb != BUN_NONE;
hb = HASHgetlink(h, hb)) {
- if (ATOMcmp(h->type, v, BUNtail(bi,
hb)) == 0)
+ if (atomcmp(v, BUNtail(bi, hb)) == 0)
break;
}
h->nunique += hb == BUN_NONE;
@@ -973,6 +975,7 @@ BAThash_impl(BAT *restrict b, struct can
GOTO_LABEL_TIMEOUT_HANDLER(bailout, qry_ctx));
break;
}
+ }
bat_iterator_end(&bi);
/* if the number of unique values is equal to the bat count,
* all values are necessarily distinct */
diff --git a/monetdb5/optimizer/opt_mergetable.c
b/monetdb5/optimizer/opt_mergetable.c
--- a/monetdb5/optimizer/opt_mergetable.c
+++ b/monetdb5/optimizer/opt_mergetable.c
@@ -944,6 +944,68 @@ mat_join2(MalBlkPtr mb, InstrPtr p, matl
}
static int
+mat_rangejoin(MalBlkPtr mb, InstrPtr p, matlist_t *ml, int m, int n)
+{
+ int tpe1 = getArgType(mb, p, 0), tpe2 = getArgType(mb, p, 1), k, nr = 1;
+ mat_t *mat = ml->v;
+ InstrPtr l;
+ InstrPtr r;
+
+ //printf("# %s.%s(%d,%d)", getModuleId(p), getFunctionId(p), m, n);
+
+ assert(m >= 0 && n >= 0 && mat[m].mi->argc == mat[n].mi->argc);
+ l = newInstructionArgs(mb, matRef, packRef, mat[m].mi->argc *
mat[n].mi->argc);
+ r = newInstructionArgs(mb, matRef, packRef, mat[m].mi->argc *
mat[n].mi->argc);
+ if (!l || !r) {
+ freeInstruction(l);
+ freeInstruction(r);
+ return -1;
+ }
+
+ getArg(l, 0) = getArg(p, 0);
+ getArg(r, 0) = getArg(p, 1);
+
+ for (k = 1; k < mat[m].mi->argc; k++) {
+ InstrPtr q = copyInstruction(p);
+
+ if (!q) {
+ freeInstruction(l);
+ freeInstruction(r);
+ return -1;
+ }
+
+ getArg(q, 0) = newTmpVariable(mb, tpe1);
+ getArg(q, 1) = newTmpVariable(mb, tpe2);
+
+ getArg(q, 3) = getArg(mat[m].mi, k);
+ getArg(q, 4) = getArg(mat[n].mi, k);
+ pushInstruction(mb, q);
+
+ if (mb->errors || propagatePartnr(ml, getArg(mat[m].mi, k),
getArg(q, 0), nr)
+ || propagatePartnr(ml, getArg(mat[n].mi,
k), getArg(q, 1), nr)) {
+ freeInstruction(r);
+ freeInstruction(l);
+ return -1;
+ }
+
+ /* add result to mat */
+ l = pushArgument(mb, l, getArg(q, 0));
+ r = pushArgument(mb, r, getArg(q, 1));
+ nr++;
+ }
+ if (mb->errors || mat_add(ml, l, mat_none, getFunctionId(p))) {
+ freeInstruction(l);
+ freeInstruction(r);
+ return -1;
+ }
+ if (mat_add(ml, r, mat_none, getFunctionId(p))) {
+ freeInstruction(r);
+ return -1;
+ }
+ return 0;
+}
+
+static int
join_split(Client cntxt, InstrPtr p, int args)
{
char *name = NULL;
@@ -2420,6 +2482,15 @@ OPTmergetableImplementation(Client cntxt
goto cleanup;
}
} else {
+ if (bats + nilbats == 5 && !is_a_mat(getArg(p,
p->retc), &ml) && match == 2) {
+ n = is_a_mat(getArg(p, p->retc + 1),
&ml);
+ o = is_a_mat(getArg(p, p->retc + 2),
&ml);
+ if (mat_rangejoin(mb, p, &ml, n, o)) {
+ msg = createException(MAL,
"optimizer.mergetable",
+
SQLSTATE(HY013) MAL_MALLOC_FAIL);
+ goto cleanup;
+ }
+ } else
if (mat_joinNxM(cntxt, mb, p, &ml, bats)) {
msg = createException(MAL,
"optimizer.mergetable",
SQLSTATE(HY013) MAL_MALLOC_FAIL);
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
@@ -6617,17 +6617,24 @@ sql_update_dec2023_sp4(Client c, mvc *sq
b = BATdescriptor(output->cols[0].b);
if (b) {
if (BATcount(b) > 0) {
- const char query[] = "delete from sys.dependencies
where (id, depend_id) in (select c.id, f.id from sys.functions f, sys._tables
t, sys._columns c, sys.dependencies d where c.table_id = t.id and f.id =
d.depend_id and c.id = d.id and f.schema_id = 2000 and t.schema_id = 2000 and
(f.name, t.name, c.name) in (values ('describe_columns', '_columns',
'storage'), ('describe_function', 'function_languages', 'language_name'),
('describe_function', 'function_types', 'function_type_name'),
('describe_function', 'functions', 'func'), ('describe_function', 'functions',
'mod'), ('describe_function', 'functions', 'semantics'), ('describe_function',
'functions', 'side_effect'), ('describe_function', 'functions', 'system'),
('describe_function', 'functions', 'vararg'), ('describe_function',
'functions', 'varres'), ('describe_function', 'schemas', 'authorization'),
('describe_function', 'schemas', 'owner'), ('describe_function', 'schemas',
'system'), ('describe_table', '_tables', 'access'), ('de
scribe_table', '_tables', 'commit_action'), ('describe_table', '_tables',
'system')));\n"
+ const char query[] =
+ "delete from sys.dependencies where (id,
depend_id) in (select c.id, f.id from sys.functions f, sys._tables t,
sys._columns c, sys.dependencies d where c.table_id = t.id and f.id =
d.depend_id and c.id = d.id and f.schema_id = 2000 and t.schema_id = 2000 and
(f.name, t.name, c.name) in (values ('describe_columns', '_columns',
'storage'), ('describe_function', 'function_languages', 'language_name'),
('describe_function', 'function_types', 'function_type_name'),
('describe_function', 'functions', 'func'), ('describe_function', 'functions',
'mod'), ('describe_function', 'functions', 'semantics'), ('describe_function',
'functions', 'side_effect'), ('describe_function', 'functions', 'system'),
('describe_function', 'functions', 'vararg'), ('describe_function',
'functions', 'varres'), ('describe_function', 'schemas', 'authorization'),
('describe_function', 'schemas', 'owner'), ('describe_function', 'schemas',
'system'), ('describe_table', '_tables', 'access'), ('describe_table', '_tab
les', 'commit_action'), ('describe_table', '_tables', 'system')));\n"
"delete from sys.dependencies where (id,
depend_id) in (select c.id, v.id from sys._tables v, sys._tables t,
sys._columns c, sys.dependencies d where c.table_id = t.id and v.id =
d.depend_id and c.id = d.id and v.schema_id = 2000 and t.schema_id = 2000 and
(v.name, t.name, c.name) in (values ('dependency_columns_on_indexes',
'_columns', 'name'), ('dependency_columns_on_indexes', '_columns', 'number'),
('dependency_columns_on_indexes', '_columns', 'storage'),
('dependency_columns_on_indexes', '_columns', 'table_id'),
('dependency_columns_on_indexes', '_columns', 'type_digits'),
('dependency_columns_on_indexes', 'keys', 'id'),
('dependency_columns_on_indexes', 'triggers', 'name'),
('dependency_columns_on_indexes', 'triggers', 'orientation'),
('dependency_columns_on_indexes', 'triggers', 'table_id'),
('dependency_columns_on_indexes', 'triggers', 'time'),
('dependency_columns_on_keys', '_columns', 'name'),
('dependency_columns_on_keys', '_columns', 'table_id'), ('dependency_columns_
on_keys', '_columns', 'type'), ('dependency_columns_on_keys', '_columns',
'type_digits'), ('dependency_columns_on_keys', '_columns', 'type_scale'),
('dependency_columns_on_keys', 'triggers', 'name'),
('dependency_columns_on_keys', 'triggers', 'orientation'),
('dependency_columns_on_keys', 'triggers', 'table_id'),
('dependency_columns_on_keys', 'triggers', 'time'),
('dependency_columns_on_triggers', 'keys', 'name'),
('dependency_columns_on_triggers', 'keys', 'rkey'),
('dependency_columns_on_triggers', 'keys', 'type'),
('dependency_functions_on_triggers', 'keys', 'action'),
('dependency_functions_on_triggers', 'keys', 'name'),
('dependency_functions_on_triggers', 'keys', 'rkey'),
('dependency_functions_on_triggers', 'keys', 'type'),
('dependency_keys_on_foreignkeys', '_columns', 'default'),
('dependency_keys_on_foreignkeys', '_columns', 'name'),
('dependency_keys_on_foreignkeys', '_columns', 'table_id'),
('dependency_keys_on_foreignkeys', '_columns', 'type'),
('dependency_keys_on_fore
ignkeys', '_columns', 'type_digits'), ('dependency_keys_on_foreignkeys',
'_columns', 'type_scale'), ('dependency_tables_on_foreignkeys', '_columns',
'default'), ('dependency_tables_on_foreignkeys', '_columns', 'name'),
('dependency_tables_on_foreignkeys', '_columns', 'table_id'),
('dependency_tables_on_foreignkeys', '_columns', 'type'),
('dependency_tables_on_foreignkeys', '_columns', 'type_digits'),
('dependency_tables_on_foreignkeys', '_columns', 'type_scale'),
('dependency_tables_on_indexes', '_columns', 'name'),
('dependency_tables_on_indexes', '_columns', 'number'),
('dependency_tables_on_indexes', '_columns', 'storage'),
('dependency_tables_on_indexes', '_columns', 'table_id'),
('dependency_tables_on_indexes', '_columns', 'type_digits'),
('dependency_tables_on_indexes', 'keys', 'id'),
('dependency_tables_on_triggers', 'keys', 'action'),
('dependency_tables_on_triggers', 'keys', 'name'),
('dependency_tables_on_triggers', 'keys', 'rkey'),
('dependency_tables_on_triggers', 'keys'
, 'type')));\n"
- /* the "chain" upgrade check tests fail with a
duplicate
- * entry in sys.dependencies, but running this
query
- * results in NULL values in sys.dependencies
in some of
- * the upgrade tests, so it is disabled */
- /*"create temporary table d as (select distinct
* from sys.dependencies);\ndelete from sys.dependencies;\ninsert into
sys.dependencies (select * from d);\n"*/;
+ "delete from sys.triggers where table_id not in
(select id from sys._tables);\n"
+ "commit;\n";
assert(BATcount(b) == 1);
printf("Running database upgrade commands:\n%s\n",
query);
fflush(stdout);
err = SQLstatementIntern(c, query, "update", true,
false, NULL);
+ if (err == MAL_SUCCEED) {
+ const char query2[] =
+ "create temporary table d as (select
distinct * from sys.dependencies);\n"
+ "delete from sys.dependencies;\n"
+ "insert into sys.dependencies (select *
from d);\n";
+ printf("Running database upgrade
commands:\n%s\n", query2);
+ fflush(stdout);
+ err = SQLstatementIntern(c, query2, "update",
true, false, NULL);
+ }
}
BBPunfix(b->batCacheid);
}
diff --git a/sql/test/emptydb-previous-upgrade-chain-hge/Tests/All
b/sql/test/emptydb-previous-upgrade-chain-hge/Tests/All
--- a/sql/test/emptydb-previous-upgrade-chain-hge/Tests/All
+++ b/sql/test/emptydb-previous-upgrade-chain-hge/Tests/All
@@ -1,2 +1,2 @@
HAVE_HGE&HAVE_FITS&HAVE_GEOM&HAVE_NETCDF&HAVE_SHP&PREVHGECHAINRELEMPTY&!NOWAL?upgrade
-KNOWNFAIL?check
+check
diff --git
a/sql/test/emptydb-previous-upgrade-chain-hge/Tests/upgrade.stable.out.int128
b/sql/test/emptydb-previous-upgrade-chain-hge/Tests/upgrade.stable.out.int128
---
a/sql/test/emptydb-previous-upgrade-chain-hge/Tests/upgrade.stable.out.int128
+++
b/sql/test/emptydb-previous-upgrade-chain-hge/Tests/upgrade.stable.out.int128
@@ -613,6 +613,13 @@ update sys.functions set system = true w
Running database upgrade commands:
delete from sys.dependencies where (id, depend_id) in (select c.id, f.id from
sys.functions f, sys._tables t, sys._columns c, sys.dependencies d where
c.table_id = t.id and f.id = d.depend_id and c.id = d.id and f.schema_id = 2000
and t.schema_id = 2000 and (f.name, t.name, c.name) in (values
('describe_columns', '_columns', 'storage'), ('describe_function',
'function_languages', 'language_name'), ('describe_function', 'function_types',
'function_type_name'), ('describe_function', 'functions', 'func'),
('describe_function', 'functions', 'mod'), ('describe_function', 'functions',
'semantics'), ('describe_function', 'functions', 'side_effect'),
('describe_function', 'functions', 'system'), ('describe_function',
'functions', 'vararg'), ('describe_function', 'functions', 'varres'),
('describe_function', 'schemas', 'authorization'), ('describe_function',
'schemas', 'owner'), ('describe_function', 'schemas', 'system'),
('describe_table', '_tables', 'access'), ('describe_table', '_tables',
'commit_action'), ('describe_table', '_tables', 'system')));
delete from sys.dependencies where (id, depend_id) in (select c.id, v.id from
sys._tables v, sys._tables t, sys._columns c, sys.dependencies d where
c.table_id = t.id and v.id = d.depend_id and c.id = d.id and v.schema_id = 2000
and t.schema_id = 2000 and (v.name, t.name, c.name) in (values
('dependency_columns_on_indexes', '_columns', 'name'),
('dependency_columns_on_indexes', '_columns', 'number'),
('dependency_columns_on_indexes', '_columns', 'storage'),
('dependency_columns_on_indexes', '_columns', 'table_id'),
('dependency_columns_on_indexes', '_columns', 'type_digits'),
('dependency_columns_on_indexes', 'keys', 'id'),
('dependency_columns_on_indexes', 'triggers', 'name'),
('dependency_columns_on_indexes', 'triggers', 'orientation'),
('dependency_columns_on_indexes', 'triggers', 'table_id'),
('dependency_columns_on_indexes', 'triggers', 'time'),
('dependency_columns_on_keys', '_columns', 'name'),
('dependency_columns_on_keys', '_columns', 'table_id'),
('dependency_columns_on_ke
ys', '_columns', 'type'), ('dependency_columns_on_keys', '_columns',
'type_digits'), ('dependency_columns_on_keys', '_columns', 'type_scale'),
('dependency_columns_on_keys', 'triggers', 'name'),
('dependency_columns_on_keys', 'triggers', 'orientation'),
('dependency_columns_on_keys', 'triggers', 'table_id'),
('dependency_columns_on_keys', 'triggers', 'time'),
('dependency_columns_on_triggers', 'keys', 'name'),
('dependency_columns_on_triggers', 'keys', 'rkey'),
('dependency_columns_on_triggers', 'keys', 'type'),
('dependency_functions_on_triggers', 'keys', 'action'),
('dependency_functions_on_triggers', 'keys', 'name'),
('dependency_functions_on_triggers', 'keys', 'rkey'),
('dependency_functions_on_triggers', 'keys', 'type'),
('dependency_keys_on_foreignkeys', '_columns', 'default'),
('dependency_keys_on_foreignkeys', '_columns', 'name'),
('dependency_keys_on_foreignkeys', '_columns', 'table_id'),
('dependency_keys_on_foreignkeys', '_columns', 'type'),
('dependency_keys_on_foreignke
ys', '_columns', 'type_digits'), ('dependency_keys_on_foreignkeys',
'_columns', 'type_scale'), ('dependency_tables_on_foreignkeys', '_columns',
'default'), ('dependency_tables_on_foreignkeys', '_columns', 'name'),
('dependency_tables_on_foreignkeys', '_columns', 'table_id'),
('dependency_tables_on_foreignkeys', '_columns', 'type'),
('dependency_tables_on_foreignkeys', '_columns', 'type_digits'),
('dependency_tables_on_foreignkeys', '_columns', 'type_scale'),
('dependency_tables_on_indexes', '_columns', 'name'),
('dependency_tables_on_indexes', '_columns', 'number'),
('dependency_tables_on_indexes', '_columns', 'storage'),
('dependency_tables_on_indexes', '_columns', 'table_id'),
('dependency_tables_on_indexes', '_columns', 'type_digits'),
('dependency_tables_on_indexes', 'keys', 'id'),
('dependency_tables_on_triggers', 'keys', 'action'),
('dependency_tables_on_triggers', 'keys', 'name'),
('dependency_tables_on_triggers', 'keys', 'rkey'),
('dependency_tables_on_triggers', 'keys', 'ty
pe')));
+delete from sys.triggers where table_id not in (select id from sys._tables);
+commit;
+
+Running database upgrade commands:
+create temporary table d as (select distinct * from sys.dependencies);
+delete from sys.dependencies;
+insert into sys.dependencies (select * from d);
Running database upgrade commands:
update sys._columns set type_digits = 7 where type = 'tinyint' and type_digits
<> 7;
diff --git a/sql/test/emptydb-previous-upgrade-chain/Tests/All
b/sql/test/emptydb-previous-upgrade-chain/Tests/All
--- a/sql/test/emptydb-previous-upgrade-chain/Tests/All
+++ b/sql/test/emptydb-previous-upgrade-chain/Tests/All
@@ -1,2 +1,2 @@
HAVE_FITS&HAVE_GEOM&HAVE_NETCDF&HAVE_SHP&PREVCHAINRELEMPTY&!NOWAL?upgrade
-KNOWNFAIL?check
+check
diff --git a/sql/test/emptydb-previous-upgrade-chain/Tests/upgrade.stable.out
b/sql/test/emptydb-previous-upgrade-chain/Tests/upgrade.stable.out
--- a/sql/test/emptydb-previous-upgrade-chain/Tests/upgrade.stable.out
+++ b/sql/test/emptydb-previous-upgrade-chain/Tests/upgrade.stable.out
@@ -613,6 +613,13 @@ update sys.functions set system = true w
Running database upgrade commands:
delete from sys.dependencies where (id, depend_id) in (select c.id, f.id from
sys.functions f, sys._tables t, sys._columns c, sys.dependencies d where
c.table_id = t.id and f.id = d.depend_id and c.id = d.id and f.schema_id = 2000
and t.schema_id = 2000 and (f.name, t.name, c.name) in (values
('describe_columns', '_columns', 'storage'), ('describe_function',
'function_languages', 'language_name'), ('describe_function', 'function_types',
'function_type_name'), ('describe_function', 'functions', 'func'),
('describe_function', 'functions', 'mod'), ('describe_function', 'functions',
'semantics'), ('describe_function', 'functions', 'side_effect'),
('describe_function', 'functions', 'system'), ('describe_function',
'functions', 'vararg'), ('describe_function', 'functions', 'varres'),
('describe_function', 'schemas', 'authorization'), ('describe_function',
'schemas', 'owner'), ('describe_function', 'schemas', 'system'),
('describe_table', '_tables', 'access'), ('describe_table', '_tables',
'commit_action'), ('describe_table', '_tables', 'system')));
delete from sys.dependencies where (id, depend_id) in (select c.id, v.id from
sys._tables v, sys._tables t, sys._columns c, sys.dependencies d where
c.table_id = t.id and v.id = d.depend_id and c.id = d.id and v.schema_id = 2000
and t.schema_id = 2000 and (v.name, t.name, c.name) in (values
('dependency_columns_on_indexes', '_columns', 'name'),
('dependency_columns_on_indexes', '_columns', 'number'),
('dependency_columns_on_indexes', '_columns', 'storage'),
('dependency_columns_on_indexes', '_columns', 'table_id'),
('dependency_columns_on_indexes', '_columns', 'type_digits'),
('dependency_columns_on_indexes', 'keys', 'id'),
('dependency_columns_on_indexes', 'triggers', 'name'),
('dependency_columns_on_indexes', 'triggers', 'orientation'),
('dependency_columns_on_indexes', 'triggers', 'table_id'),
('dependency_columns_on_indexes', 'triggers', 'time'),
('dependency_columns_on_keys', '_columns', 'name'),
('dependency_columns_on_keys', '_columns', 'table_id'),
('dependency_columns_on_ke
ys', '_columns', 'type'), ('dependency_columns_on_keys', '_columns',
'type_digits'), ('dependency_columns_on_keys', '_columns', 'type_scale'),
('dependency_columns_on_keys', 'triggers', 'name'),
('dependency_columns_on_keys', 'triggers', 'orientation'),
('dependency_columns_on_keys', 'triggers', 'table_id'),
('dependency_columns_on_keys', 'triggers', 'time'),
('dependency_columns_on_triggers', 'keys', 'name'),
('dependency_columns_on_triggers', 'keys', 'rkey'),
('dependency_columns_on_triggers', 'keys', 'type'),
('dependency_functions_on_triggers', 'keys', 'action'),
('dependency_functions_on_triggers', 'keys', 'name'),
('dependency_functions_on_triggers', 'keys', 'rkey'),
('dependency_functions_on_triggers', 'keys', 'type'),
('dependency_keys_on_foreignkeys', '_columns', 'default'),
('dependency_keys_on_foreignkeys', '_columns', 'name'),
('dependency_keys_on_foreignkeys', '_columns', 'table_id'),
('dependency_keys_on_foreignkeys', '_columns', 'type'),
('dependency_keys_on_foreignke
ys', '_columns', 'type_digits'), ('dependency_keys_on_foreignkeys',
'_columns', 'type_scale'), ('dependency_tables_on_foreignkeys', '_columns',
'default'), ('dependency_tables_on_foreignkeys', '_columns', 'name'),
('dependency_tables_on_foreignkeys', '_columns', 'table_id'),
('dependency_tables_on_foreignkeys', '_columns', 'type'),
('dependency_tables_on_foreignkeys', '_columns', 'type_digits'),
('dependency_tables_on_foreignkeys', '_columns', 'type_scale'),
('dependency_tables_on_indexes', '_columns', 'name'),
('dependency_tables_on_indexes', '_columns', 'number'),
('dependency_tables_on_indexes', '_columns', 'storage'),
('dependency_tables_on_indexes', '_columns', 'table_id'),
('dependency_tables_on_indexes', '_columns', 'type_digits'),
('dependency_tables_on_indexes', 'keys', 'id'),
('dependency_tables_on_triggers', 'keys', 'action'),
('dependency_tables_on_triggers', 'keys', 'name'),
('dependency_tables_on_triggers', 'keys', 'rkey'),
('dependency_tables_on_triggers', 'keys', 'ty
pe')));
+delete from sys.triggers where table_id not in (select id from sys._tables);
+commit;
+
+Running database upgrade commands:
+create temporary table d as (select distinct * from sys.dependencies);
+delete from sys.dependencies;
+insert into sys.dependencies (select * from d);
Running database upgrade commands:
update sys._columns set type_digits = 7 where type = 'tinyint' and type_digits
<> 7;
diff --git
a/sql/test/emptydb-previous-upgrade-chain/Tests/upgrade.stable.out.int128
b/sql/test/emptydb-previous-upgrade-chain/Tests/upgrade.stable.out.int128
--- a/sql/test/emptydb-previous-upgrade-chain/Tests/upgrade.stable.out.int128
+++ b/sql/test/emptydb-previous-upgrade-chain/Tests/upgrade.stable.out.int128
@@ -682,6 +682,13 @@ update sys.functions set system = true w
Running database upgrade commands:
delete from sys.dependencies where (id, depend_id) in (select c.id, f.id from
sys.functions f, sys._tables t, sys._columns c, sys.dependencies d where
c.table_id = t.id and f.id = d.depend_id and c.id = d.id and f.schema_id = 2000
and t.schema_id = 2000 and (f.name, t.name, c.name) in (values
('describe_columns', '_columns', 'storage'), ('describe_function',
'function_languages', 'language_name'), ('describe_function', 'function_types',
'function_type_name'), ('describe_function', 'functions', 'func'),
('describe_function', 'functions', 'mod'), ('describe_function', 'functions',
'semantics'), ('describe_function', 'functions', 'side_effect'),
('describe_function', 'functions', 'system'), ('describe_function',
'functions', 'vararg'), ('describe_function', 'functions', 'varres'),
('describe_function', 'schemas', 'authorization'), ('describe_function',
'schemas', 'owner'), ('describe_function', 'schemas', 'system'),
('describe_table', '_tables', 'access'), ('describe_table', '_tables',
'commit_action'), ('describe_table', '_tables', 'system')));
delete from sys.dependencies where (id, depend_id) in (select c.id, v.id from
sys._tables v, sys._tables t, sys._columns c, sys.dependencies d where
c.table_id = t.id and v.id = d.depend_id and c.id = d.id and v.schema_id = 2000
and t.schema_id = 2000 and (v.name, t.name, c.name) in (values
('dependency_columns_on_indexes', '_columns', 'name'),
('dependency_columns_on_indexes', '_columns', 'number'),
('dependency_columns_on_indexes', '_columns', 'storage'),
('dependency_columns_on_indexes', '_columns', 'table_id'),
('dependency_columns_on_indexes', '_columns', 'type_digits'),
('dependency_columns_on_indexes', 'keys', 'id'),
('dependency_columns_on_indexes', 'triggers', 'name'),
('dependency_columns_on_indexes', 'triggers', 'orientation'),
('dependency_columns_on_indexes', 'triggers', 'table_id'),
('dependency_columns_on_indexes', 'triggers', 'time'),
('dependency_columns_on_keys', '_columns', 'name'),
('dependency_columns_on_keys', '_columns', 'table_id'),
('dependency_columns_on_ke
ys', '_columns', 'type'), ('dependency_columns_on_keys', '_columns',
'type_digits'), ('dependency_columns_on_keys', '_columns', 'type_scale'),
('dependency_columns_on_keys', 'triggers', 'name'),
('dependency_columns_on_keys', 'triggers', 'orientation'),
('dependency_columns_on_keys', 'triggers', 'table_id'),
('dependency_columns_on_keys', 'triggers', 'time'),
('dependency_columns_on_triggers', 'keys', 'name'),
('dependency_columns_on_triggers', 'keys', 'rkey'),
('dependency_columns_on_triggers', 'keys', 'type'),
('dependency_functions_on_triggers', 'keys', 'action'),
('dependency_functions_on_triggers', 'keys', 'name'),
('dependency_functions_on_triggers', 'keys', 'rkey'),
('dependency_functions_on_triggers', 'keys', 'type'),
('dependency_keys_on_foreignkeys', '_columns', 'default'),
('dependency_keys_on_foreignkeys', '_columns', 'name'),
('dependency_keys_on_foreignkeys', '_columns', 'table_id'),
('dependency_keys_on_foreignkeys', '_columns', 'type'),
('dependency_keys_on_foreignke
ys', '_columns', 'type_digits'), ('dependency_keys_on_foreignkeys',
'_columns', 'type_scale'), ('dependency_tables_on_foreignkeys', '_columns',
'default'), ('dependency_tables_on_foreignkeys', '_columns', 'name'),
('dependency_tables_on_foreignkeys', '_columns', 'table_id'),
('dependency_tables_on_foreignkeys', '_columns', 'type'),
('dependency_tables_on_foreignkeys', '_columns', 'type_digits'),
('dependency_tables_on_foreignkeys', '_columns', 'type_scale'),
('dependency_tables_on_indexes', '_columns', 'name'),
('dependency_tables_on_indexes', '_columns', 'number'),
('dependency_tables_on_indexes', '_columns', 'storage'),
('dependency_tables_on_indexes', '_columns', 'table_id'),
('dependency_tables_on_indexes', '_columns', 'type_digits'),
('dependency_tables_on_indexes', 'keys', 'id'),
('dependency_tables_on_triggers', 'keys', 'action'),
('dependency_tables_on_triggers', 'keys', 'name'),
('dependency_tables_on_triggers', 'keys', 'rkey'),
('dependency_tables_on_triggers', 'keys', 'ty
pe')));
+delete from sys.triggers where table_id not in (select id from sys._tables);
+commit;
+
+Running database upgrade commands:
+create temporary table d as (select distinct * from sys.dependencies);
+delete from sys.dependencies;
+insert into sys.dependencies (select * from d);
Running database upgrade commands:
update sys._columns set type_digits = 7 where type = 'tinyint' and type_digits
<> 7;
diff --git
a/sql/test/emptydb-previous-upgrade-hge/Tests/upgrade.stable.out.int128
b/sql/test/emptydb-previous-upgrade-hge/Tests/upgrade.stable.out.int128
--- a/sql/test/emptydb-previous-upgrade-hge/Tests/upgrade.stable.out.int128
+++ b/sql/test/emptydb-previous-upgrade-hge/Tests/upgrade.stable.out.int128
@@ -613,6 +613,13 @@ update sys.functions set system = true w
Running database upgrade commands:
delete from sys.dependencies where (id, depend_id) in (select c.id, f.id from
sys.functions f, sys._tables t, sys._columns c, sys.dependencies d where
c.table_id = t.id and f.id = d.depend_id and c.id = d.id and f.schema_id = 2000
and t.schema_id = 2000 and (f.name, t.name, c.name) in (values
('describe_columns', '_columns', 'storage'), ('describe_function',
'function_languages', 'language_name'), ('describe_function', 'function_types',
'function_type_name'), ('describe_function', 'functions', 'func'),
('describe_function', 'functions', 'mod'), ('describe_function', 'functions',
'semantics'), ('describe_function', 'functions', 'side_effect'),
('describe_function', 'functions', 'system'), ('describe_function',
'functions', 'vararg'), ('describe_function', 'functions', 'varres'),
('describe_function', 'schemas', 'authorization'), ('describe_function',
'schemas', 'owner'), ('describe_function', 'schemas', 'system'),
('describe_table', '_tables', 'access'), ('describe_table', '_tables',
'commit_action'), ('describe_table', '_tables', 'system')));
delete from sys.dependencies where (id, depend_id) in (select c.id, v.id from
sys._tables v, sys._tables t, sys._columns c, sys.dependencies d where
c.table_id = t.id and v.id = d.depend_id and c.id = d.id and v.schema_id = 2000
and t.schema_id = 2000 and (v.name, t.name, c.name) in (values
('dependency_columns_on_indexes', '_columns', 'name'),
('dependency_columns_on_indexes', '_columns', 'number'),
('dependency_columns_on_indexes', '_columns', 'storage'),
('dependency_columns_on_indexes', '_columns', 'table_id'),
('dependency_columns_on_indexes', '_columns', 'type_digits'),
('dependency_columns_on_indexes', 'keys', 'id'),
('dependency_columns_on_indexes', 'triggers', 'name'),
('dependency_columns_on_indexes', 'triggers', 'orientation'),
('dependency_columns_on_indexes', 'triggers', 'table_id'),
('dependency_columns_on_indexes', 'triggers', 'time'),
('dependency_columns_on_keys', '_columns', 'name'),
('dependency_columns_on_keys', '_columns', 'table_id'),
('dependency_columns_on_ke
ys', '_columns', 'type'), ('dependency_columns_on_keys', '_columns',
'type_digits'), ('dependency_columns_on_keys', '_columns', 'type_scale'),
('dependency_columns_on_keys', 'triggers', 'name'),
('dependency_columns_on_keys', 'triggers', 'orientation'),
('dependency_columns_on_keys', 'triggers', 'table_id'),
('dependency_columns_on_keys', 'triggers', 'time'),
('dependency_columns_on_triggers', 'keys', 'name'),
('dependency_columns_on_triggers', 'keys', 'rkey'),
('dependency_columns_on_triggers', 'keys', 'type'),
('dependency_functions_on_triggers', 'keys', 'action'),
('dependency_functions_on_triggers', 'keys', 'name'),
('dependency_functions_on_triggers', 'keys', 'rkey'),
('dependency_functions_on_triggers', 'keys', 'type'),
('dependency_keys_on_foreignkeys', '_columns', 'default'),
('dependency_keys_on_foreignkeys', '_columns', 'name'),
('dependency_keys_on_foreignkeys', '_columns', 'table_id'),
('dependency_keys_on_foreignkeys', '_columns', 'type'),
('dependency_keys_on_foreignke
ys', '_columns', 'type_digits'), ('dependency_keys_on_foreignkeys',
'_columns', 'type_scale'), ('dependency_tables_on_foreignkeys', '_columns',
'default'), ('dependency_tables_on_foreignkeys', '_columns', 'name'),
('dependency_tables_on_foreignkeys', '_columns', 'table_id'),
('dependency_tables_on_foreignkeys', '_columns', 'type'),
('dependency_tables_on_foreignkeys', '_columns', 'type_digits'),
('dependency_tables_on_foreignkeys', '_columns', 'type_scale'),
('dependency_tables_on_indexes', '_columns', 'name'),
('dependency_tables_on_indexes', '_columns', 'number'),
('dependency_tables_on_indexes', '_columns', 'storage'),
('dependency_tables_on_indexes', '_columns', 'table_id'),
('dependency_tables_on_indexes', '_columns', 'type_digits'),
('dependency_tables_on_indexes', 'keys', 'id'),
('dependency_tables_on_triggers', 'keys', 'action'),
('dependency_tables_on_triggers', 'keys', 'name'),
('dependency_tables_on_triggers', 'keys', 'rkey'),
('dependency_tables_on_triggers', 'keys', 'ty
pe')));
+delete from sys.triggers where table_id not in (select id from sys._tables);
+commit;
+
+Running database upgrade commands:
+create temporary table d as (select distinct * from sys.dependencies);
+delete from sys.dependencies;
+insert into sys.dependencies (select * from d);
Running database upgrade commands:
update sys._columns set type_digits = 7 where type = 'tinyint' and type_digits
<> 7;
diff --git a/sql/test/emptydb-previous-upgrade/Tests/upgrade.stable.out
b/sql/test/emptydb-previous-upgrade/Tests/upgrade.stable.out
--- a/sql/test/emptydb-previous-upgrade/Tests/upgrade.stable.out
+++ b/sql/test/emptydb-previous-upgrade/Tests/upgrade.stable.out
@@ -613,6 +613,13 @@ update sys.functions set system = true w
Running database upgrade commands:
delete from sys.dependencies where (id, depend_id) in (select c.id, f.id from
sys.functions f, sys._tables t, sys._columns c, sys.dependencies d where
c.table_id = t.id and f.id = d.depend_id and c.id = d.id and f.schema_id = 2000
and t.schema_id = 2000 and (f.name, t.name, c.name) in (values
('describe_columns', '_columns', 'storage'), ('describe_function',
'function_languages', 'language_name'), ('describe_function', 'function_types',
'function_type_name'), ('describe_function', 'functions', 'func'),
('describe_function', 'functions', 'mod'), ('describe_function', 'functions',
'semantics'), ('describe_function', 'functions', 'side_effect'),
('describe_function', 'functions', 'system'), ('describe_function',
'functions', 'vararg'), ('describe_function', 'functions', 'varres'),
('describe_function', 'schemas', 'authorization'), ('describe_function',
'schemas', 'owner'), ('describe_function', 'schemas', 'system'),
('describe_table', '_tables', 'access'), ('describe_table', '_tables',
'commit_action'), ('describe_table', '_tables', 'system')));
delete from sys.dependencies where (id, depend_id) in (select c.id, v.id from
sys._tables v, sys._tables t, sys._columns c, sys.dependencies d where
c.table_id = t.id and v.id = d.depend_id and c.id = d.id and v.schema_id = 2000
and t.schema_id = 2000 and (v.name, t.name, c.name) in (values
('dependency_columns_on_indexes', '_columns', 'name'),
('dependency_columns_on_indexes', '_columns', 'number'),
('dependency_columns_on_indexes', '_columns', 'storage'),
('dependency_columns_on_indexes', '_columns', 'table_id'),
('dependency_columns_on_indexes', '_columns', 'type_digits'),
('dependency_columns_on_indexes', 'keys', 'id'),
('dependency_columns_on_indexes', 'triggers', 'name'),
('dependency_columns_on_indexes', 'triggers', 'orientation'),
('dependency_columns_on_indexes', 'triggers', 'table_id'),
('dependency_columns_on_indexes', 'triggers', 'time'),
('dependency_columns_on_keys', '_columns', 'name'),
('dependency_columns_on_keys', '_columns', 'table_id'),
('dependency_columns_on_ke
ys', '_columns', 'type'), ('dependency_columns_on_keys', '_columns',
'type_digits'), ('dependency_columns_on_keys', '_columns', 'type_scale'),
('dependency_columns_on_keys', 'triggers', 'name'),
('dependency_columns_on_keys', 'triggers', 'orientation'),
('dependency_columns_on_keys', 'triggers', 'table_id'),
('dependency_columns_on_keys', 'triggers', 'time'),
('dependency_columns_on_triggers', 'keys', 'name'),
('dependency_columns_on_triggers', 'keys', 'rkey'),
('dependency_columns_on_triggers', 'keys', 'type'),
('dependency_functions_on_triggers', 'keys', 'action'),
('dependency_functions_on_triggers', 'keys', 'name'),
('dependency_functions_on_triggers', 'keys', 'rkey'),
('dependency_functions_on_triggers', 'keys', 'type'),
('dependency_keys_on_foreignkeys', '_columns', 'default'),
('dependency_keys_on_foreignkeys', '_columns', 'name'),
('dependency_keys_on_foreignkeys', '_columns', 'table_id'),
('dependency_keys_on_foreignkeys', '_columns', 'type'),
('dependency_keys_on_foreignke
ys', '_columns', 'type_digits'), ('dependency_keys_on_foreignkeys',
'_columns', 'type_scale'), ('dependency_tables_on_foreignkeys', '_columns',
'default'), ('dependency_tables_on_foreignkeys', '_columns', 'name'),
('dependency_tables_on_foreignkeys', '_columns', 'table_id'),
('dependency_tables_on_foreignkeys', '_columns', 'type'),
('dependency_tables_on_foreignkeys', '_columns', 'type_digits'),
('dependency_tables_on_foreignkeys', '_columns', 'type_scale'),
('dependency_tables_on_indexes', '_columns', 'name'),
('dependency_tables_on_indexes', '_columns', 'number'),
('dependency_tables_on_indexes', '_columns', 'storage'),
('dependency_tables_on_indexes', '_columns', 'table_id'),
('dependency_tables_on_indexes', '_columns', 'type_digits'),
('dependency_tables_on_indexes', 'keys', 'id'),
('dependency_tables_on_triggers', 'keys', 'action'),
('dependency_tables_on_triggers', 'keys', 'name'),
('dependency_tables_on_triggers', 'keys', 'rkey'),
('dependency_tables_on_triggers', 'keys', 'ty
pe')));
+delete from sys.triggers where table_id not in (select id from sys._tables);
+commit;
+
+Running database upgrade commands:
+create temporary table d as (select distinct * from sys.dependencies);
+delete from sys.dependencies;
+insert into sys.dependencies (select * from d);
Running database upgrade commands:
update sys._columns set type_digits = 7 where type = 'tinyint' and type_digits
<> 7;
diff --git a/sql/test/emptydb-previous-upgrade/Tests/upgrade.stable.out.int128
b/sql/test/emptydb-previous-upgrade/Tests/upgrade.stable.out.int128
_______________________________________________
checkin-list mailing list -- checkin-list@monetdb.org
To unsubscribe send an email to checkin-list-le...@monetdb.org
