Changeset: e74d5531b3f0 for MonetDB URL: https://dev.monetdb.org/hg/MonetDB/rev/e74d5531b3f0 Modified Files: sql/ChangeLog sql/backends/monet5/Tests/limithack.test sql/backends/monet5/sql_scenario.c sql/backends/monet5/sql_upgrades.c sql/scripts/99_system.sql sql/test/BugTracker-2009/Tests/having_min_crash.SF-2795951.test sql/test/BugTracker-2010/Tests/multiple-common-table-expressions.Bug-2664.test sql/test/BugTracker-2010/Tests/slow-query.Bug-2671.test sql/test/BugTracker-2011/Tests/count-count-distinct.Bug-2808.test sql/test/BugTracker-2012/Tests/aggregate_vs_positional_column_crash.Bug-3085.test sql/test/BugTracker-2012/Tests/predicate_select.Bug-3090.test sql/test/BugTracker-2013/Tests/qualified_aggrname.Bug-3332.test sql/test/BugTracker-2013/Tests/swapped_likejoin.Bug-3375.test sql/test/BugTracker-2014/Tests/select-having.Bug-3458.test sql/test/BugTracker-2015/Tests/cardinality.Bug-3761.test sql/test/BugTracker-2017/Tests/crash_correlated_subqueries_in_select.Bug-6254.test sql/test/BugTracker-2017/Tests/crash_correlated_subqueries_not_in_select.Bug-6290.test sql/test/BugTracker-2017/Tests/sqlsmith03.test sql/test/BugTracker-2018/Tests/count_from_commented_function_signatures.Bug-6542.test sql/test/BugTracker-2018/Tests/truncate_sys_tables.Bug-6543.test sql/test/BugTracker/Tests/subquery_orderby.SF-1688915.test sql/test/Dependencies/Tests/dependency_owner_schema_3.test sql/test/bugs/Tests/crash_order_by.test sql/test/bugs/Tests/insert_delete-bug-sf-904025.test sql/test/bugs/Tests/simple_view.test 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/mergetables/Tests/mergedb_create.test sql/test/prepare/Tests/limit_in_prepare.Bug-2552.sql sql/test/prepare/Tests/limit_in_prepare.Bug-2552.stable.out sql/test/sys-schema/Tests/check_Not_Nullable_columns.test sql/test/sys-schema/Tests/check_PrimaryKey_uniqueness.test Branch: default Log Message:
Removed deprecated system view sys.systemfunctions. It was marked as deprecated from release Apr2019 (11.33.3). diffs (truncated from 1040 to 300 lines): diff --git a/sql/ChangeLog b/sql/ChangeLog --- a/sql/ChangeLog +++ b/sql/ChangeLog @@ -1,6 +1,12 @@ # ChangeLog file for sql # This file is updated with Maddlog +* Thu Nov 4 2021 Martin van Dinther <[email protected]> +- Removed deprecated system view sys.systemfunctions. It was marked + as deprecated from release Apr2019 (11.33.3). Use query: + select id as function_id from sys.functions where system; + to get the same data as the old view. + * Thu Oct 21 2021 Martin van Dinther <[email protected]> - Extended SQL system catalog with lookup table sys.fkey_actions and view sys.fkeys to provide user friendly querying of existing foreign diff --git a/sql/backends/monet5/Tests/limithack.test b/sql/backends/monet5/Tests/limithack.test --- a/sql/backends/monet5/Tests/limithack.test +++ b/sql/backends/monet5/Tests/limithack.test @@ -12,7 +12,7 @@ CREATE TABLE "sys"."tbls" ( ) statement ok -COPY 40 RECORDS INTO "sys"."tbls" FROM stdin USING DELIMITERS E'\t',E'\n','"' +COPY 39 RECORDS INTO "sys"."tbls" FROM stdin USING DELIMITERS E'\t',E'\n','"' <COPY_INTO_DATA> 2001 "schemas" 2000 NULL 0 true 0 false 0 2007 "types" 2000 NULL 0 true 0 false 0 @@ -53,7 +53,6 @@ 6380 "storagemodelinput" 2000 NULL 0 tru 6428 "storagemodel" 2000 "create view sys.storagemodel as select * from sys.storagemodel();" 1 true 0 false 0 6438 "tablestoragemodel" 2000 "-- A summary of the table storage requirement is is available as a table view.\n-- The auxiliary column denotes the maximum space if all non-sorted columns\n-- would be augmented with a hash (rare situation)\ncreate view sys.tablestoragemodel\nas select ""schema"",""table"",max(count) as ""count"",\n\tsum(columnsize) as columnsize,\n\tsum(heapsize) as heapsize,\n\tsum(hashes) as hashes,\n\tsum(imprints) as imprints,\n\tsum(case when sorted = false then 8 * count else 0 end) as auxiliary\nfrom sys.storagemodel() group by ""schema"",""table"";" 1 true 0 false 0 6453 "statistics" 2000 NULL 0 true 0 false 0 -6616 "systemfunctions" 2000 NULL 0 true 0 false 0 statement ok create function limited() @@ -65,8 +64,8 @@ end query ITITIIIII rowsort select * from tbls where id in (select id from limited()) ---- -6616 -systemfunctions +6453 +statistics 2000 NULL 0 diff --git a/sql/backends/monet5/sql_scenario.c b/sql/backends/monet5/sql_scenario.c --- a/sql/backends/monet5/sql_scenario.c +++ b/sql/backends/monet5/sql_scenario.c @@ -470,13 +470,13 @@ SQLinit(Client c) /* initialize the database with predefined SQL functions */ sqlstore *store = SQLstore; if (store->first == 0) { - /* check whether table sys.systemfunctions exists: if - * it doesn't, this is probably a restart of the + /* check whether last created object trigger sys.system_update_tables (from 99_system.sql) exists. + * if it doesn't, this is probably a restart of the * server after an incomplete initialization */ if ((msg = SQLtrans(m)) == MAL_SUCCEED) { sql_schema *s = mvc_bind_schema(m, "sys"); - sql_table *t = s ? mvc_bind_table(m, s, "systemfunctions") : NULL; - if (t == NULL) + sql_trigger *tri = s ? mvc_bind_trigger(m, s, "system_update_tables") : NULL; + if (tri == NULL) store->first = 1; msg = mvc_rollback(m, 0, NULL, false); } @@ -504,8 +504,6 @@ SQLinit(Client c) create trigger system_update_schemas after update on sys.schemas for each statement call sys_update_schemas(); \ create trigger system_update_tables after update on sys._tables for each statement call sys_update_tables(); \ update sys.functions set system = true; \ - create view sys.systemfunctions as select id as function_id from sys.functions where system; \ - grant select on sys.systemfunctions to public; \ update sys._tables set system = true; \ update sys.schemas set system = true; \ UPDATE sys.types SET schema_id = (SELECT id FROM sys.schemas WHERE name = 'sys') WHERE schema_id = 0 AND schema_id NOT IN (SELECT id from sys.schemas); \ 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 @@ -4256,6 +4256,12 @@ sql_update_default(Client c, mvc *sql, c pos += snprintf(buf + pos, bufsize - pos, "update sys._tables set system = true where name in ('fkey_actions', 'fkeys') AND schema_id = 2000;\n"); + /* 99_system.sql */ + t = mvc_bind_table(sql, s, "systemfunctions"); + t->system = 0; + pos += snprintf(buf + pos, bufsize - pos, + "drop view sys.systemfunctions;\n"); + assert(pos < bufsize); printf("Running database upgrade commands:\n%s\n", buf); err = SQLstatementIntern(c, buf, "update", true, false, NULL); diff --git a/sql/scripts/99_system.sql b/sql/scripts/99_system.sql --- a/sql/scripts/99_system.sql +++ b/sql/scripts/99_system.sql @@ -9,8 +9,6 @@ create trigger system_update_tables afte -- only system functions until now update sys.functions set system = true; -create view sys.systemfunctions as select id as function_id from sys.functions where system; -grant select on sys.systemfunctions to public; -- only system tables until now update sys._tables set system = true; diff --git a/sql/test/BugTracker-2009/Tests/having_min_crash.SF-2795951.test b/sql/test/BugTracker-2009/Tests/having_min_crash.SF-2795951.test --- a/sql/test/BugTracker-2009/Tests/having_min_crash.SF-2795951.test +++ b/sql/test/BugTracker-2009/Tests/having_min_crash.SF-2795951.test @@ -14,7 +14,7 @@ CREATE TABLE "sys"."mytables" ( ) statement ok -COPY 96 RECORDS INTO "sys"."mytables" FROM stdin USING DELIMITERS E'\t',E'\n','"' +COPY 95 RECORDS INTO "sys"."mytables" FROM stdin USING DELIMITERS E'\t',E'\n','"' <COPY_INTO_DATA> 2001 "schemas" 2000 NULL 0 true 0 0 2007 "types" 2000 NULL 0 true 0 0 @@ -108,7 +108,6 @@ 9313 "storagemodel" 2000 "create view sy 9324 "tablestoragemodel" 2000 "create view sys.tablestoragemodel as\nselect ""schema"", ""table"",\n max(""count"") as ""rowcount"",\n count(*) as ""storages"",\n sum(sys.columnsize(""type"", ""count"")) as columnsize,\n sum(sys.heapsize(""type"", ""count"", ""distinct"", ""atomwidth"")) as heapsize,\n sum(sys.hashsize(""reference"", ""count"")) as hashsize,\n sum(case when isacolumn then sys.imprintsize(""type"", ""count"") else 0 end) as imprintsize,\n sum(case when (isacolumn and not sorted) then cast(8 * ""count"" as bigint) else 0 end) as orderidxsize\n from sys.storagemodelinput\ngroup by ""schema"", ""table""\norder by ""schema"", ""table"";" 1 true 0 0 9338 "statistics" 2000 NULL 0 true 0 0 9421 "compinfo" 9385 "create view logging.compinfo as select * from logging.compinfo();" 1 true 0 0 -9505 "systemfunctions" 2000 "create view sys.systemfunctions as select id as function_id from sys.functions where system;" 1 true 0 0 9588 "integers" 2000 NULL 0 false 0 0 9594 "tbl_productsales" 2000 NULL 0 false 0 0 9604 "another_t" 2000 NULL 0 false 0 0 diff --git a/sql/test/BugTracker-2010/Tests/multiple-common-table-expressions.Bug-2664.test b/sql/test/BugTracker-2010/Tests/multiple-common-table-expressions.Bug-2664.test --- a/sql/test/BugTracker-2010/Tests/multiple-common-table-expressions.Bug-2664.test +++ b/sql/test/BugTracker-2010/Tests/multiple-common-table-expressions.Bug-2664.test @@ -11,7 +11,7 @@ CREATE TABLE "t2664" ( ) statement ok -COPY 30 RECORDS INTO "t2664" FROM stdin USING DELIMITERS E'\t',E'\n','"' +COPY 29 RECORDS INTO "t2664" FROM stdin USING DELIMITERS E'\t',E'\n','"' <COPY_INTO_DATA> 2001 "schemas" 2000 NULL 0 true 0 false 2006 "types" 2000 NULL 0 true 0 false @@ -42,7 +42,6 @@ 5088 "privileges" 2000 NULL 0 true 0 fal 5201 "queryhistory" 2000 NULL 0 true 0 false 5215 "callhistory" 2000 NULL 0 true 0 false 5232 "querylog" 2000 "create view querylog as\nselect qd.*, ql.ctime, ql.arguments, ql.exec, ql.result, ql.foot, ql.memory, ql.tuples, ql.inblock, ql.oublock from queryhistory qd, callhistory ql\nwhere qd.id = ql.id;" 1 true 0 false -5465 "systemfunctions" 2000 NULL 0 true 0 false query I rowsort with t(id) as (select id from "t2664") @@ -80,7 +79,6 @@ 5088 5201 5215 5232 -5465 query I rowsort with t(id) as (select id from "t2664"), @@ -117,7 +115,6 @@ 5088 5201 5215 5232 -5465 statement ok drop table "t2664" diff --git a/sql/test/BugTracker-2010/Tests/slow-query.Bug-2671.test b/sql/test/BugTracker-2010/Tests/slow-query.Bug-2671.test --- a/sql/test/BugTracker-2010/Tests/slow-query.Bug-2671.test +++ b/sql/test/BugTracker-2010/Tests/slow-query.Bug-2671.test @@ -10,7 +10,7 @@ SELECT "f"."id", "a"."number" FROM "sys"."args" "a", "sys"."functions" "f" -WHERE "f"."id" NOT IN (SELECT "function_id" FROM "sys"."systemfunctions") AND +WHERE "f"."id" NOT IN (SELECT id as "function_id" FROM "sys"."functions" WHERE system) AND "f"."language" <> 1 AND "a"."func_id" = "f"."id" ORDER BY "f"."id", "a"."number" diff --git a/sql/test/BugTracker-2011/Tests/count-count-distinct.Bug-2808.test b/sql/test/BugTracker-2011/Tests/count-count-distinct.Bug-2808.test --- a/sql/test/BugTracker-2011/Tests/count-count-distinct.Bug-2808.test +++ b/sql/test/BugTracker-2011/Tests/count-count-distinct.Bug-2808.test @@ -12,7 +12,7 @@ CREATE TABLE "sys"."tbls" ( ) statement ok -COPY 40 RECORDS INTO "sys"."tbls" FROM stdin USING DELIMITERS E'\t',E'\n','"' +COPY 39 RECORDS INTO "sys"."tbls" FROM stdin USING DELIMITERS E'\t',E'\n','"' <COPY_INTO_DATA> 2001 "schemas" 2000 NULL 0 true 0 false 0 2007 "types" 2000 NULL 0 true 0 false 0 @@ -53,23 +53,22 @@ 6380 "storagemodelinput" 2000 NULL 0 tru 6428 "storagemodel" 2000 "create view sys.storagemodel as select * from sys.storagemodel();" 1 true 0 false 0 6438 "tablestoragemodel" 2000 "-- A summary of the table storage requirement is is available as a table view.\n-- The auxiliary column denotes the maximum space if all non-sorted columns\n-- would be augmented with a hash (rare situation)\ncreate view sys.tablestoragemodel\nas select ""schema"",""table"",max(count) as ""count"",\n\tsum(columnsize) as columnsize,\n\tsum(heapsize) as heapsize,\n\tsum(hashes) as hashes,\n\tsum(""imprints"") as ""imprints"",\n\tsum(case when sorted = false then 8 * count else 0 end) as auxiliary\nfrom sys.storagemodel() group by ""schema"",""table"";" 1 true 0 false 0 6453 "statistics" 2000 NULL 0 true 0 false 0 -6616 "systemfunctions" 2000 NULL 0 true 0 false 0 query I rowsort select count(name) from tbls ---- -40 +39 query I rowsort select count(distinct name) from tbls ---- -34 +33 query II rowsort select count(name) , count(distinct name) from tbls ---- -40 -34 +39 +33 statement ok drop table tbls diff --git a/sql/test/BugTracker-2012/Tests/aggregate_vs_positional_column_crash.Bug-3085.test b/sql/test/BugTracker-2012/Tests/aggregate_vs_positional_column_crash.Bug-3085.test --- a/sql/test/BugTracker-2012/Tests/aggregate_vs_positional_column_crash.Bug-3085.test +++ b/sql/test/BugTracker-2012/Tests/aggregate_vs_positional_column_crash.Bug-3085.test @@ -12,7 +12,7 @@ CREATE TABLE "sys"."tbls" ( ) statement ok -COPY 40 RECORDS INTO "sys"."tbls" FROM stdin USING DELIMITERS E'\t',E'\n','"' +COPY 39 RECORDS INTO "sys"."tbls" FROM stdin USING DELIMITERS E'\t',E'\n','"' <COPY_INTO_DATA> 2001 "schemas" 2000 NULL 0 true 0 false 0 2007 "types" 2000 NULL 0 true 0 false 0 @@ -53,18 +53,17 @@ 6380 "storagemodelinput" 2000 NULL 0 tru 6428 "storagemodel" 2000 "create view sys.storagemodel as select * from sys.storagemodel();" 1 true 0 false 0 6438 "tablestoragemodel" 2000 "-- A summary of the table storage requirement is is available as a table view.\n-- The auxiliary column denotes the maximum space if all non-sorted columns\n-- would be augmented with a hash (rare situation)\ncreate view sys.tablestoragemodel\nas select ""schema"",""table"",max(count) as ""count"",\n\tsum(columnsize) as columnsize,\n\tsum(heapsize) as heapsize,\n\tsum(hashes) as hashes,\n\tsum(""imprints"") as ""imprints"",\n\tsum(case when sorted = false then 8 * count else 0 end) as auxiliary\nfrom sys.storagemodel() group by ""schema"",""table"";" 1 true 0 false 0 6453 "statistics" 2000 NULL 0 true 0 false 0 -6616 "systemfunctions" 2000 NULL 0 true 0 false 0 query II rowsort SELECT 1, (SELECT count(*) FROM tbls) ---- 1 -40 +39 query II rowsort SELECT (SELECT count(*) FROM tbls), 1 ---- -40 +39 1 statement ok diff --git a/sql/test/BugTracker-2012/Tests/predicate_select.Bug-3090.test b/sql/test/BugTracker-2012/Tests/predicate_select.Bug-3090.test --- a/sql/test/BugTracker-2012/Tests/predicate_select.Bug-3090.test +++ b/sql/test/BugTracker-2012/Tests/predicate_select.Bug-3090.test @@ -12,7 +12,7 @@ CREATE TABLE "sys"."tbls" ( ) statement ok -COPY 40 RECORDS INTO "sys"."tbls" FROM stdin USING DELIMITERS E'\t',E'\n','"' +COPY 39 RECORDS INTO "sys"."tbls" FROM stdin USING DELIMITERS E'\t',E'\n','"' <COPY_INTO_DATA> 2001 "schemas" 2000 NULL 0 true 0 false 0 2007 "types" 2000 NULL 0 true 0 false 0 @@ -53,12 +53,11 @@ 6380 "storagemodelinput" 2000 NULL 0 tru 6428 "storagemodel" 2000 "create view sys.storagemodel as select * from sys.storagemodel();" 1 true 0 false 0 6438 "tablestoragemodel" 2000 "-- A summary of the table storage requirement is is available as a table view.\n-- The auxiliary column denotes the maximum space if all non-sorted columns\n-- would be augmented with a hash (rare situation)\ncreate view sys.tablestoragemodel\nas select ""schema"",""table"",max(count) as ""count"",\n\tsum(columnsize) as columnsize,\n\tsum(heapsize) as heapsize,\n\tsum(hashes) as hashes,\n\tsum(""imprints"") as ""imprints"",\n\tsum(case when sorted = false then 8 * count else 0 end) as auxiliary\nfrom sys.storagemodel() group by ""schema"",""table"";" 1 true 0 false 0 6453 "statistics" 2000 NULL 0 true 0 false 0 -6616 "systemfunctions" 2000 NULL 0 true 0 false 0 query TITIIIII rowsort select name, schema_id, query, type, system, commit_action, readonly, temporary from tbls where 1 ---- -320 values hashing to e6721987082cb8a70cf73974bb3a3bb9 +312 values hashing to 68834d7c31df9f75e2d5613589c20f2b statement error select name, schema_id, query, type, system, commit_action, readonly, temporary from tbls where name diff --git a/sql/test/BugTracker-2013/Tests/qualified_aggrname.Bug-3332.test b/sql/test/BugTracker-2013/Tests/qualified_aggrname.Bug-3332.test --- a/sql/test/BugTracker-2013/Tests/qualified_aggrname.Bug-3332.test +++ b/sql/test/BugTracker-2013/Tests/qualified_aggrname.Bug-3332.test @@ -12,7 +12,7 @@ CREATE TABLE "sys"."tbls" ( ) statement ok -COPY 40 RECORDS INTO "sys"."tbls" FROM stdin USING DELIMITERS E'\t',E'\n','"' +COPY 39 RECORDS INTO "sys"."tbls" FROM stdin USING DELIMITERS E'\t',E'\n','"' <COPY_INTO_DATA> 2001 "schemas" 2000 NULL 0 true 0 false 0 2007 "types" 2000 NULL 0 true 0 false 0 @@ -53,7 +53,6 @@ 6380 "storagemodelinput" 2000 NULL 0 tru 6428 "storagemodel" 2000 "create view sys.storagemodel as select * from sys.storagemodel();" 1 true 0 false 0 6438 "tablestoragemodel" 2000 "-- A summary of the table storage requirement is is available as a table view.\n-- The auxiliary column denotes the maximum space if all non-sorted columns\n-- would be augmented with a hash (rare situation)\ncreate view sys.tablestoragemodel\nas select ""schema"",""table"",max(count) as ""count"",\n\tsum(columnsize) as columnsize,\n\tsum(heapsize) as heapsize,\n\tsum(hashes) as hashes,\n\tsum(""imprints"") as ""imprints"",\n\tsum(case when sorted = false then 8 * count else 0 end) as auxiliary\nfrom sys.storagemodel() group by ""schema"",""table"";" 1 true 0 false 0 6453 "statistics" 2000 NULL 0 true 0 false 0 -6616 "systemfunctions" 2000 NULL 0 true 0 false 0 query R rowsort select sys.median(cast (id as double)) from tbls diff --git a/sql/test/BugTracker-2013/Tests/swapped_likejoin.Bug-3375.test b/sql/test/BugTracker-2013/Tests/swapped_likejoin.Bug-3375.test --- a/sql/test/BugTracker-2013/Tests/swapped_likejoin.Bug-3375.test +++ b/sql/test/BugTracker-2013/Tests/swapped_likejoin.Bug-3375.test @@ -11,7 +11,7 @@ CREATE TABLE "sys"."tbls" ( ) statement ok -COPY 49 RECORDS INTO "sys"."tbls" FROM stdin USING DELIMITERS E'\t',E'\n','"' _______________________________________________ checkin-list mailing list [email protected] https://www.monetdb.org/mailman/listinfo/checkin-list
