Changeset: b9ac81b85a72 for MonetDB
URL: https://dev.monetdb.org/hg/MonetDB/rev/b9ac81b85a72
Modified Files:
sql/scripts/52_describe.sql
sql/server/rel_unnest.c
sql/test/Dependencies/Tests/dependency_owner_schema_3.test
sql/test/emptydb/Tests/check.stable.out
sql/test/emptydb/Tests/check.stable.out.32bit
sql/test/emptydb/Tests/check.stable.out.int128
Branch: ordered-set-aggregates
Log Message:
approved output
removed (mis)use of over(partition/order by) and replace by the now possible
group_concat with order by
fixed problem with optimizers removing the order by expressions
diffs (truncated from 320 to 300 lines):
diff --git a/sql/scripts/52_describe.sql b/sql/scripts/52_describe.sql
--- a/sql/scripts/52_describe.sql
+++ b/sql/scripts/52_describe.sql
@@ -243,27 +243,20 @@ CREATE VIEW sys.describe_triggers AS
WHERE s.id = t.schema_id AND t.id = tr.table_id AND NOT
t.system;
CREATE VIEW sys.fully_qualified_functions AS
- WITH fqn(id, tpe, sig, num) AS
- (
- SELECT
- f.id,
- ft.function_type_keyword,
- CASE WHEN a.type IS NULL THEN
- sys.fqn(s.name, f.name) || '()'
- ELSE
- sys.fqn(s.name, f.name) || '(' ||
group_concat(sys.describe_type(a.type, a.type_digits, a.type_scale), ',') OVER
(PARTITION BY f.id ORDER BY a.number) || ')'
- END,
- a.number
- FROM sys.schemas s, sys.function_types ft, sys.functions f LEFT
JOIN sys.args a ON f.id = a.func_id
- WHERE s.id= f.schema_id AND f.type = ft.function_type_id
- )
SELECT
- fqn1.id id,
- fqn1.tpe tpe,
- fqn1.sig nme
- FROM
- fqn fqn1 JOIN (SELECT id, max(num) FROM fqn GROUP BY id)
fqn2(id, num)
- ON fqn1.id = fqn2.id AND (fqn1.num = fqn2.num OR fqn1.num IS
NULL AND fqn2.num is NULL);
+ f.id id,
+ ft.function_type_keyword tpe,
+ sys.fqn(s.name, f.name) || '(' ||
group_concat(sys.describe_type(a.type, a.type_digits, a.type_scale), ',' order
by a.number) || ')' nme
+ FROM sys.schemas s, sys.function_types ft, sys.functions f JOIN
sys.args a ON f.id = a.func_id
+ WHERE s.id= f.schema_id AND f.type = ft.function_type_id
+ group by f.id, ft.function_type_keyword, f.name, s.name
+ UNION
+ SELECT f.id id,
+ ft.function_type_keyword tpe,
+ sys.fqn(s.name, f.name) || '()' nme
+ FROM sys.schemas s, sys.function_types ft, sys.functions f
+ WHERE s.id= f.schema_id AND f.type = ft.function_type_id and f.id not
in ( select func_id from sys.args )
+ group by f.id, ft.function_type_keyword, f.name, s.name;
CREATE VIEW sys.describe_comments AS
SELECT o.id AS id, o.tpe AS tpe, o.nme AS fqn, cm.remark AS rem
@@ -439,41 +432,24 @@ CREATE VIEW sys.describe_sequences AS
ORDER BY s.name, seq.name;
CREATE VIEW sys.describe_functions AS
- WITH func_args_all(func_id, number, max_number, func_arg) AS
+ WITH func_args(func_id, func_arg) AS
(
SELECT
func_id,
- number,
- max(number) OVER (PARTITION BY func_id ORDER BY number
DESC),
- group_concat(sys.dq(name) || ' ' ||
sys.describe_type(type, type_digits, type_scale),', ') OVER (PARTITION BY
func_id ORDER BY number)
+ group_concat(sys.dq(name) || ' ' ||
sys.describe_type(type, type_digits, type_scale),', ' order by number)
FROM sys.args
WHERE inout = 1
- ),
- func_args(func_id, func_arg) AS
- (
- SELECT func_id, func_arg
- FROM func_args_all
- WHERE number = max_number
- ),
- func_rets_all(func_id, number, max_number, func_ret, func_ret_type) AS
- (
- SELECT
- func_id,
- number,
- max(number) OVER (PARTITION BY func_id ORDER BY number
DESC),
- group_concat(sys.dq(name) || ' ' ||
sys.describe_type(type, type_digits, type_scale),', ') OVER (PARTITION BY
func_id ORDER BY number),
- group_concat(sys.describe_type(type, type_digits,
type_scale),', ') OVER (PARTITION BY func_id ORDER BY number)
- FROM sys.args
- WHERE inout = 0
+ group by func_id
),
func_rets(func_id, func_ret, func_ret_type) AS
(
SELECT
func_id,
- func_ret,
- func_ret_type
- FROM func_rets_all
- WHERE number = max_number
+ group_concat(sys.dq(name) || ' ' ||
sys.describe_type(type, type_digits, type_scale),', ' order by number),
+ group_concat(sys.describe_type(type, type_digits,
type_scale),', ' order by number)
+ FROM sys.args
+ WHERE inout = 0
+ group by func_id
)
SELECT
f.id o,
diff --git a/sql/server/rel_unnest.c b/sql/server/rel_unnest.c
--- a/sql/server/rel_unnest.c
+++ b/sql/server/rel_unnest.c
@@ -2571,38 +2571,48 @@ aggrs_split_args(mvc *sql, list *aggrs,
continue;
}
list *args = a->l;
-
- if (!list_empty(args)) {
- for (node *an = args->h; an; an = an->next) {
- sql_exp *e1 = an->data, *found = NULL, *eo = e1;
- /* we keep converts as they reuse names of
inner columns */
- int convert = is_convert(e1->type);
-
- if (convert)
- e1 = e1->l;
- for (node *nn = exps->h; nn && !found; nn =
nn->next) {
- sql_exp *e2 = nn->data;
-
- if (!exp_equal(e1, e2))
- found = e2;
- }
- if (!found) {
+ list *r = a->r;
+ node *rn = r?r->h:NULL;
+
+ while(args) {
+ if (!list_empty(args)) {
+ for (node *an = args->h; an; an = an->next) {
+ sql_exp *e1 = an->data, *found = NULL,
*eo = e1;
+ /* we keep converts as they reuse names
of inner columns */
+ int convert = is_convert(e1->type);
+
+ if (convert)
+ e1 = e1->l;
+ for (node *nn = exps->h; nn && !found;
nn = nn->next) {
+ sql_exp *e2 = nn->data;
+
+ if (!exp_equal(e1, e2))
+ found = e2;
+ }
+ if (!found) {
+ if (!e1->alias.label)
+ e1 = exp_label(sql->sa,
e1, ++sql->label);
+ append(exps, e1);
+ } else {
+ e1 = found;
+ }
if (!e1->alias.label)
e1 = exp_label(sql->sa, e1,
++sql->label);
- append(exps, e1);
- } else {
- e1 = found;
+ e1 = exp_ref(sql, e1);
+ /* replace by reference */
+ if (convert) {
+ eo->l = e1;
+ } else {
+ an->data = e1;
+ clear_hash = true;
+ }
}
- if (!e1->alias.label)
- e1 = exp_label(sql->sa, e1,
++sql->label);
- e1 = exp_ref(sql, e1);
- /* replace by reference */
- if (convert) {
- eo->l = e1;
- } else {
- an->data = e1;
- clear_hash = true;
- }
+ }
+ if (rn) {
+ args = rn->data;
+ rn = rn->next;
+ } else {
+ args = NULL;
}
}
}
diff --git a/sql/test/Dependencies/Tests/dependency_owner_schema_3.test
b/sql/test/Dependencies/Tests/dependency_owner_schema_3.test
--- a/sql/test/Dependencies/Tests/dependency_owner_schema_3.test
+++ b/sql/test/Dependencies/Tests/dependency_owner_schema_3.test
@@ -886,7 +886,7 @@ DEP_FUNC
query TTT rowsort
SELECT distinct c.name, v.name, 'DEP_VIEW' from sys.columns as c, sys.tables
as v, sys.dependencies as dep where c.id = dep.id AND v.id = dep.depend_id AND
dep.depend_type = 5 AND v.type in (1, 11, 21, 31) order by c.name, v.name
----
-1851 values hashing to 43c843369c24d9e12a0cc728ce11aa67
+1812 values hashing to 6912e4830c92f0bbc05a66d11a4e8eaf
query TTT rowsort
SELECT c.name, k.name, 'DEP_KEY' from sys.columns as c, sys.objects as kc,
sys.keys as k where kc."name" = c.name AND kc.id = k.id AND k.table_id =
c.table_id AND k.rkey = -1 order by c.name, k.name
diff --git a/sql/test/emptydb/Tests/check.stable.out
b/sql/test/emptydb/Tests/check.stable.out
--- a/sql/test/emptydb/Tests/check.stable.out
+++ b/sql/test/emptydb/Tests/check.stable.out
@@ -498,7 +498,7 @@ select 'null in fkeys.delete_action', de
[ "sys._tables", "sys", "describe_comments", "create view
sys.describe_comments as select o.id as id, o.tpe as tpe, o.nme as fqn,
cm.remark as rem from (select id, 'SCHEMA', sys.dq(name) from sys.schemas where
not system union all select t.id, ifthenelse(ts.table_type_name = 'VIEW',
'VIEW', 'TABLE'), sys.fqn(s.name, t.name) from sys.schemas s join sys._tables t
on s.id = t.schema_id join sys.table_types ts on t.type = ts.table_type_id
where not t.system union all select c.id, 'COLUMN', sys.fqn(s.name, t.name) ||
'.' || sys.dq(c.name) from sys.columns c, sys._tables t, sys.schemas s where
not t.system and c.table_id = t.id and t.schema_id = s.id union all select
idx.id, 'INDEX', sys.fqn(s.name, idx.name) from sys.idxs idx, sys._tables t,
sys.schemas s where not t.system and idx.table_id = t.id and t.schema_id = s.id
union all select seq.id, 'SEQUENCE', sys.fqn(s.name, seq.name) from
sys.sequences seq, sys.schemas s where seq.schema_id = s.id union all select
f.id, ft.function_type_ke
yword, qf.nme from sys.functions f, sys.function_types ft, sys.schemas s,
sys.fully_qualified_functions qf where not f.system and f.type =
ft.function_type_id and f.schema_id = s.id and qf.id = f.id) as o(id, tpe, nme)
join sys.comments cm on cm.id = o.id;", "VIEW", true, "COMMIT",
"WRITABLE", NULL ]
[ "sys._tables", "sys", "describe_constraints", "create view
sys.describe_constraints as select s.name sch, t.name tbl, kc.name col, k.name
con, case k.type when 0 then 'PRIMARY KEY' when 1 then 'UNIQUE' end tpe from
sys.schemas s, sys._tables t, sys.objects kc, sys.keys k where kc.id = k.id and
k.table_id = t.id and s.id = t.schema_id and t.system = false and k.type in (0,
1);", "VIEW", true, "COMMIT", "WRITABLE", NULL ]
[ "sys._tables", "sys", "describe_foreign_keys", "create view
sys.describe_foreign_keys as with action_type (id, act) as (values (0, 'NO
ACTION'), (1, 'CASCADE'), (2, 'RESTRICT'), (3, 'SET NULL'), (4, 'SET DEFAULT'))
select fs.name fk_s, fkt.name fk_t, fkkc.name fk_c, fkkc.nr o, fkk.name fk,
ps.name pk_s, pkt.name pk_t, pkkc.name pk_c, ou.act on_update, od.act on_delete
from sys._tables fkt, sys.objects fkkc, sys.keys fkk, sys._tables pkt,
sys.objects pkkc, sys.keys pkk, sys.schemas ps, sys.schemas fs, action_type ou,
action_type od where fkt.id = fkk.table_id and pkt.id = pkk.table_id and fkk.id
= fkkc.id and pkk.id = pkkc.id and fkk.rkey = pkk.id and fkkc.nr = pkkc.nr and
pkt.schema_id = ps.id and fkt.schema_id = fs.id and (fkk.\"action\" & 255) =
od.id and ((fkk.\"action\" >> 8) & 255) = ou.id order by fkk.name, fkkc.nr;",
"VIEW", true, "COMMIT", "WRITABLE", NULL ]
-[ "sys._tables", "sys", "describe_functions", "create view
sys.describe_functions as with func_args_all(func_id, number, max_number,
func_arg) as (select func_id, number, max(number) over (partition by func_id
order by number desc), group_concat(sys.dq(name) || ' ' ||
sys.describe_type(type, type_digits, type_scale),', ') over (partition by
func_id order by number) from sys.args where inout = 1), func_args(func_id,
func_arg) as (select func_id, func_arg from func_args_all where number =
max_number), func_rets_all(func_id, number, max_number, func_ret,
func_ret_type) as (select func_id, number, max(number) over (partition by
func_id order by number desc), group_concat(sys.dq(name) || ' ' ||
sys.describe_type(type, type_digits, type_scale),', ') over (partition by
func_id order by number), group_concat(sys.describe_type(type, type_digits,
type_scale),', ') over (partition by func_id order by number) from sys.args
where inout = 0), func_rets(func_id, func_ret, func_ret_type) as (select
func_id, func_ret, func_ret_type from func_rets_all where number =
max_number) select f.id o, s.name sch, f.name fun, case when f.language in (1,
2) then f.func else 'CREATE ' || ft.function_type_keyword || ' ' ||
sys.fqn(s.name, f.name) || '(' || coalesce(fa.func_arg, '') || ')' || case when
f.type = 5 then ' RETURNS TABLE (' || coalesce(fr.func_ret, '') || ')' when
f.type in (1,3) then ' RETURNS ' || fr.func_ret_type else '' end || case when
fl.language_keyword is null then '' else ' LANGUAGE ' || fl.language_keyword
end || ' ' || f.func end def from sys.functions f left outer join func_args fa
on fa.func_id = f.id left outer join func_rets fr on fr.func_id = f.id join
sys.schemas s on f.schema_id = s.id join sys.function_types ft on f.type =
ft.function_type_id left outer join sys.function_languages fl on f.language =
fl.language_id where s.name <> 'tmp' and not f.system;", "VIEW", true,
"COMMIT", "WRITABLE", NULL ]
+[ "sys._tables", "sys", "describe_functions", "create view
sys.describe_functions as with func_args(func_id, func_arg) as (select func_id,
group_concat(sys.dq(name) || ' ' || sys.describe_type(type, type_digits,
type_scale),', ' order by number) from sys.args where inout = 1 group by
func_id), func_rets(func_id, func_ret, func_ret_type) as (select func_id,
group_concat(sys.dq(name) || ' ' || sys.describe_type(type, type_digits,
type_scale),', ' order by number), group_concat(sys.describe_type(type,
type_digits, type_scale),', ' order by number) from sys.args where inout = 0
group by func_id) select f.id o, s.name sch, f.name fun, case when f.language
in (1, 2) then f.func else 'CREATE ' || ft.function_type_keyword || ' ' ||
sys.fqn(s.name, f.name) || '(' || coalesce(fa.func_arg, '') || ')' || case when
f.type = 5 then ' RETURNS TABLE (' || coalesce(fr.func_ret, '') || ')' when
f.type in (1,3) then ' RETURNS ' || fr.func_ret_type else '' end || case when
fl.language_keyword is null t
hen '' else ' LANGUAGE ' || fl.language_keyword end || ' ' || f.func end def
from sys.functions f left outer join func_args fa on fa.func_id = f.id left
outer join func_rets fr on fr.func_id = f.id join sys.schemas s on f.schema_id
= s.id join sys.function_types ft on f.type = ft.function_type_id left outer
join sys.function_languages fl on f.language = fl.language_id where s.name <>
'tmp' and not f.system;", "VIEW", true, "COMMIT", "WRITABLE",
NULL ]
[ "sys._tables", "sys", "describe_indices", "create view
sys.describe_indices as with it (id, idx) as (values (0, 'INDEX'), (4,
'IMPRINTS INDEX'), (5, 'ORDERED INDEX')) select i.name ind, s.name sch, t.name
tbl, c.name col, it.idx tpe from sys.idxs as i left join sys.keys as k on
i.name = k.name, sys.objects as kc, sys._columns as c, sys.schemas s,
sys._tables as t, it where i.table_id = t.id and i.id = kc.id and kc.name =
c.name and t.id = c.table_id and t.schema_id = s.id and k.type is null and
i.type = it.id order by i.name, kc.nr;", "VIEW", true, "COMMIT",
"WRITABLE", NULL ]
[ "sys._tables", "sys", "describe_partition_tables", "create view
sys.describe_partition_tables as select m_sch, m_tbl, p_sch, p_tbl, case when
p_raw_type is null then 'READ ONLY' when (p_raw_type = 'VALUES' and pvalues is
null) or (p_raw_type = 'RANGE' and minimum is null and maximum is null and
with_nulls) then 'FOR NULLS' else p_raw_type end as tpe, pvalues, minimum,
maximum, with_nulls from (with tp(\"type\", table_id) as (select
ifthenelse((table_partitions.\"type\" & 2) = 2, 'VALUES', 'RANGE'),
table_partitions.table_id from sys.table_partitions), subq(m_tid, p_mid,
\"type\", m_sch, m_tbl, p_sch, p_tbl) as (select m_t.id, p_m.id, m_t.\"type\",
m_s.name, m_t.name, p_s.name, p_m.name from sys.schemas m_s, sys._tables m_t,
sys.dependencies d, sys.schemas p_s, sys._tables p_m where m_t.\"type\" in (3,
6) and m_t.schema_id = m_s.id and m_s.name <> 'tmp' and m_t.system = false and
m_t.id = d.depend_id and d.id = p_m.id and p_m.schema_id = p_s.id order by
m_t.id, p_m.id), vals(id,val
s) as (select vp.table_id, group_concat(vp.value, ',') from
sys.value_partitions vp group by vp.table_id) select subq.m_sch, subq.m_tbl,
subq.p_sch, subq.p_tbl, tp.\"type\" as p_raw_type, case when tp.\"type\" =
'VALUES' then (select vals.vals from vals where vals.id = subq.p_mid) else null
end as pvalues, case when tp.\"type\" = 'RANGE' then (select minimum from
sys.range_partitions rp where rp.table_id = subq.p_mid) else null end as
minimum, case when tp.\"type\" = 'RANGE' then (select maximum from
sys.range_partitions rp where rp.table_id = subq.p_mid) else null end as
maximum, case when tp.\"type\" = 'VALUES' then exists(select vp.value from
sys.value_partitions vp where vp.table_id = subq.p_mid and vp.value is null)
else (select rp.with_nulls from sys.range_partitions rp where rp.table_id =
subq.p_mid) end as with_nulls from subq left outer join tp on subq.m_tid =
tp.table_id) as tmp_pi;", "VIEW", true, "COMMIT", "WRITABLE",
NULL ]
[ "sys._tables", "sys", "describe_privileges", "create view
sys.describe_privileges as select case when o.tpe is null and
pc.privilege_code_name = 'SELECT' then 'COPY FROM' when o.tpe is null and
pc.privilege_code_name = 'UPDATE' then 'COPY INTO' else o.nme end o_nme,
coalesce(o.tpe, 'GLOBAL') o_tpe, pc.privilege_code_name p_nme, a.name a_nme,
g.name g_nme, p.grantable grantable from sys.privileges p left join (select
t.id, s.name || '.' || t.name , 'TABLE' from sys.schemas s, sys.tables t where
s.id = t.schema_id union all select c.id, s.name || '.' || t.name || '.' ||
c.name, 'COLUMN' from sys.schemas s, sys.tables t, sys.columns c where s.id =
t.schema_id and t.id = c.table_id union all select f.id, f.nme, f.tpe from
sys.fully_qualified_functions f) o(id, nme, tpe) on o.id = p.obj_id,
sys.privilege_codes pc, auths a, auths g where p.privileges =
pc.privilege_code_id and p.auth_id = a.id and p.grantor = g.id;", "VIEW", true,
"COMMIT", "WRITABLE", NULL ]
@@ -530,7 +530,7 @@ select 'null in fkeys.delete_action', de
[ "sys._tables", "sys", "environment", "create view sys.environment as
select * from sys.env();", "VIEW", true, "COMMIT", "WRITABLE",
NULL ]
[ "sys._tables", "sys", "fkey_actions", NULL, "TABLE", true,
"COMMIT", "READONLY", NULL ]
[ "sys._tables", "sys", "fkeys", "create view sys.fkeys as
select id, table_id, type, name, rkey, update_action_id, upd.action_name as
update_action, delete_action_id, del.action_name as delete_action from (select
id, table_id, type, name, rkey, cast(((\"action\" >> 8) & 255) as smallint) as
update_action_id, cast((\"action\" & 255) as smallint) as delete_action_id from
sys.keys where type = 2 union all select id, table_id, type, name, rkey,
cast(((\"action\" >> 8) & 255) as smallint) as update_action_id,
cast((\"action\" & 255) as smallint) as delete_action_id from tmp.keys where
type = 2) as fks join sys.fkey_actions upd on fks.update_action_id =
upd.action_id join sys.fkey_actions del on fks.delete_action_id =
del.action_id;", "VIEW", true, "COMMIT", "WRITABLE", NULL ]
-[ "sys._tables", "sys", "fully_qualified_functions", "create view
sys.fully_qualified_functions as with fqn(id, tpe, sig, num) as (select f.id,
ft.function_type_keyword, case when a.type is null then sys.fqn(s.name, f.name)
|| '()' else sys.fqn(s.name, f.name) || '(' ||
group_concat(sys.describe_type(a.type, a.type_digits, a.type_scale), ',') over
(partition by f.id order by a.number) || ')' end, a.number from sys.schemas s,
sys.function_types ft, sys.functions f left join sys.args a on f.id = a.func_id
where s.id= f.schema_id and f.type = ft.function_type_id) select fqn1.id id,
fqn1.tpe tpe, fqn1.sig nme from fqn fqn1 join (select id, max(num) from fqn
group by id) fqn2(id, num) on fqn1.id = fqn2.id and (fqn1.num = fqn2.num or
fqn1.num is null and fqn2.num is null);", "VIEW", true, "COMMIT",
"WRITABLE", NULL ]
+[ "sys._tables", "sys", "fully_qualified_functions", "create view
sys.fully_qualified_functions as select f.id id, ft.function_type_keyword tpe,
sys.fqn(s.name, f.name) || '(' || group_concat(sys.describe_type(a.type,
a.type_digits, a.type_scale), ',' order by a.number) || ')' nme from
sys.schemas s, sys.function_types ft, sys.functions f join sys.args a on f.id =
a.func_id where s.id= f.schema_id and f.type = ft.function_type_id group by
f.id, ft.function_type_keyword, f.name, s.name union select f.id id,
ft.function_type_keyword tpe, sys.fqn(s.name, f.name) || '()' nme from
sys.schemas s, sys.function_types ft, sys.functions f where s.id= f.schema_id
and f.type = ft.function_type_id and f.id not in (select func_id from sys.args)
group by f.id, ft.function_type_keyword, f.name, s.name;", "VIEW", true,
"COMMIT", "WRITABLE", NULL ]
[ "sys._tables", "sys", "function_languages", NULL, "TABLE",
true, "COMMIT", "READONLY", NULL ]
[ "sys._tables", "sys", "function_types", NULL, "TABLE",
true, "COMMIT", "READONLY", NULL ]
[ "sys._tables", "sys", "functions", NULL, "TABLE", true,
"COMMIT", "WRITABLE", NULL ]
@@ -4219,34 +4219,18 @@ select 'null in fkeys.delete_action', de
[ "column used by view", "tmp", "keys", "table_id", "sys",
"fkeys", "VIEW" ]
[ "column used by view", "tmp", "keys", "type", "sys", "fkeys",
"VIEW" ]
[ "column used by view", "sys", "args", "func_id", "sys",
"fully_qualified_functions", "VIEW" ]
-[ "column used by view", "sys", "args", "id", "sys",
"fully_qualified_functions", "VIEW" ]
-[ "column used by view", "sys", "args", "inout", "sys",
"fully_qualified_functions", "VIEW" ]
-[ "column used by view", "sys", "args", "name", "sys",
"fully_qualified_functions", "VIEW" ]
[ "column used by view", "sys", "args", "number", "sys",
"fully_qualified_functions", "VIEW" ]
[ "column used by view", "sys", "args", "type", "sys",
"fully_qualified_functions", "VIEW" ]
[ "column used by view", "sys", "args", "type_digits", "sys",
"fully_qualified_functions", "VIEW" ]
[ "column used by view", "sys", "args", "type_scale", "sys",
"fully_qualified_functions", "VIEW" ]
[ "column used by view", "sys", "function_types",
"function_type_id", "sys", "fully_qualified_functions", "VIEW" ]
[ "column used by view", "sys", "function_types",
"function_type_keyword", "sys", "fully_qualified_functions", "VIEW"
]
-[ "column used by view", "sys", "function_types",
"function_type_name", "sys", "fully_qualified_functions", "VIEW" ]
-[ "column used by view", "sys", "functions", "func", "sys",
"fully_qualified_functions", "VIEW" ]
[ "column used by view", "sys", "functions", "id", "sys",
"fully_qualified_functions", "VIEW" ]
-[ "column used by view", "sys", "functions", "language", "sys",
"fully_qualified_functions", "VIEW" ]
-[ "column used by view", "sys", "functions", "mod", "sys",
"fully_qualified_functions", "VIEW" ]
[ "column used by view", "sys", "functions", "name", "sys",
"fully_qualified_functions", "VIEW" ]
-[ "column used by view", "sys", "functions", "order_specification",
"sys", "fully_qualified_functions", "VIEW" ]
[ "column used by view", "sys", "functions", "schema_id", "sys",
"fully_qualified_functions", "VIEW" ]
-[ "column used by view", "sys", "functions", "semantics", "sys",
"fully_qualified_functions", "VIEW" ]
-[ "column used by view", "sys", "functions", "side_effect", "sys",
"fully_qualified_functions", "VIEW" ]
-[ "column used by view", "sys", "functions", "system", "sys",
"fully_qualified_functions", "VIEW" ]
[ "column used by view", "sys", "functions", "type", "sys",
"fully_qualified_functions", "VIEW" ]
-[ "column used by view", "sys", "functions", "vararg", "sys",
"fully_qualified_functions", "VIEW" ]
-[ "column used by view", "sys", "functions", "varres", "sys",
"fully_qualified_functions", "VIEW" ]
-[ "column used by view", "sys", "schemas", "authorization",
"sys", "fully_qualified_functions", "VIEW" ]
[ "column used by view", "sys", "schemas", "id", "sys",
"fully_qualified_functions", "VIEW" ]
[ "column used by view", "sys", "schemas", "name", "sys",
"fully_qualified_functions", "VIEW" ]
-[ "column used by view", "sys", "schemas", "owner", "sys",
"fully_qualified_functions", "VIEW" ]
-[ "column used by view", "sys", "schemas", "system", "sys",
"fully_qualified_functions", "VIEW" ]
[ "column used by view", "sys", "columns", "name", "sys",
"geometry_columns", "VIEW" ]
[ "column used by view", "sys", "columns", "table_id", "sys",
"geometry_columns", "VIEW" ]
[ "column used by view", "sys", "columns", "type", "sys",
"geometry_columns", "VIEW" ]
diff --git a/sql/test/emptydb/Tests/check.stable.out.32bit
b/sql/test/emptydb/Tests/check.stable.out.32bit
--- a/sql/test/emptydb/Tests/check.stable.out.32bit
+++ b/sql/test/emptydb/Tests/check.stable.out.32bit
@@ -498,7 +498,7 @@ select 'null in fkeys.delete_action', de
[ "sys._tables", "sys", "describe_comments", "create view
sys.describe_comments as select o.id as id, o.tpe as tpe, o.nme as fqn,
cm.remark as rem from (select id, 'SCHEMA', sys.dq(name) from sys.schemas where
not system union all select t.id, ifthenelse(ts.table_type_name = 'VIEW',
'VIEW', 'TABLE'), sys.fqn(s.name, t.name) from sys.schemas s join sys._tables t
on s.id = t.schema_id join sys.table_types ts on t.type = ts.table_type_id
where not t.system union all select c.id, 'COLUMN', sys.fqn(s.name, t.name) ||
'.' || sys.dq(c.name) from sys.columns c, sys._tables t, sys.schemas s where
not t.system and c.table_id = t.id and t.schema_id = s.id union all select
idx.id, 'INDEX', sys.fqn(s.name, idx.name) from sys.idxs idx, sys._tables t,
sys.schemas s where not t.system and idx.table_id = t.id and t.schema_id = s.id
union all select seq.id, 'SEQUENCE', sys.fqn(s.name, seq.name) from
sys.sequences seq, sys.schemas s where seq.schema_id = s.id union all select
f.id, ft.function_type_ke
yword, qf.nme from sys.functions f, sys.function_types ft, sys.schemas s,
sys.fully_qualified_functions qf where not f.system and f.type =
ft.function_type_id and f.schema_id = s.id and qf.id = f.id) as o(id, tpe, nme)
join sys.comments cm on cm.id = o.id;", "VIEW", true, "COMMIT",
"WRITABLE", NULL ]
[ "sys._tables", "sys", "describe_constraints", "create view
sys.describe_constraints as select s.name sch, t.name tbl, kc.name col, k.name
con, case k.type when 0 then 'PRIMARY KEY' when 1 then 'UNIQUE' end tpe from
sys.schemas s, sys._tables t, sys.objects kc, sys.keys k where kc.id = k.id and
k.table_id = t.id and s.id = t.schema_id and t.system = false and k.type in (0,
1);", "VIEW", true, "COMMIT", "WRITABLE", NULL ]
[ "sys._tables", "sys", "describe_foreign_keys", "create view
sys.describe_foreign_keys as with action_type (id, act) as (values (0, 'NO
ACTION'), (1, 'CASCADE'), (2, 'RESTRICT'), (3, 'SET NULL'), (4, 'SET DEFAULT'))
select fs.name fk_s, fkt.name fk_t, fkkc.name fk_c, fkkc.nr o, fkk.name fk,
ps.name pk_s, pkt.name pk_t, pkkc.name pk_c, ou.act on_update, od.act on_delete
from sys._tables fkt, sys.objects fkkc, sys.keys fkk, sys._tables pkt,
sys.objects pkkc, sys.keys pkk, sys.schemas ps, sys.schemas fs, action_type ou,
action_type od where fkt.id = fkk.table_id and pkt.id = pkk.table_id and fkk.id
= fkkc.id and pkk.id = pkkc.id and fkk.rkey = pkk.id and fkkc.nr = pkkc.nr and
pkt.schema_id = ps.id and fkt.schema_id = fs.id and (fkk.\"action\" & 255) =
od.id and ((fkk.\"action\" >> 8) & 255) = ou.id order by fkk.name, fkkc.nr;",
"VIEW", true, "COMMIT", "WRITABLE", NULL ]
-[ "sys._tables", "sys", "describe_functions", "create view
sys.describe_functions as with func_args_all(func_id, number, max_number,
func_arg) as (select func_id, number, max(number) over (partition by func_id
order by number desc), group_concat(sys.dq(name) || ' ' ||
sys.describe_type(type, type_digits, type_scale),', ') over (partition by
func_id order by number) from sys.args where inout = 1), func_args(func_id,
func_arg) as (select func_id, func_arg from func_args_all where number =
max_number), func_rets_all(func_id, number, max_number, func_ret,
func_ret_type) as (select func_id, number, max(number) over (partition by
func_id order by number desc), group_concat(sys.dq(name) || ' ' ||
sys.describe_type(type, type_digits, type_scale),', ') over (partition by
func_id order by number), group_concat(sys.describe_type(type, type_digits,
type_scale),', ') over (partition by func_id order by number) from sys.args
where inout = 0), func_rets(func_id, func_ret, func_ret_type) as (select
func_id, func_ret, func_ret_type from func_rets_all where number =
max_number) select f.id o, s.name sch, f.name fun, case when f.language in (1,
2) then f.func else 'CREATE ' || ft.function_type_keyword || ' ' ||
sys.fqn(s.name, f.name) || '(' || coalesce(fa.func_arg, '') || ')' || case when
f.type = 5 then ' RETURNS TABLE (' || coalesce(fr.func_ret, '') || ')' when
f.type in (1,3) then ' RETURNS ' || fr.func_ret_type else '' end || case when
fl.language_keyword is null then '' else ' LANGUAGE ' || fl.language_keyword
end || ' ' || f.func end def from sys.functions f left outer join func_args fa
on fa.func_id = f.id left outer join func_rets fr on fr.func_id = f.id join
sys.schemas s on f.schema_id = s.id join sys.function_types ft on f.type =
ft.function_type_id left outer join sys.function_languages fl on f.language =
fl.language_id where s.name <> 'tmp' and not f.system;", "VIEW", true,
"COMMIT", "WRITABLE", NULL ]
+[ "sys._tables", "sys", "describe_functions", "create view
sys.describe_functions as with func_args(func_id, func_arg) as (select func_id,
group_concat(sys.dq(name) || ' ' || sys.describe_type(type, type_digits,
type_scale),', ' order by number) from sys.args where inout = 1 group by
func_id), func_rets(func_id, func_ret, func_ret_type) as (select func_id,
group_concat(sys.dq(name) || ' ' || sys.describe_type(type, type_digits,
type_scale),', ' order by number), group_concat(sys.describe_type(type,
type_digits, type_scale),', ' order by number) from sys.args where inout = 0
group by func_id) select f.id o, s.name sch, f.name fun, case when f.language
in (1, 2) then f.func else 'CREATE ' || ft.function_type_keyword || ' ' ||
sys.fqn(s.name, f.name) || '(' || coalesce(fa.func_arg, '') || ')' || case when
f.type = 5 then ' RETURNS TABLE (' || coalesce(fr.func_ret, '') || ')' when
f.type in (1,3) then ' RETURNS ' || fr.func_ret_type else '' end || case when
fl.language_keyword is null t
hen '' else ' LANGUAGE ' || fl.language_keyword end || ' ' || f.func end def
from sys.functions f left outer join func_args fa on fa.func_id = f.id left
outer join func_rets fr on fr.func_id = f.id join sys.schemas s on f.schema_id
= s.id join sys.function_types ft on f.type = ft.function_type_id left outer
join sys.function_languages fl on f.language = fl.language_id where s.name <>
'tmp' and not f.system;", "VIEW", true, "COMMIT", "WRITABLE",
NULL ]
[ "sys._tables", "sys", "describe_indices", "create view
sys.describe_indices as with it (id, idx) as (values (0, 'INDEX'), (4,
'IMPRINTS INDEX'), (5, 'ORDERED INDEX')) select i.name ind, s.name sch, t.name
tbl, c.name col, it.idx tpe from sys.idxs as i left join sys.keys as k on
i.name = k.name, sys.objects as kc, sys._columns as c, sys.schemas s,
sys._tables as t, it where i.table_id = t.id and i.id = kc.id and kc.name =
c.name and t.id = c.table_id and t.schema_id = s.id and k.type is null and
i.type = it.id order by i.name, kc.nr;", "VIEW", true, "COMMIT",
"WRITABLE", NULL ]
[ "sys._tables", "sys", "describe_partition_tables", "create view
sys.describe_partition_tables as select m_sch, m_tbl, p_sch, p_tbl, case when
p_raw_type is null then 'READ ONLY' when (p_raw_type = 'VALUES' and pvalues is
null) or (p_raw_type = 'RANGE' and minimum is null and maximum is null and
with_nulls) then 'FOR NULLS' else p_raw_type end as tpe, pvalues, minimum,
maximum, with_nulls from (with tp(\"type\", table_id) as (select
ifthenelse((table_partitions.\"type\" & 2) = 2, 'VALUES', 'RANGE'),
table_partitions.table_id from sys.table_partitions), subq(m_tid, p_mid,
\"type\", m_sch, m_tbl, p_sch, p_tbl) as (select m_t.id, p_m.id, m_t.\"type\",
m_s.name, m_t.name, p_s.name, p_m.name from sys.schemas m_s, sys._tables m_t,
sys.dependencies d, sys.schemas p_s, sys._tables p_m where m_t.\"type\" in (3,
6) and m_t.schema_id = m_s.id and m_s.name <> 'tmp' and m_t.system = false and
m_t.id = d.depend_id and d.id = p_m.id and p_m.schema_id = p_s.id order by
m_t.id, p_m.id), vals(id,val
s) as (select vp.table_id, group_concat(vp.value, ',') from
sys.value_partitions vp group by vp.table_id) select subq.m_sch, subq.m_tbl,
subq.p_sch, subq.p_tbl, tp.\"type\" as p_raw_type, case when tp.\"type\" =
'VALUES' then (select vals.vals from vals where vals.id = subq.p_mid) else null
end as pvalues, case when tp.\"type\" = 'RANGE' then (select minimum from
sys.range_partitions rp where rp.table_id = subq.p_mid) else null end as
minimum, case when tp.\"type\" = 'RANGE' then (select maximum from
sys.range_partitions rp where rp.table_id = subq.p_mid) else null end as
maximum, case when tp.\"type\" = 'VALUES' then exists(select vp.value from
sys.value_partitions vp where vp.table_id = subq.p_mid and vp.value is null)
else (select rp.with_nulls from sys.range_partitions rp where rp.table_id =
subq.p_mid) end as with_nulls from subq left outer join tp on subq.m_tid =
tp.table_id) as tmp_pi;", "VIEW", true, "COMMIT", "WRITABLE",
NULL ]
[ "sys._tables", "sys", "describe_privileges", "create view
sys.describe_privileges as select case when o.tpe is null and
pc.privilege_code_name = 'SELECT' then 'COPY FROM' when o.tpe is null and
pc.privilege_code_name = 'UPDATE' then 'COPY INTO' else o.nme end o_nme,
coalesce(o.tpe, 'GLOBAL') o_tpe, pc.privilege_code_name p_nme, a.name a_nme,
g.name g_nme, p.grantable grantable from sys.privileges p left join (select
t.id, s.name || '.' || t.name , 'TABLE' from sys.schemas s, sys.tables t where
s.id = t.schema_id union all select c.id, s.name || '.' || t.name || '.' ||
c.name, 'COLUMN' from sys.schemas s, sys.tables t, sys.columns c where s.id =
t.schema_id and t.id = c.table_id union all select f.id, f.nme, f.tpe from
sys.fully_qualified_functions f) o(id, nme, tpe) on o.id = p.obj_id,
sys.privilege_codes pc, auths a, auths g where p.privileges =
pc.privilege_code_id and p.auth_id = a.id and p.grantor = g.id;", "VIEW", true,
"COMMIT", "WRITABLE", NULL ]
@@ -530,7 +530,7 @@ select 'null in fkeys.delete_action', de
[ "sys._tables", "sys", "environment", "create view sys.environment as
select * from sys.env();", "VIEW", true, "COMMIT", "WRITABLE",
NULL ]
[ "sys._tables", "sys", "fkey_actions", NULL, "TABLE", true,
"COMMIT", "READONLY", NULL ]
[ "sys._tables", "sys", "fkeys", "create view sys.fkeys as
select id, table_id, type, name, rkey, update_action_id, upd.action_name as
update_action, delete_action_id, del.action_name as delete_action from (select
id, table_id, type, name, rkey, cast(((\"action\" >> 8) & 255) as smallint) as
update_action_id, cast((\"action\" & 255) as smallint) as delete_action_id from
sys.keys where type = 2 union all select id, table_id, type, name, rkey,
cast(((\"action\" >> 8) & 255) as smallint) as update_action_id,
cast((\"action\" & 255) as smallint) as delete_action_id from tmp.keys where
type = 2) as fks join sys.fkey_actions upd on fks.update_action_id =
upd.action_id join sys.fkey_actions del on fks.delete_action_id =
del.action_id;", "VIEW", true, "COMMIT", "WRITABLE", NULL ]
-[ "sys._tables", "sys", "fully_qualified_functions", "create view
sys.fully_qualified_functions as with fqn(id, tpe, sig, num) as (select f.id,
ft.function_type_keyword, case when a.type is null then sys.fqn(s.name, f.name)
|| '()' else sys.fqn(s.name, f.name) || '(' ||
group_concat(sys.describe_type(a.type, a.type_digits, a.type_scale), ',') over
(partition by f.id order by a.number) || ')' end, a.number from sys.schemas s,
sys.function_types ft, sys.functions f left join sys.args a on f.id = a.func_id
where s.id= f.schema_id and f.type = ft.function_type_id) select fqn1.id id,
fqn1.tpe tpe, fqn1.sig nme from fqn fqn1 join (select id, max(num) from fqn
group by id) fqn2(id, num) on fqn1.id = fqn2.id and (fqn1.num = fqn2.num or
fqn1.num is null and fqn2.num is null);", "VIEW", true, "COMMIT",
"WRITABLE", NULL ]
+[ "sys._tables", "sys", "fully_qualified_functions", "create view
sys.fully_qualified_functions as select f.id id, ft.function_type_keyword tpe,
sys.fqn(s.name, f.name) || '(' || group_concat(sys.describe_type(a.type,
a.type_digits, a.type_scale), ',' order by a.number) || ')' nme from
sys.schemas s, sys.function_types ft, sys.functions f join sys.args a on f.id =
a.func_id where s.id= f.schema_id and f.type = ft.function_type_id group by
f.id, ft.function_type_keyword, f.name, s.name union select f.id id,
ft.function_type_keyword tpe, sys.fqn(s.name, f.name) || '()' nme from
sys.schemas s, sys.function_types ft, sys.functions f where s.id= f.schema_id
and f.type = ft.function_type_id and f.id not in (select func_id from sys.args)
group by f.id, ft.function_type_keyword, f.name, s.name;", "VIEW", true,
"COMMIT", "WRITABLE", NULL ]
[ "sys._tables", "sys", "function_languages", NULL, "TABLE",
true, "COMMIT", "READONLY", NULL ]
[ "sys._tables", "sys", "function_types", NULL, "TABLE",
true, "COMMIT", "READONLY", NULL ]
[ "sys._tables", "sys", "functions", NULL, "TABLE", true,
"COMMIT", "WRITABLE", NULL ]
diff --git a/sql/test/emptydb/Tests/check.stable.out.int128
b/sql/test/emptydb/Tests/check.stable.out.int128
--- a/sql/test/emptydb/Tests/check.stable.out.int128
+++ b/sql/test/emptydb/Tests/check.stable.out.int128
@@ -498,7 +498,7 @@ select 'null in fkeys.delete_action', de
[ "sys._tables", "sys", "describe_comments", "create view
sys.describe_comments as select o.id as id, o.tpe as tpe, o.nme as fqn,
cm.remark as rem from (select id, 'SCHEMA', sys.dq(name) from sys.schemas where
not system union all select t.id, ifthenelse(ts.table_type_name = 'VIEW',
'VIEW', 'TABLE'), sys.fqn(s.name, t.name) from sys.schemas s join sys._tables t
on s.id = t.schema_id join sys.table_types ts on t.type = ts.table_type_id
where not t.system union all select c.id, 'COLUMN', sys.fqn(s.name, t.name) ||
'.' || sys.dq(c.name) from sys.columns c, sys._tables t, sys.schemas s where
not t.system and c.table_id = t.id and t.schema_id = s.id union all select
idx.id, 'INDEX', sys.fqn(s.name, idx.name) from sys.idxs idx, sys._tables t,
sys.schemas s where not t.system and idx.table_id = t.id and t.schema_id = s.id
union all select seq.id, 'SEQUENCE', sys.fqn(s.name, seq.name) from
sys.sequences seq, sys.schemas s where seq.schema_id = s.id union all select
f.id, ft.function_type_ke
yword, qf.nme from sys.functions f, sys.function_types ft, sys.schemas s,
sys.fully_qualified_functions qf where not f.system and f.type =
ft.function_type_id and f.schema_id = s.id and qf.id = f.id) as o(id, tpe, nme)
join sys.comments cm on cm.id = o.id;", "VIEW", true, "COMMIT",
"WRITABLE", NULL ]
[ "sys._tables", "sys", "describe_constraints", "create view
sys.describe_constraints as select s.name sch, t.name tbl, kc.name col, k.name
con, case k.type when 0 then 'PRIMARY KEY' when 1 then 'UNIQUE' end tpe from
sys.schemas s, sys._tables t, sys.objects kc, sys.keys k where kc.id = k.id and
k.table_id = t.id and s.id = t.schema_id and t.system = false and k.type in (0,
1);", "VIEW", true, "COMMIT", "WRITABLE", NULL ]
[ "sys._tables", "sys", "describe_foreign_keys", "create view
sys.describe_foreign_keys as with action_type (id, act) as (values (0, 'NO
ACTION'), (1, 'CASCADE'), (2, 'RESTRICT'), (3, 'SET NULL'), (4, 'SET DEFAULT'))
select fs.name fk_s, fkt.name fk_t, fkkc.name fk_c, fkkc.nr o, fkk.name fk,
ps.name pk_s, pkt.name pk_t, pkkc.name pk_c, ou.act on_update, od.act on_delete
from sys._tables fkt, sys.objects fkkc, sys.keys fkk, sys._tables pkt,
sys.objects pkkc, sys.keys pkk, sys.schemas ps, sys.schemas fs, action_type ou,
action_type od where fkt.id = fkk.table_id and pkt.id = pkk.table_id and fkk.id
= fkkc.id and pkk.id = pkkc.id and fkk.rkey = pkk.id and fkkc.nr = pkkc.nr and
pkt.schema_id = ps.id and fkt.schema_id = fs.id and (fkk.\"action\" & 255) =
od.id and ((fkk.\"action\" >> 8) & 255) = ou.id order by fkk.name, fkkc.nr;",
"VIEW", true, "COMMIT", "WRITABLE", NULL ]
-[ "sys._tables", "sys", "describe_functions", "create view
sys.describe_functions as with func_args_all(func_id, number, max_number,
func_arg) as (select func_id, number, max(number) over (partition by func_id
order by number desc), group_concat(sys.dq(name) || ' ' ||
sys.describe_type(type, type_digits, type_scale),', ') over (partition by
func_id order by number) from sys.args where inout = 1), func_args(func_id,
func_arg) as (select func_id, func_arg from func_args_all where number =
max_number), func_rets_all(func_id, number, max_number, func_ret,
func_ret_type) as (select func_id, number, max(number) over (partition by
func_id order by number desc), group_concat(sys.dq(name) || ' ' ||
sys.describe_type(type, type_digits, type_scale),', ') over (partition by
func_id order by number), group_concat(sys.describe_type(type, type_digits,
type_scale),', ') over (partition by func_id order by number) from sys.args
where inout = 0), func_rets(func_id, func_ret, func_ret_type) as (select
func_id, func_ret, func_ret_type from func_rets_all where number =
max_number) select f.id o, s.name sch, f.name fun, case when f.language in (1,
2) then f.func else 'CREATE ' || ft.function_type_keyword || ' ' ||
sys.fqn(s.name, f.name) || '(' || coalesce(fa.func_arg, '') || ')' || case when
f.type = 5 then ' RETURNS TABLE (' || coalesce(fr.func_ret, '') || ')' when
f.type in (1,3) then ' RETURNS ' || fr.func_ret_type else '' end || case when
fl.language_keyword is null then '' else ' LANGUAGE ' || fl.language_keyword
end || ' ' || f.func end def from sys.functions f left outer join func_args fa
on fa.func_id = f.id left outer join func_rets fr on fr.func_id = f.id join
sys.schemas s on f.schema_id = s.id join sys.function_types ft on f.type =
ft.function_type_id left outer join sys.function_languages fl on f.language =
fl.language_id where s.name <> 'tmp' and not f.system;", "VIEW", true,
"COMMIT", "WRITABLE", NULL ]
+[ "sys._tables", "sys", "describe_functions", "create view
sys.describe_functions as with func_args(func_id, func_arg) as (select func_id,
group_concat(sys.dq(name) || ' ' || sys.describe_type(type, type_digits,
type_scale),', ' order by number) from sys.args where inout = 1 group by
func_id), func_rets(func_id, func_ret, func_ret_type) as (select func_id,
group_concat(sys.dq(name) || ' ' || sys.describe_type(type, type_digits,
type_scale),', ' order by number), group_concat(sys.describe_type(type,
type_digits, type_scale),', ' order by number) from sys.args where inout = 0
group by func_id) select f.id o, s.name sch, f.name fun, case when f.language
in (1, 2) then f.func else 'CREATE ' || ft.function_type_keyword || ' ' ||
sys.fqn(s.name, f.name) || '(' || coalesce(fa.func_arg, '') || ')' || case when
f.type = 5 then ' RETURNS TABLE (' || coalesce(fr.func_ret, '') || ')' when
f.type in (1,3) then ' RETURNS ' || fr.func_ret_type else '' end || case when
fl.language_keyword is null t
hen '' else ' LANGUAGE ' || fl.language_keyword end || ' ' || f.func end def
from sys.functions f left outer join func_args fa on fa.func_id = f.id left
outer join func_rets fr on fr.func_id = f.id join sys.schemas s on f.schema_id
= s.id join sys.function_types ft on f.type = ft.function_type_id left outer
join sys.function_languages fl on f.language = fl.language_id where s.name <>
'tmp' and not f.system;", "VIEW", true, "COMMIT", "WRITABLE",
NULL ]
[ "sys._tables", "sys", "describe_indices", "create view
sys.describe_indices as with it (id, idx) as (values (0, 'INDEX'), (4,
'IMPRINTS INDEX'), (5, 'ORDERED INDEX')) select i.name ind, s.name sch, t.name
tbl, c.name col, it.idx tpe from sys.idxs as i left join sys.keys as k on
i.name = k.name, sys.objects as kc, sys._columns as c, sys.schemas s,
sys._tables as t, it where i.table_id = t.id and i.id = kc.id and kc.name =
c.name and t.id = c.table_id and t.schema_id = s.id and k.type is null and
i.type = it.id order by i.name, kc.nr;", "VIEW", true, "COMMIT",
"WRITABLE", NULL ]
[ "sys._tables", "sys", "describe_partition_tables", "create view
sys.describe_partition_tables as select m_sch, m_tbl, p_sch, p_tbl, case when
p_raw_type is null then 'READ ONLY' when (p_raw_type = 'VALUES' and pvalues is
null) or (p_raw_type = 'RANGE' and minimum is null and maximum is null and
with_nulls) then 'FOR NULLS' else p_raw_type end as tpe, pvalues, minimum,
maximum, with_nulls from (with tp(\"type\", table_id) as (select
ifthenelse((table_partitions.\"type\" & 2) = 2, 'VALUES', 'RANGE'),
table_partitions.table_id from sys.table_partitions), subq(m_tid, p_mid,
\"type\", m_sch, m_tbl, p_sch, p_tbl) as (select m_t.id, p_m.id, m_t.\"type\",
m_s.name, m_t.name, p_s.name, p_m.name from sys.schemas m_s, sys._tables m_t,
sys.dependencies d, sys.schemas p_s, sys._tables p_m where m_t.\"type\" in (3,
6) and m_t.schema_id = m_s.id and m_s.name <> 'tmp' and m_t.system = false and
m_t.id = d.depend_id and d.id = p_m.id and p_m.schema_id = p_s.id order by
m_t.id, p_m.id), vals(id,val
s) as (select vp.table_id, group_concat(vp.value, ',') from
sys.value_partitions vp group by vp.table_id) select subq.m_sch, subq.m_tbl,
subq.p_sch, subq.p_tbl, tp.\"type\" as p_raw_type, case when tp.\"type\" =
'VALUES' then (select vals.vals from vals where vals.id = subq.p_mid) else null
end as pvalues, case when tp.\"type\" = 'RANGE' then (select minimum from
sys.range_partitions rp where rp.table_id = subq.p_mid) else null end as
minimum, case when tp.\"type\" = 'RANGE' then (select maximum from
sys.range_partitions rp where rp.table_id = subq.p_mid) else null end as
maximum, case when tp.\"type\" = 'VALUES' then exists(select vp.value from
sys.value_partitions vp where vp.table_id = subq.p_mid and vp.value is null)
else (select rp.with_nulls from sys.range_partitions rp where rp.table_id =
subq.p_mid) end as with_nulls from subq left outer join tp on subq.m_tid =
tp.table_id) as tmp_pi;", "VIEW", true, "COMMIT", "WRITABLE",
NULL ]
[ "sys._tables", "sys", "describe_privileges", "create view
sys.describe_privileges as select case when o.tpe is null and
pc.privilege_code_name = 'SELECT' then 'COPY FROM' when o.tpe is null and
pc.privilege_code_name = 'UPDATE' then 'COPY INTO' else o.nme end o_nme,
coalesce(o.tpe, 'GLOBAL') o_tpe, pc.privilege_code_name p_nme, a.name a_nme,
g.name g_nme, p.grantable grantable from sys.privileges p left join (select
t.id, s.name || '.' || t.name , 'TABLE' from sys.schemas s, sys.tables t where
s.id = t.schema_id union all select c.id, s.name || '.' || t.name || '.' ||
c.name, 'COLUMN' from sys.schemas s, sys.tables t, sys.columns c where s.id =
t.schema_id and t.id = c.table_id union all select f.id, f.nme, f.tpe from
sys.fully_qualified_functions f) o(id, nme, tpe) on o.id = p.obj_id,
sys.privilege_codes pc, auths a, auths g where p.privileges =
pc.privilege_code_id and p.auth_id = a.id and p.grantor = g.id;", "VIEW", true,
"COMMIT", "WRITABLE", NULL ]
@@ -530,7 +530,7 @@ select 'null in fkeys.delete_action', de
[ "sys._tables", "sys", "environment", "create view sys.environment as
select * from sys.env();", "VIEW", true, "COMMIT", "WRITABLE",
NULL ]
[ "sys._tables", "sys", "fkey_actions", NULL, "TABLE", true,
"COMMIT", "READONLY", NULL ]
[ "sys._tables", "sys", "fkeys", "create view sys.fkeys as
select id, table_id, type, name, rkey, update_action_id, upd.action_name as
update_action, delete_action_id, del.action_name as delete_action from (select
id, table_id, type, name, rkey, cast(((\"action\" >> 8) & 255) as smallint) as
update_action_id, cast((\"action\" & 255) as smallint) as delete_action_id from
sys.keys where type = 2 union all select id, table_id, type, name, rkey,
cast(((\"action\" >> 8) & 255) as smallint) as update_action_id,
cast((\"action\" & 255) as smallint) as delete_action_id from tmp.keys where
type = 2) as fks join sys.fkey_actions upd on fks.update_action_id =
upd.action_id join sys.fkey_actions del on fks.delete_action_id =
del.action_id;", "VIEW", true, "COMMIT", "WRITABLE", NULL ]
-[ "sys._tables", "sys", "fully_qualified_functions", "create view
sys.fully_qualified_functions as with fqn(id, tpe, sig, num) as (select f.id,
ft.function_type_keyword, case when a.type is null then sys.fqn(s.name, f.name)
|| '()' else sys.fqn(s.name, f.name) || '(' ||
group_concat(sys.describe_type(a.type, a.type_digits, a.type_scale), ',') over
(partition by f.id order by a.number) || ')' end, a.number from sys.schemas s,
sys.function_types ft, sys.functions f left join sys.args a on f.id = a.func_id
where s.id= f.schema_id and f.type = ft.function_type_id) select fqn1.id id,
fqn1.tpe tpe, fqn1.sig nme from fqn fqn1 join (select id, max(num) from fqn
group by id) fqn2(id, num) on fqn1.id = fqn2.id and (fqn1.num = fqn2.num or
fqn1.num is null and fqn2.num is null);", "VIEW", true, "COMMIT",
"WRITABLE", NULL ]
+[ "sys._tables", "sys", "fully_qualified_functions", "create view
sys.fully_qualified_functions as select f.id id, ft.function_type_keyword tpe,
sys.fqn(s.name, f.name) || '(' || group_concat(sys.describe_type(a.type,
a.type_digits, a.type_scale), ',' order by a.number) || ')' nme from
sys.schemas s, sys.function_types ft, sys.functions f join sys.args a on f.id =
a.func_id where s.id= f.schema_id and f.type = ft.function_type_id group by
f.id, ft.function_type_keyword, f.name, s.name union select f.id id,
ft.function_type_keyword tpe, sys.fqn(s.name, f.name) || '()' nme from
sys.schemas s, sys.function_types ft, sys.functions f where s.id= f.schema_id
and f.type = ft.function_type_id and f.id not in (select func_id from sys.args)
group by f.id, ft.function_type_keyword, f.name, s.name;", "VIEW", true,
"COMMIT", "WRITABLE", NULL ]
[ "sys._tables", "sys", "function_languages", NULL, "TABLE",
true, "COMMIT", "READONLY", NULL ]
[ "sys._tables", "sys", "function_types", NULL, "TABLE",
true, "COMMIT", "READONLY", NULL ]
[ "sys._tables", "sys", "functions", NULL, "TABLE", true,
"COMMIT", "WRITABLE", NULL ]
@@ -4334,34 +4334,18 @@ select 'null in fkeys.delete_action', de
[ "column used by view", "tmp", "keys", "table_id", "sys",
"fkeys", "VIEW" ]
[ "column used by view", "tmp", "keys", "type", "sys", "fkeys",
"VIEW" ]
[ "column used by view", "sys", "args", "func_id", "sys",
"fully_qualified_functions", "VIEW" ]
-[ "column used by view", "sys", "args", "id", "sys",
"fully_qualified_functions", "VIEW" ]
-[ "column used by view", "sys", "args", "inout", "sys",
"fully_qualified_functions", "VIEW" ]
-[ "column used by view", "sys", "args", "name", "sys",
"fully_qualified_functions", "VIEW" ]
[ "column used by view", "sys", "args", "number", "sys",
"fully_qualified_functions", "VIEW" ]
[ "column used by view", "sys", "args", "type", "sys",
"fully_qualified_functions", "VIEW" ]
[ "column used by view", "sys", "args", "type_digits", "sys",
"fully_qualified_functions", "VIEW" ]
[ "column used by view", "sys", "args", "type_scale", "sys",
"fully_qualified_functions", "VIEW" ]
[ "column used by view", "sys", "function_types",
"function_type_id", "sys", "fully_qualified_functions", "VIEW" ]
[ "column used by view", "sys", "function_types",
"function_type_keyword", "sys", "fully_qualified_functions", "VIEW"
]
-[ "column used by view", "sys", "function_types",
"function_type_name", "sys", "fully_qualified_functions", "VIEW" ]
-[ "column used by view", "sys", "functions", "func", "sys",
"fully_qualified_functions", "VIEW" ]
_______________________________________________
checkin-list mailing list -- [email protected]
To unsubscribe send an email to [email protected]
