Changeset: 2f8682ba893a for MonetDB
URL: https://dev.monetdb.org/hg/MonetDB/rev/2f8682ba893a
Modified Files:
clients/mapiclient/dump.c
sql/backends/monet5/sql_upgrades.c
sql/scripts/52_describe.sql
sql/storage/store_sequence.c
sql/test/BugTracker-2018/Tests/alter-sequence-subquery.Bug-6657.test
sql/test/BugTracker-2018/Tests/negative-sequences.Bug-6665.test
sql/test/BugTracker-2019/Tests/sequence-first-next-value.Bug-6743.test
sql/test/emptydb/Tests/check.stable.out
sql/test/emptydb/Tests/check.stable.out.int128
sql/test/sys-schema/Tests/webExamplesMathematicalFunctionsOperators.test
Branch: Jan2022
Log Message:
Improve get_value_for and use peak_next_value_for in all dump logic.
diffs (truncated from 344 to 300 lines):
diff --git a/clients/mapiclient/dump.c b/clients/mapiclient/dump.c
--- a/clients/mapiclient/dump.c
+++ b/clients/mapiclient/dump.c
@@ -1506,15 +1506,15 @@ describe_sequence(Mapi mid, const char *
goto bailout;
snprintf(query, maxquerylen,
- "SELECT s.name, "
/* 0 */
- "seq.name, "
/* 1 */
- "get_value_for(s.name, seq.name), " /* 2 */
- "seq.\"minvalue\", "
/* 3 */
- "seq.\"maxvalue\", "
/* 4 */
- "seq.\"increment\", " /* 5 */
- "seq.\"cycle\", "
/* 6 */
- "seq.\"cacheinc\", "
/* 7 */
- "rem.\"remark\" "
/* 8 */
+ "SELECT s.name, "
/* 0 */
+ "seq.name, "
/* 1 */
+ "peak_next_value_for(s.name, seq.name), " /* 2 */
+ "seq.\"minvalue\", "
/* 3 */
+ "seq.\"maxvalue\", "
/* 4 */
+ "seq.\"increment\", "
/* 5 */
+ "seq.\"cycle\", "
/* 6 */
+ "seq.\"cacheinc\", "
/* 7 */
+ "rem.\"remark\" "
/* 8 */
"FROM sys.sequences seq LEFT OUTER JOIN sys.comments rem ON
seq.id = rem.id, "
"sys.schemas s "
"WHERE s.id = seq.schema_id "
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
@@ -2774,7 +2774,7 @@ sql_update_jul2021(Client c, mvc *sql, c
" s.name as sch,\n"
" seq.name as seq,\n"
" seq.\"start\" s,\n"
- " get_value_for(s.name,
seq.name) AS rs,\n"
+ "
sys.peak_next_value_for(s.name, seq.name) AS rs,\n"
" seq.\"minvalue\" mi,\n"
" seq.\"maxvalue\" ma,\n"
" seq.\"increment\" inc,\n"
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
@@ -491,7 +491,7 @@ CREATE VIEW sys.describe_sequences AS
s.name as sch,
seq.name as seq,
seq."start" s,
- get_value_for(s.name, seq.name) AS rs,
+ peak_next_value_for(s.name, seq.name) AS rs,
seq."minvalue" mi,
seq."maxvalue" ma,
seq."increment" inc,
diff --git a/sql/storage/store_sequence.c b/sql/storage/store_sequence.c
--- a/sql/storage/store_sequence.c
+++ b/sql/storage/store_sequence.c
@@ -161,7 +161,7 @@ seqbulk_next_value(sql_store store, sql_
lng start_index = 0;
if (!s->called) {
- s->cur = seq->start;
+ s->cur = isNew(seq) ? seq->start : s->cached;
*dest = s->cur;
start_index = 1;
s->called = 1;
@@ -187,7 +187,7 @@ seqbulk_next_value(sql_store store, sql_
}
}
else { // seq->increment < 0
- lng inc = -seq->increment; // new value = old value - inc;
+ lng inc = -seq->increment; // new value = old value - inc;
for(lng i = start_index; i < cnt; i++) {
if ((-GDK_lng_max + inc > s->cur) || ((s->cur -= inc)
< min)) {
// underflow
@@ -244,8 +244,8 @@ seq_get_value(sql_store store, sql_seque
s = n->data;
}
- *val = s->cur;
-
+ *val = s->called ? s->cur : lng_nil;
+
store_unlock(store);
return 1;
}
@@ -276,16 +276,9 @@ seq_peak_next_value(sql_store store, sql
}
if (!s->called) {
- if (isNew(seq)) {
- *val = seq->start;
- store_unlock(store);
- return 1;
- }
- else {
- *val = s->cached;
- store_unlock(store);
- return 1;
- }
+ *val = isNew(seq) ? seq->start : s->cached;
+ store_unlock(store);
+ return 1;
}
lng min = seq->minvalue;
diff --git
a/sql/test/BugTracker-2018/Tests/alter-sequence-subquery.Bug-6657.test
b/sql/test/BugTracker-2018/Tests/alter-sequence-subquery.Bug-6657.test
--- a/sql/test/BugTracker-2018/Tests/alter-sequence-subquery.Bug-6657.test
+++ b/sql/test/BugTracker-2018/Tests/alter-sequence-subquery.Bug-6657.test
@@ -4,6 +4,11 @@ create sequence "testme" as integer star
query I rowsort
select get_value_for('sys', 'testme')
----
+NULL
+
+query I rowsort
+select peak_next_value_for('sys', 'testme')
+----
2
statement ok
@@ -12,6 +17,11 @@ alter sequence "testme" restart with (se
query I rowsort
select get_value_for('sys', 'testme')
----
+NULL
+
+query I rowsort
+select peak_next_value_for('sys', 'testme')
+----
1
statement error
@@ -20,7 +30,7 @@ alter sequence "testme" restart with (se
query I rowsort
select get_value_for('sys', 'testme')
----
-1
+NULL
statement ok
drop sequence "testme"
diff --git a/sql/test/BugTracker-2018/Tests/negative-sequences.Bug-6665.test
b/sql/test/BugTracker-2018/Tests/negative-sequences.Bug-6665.test
--- a/sql/test/BugTracker-2018/Tests/negative-sequences.Bug-6665.test
+++ b/sql/test/BugTracker-2018/Tests/negative-sequences.Bug-6665.test
@@ -7,6 +7,11 @@ create sequence "other_seq" as integer s
query I rowsort
select get_value_for('sys', 'other_seq')
----
+NULL
+
+query I rowsort
+select peak_next_value_for('sys', 'other_seq')
+----
-300
query I rowsort
@@ -19,6 +24,11 @@ select next value for "other_seq"
----
-320
+query I rowsort
+select peak_next_value_for('sys', 'other_seq')
+----
+-340
+
statement ok
create table "testme" ("col1" int default next value for "other_seq", "col2"
int)
@@ -46,6 +56,11 @@ alter sequence "other_seq" restart with
query I rowsort
select get_value_for('sys', 'other_seq')
----
+NULL
+
+query I rowsort
+select peak_next_value_for('sys', 'other_seq')
+----
-400
query I rowsort
diff --git
a/sql/test/BugTracker-2019/Tests/sequence-first-next-value.Bug-6743.test
b/sql/test/BugTracker-2019/Tests/sequence-first-next-value.Bug-6743.test
--- a/sql/test/BugTracker-2019/Tests/sequence-first-next-value.Bug-6743.test
+++ b/sql/test/BugTracker-2019/Tests/sequence-first-next-value.Bug-6743.test
@@ -4,10 +4,20 @@ create sequence seq as int
query I rowsort
select get_value_for('sys','seq')
----
+NULL
+
+query I rowsort
+select get_value_for('sys','seq')
+----
+NULL
+
+query I rowsort
+select peak_next_value_for('sys','seq')
+----
1
query I rowsort
-select get_value_for('sys','seq')
+select peak_next_value_for('sys','seq')
----
1
@@ -51,6 +61,16 @@ select get_value_for('sys','seq')
----
4
+query I rowsort
+select peak_next_value_for('sys','seq')
+----
+5
+
+query I rowsort
+select peak_next_value_for('sys','seq')
+----
+5
+
statement ok
drop sequence seq
@@ -63,10 +83,20 @@ create sequence seq as int
query I rowsort
select get_value_for('sys','seq')
----
+NULL
+
+query I rowsort
+select get_value_for('sys','seq')
+----
+NULL
+
+query I rowsort
+select peak_next_value_for('sys','seq')
+----
1
query I rowsort
-select get_value_for('sys','seq')
+select peak_next_value_for('sys','seq')
----
1
@@ -110,6 +140,16 @@ select get_value_for('sys','seq')
----
4
+query I rowsort
+select peak_next_value_for('sys','seq')
+----
+5
+
+query I rowsort
+select peak_next_value_for('sys','seq')
+----
+5
+
statement ok
rollback
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
@@ -1044,7 +1044,7 @@ create view sys.describe_functions as wi
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;
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) select subq.m_sch, subq.m_tbl, subq.p_sch, subq.p_tbl,
tp."type" as p_raw_ty
pe, case when tp."type" = 'VALUES' then (select group_concat(vp.value, ',')
from sys.value_partitions vp where vp.table_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;
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;
-create view sys.describe_sequences as select s.name as sch, seq.name as seq,
seq."start" s, get_value_for(s.name, seq.name) as rs, seq."minvalue" mi,
seq."maxvalue" ma, seq."increment" inc, seq."cacheinc" cache, seq."cycle" cycle
from sys.sequences seq, sys.schemas s where s.id = seq.schema_id and s.name <>
'tmp' order by s.name, seq.name;
+create view sys.describe_sequences as select s.name as sch, seq.name as seq,
seq."start" s, peak_next_value_for(s.name, seq.name) as rs, seq."minvalue" mi,
seq."maxvalue" ma, seq."increment" inc, seq."cacheinc" cache, seq."cycle" cycle
from sys.sequences seq, sys.schemas s where s.id = seq.schema_id and s.name <>
'tmp' order by s.name, seq.name;
create view sys.describe_tables as select t.id o, s.name sch, t.name tab,
ts.table_type_name typ, (select ' (' || group_concat(sys.dq(c.name) || ' ' ||
sys.describe_type(c.type, c.type_digits, c.type_scale) || ifthenelse(c."null" =
'false', ' NOT NULL', '') , ', ') || ')' from sys._columns c where c.table_id =
t.id) col, case ts.table_type_name when 'REMOTE TABLE' then
sys.get_remote_table_expressions(s.name, t.name) when 'MERGE TABLE' then
sys.get_merge_table_partition_expressions(t.id) when 'VIEW' then
sys.schema_guard(s.name, t.name, t.query) else '' end opt from sys.schemas s,
sys.table_types ts, sys.tables t where ts.table_type_name in ('TABLE', 'VIEW',
'MERGE TABLE', 'REMOTE TABLE', 'REPLICA TABLE') and t.system = false and s.id =
t.schema_id and ts.table_type_id = t.type and s.name <> 'tmp';
create view sys.describe_triggers as select s.name sch, t.name tab, tr.name
tri, tr.statement def from sys.schemas s, sys.tables t, sys.triggers tr where
s.id = t.schema_id and t.id = tr.table_id and not t.system;
create view sys.describe_user_defined_types as select s.name sch, t.sqlname
sql_tpe, t.systemname ext_tpe from sys.types t join sys.schemas s on
t.schema_id = s.id where t.eclass = 18 and ((s.name = 'sys' and t.sqlname not
in ('geometrya', 'mbr', 'url', 'inet', 'json', 'uuid', 'xml')) or (s.name <>
'sys'));
@@ -1990,7 +1990,7 @@ select 'null in value_partitions.value',
[ "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" ]
[ "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) 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 group_concat(vp.value, ',') from
sys.value_partitions vp where vp.table_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" ]
[ "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" ]
-[ "sys._tables", "sys", "describe_sequences", "create view
sys.describe_sequences as select s.name as sch, seq.name as seq, seq.\"start\"
s, get_value_for(s.name, seq.name) as rs, seq.\"minvalue\" mi, seq.\"maxvalue\"
ma, seq.\"increment\" inc, seq.\"cacheinc\" cache, seq.\"cycle\" cycle from
sys.sequences seq, sys.schemas s where s.id = seq.schema_id and s.name <> 'tmp'
order by s.name, seq.name;", "VIEW", true, "COMMIT", "WRITABLE"
]
+[ "sys._tables", "sys", "describe_sequences", "create view
sys.describe_sequences as select s.name as sch, seq.name as seq, seq.\"start\"
s, peak_next_value_for(s.name, seq.name) as rs, seq.\"minvalue\" mi,
seq.\"maxvalue\" ma, seq.\"increment\" inc, seq.\"cacheinc\" cache,
seq.\"cycle\" cycle from sys.sequences seq, sys.schemas s where s.id =
seq.schema_id and s.name <> 'tmp' order by s.name, seq.name;", "VIEW",
true, "COMMIT", "WRITABLE" ]
[ "sys._tables", "sys", "describe_tables", "create view
sys.describe_tables as select t.id o, s.name sch, t.name tab,
ts.table_type_name typ, (select ' (' || group_concat(sys.dq(c.name) || ' ' ||
sys.describe_type(c.type, c.type_digits, c.type_scale) || ifthenelse(c.\"null\"
= 'false', ' NOT NULL', '') , ', ') || ')' from sys._columns c where c.table_id
= t.id) col, case ts.table_type_name when 'REMOTE TABLE' then
sys.get_remote_table_expressions(s.name, t.name) when 'MERGE TABLE' then
sys.get_merge_table_partition_expressions(t.id) when 'VIEW' then
sys.schema_guard(s.name, t.name, t.query) else '' end opt from sys.schemas s,
sys.table_types ts, sys.tables t where ts.table_type_name in ('TABLE', 'VIEW',
'MERGE TABLE', 'REMOTE TABLE', 'REPLICA TABLE') and t.system = false and s.id =
t.schema_id and ts.table_type_id = t.type and s.name <> 'tmp';", "VIEW", true,
"COMMIT", "WRITABLE" ]
[ "sys._tables", "sys", "describe_triggers", "create view
sys.describe_triggers as select s.name sch, t.name tab, tr.name tri,
tr.statement def from sys.schemas s, sys.tables t, sys.triggers tr where s.id =
t.schema_id and t.id = tr.table_id and not t.system;", "VIEW", true,
"COMMIT", "WRITABLE" ]
[ "sys._tables", "sys", "describe_user_defined_types", "create view
sys.describe_user_defined_types as select s.name sch, t.sqlname sql_tpe,
t.systemname ext_tpe from sys.types t join sys.schemas s on t.schema_id = s.id
where t.eclass = 18 and ((s.name = 'sys' and t.sqlname not in ('geometrya',
'mbr', 'url', 'inet', 'json', 'uuid', 'xml')) or (s.name <> 'sys'));", "VIEW",
true, "COMMIT", "WRITABLE" ]
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
@@ -1044,7 +1044,7 @@ create view sys.describe_functions as wi
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;
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) select subq.m_sch, subq.m_tbl, subq.p_sch, subq.p_tbl,
tp."type" as p_raw_ty
pe, case when tp."type" = 'VALUES' then (select group_concat(vp.value, ',')
from sys.value_partitions vp where vp.table_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;
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;
-create view sys.describe_sequences as select s.name as sch, seq.name as seq,
seq."start" s, get_value_for(s.name, seq.name) as rs, seq."minvalue" mi,
seq."maxvalue" ma, seq."increment" inc, seq."cacheinc" cache, seq."cycle" cycle
from sys.sequences seq, sys.schemas s where s.id = seq.schema_id and s.name <>
'tmp' order by s.name, seq.name;
+create view sys.describe_sequences as select s.name as sch, seq.name as seq,
seq."start" s, peak_next_value_for(s.name, seq.name) as rs, seq."minvalue" mi,
seq."maxvalue" ma, seq."increment" inc, seq."cacheinc" cache, seq."cycle" cycle
from sys.sequences seq, sys.schemas s where s.id = seq.schema_id and s.name <>
'tmp' order by s.name, seq.name;
create view sys.describe_tables as select t.id o, s.name sch, t.name tab,
ts.table_type_name typ, (select ' (' || group_concat(sys.dq(c.name) || ' ' ||
sys.describe_type(c.type, c.type_digits, c.type_scale) || ifthenelse(c."null" =
'false', ' NOT NULL', '') , ', ') || ')' from sys._columns c where c.table_id =
t.id) col, case ts.table_type_name when 'REMOTE TABLE' then
sys.get_remote_table_expressions(s.name, t.name) when 'MERGE TABLE' then
sys.get_merge_table_partition_expressions(t.id) when 'VIEW' then
sys.schema_guard(s.name, t.name, t.query) else '' end opt from sys.schemas s,
sys.table_types ts, sys.tables t where ts.table_type_name in ('TABLE', 'VIEW',
'MERGE TABLE', 'REMOTE TABLE', 'REPLICA TABLE') and t.system = false and s.id =
t.schema_id and ts.table_type_id = t.type and s.name <> 'tmp';
create view sys.describe_triggers as select s.name sch, t.name tab, tr.name
tri, tr.statement def from sys.schemas s, sys.tables t, sys.triggers tr where
s.id = t.schema_id and t.id = tr.table_id and not t.system;
create view sys.describe_user_defined_types as select s.name sch, t.sqlname
sql_tpe, t.systemname ext_tpe from sys.types t join sys.schemas s on
t.schema_id = s.id where t.eclass = 18 and ((s.name = 'sys' and t.sqlname not
in ('geometrya', 'mbr', 'url', 'inet', 'json', 'uuid', 'xml')) or (s.name <>
'sys'));
@@ -2010,7 +2010,7 @@ select 'null in value_partitions.value',
[ "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" ]
[ "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) 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 group_concat(vp.value, ',') from
sys.value_partitions vp where vp.table_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" ]
[ "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" ]
-[ "sys._tables", "sys", "describe_sequences", "create view
sys.describe_sequences as select s.name as sch, seq.name as seq, seq.\"start\"
s, get_value_for(s.name, seq.name) as rs, seq.\"minvalue\" mi, seq.\"maxvalue\"
ma, seq.\"increment\" inc, seq.\"cacheinc\" cache, seq.\"cycle\" cycle from
sys.sequences seq, sys.schemas s where s.id = seq.schema_id and s.name <> 'tmp'
order by s.name, seq.name;", "VIEW", true, "COMMIT", "WRITABLE"
]
+[ "sys._tables", "sys", "describe_sequences", "create view
sys.describe_sequences as select s.name as sch, seq.name as seq, seq.\"start\"
s, peak_next_value_for(s.name, seq.name) as rs, seq.\"minvalue\" mi,
seq.\"maxvalue\" ma, seq.\"increment\" inc, seq.\"cacheinc\" cache,
seq.\"cycle\" cycle from sys.sequences seq, sys.schemas s where s.id =
seq.schema_id and s.name <> 'tmp' order by s.name, seq.name;", "VIEW",
true, "COMMIT", "WRITABLE" ]
[ "sys._tables", "sys", "describe_tables", "create view
sys.describe_tables as select t.id o, s.name sch, t.name tab,
ts.table_type_name typ, (select ' (' || group_concat(sys.dq(c.name) || ' ' ||
sys.describe_type(c.type, c.type_digits, c.type_scale) || ifthenelse(c.\"null\"
= 'false', ' NOT NULL', '') , ', ') || ')' from sys._columns c where c.table_id
= t.id) col, case ts.table_type_name when 'REMOTE TABLE' then
sys.get_remote_table_expressions(s.name, t.name) when 'MERGE TABLE' then
sys.get_merge_table_partition_expressions(t.id) when 'VIEW' then
sys.schema_guard(s.name, t.name, t.query) else '' end opt from sys.schemas s,
sys.table_types ts, sys.tables t where ts.table_type_name in ('TABLE', 'VIEW',
'MERGE TABLE', 'REMOTE TABLE', 'REPLICA TABLE') and t.system = false and s.id =
t.schema_id and ts.table_type_id = t.type and s.name <> 'tmp';", "VIEW", true,
"COMMIT", "WRITABLE" ]
_______________________________________________
checkin-list mailing list
[email protected]
https://www.monetdb.org/mailman/listinfo/checkin-list
