Changeset: d13f9dd4f2e3 for MonetDB
URL: https://dev.monetdb.org/hg/MonetDB/rev/d13f9dd4f2e3
Modified Files:
sql/backends/monet5/sql_upgrades.c
sql/scripts/91_information_schema.sql
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/information-schema/Tests/columns.test
Branch: Dec2023
Log Message:
Fix a problem with CHARACTER_OCTET_LENGTH in views INFORMATION_SCHEMA.COLUMNS,
INFORMATION_SCHEMA.ROUTINES and INFORMATION_SCHEMA.PARAMETERS.
It could overflow and generate a runtime error for the cast to int when the
computed CHARACTER_OCTET_LENGTH became higher than 2147483647.
diffs (175 lines):
diff --git a/sql/backends/monet5/sql_upgrades.c
b/sql/backends/monet5/sql_upgrades.c
--- a/sql/backends/monet5/sql_upgrades.c
+++ b/sql/backends/monet5/sql_upgrades.c
@@ -6231,7 +6231,7 @@ sql_update_dec2023(Client c, mvc *sql, s
" cast(sys.ifthenelse(c.\"null\", 'YES', 'NO') AS varchar(3))
AS IS_NULLABLE,\n"
" cast(sys.\"sql_datatype\"(c.\"type\", c.\"type_digits\",
c.\"type_scale\", true, true) AS varchar(1024)) AS DATA_TYPE,\n"
" cast(sys.ifthenelse(c.\"type\" IN
('varchar','clob','char','json','url','xml') AND c.\"type_digits\" > 0,
c.\"type_digits\", NULL) AS int) AS CHARACTER_MAXIMUM_LENGTH,\n"
- " cast(sys.ifthenelse(c.\"type\" IN
('varchar','clob','char','json','url','xml') AND c.\"type_digits\" > 0,
c.\"type_digits\" * 4, NULL) AS int) AS CHARACTER_OCTET_LENGTH,\n"
+ " cast(sys.ifthenelse(c.\"type\" IN
('varchar','clob','char','json','url','xml') AND c.\"type_digits\" > 0,
sys.sql_min(4 * c.\"type_digits\", 2147483647), NULL) AS int) AS
CHARACTER_OCTET_LENGTH,\n"
" cast(sys.ifthenelse(c.\"type\" IN
('int','smallint','tinyint','bigint','hugeint','float','real','double','decimal','numeric','oid'),
c.\"type_digits\", NULL) AS int) AS NUMERIC_PRECISION,\n"
" cast(sys.ifthenelse(c.\"type\" IN
('int','smallint','tinyint','bigint','hugeint','float','real','double','oid'),
2, sys.ifthenelse(c.\"type\" IN ('decimal','numeric'), 10, NULL)) AS int) AS
NUMERIC_PRECISION_RADIX,\n"
" cast(sys.ifthenelse(c.\"type\" IN
('int','smallint','tinyint','bigint','hugeint','float','real','double','decimal','numeric','oid'),
c.\"type_scale\", NULL) AS int) AS NUMERIC_SCALE,\n"
@@ -6360,7 +6360,7 @@ sql_update_dec2023(Client c, mvc *sql, s
" cast(NULL AS varchar(1)) AS UDT_NAME,\n"
" cast(CASE f.\"type\" WHEN 1 THEN
sys.\"sql_datatype\"(a.\"type\", a.\"type_digits\", a.\"type_scale\", true,
true) WHEN 2 THEN NULL WHEN 5 THEN 'TABLE' WHEN 7 THEN 'TABLE' ELSE NULL END AS
varchar(1024)) AS DATA_TYPE,\n"
" cast(sys.ifthenelse(a.\"type\" IN
('varchar','clob','char','json','url','xml') AND a.\"type_digits\" > 0,
a.\"type_digits\", NULL) AS int) AS CHARACTER_MAXIMUM_LENGTH,\n"
- " cast(sys.ifthenelse(a.\"type\" IN
('varchar','clob','char','json','url','xml') AND a.\"type_digits\" > 0,
a.\"type_digits\" * 4, NULL) AS int) AS CHARACTER_OCTET_LENGTH,\n"
+ " cast(sys.ifthenelse(a.\"type\" IN
('varchar','clob','char','json','url','xml') AND a.\"type_digits\" > 0,
sys.sql_min(4 * a.\"type_digits\", 2147483647), NULL) AS int) AS
CHARACTER_OCTET_LENGTH,\n"
" cast(NULL AS varchar(1)) AS CHARACTER_SET_CATALOG,\n"
" cast(NULL AS varchar(1)) AS CHARACTER_SET_SCHEMA,\n"
" 'UTF-8' AS CHARACTER_SET_NAME,\n"
@@ -6466,7 +6466,7 @@ sql_update_dec2023(Client c, mvc *sql, s
" cast(NULL AS varchar(1)) AS TO_SQL_SPECIFIC_NAME,\n"
" cast(sys.\"sql_datatype\"(a.\"type\", a.\"type_digits\",
a.\"type_scale\", true, true) AS varchar(1024)) AS DATA_TYPE,\n"
" cast(sys.ifthenelse(a.\"type\" IN
('varchar','clob','char','json','url','xml') AND a.\"type_digits\" > 0,
a.\"type_digits\", NULL) AS int) AS CHARACTER_MAXIMUM_LENGTH,\n"
- " cast(sys.ifthenelse(a.\"type\" IN
('varchar','clob','char','json','url','xml') AND a.\"type_digits\" > 0,
a.\"type_digits\" * 4, NULL) AS int) AS CHARACTER_OCTET_LENGTH,\n"
+ " cast(sys.ifthenelse(a.\"type\" IN
('varchar','clob','char','json','url','xml') AND a.\"type_digits\" > 0,
sys.sql_min(4 * a.\"type_digits\", 2147483647), NULL) AS int) AS
CHARACTER_OCTET_LENGTH,\n"
" cast(NULL AS varchar(1)) AS CHARACTER_SET_CATALOG,\n"
" cast(NULL AS varchar(1)) AS CHARACTER_SET_SCHEMA,\n"
" cast(sys.ifthenelse(a.\"type\" IN
('varchar','clob','char','json','url','xml'), 'UTF-8', NULL) AS varchar(16)) AS
CHARACTER_SET_NAME,\n"
diff --git a/sql/scripts/91_information_schema.sql
b/sql/scripts/91_information_schema.sql
--- a/sql/scripts/91_information_schema.sql
+++ b/sql/scripts/91_information_schema.sql
@@ -131,7 +131,7 @@ CREATE VIEW INFORMATION_SCHEMA.COLUMNS A
cast(sys.ifthenelse(c."null", 'YES', 'NO') AS varchar(3)) AS IS_NULLABLE,
cast(sys."sql_datatype"(c."type", c."type_digits", c."type_scale", true,
true) AS varchar(1024)) AS DATA_TYPE,
cast(sys.ifthenelse(c."type" IN ('varchar','clob','char','json','url','xml')
AND c."type_digits" > 0, c."type_digits", NULL) AS int) AS
CHARACTER_MAXIMUM_LENGTH,
- cast(sys.ifthenelse(c."type" IN ('varchar','clob','char','json','url','xml')
AND c."type_digits" > 0, c."type_digits" * 4, NULL) AS int) AS
CHARACTER_OCTET_LENGTH,
+ cast(sys.ifthenelse(c."type" IN ('varchar','clob','char','json','url','xml')
AND c."type_digits" > 0, sys.sql_min(4 * c."type_digits", 2147483647), NULL) AS
int) AS CHARACTER_OCTET_LENGTH,
cast(sys.ifthenelse(c."type" IN
('int','smallint','tinyint','bigint','hugeint','float','real','double','decimal','numeric','oid'),
c."type_digits", NULL) AS int) AS NUMERIC_PRECISION,
cast(sys.ifthenelse(c."type" IN
('int','smallint','tinyint','bigint','hugeint','float','real','double','oid'),
2, sys.ifthenelse(c."type" IN ('decimal','numeric'), 10, NULL)) AS int) AS
NUMERIC_PRECISION_RADIX,
cast(sys.ifthenelse(c."type" IN
('int','smallint','tinyint','bigint','hugeint','float','real','double','decimal','numeric','oid'),
c."type_scale", NULL) AS int) AS NUMERIC_SCALE,
@@ -277,7 +277,7 @@ CREATE VIEW INFORMATION_SCHEMA.ROUTINES
cast(NULL AS varchar(1)) AS UDT_NAME,
cast(CASE f."type" WHEN 1 THEN sys."sql_datatype"(a."type", a."type_digits",
a."type_scale", true, true) WHEN 2 THEN NULL WHEN 5 THEN 'TABLE' WHEN 7 THEN
'TABLE' ELSE NULL END AS varchar(1024)) AS DATA_TYPE,
cast(sys.ifthenelse(a."type" IN ('varchar','clob','char','json','url','xml')
AND a."type_digits" > 0, a."type_digits", NULL) AS int) AS
CHARACTER_MAXIMUM_LENGTH,
- cast(sys.ifthenelse(a."type" IN ('varchar','clob','char','json','url','xml')
AND a."type_digits" > 0, a."type_digits" * 4, NULL) AS int) AS
CHARACTER_OCTET_LENGTH,
+ cast(sys.ifthenelse(a."type" IN ('varchar','clob','char','json','url','xml')
AND a."type_digits" > 0, sys.sql_min(4 * a."type_digits", 2147483647), NULL) AS
int) AS CHARACTER_OCTET_LENGTH,
cast(NULL AS varchar(1)) AS CHARACTER_SET_CATALOG,
cast(NULL AS varchar(1)) AS CHARACTER_SET_SCHEMA,
'UTF-8' AS CHARACTER_SET_NAME,
@@ -389,7 +389,7 @@ CREATE VIEW INFORMATION_SCHEMA.PARAMETER
cast(NULL AS varchar(1)) AS TO_SQL_SPECIFIC_NAME,
cast(sys."sql_datatype"(a."type", a."type_digits", a."type_scale", true,
true) AS varchar(1024)) AS DATA_TYPE,
cast(sys.ifthenelse(a."type" IN ('varchar','clob','char','json','url','xml')
AND a."type_digits" > 0, a."type_digits", NULL) AS int) AS
CHARACTER_MAXIMUM_LENGTH,
- cast(sys.ifthenelse(a."type" IN ('varchar','clob','char','json','url','xml')
AND a."type_digits" > 0, a."type_digits" * 4, NULL) AS int) AS
CHARACTER_OCTET_LENGTH,
+ cast(sys.ifthenelse(a."type" IN ('varchar','clob','char','json','url','xml')
AND a."type_digits" > 0, sys.sql_min(4 * a."type_digits", 2147483647), NULL) AS
int) AS CHARACTER_OCTET_LENGTH,
cast(NULL AS varchar(1)) AS CHARACTER_SET_CATALOG,
cast(NULL AS varchar(1)) AS CHARACTER_SET_SCHEMA,
cast(sys.ifthenelse(a."type" IN
('varchar','clob','char','json','url','xml'), 'UTF-8', NULL) AS varchar(16)) AS
CHARACTER_SET_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
@@ -444,13 +444,13 @@ select 'null in fkeys.delete_action', de
% .%25, .s, .t, ., ., .t, ., ., . #
table_name
% %25, name, name, query, type, system, commit_action, access, comment
# name
% char, varchar, varchar, clob, varchar,
boolean, char, char, varchar # type
-% 11, 18, 34, 6793, 5, 5, 8, 10, 0 # length
+% 11, 18, 34, 6818, 5, 5, 8, 10, 0 # length
[ "sys._tables", "information_schema", "character_sets", "create
view information_schema.character_sets as select cast(null as varchar(1)) as
character_set_catalog, cast(null as varchar(1)) as character_set_schema,
cast('UTF-8' as varchar(16)) as character_set_name, cast('ISO/IEC 10646:2021'
as varchar(20)) as character_repertoire, cast('UTF-8' as varchar(16)) as
form_of_use, cast(null as varchar(1)) as default_collate_catalog, cast(null as
varchar(1)) as default_collate_schema, cast(null as varchar(1)) as
default_collate_name;", "VIEW", true, "COMMIT", "WRITABLE", NULL
]
[ "sys._tables", "information_schema", "check_constraints", "create
view information_schema.check_constraints as select cast(null as varchar(1)) as
constraint_catalog, cast(null as varchar(1024)) as constraint_schema, cast(null
as varchar(1024)) as constraint_name, cast(null as varchar(1024)) as
check_clause where 1=0;", "VIEW", true, "COMMIT", "WRITABLE", NULL
]
-[ "sys._tables", "information_schema", "columns", "create view
information_schema.columns as select cast(null as varchar(1)) as table_catalog,
s.\"name\" as table_schema, t.\"name\" as table_name, c.\"name\" as
column_name, cast(1 + c.\"number\" as int) as ordinal_position, c.\"default\"
as column_default, cast(sys.ifthenelse(c.\"null\", 'YES', 'NO') as varchar(3))
as is_nullable, cast(sys.\"sql_datatype\"(c.\"type\", c.\"type_digits\",
c.\"type_scale\", true, true) as varchar(1024)) as data_type,
cast(sys.ifthenelse(c.\"type\" in ('varchar','clob','char','json','url','xml')
and c.\"type_digits\" > 0, c.\"type_digits\", null) as int) as
character_maximum_length, cast(sys.ifthenelse(c.\"type\" in
('varchar','clob','char','json','url','xml') and c.\"type_digits\" > 0,
c.\"type_digits\" * 4, null) as int) as character_octet_length,
cast(sys.ifthenelse(c.\"type\" in
('int','smallint','tinyint','bigint','hugeint','float','real','double','decimal','numeric','oid'),
c.\"type_digits\", null
) as int) as numeric_precision, cast(sys.ifthenelse(c.\"type\" in
('int','smallint','tinyint','bigint','hugeint','float','real','double','oid'),
2, sys.ifthenelse(c.\"type\" in ('decimal','numeric'), 10, null)) as int) as
numeric_precision_radix, cast(sys.ifthenelse(c.\"type\" in
('int','smallint','tinyint','bigint','hugeint','float','real','double','decimal','numeric','oid'),
c.\"type_scale\", null) as int) as numeric_scale,
cast(sys.ifthenelse(c.\"type\" in
('date','timestamp','timestamptz','time','timetz'),
sys.ifthenelse(c.\"type_scale\" > 0, c.\"type_scale\" -1, 0), null) as int) as
datetime_precision, cast(sys.ifthenelse(c.\"type\" in
('day_interval','month_interval','sec_interval'),
sys.\"sql_datatype\"(c.\"type\", c.\"type_digits\", c.\"type_scale\", true,
true), null) as varchar(40)) as interval_type, cast(case c.\"type\" when
'day_interval' then 0 when 'month_interval' then 0 when 'sec_interval' then
(sys.ifthenelse(c.\"type_digits\" in (7, 10, 12, 13), sys.ifthenelse(c.\"
type_scale\" > 0, c.\"type_scale\", 3), 0)) else null end as int) as
interval_precision, cast(null as varchar(1)) as character_set_catalog,
cast(null as varchar(1)) as character_set_schema,
cast(sys.ifthenelse(c.\"type\" in ('varchar','clob','char','json','url','xml'),
'UTF-8', null) as varchar(16)) as character_set_name, cast(null as varchar(1))
as collation_catalog, cast(null as varchar(1)) as collation_schema, cast(null
as varchar(1)) as collation_name, cast(null as varchar(1)) as domain_catalog,
cast(null as varchar(1)) as domain_schema, cast(null as varchar(1)) as
domain_name, cast(null as varchar(1)) as udt_catalog, cast(null as varchar(1))
as udt_schema, cast(null as varchar(1)) as udt_name, cast(null as varchar(1))
as scope_catalog, cast(null as varchar(1)) as scope_schema, cast(null as
varchar(1)) as scope_name, cast(null as int) as maximum_cardinality, cast(null
as varchar(1)) as dtd_identifier, cast('NO' as varchar(3)) as
is_self_referencing, cast(sys.ifthenelse(seq.\"nam
e\" is null or c.\"null\", 'NO', 'YES') as varchar(3)) as is_identity,
seq.\"name\" as identity_generation, seq.\"start\" as identity_start,
seq.\"increment\" as identity_increment, seq.\"maxvalue\" as identity_maximum,
seq.\"minvalue\" as identity_minimum, cast(sys.ifthenelse(seq.\"name\" is null,
null, sys.ifthenelse(seq.\"cycle\", 'YES', 'NO')) as varchar(3)) as
identity_cycle, cast(sys.ifthenelse(seq.\"name\" is null, 'NO', 'YES') as
varchar(3)) as is_generated, cast(sys.ifthenelse(seq.\"name\" is null, null,
c.\"default\") as varchar(1024)) as generation_expression, cast('NO' as
varchar(3)) as is_system_time_period_start, cast('NO' as varchar(3)) as
is_system_time_period_end, cast('NO' as varchar(3)) as
system_time_period_timestamp_generation, cast(sys.ifthenelse(t.\"type\" in
(0,3,7,20,30), 'YES', 'NO') as varchar(3)) as is_updatable, cast(null as
varchar(1)) as declared_data_type, cast(null as int) as
declared_numeric_precision, cast(null as int) as declared_numeric_scale, t.
\"schema_id\" as schema_id, c.\"table_id\" as table_id, c.\"id\" as column_id,
seq.\"id\" as sequence_id, t.\"system\" as is_system, cm.\"remark\" as comments
from sys.\"columns\" c inner join sys.\"tables\" t on c.\"table_id\" = t.\"id\"
inner join sys.\"schemas\" s on t.\"schema_id\" = s.\"id\" left outer join
sys.\"comments\" cm on c.\"id\" = cm.\"id\" left outer join sys.\"sequences\"
seq on ((seq.\"name\"||'\"') = substring(c.\"default\", 3 +
sys.\"locate\"('\".\"seq_',c.\"default\",14))) order by s.\"name\", t.\"name\",
c.\"number\";", "VIEW", true, "COMMIT", "WRITABLE", NULL ]
-[ "sys._tables", "information_schema", "parameters", "create view
information_schema.parameters as select cast(null as varchar(1)) as
specific_catalog, s.\"name\" as specific_schema,
cast(f.\"name\"||'('||f.\"id\"||')' as varchar(270)) as specific_name,
cast(sys.ifthenelse((a.\"inout\" = 0 or f.\"type\" = 2), 1 + a.\"number\",
sys.ifthenelse(f.\"type\" = 1, a.\"number\", (1 + a.\"number\" -
f.count_out_cols))) as int) as ordinal_position,
cast(sys.ifthenelse(a.\"inout\" = 0, 'OUT', sys.ifthenelse(a.\"inout\" = 1,
'IN', 'INOUT')) as varchar(5)) as parameter_mode,
cast(sys.ifthenelse(a.\"inout\" = 0, 'YES', 'NO') as varchar(3)) as is_result,
cast(null as varchar(1)) as as_locator, a.\"name\" as parameter_name, cast(null
as varchar(1)) as from_sql_specific_catalog, cast(null as varchar(1)) as
from_sql_specific_schema, cast(null as varchar(1)) as from_sql_specific_name,
cast(null as varchar(1)) as to_sql_specific_catalog, cast(null as varchar(1))
as to_sql_specific_schema, cast(null as
varchar(1)) as to_sql_specific_name, cast(sys.\"sql_datatype\"(a.\"type\",
a.\"type_digits\", a.\"type_scale\", true, true) as varchar(1024)) as
data_type, cast(sys.ifthenelse(a.\"type\" in
('varchar','clob','char','json','url','xml') and a.\"type_digits\" > 0,
a.\"type_digits\", null) as int) as character_maximum_length,
cast(sys.ifthenelse(a.\"type\" in ('varchar','clob','char','json','url','xml')
and a.\"type_digits\" > 0, a.\"type_digits\" * 4, null) as int) as
character_octet_length, cast(null as varchar(1)) as character_set_catalog,
cast(null as varchar(1)) as character_set_schema,
cast(sys.ifthenelse(a.\"type\" in ('varchar','clob','char','json','url','xml'),
'UTF-8', null) as varchar(16)) as character_set_name, cast(null as varchar(1))
as collation_catalog, cast(null as varchar(1)) as collation_schema, cast(null
as varchar(1)) as collation_name, cast(sys.ifthenelse(a.\"type\" in
('int','smallint','tinyint','bigint','hugeint','float','real','double','decimal','numeric','oid')
, a.\"type_digits\", null) as int) as numeric_precision,
cast(sys.ifthenelse(a.\"type\" in
('int','smallint','tinyint','bigint','hugeint','float','real','double','oid'),
2, sys.ifthenelse(a.\"type\" in ('decimal','numeric'), 10, null)) as int) as
numeric_precision_radix, cast(sys.ifthenelse(a.\"type\" in
('int','smallint','tinyint','bigint','hugeint','float','real','double','decimal','numeric','oid'),
a.\"type_scale\", null) as int) as numeric_scale,
cast(sys.ifthenelse(a.\"type\" in
('date','timestamp','timestamptz','time','timetz'),
sys.ifthenelse(a.\"type_scale\" > 0, a.\"type_scale\" -1, 0), null) as int) as
datetime_precision, cast(sys.ifthenelse(a.\"type\" in
('day_interval','month_interval','sec_interval'),
sys.\"sql_datatype\"(a.\"type\", a.\"type_digits\", a.\"type_scale\", true,
true), null) as varchar(40)) as interval_type, cast(case a.\"type\" when
'day_interval' then 0 when 'month_interval' then 0 when 'sec_interval' then
(sys.ifthenelse(a.\"type_digits\" in (7, 10, 12,
13), sys.ifthenelse(a.\"type_scale\" > 0, a.\"type_scale\", 3), 0)) else null
end as int) as interval_precision, cast(null as varchar(1)) as udt_catalog,
cast(null as varchar(1)) as udt_schema, cast(null as varchar(1)) as udt_name,
cast(null as varchar(1)) as scope_catalog, cast(null as varchar(1)) as
scope_schema, cast(null as varchar(1)) as scope_name, cast(null as int) as
maximum_cardinality, cast(null as varchar(1)) as dtd_identifier, cast(null as
varchar(1)) as declared_data_type, cast(null as int) as
declared_numeric_precision, cast(null as int) as declared_numeric_scale,
cast(null as varchar(1)) as parameter_default, f.\"schema_id\" as schema_id,
f.\"id\" as function_id, a.\"id\" as arg_id, f.\"name\" as function_name,
f.\"type\" as function_type, f.\"system\" as is_system from sys.\"args\" a
inner join (select fun.*, (select count(*) from sys.args a0 where a0.inout = 0
and a0.func_id = fun.id) as count_out_cols from sys.\"functions\" fun where
fun.\"type\" in (1, 2, 5, 7))
f on f.\"id\" = a.\"func_id\" inner join sys.\"schemas\" s on s.\"id\" =
f.\"schema_id\" order by s.\"name\", f.\"name\", f.\"id\", a.\"inout\" desc,
a.\"number\";", "VIEW", true, "COMMIT", "WRITABLE", NULL ]
+[ "sys._tables", "information_schema", "columns", "create view
information_schema.columns as select cast(null as varchar(1)) as table_catalog,
s.\"name\" as table_schema, t.\"name\" as table_name, c.\"name\" as
column_name, cast(1 + c.\"number\" as int) as ordinal_position, c.\"default\"
as column_default, cast(sys.ifthenelse(c.\"null\", 'YES', 'NO') as varchar(3))
as is_nullable, cast(sys.\"sql_datatype\"(c.\"type\", c.\"type_digits\",
c.\"type_scale\", true, true) as varchar(1024)) as data_type,
cast(sys.ifthenelse(c.\"type\" in ('varchar','clob','char','json','url','xml')
and c.\"type_digits\" > 0, c.\"type_digits\", null) as int) as
character_maximum_length, cast(sys.ifthenelse(c.\"type\" in
('varchar','clob','char','json','url','xml') and c.\"type_digits\" > 0,
sys.sql_min(4 * c.\"type_digits\", 2147483647), null) as int) as
character_octet_length, cast(sys.ifthenelse(c.\"type\" in
('int','smallint','tinyint','bigint','hugeint','float','real','double','decimal','numeric','oid')
, c.\"type_digits\", null) as int) as numeric_precision,
cast(sys.ifthenelse(c.\"type\" in
('int','smallint','tinyint','bigint','hugeint','float','real','double','oid'),
2, sys.ifthenelse(c.\"type\" in ('decimal','numeric'), 10, null)) as int) as
numeric_precision_radix, cast(sys.ifthenelse(c.\"type\" in
('int','smallint','tinyint','bigint','hugeint','float','real','double','decimal','numeric','oid'),
c.\"type_scale\", null) as int) as numeric_scale,
cast(sys.ifthenelse(c.\"type\" in
('date','timestamp','timestamptz','time','timetz'),
sys.ifthenelse(c.\"type_scale\" > 0, c.\"type_scale\" -1, 0), null) as int) as
datetime_precision, cast(sys.ifthenelse(c.\"type\" in
('day_interval','month_interval','sec_interval'),
sys.\"sql_datatype\"(c.\"type\", c.\"type_digits\", c.\"type_scale\", true,
true), null) as varchar(40)) as interval_type, cast(case c.\"type\" when
'day_interval' then 0 when 'month_interval' then 0 when 'sec_interval' then
(sys.ifthenelse(c.\"type_digits\" in (7, 10, 12,
13), sys.ifthenelse(c.\"type_scale\" > 0, c.\"type_scale\", 3), 0)) else null
end as int) as interval_precision, cast(null as varchar(1)) as
character_set_catalog, cast(null as varchar(1)) as character_set_schema,
cast(sys.ifthenelse(c.\"type\" in ('varchar','clob','char','json','url','xml'),
'UTF-8', null) as varchar(16)) as character_set_name, cast(null as varchar(1))
as collation_catalog, cast(null as varchar(1)) as collation_schema, cast(null
as varchar(1)) as collation_name, cast(null as varchar(1)) as domain_catalog,
cast(null as varchar(1)) as domain_schema, cast(null as varchar(1)) as
domain_name, cast(null as varchar(1)) as udt_catalog, cast(null as varchar(1))
as udt_schema, cast(null as varchar(1)) as udt_name, cast(null as varchar(1))
as scope_catalog, cast(null as varchar(1)) as scope_schema, cast(null as
varchar(1)) as scope_name, cast(null as int) as maximum_cardinality, cast(null
as varchar(1)) as dtd_identifier, cast('NO' as varchar(3)) as
is_self_referencing, cast
(sys.ifthenelse(seq.\"name\" is null or c.\"null\", 'NO', 'YES') as
varchar(3)) as is_identity, seq.\"name\" as identity_generation, seq.\"start\"
as identity_start, seq.\"increment\" as identity_increment, seq.\"maxvalue\" as
identity_maximum, seq.\"minvalue\" as identity_minimum,
cast(sys.ifthenelse(seq.\"name\" is null, null, sys.ifthenelse(seq.\"cycle\",
'YES', 'NO')) as varchar(3)) as identity_cycle,
cast(sys.ifthenelse(seq.\"name\" is null, 'NO', 'YES') as varchar(3)) as
is_generated, cast(sys.ifthenelse(seq.\"name\" is null, null, c.\"default\") as
varchar(1024)) as generation_expression, cast('NO' as varchar(3)) as
is_system_time_period_start, cast('NO' as varchar(3)) as
is_system_time_period_end, cast('NO' as varchar(3)) as
system_time_period_timestamp_generation, cast(sys.ifthenelse(t.\"type\" in
(0,3,7,20,30), 'YES', 'NO') as varchar(3)) as is_updatable, cast(null as
varchar(1)) as declared_data_type, cast(null as int) as
declared_numeric_precision, cast(null as int) as d
eclared_numeric_scale, t.\"schema_id\" as schema_id, c.\"table_id\" as
table_id, c.\"id\" as column_id, seq.\"id\" as sequence_id, t.\"system\" as
is_system, cm.\"remark\" as comments from sys.\"columns\" c inner join
sys.\"tables\" t on c.\"table_id\" = t.\"id\" inner join sys.\"schemas\" s on
t.\"schema_id\" = s.\"id\" left outer join sys.\"comments\" cm on c.\"id\" =
cm.\"id\" left outer join sys.\"sequences\" seq on ((seq.\"name\"||'\"') =
substring(c.\"default\", 3 + sys.\"locate\"('\".\"seq_',c.\"default\",14)))
order by s.\"name\", t.\"name\", c.\"number\";", "VIEW", true, "COMMIT",
"WRITABLE", NULL ]
+[ "sys._tables", "information_schema", "parameters", "create view
information_schema.parameters as select cast(null as varchar(1)) as
specific_catalog, s.\"name\" as specific_schema,
cast(f.\"name\"||'('||f.\"id\"||')' as varchar(270)) as specific_name,
cast(sys.ifthenelse((a.\"inout\" = 0 or f.\"type\" = 2), 1 + a.\"number\",
sys.ifthenelse(f.\"type\" = 1, a.\"number\", (1 + a.\"number\" -
f.count_out_cols))) as int) as ordinal_position,
cast(sys.ifthenelse(a.\"inout\" = 0, 'OUT', sys.ifthenelse(a.\"inout\" = 1,
'IN', 'INOUT')) as varchar(5)) as parameter_mode,
cast(sys.ifthenelse(a.\"inout\" = 0, 'YES', 'NO') as varchar(3)) as is_result,
cast(null as varchar(1)) as as_locator, a.\"name\" as parameter_name, cast(null
as varchar(1)) as from_sql_specific_catalog, cast(null as varchar(1)) as
from_sql_specific_schema, cast(null as varchar(1)) as from_sql_specific_name,
cast(null as varchar(1)) as to_sql_specific_catalog, cast(null as varchar(1))
as to_sql_specific_schema, cast(null as
varchar(1)) as to_sql_specific_name, cast(sys.\"sql_datatype\"(a.\"type\",
a.\"type_digits\", a.\"type_scale\", true, true) as varchar(1024)) as
data_type, cast(sys.ifthenelse(a.\"type\" in
('varchar','clob','char','json','url','xml') and a.\"type_digits\" > 0,
a.\"type_digits\", null) as int) as character_maximum_length,
cast(sys.ifthenelse(a.\"type\" in ('varchar','clob','char','json','url','xml')
and a.\"type_digits\" > 0, sys.sql_min(4 * a.\"type_digits\", 2147483647),
null) as int) as character_octet_length, cast(null as varchar(1)) as
character_set_catalog, cast(null as varchar(1)) as character_set_schema,
cast(sys.ifthenelse(a.\"type\" in ('varchar','clob','char','json','url','xml'),
'UTF-8', null) as varchar(16)) as character_set_name, cast(null as varchar(1))
as collation_catalog, cast(null as varchar(1)) as collation_schema, cast(null
as varchar(1)) as collation_name, cast(sys.ifthenelse(a.\"type\" in
('int','smallint','tinyint','bigint','hugeint','float','real','double','
decimal','numeric','oid'), a.\"type_digits\", null) as int) as
numeric_precision, cast(sys.ifthenelse(a.\"type\" in
('int','smallint','tinyint','bigint','hugeint','float','real','double','oid'),
2, sys.ifthenelse(a.\"type\" in ('decimal','numeric'), 10, null)) as int) as
numeric_precision_radix, cast(sys.ifthenelse(a.\"type\" in
('int','smallint','tinyint','bigint','hugeint','float','real','double','decimal','numeric','oid'),
a.\"type_scale\", null) as int) as numeric_scale,
cast(sys.ifthenelse(a.\"type\" in
('date','timestamp','timestamptz','time','timetz'),
sys.ifthenelse(a.\"type_scale\" > 0, a.\"type_scale\" -1, 0), null) as int) as
datetime_precision, cast(sys.ifthenelse(a.\"type\" in
('day_interval','month_interval','sec_interval'),
sys.\"sql_datatype\"(a.\"type\", a.\"type_digits\", a.\"type_scale\", true,
true), null) as varchar(40)) as interval_type, cast(case a.\"type\" when
'day_interval' then 0 when 'month_interval' then 0 when 'sec_interval' then
(sys.ifthenelse(a.\"typ
e_digits\" in (7, 10, 12, 13), sys.ifthenelse(a.\"type_scale\" > 0,
a.\"type_scale\", 3), 0)) else null end as int) as interval_precision,
cast(null as varchar(1)) as udt_catalog, cast(null as varchar(1)) as
udt_schema, cast(null as varchar(1)) as udt_name, cast(null as varchar(1)) as
scope_catalog, cast(null as varchar(1)) as scope_schema, cast(null as
varchar(1)) as scope_name, cast(null as int) as maximum_cardinality, cast(null
as varchar(1)) as dtd_identifier, cast(null as varchar(1)) as
declared_data_type, cast(null as int) as declared_numeric_precision, cast(null
as int) as declared_numeric_scale, cast(null as varchar(1)) as
parameter_default, f.\"schema_id\" as schema_id, f.\"id\" as function_id,
a.\"id\" as arg_id, f.\"name\" as function_name, f.\"type\" as function_type,
f.\"system\" as is_system from sys.\"args\" a inner join (select fun.*, (select
count(*) from sys.args a0 where a0.inout = 0 and a0.func_id = fun.id) as
count_out_cols from sys.\"functions\" fun where fun.\
"type\" in (1, 2, 5, 7)) f on f.\"id\" = a.\"func_id\" inner join
sys.\"schemas\" s on s.\"id\" = f.\"schema_id\" order by s.\"name\",
f.\"name\", f.\"id\", a.\"inout\" desc, a.\"number\";", "VIEW", true,
"COMMIT", "WRITABLE", NULL ]
[ "sys._tables", "information_schema", "referential_constraints",
"create view information_schema.referential_constraints as select cast(null as
varchar(1)) as constraint_catalog, s.\"name\" as constraint_schema, fk.\"name\"
as constraint_name, cast(null as varchar(1)) as unique_constraint_catalog,
uks.\"name\" as unique_constraint_schema, uk.\"name\" as
unique_constraint_name, cast('FULL' as varchar(7)) as match_option,
fk.\"update_action\" as update_rule, fk.\"delete_action\" as delete_rule,
t.\"schema_id\" as fk_schema_id, t.\"id\" as fk_table_id, t.\"name\" as
fk_table_name, fk.\"id\" as fk_key_id, ukt.\"schema_id\" as uc_schema_id,
uk.\"table_id\" as uc_table_id, ukt.\"name\" as uc_table_name, uk.\"id\" as
uc_key_id from sys.\"fkeys\" fk inner join sys.\"tables\" t on t.\"id\" =
fk.\"table_id\" inner join sys.\"schemas\" s on s.\"id\" = t.\"schema_id\" left
outer join sys.\"keys\" uk on uk.\"id\" = fk.\"rkey\" left outer join
sys.\"tables\" ukt on ukt.\"id\" = uk.\"table_id\" l
eft outer join sys.\"schemas\" uks on uks.\"id\" = ukt.\"schema_id\" order by
s.\"name\", t.\"name\", fk.\"name\";", "VIEW", true, "COMMIT",
"WRITABLE", NULL ]
-[ "sys._tables", "information_schema", "routines", "create view
information_schema.routines as select cast(null as varchar(1)) as
specific_catalog, s.\"name\" as specific_schema,
cast(f.\"name\"||'('||f.\"id\"||')' as varchar(270)) as specific_name,
cast(null as varchar(1)) as routine_catalog, s.\"name\" as routine_schema,
f.\"name\" as routine_name, ft.\"function_type_keyword\" as routine_type,
cast(null as varchar(1)) as module_catalog, cast(null as varchar(1)) as
module_schema, cast(f.\"mod\" as varchar(128)) as module_name, cast(null as
varchar(1)) as udt_catalog, cast(null as varchar(1)) as udt_schema, cast(null
as varchar(1)) as udt_name, cast(case f.\"type\" when 1 then
sys.\"sql_datatype\"(a.\"type\", a.\"type_digits\", a.\"type_scale\", true,
true) when 2 then null when 5 then 'TABLE' when 7 then 'TABLE' else null end as
varchar(1024)) as data_type, cast(sys.ifthenelse(a.\"type\" in
('varchar','clob','char','json','url','xml') and a.\"type_digits\" > 0,
a.\"type_digits\", n
ull) as int) as character_maximum_length, cast(sys.ifthenelse(a.\"type\" in
('varchar','clob','char','json','url','xml') and a.\"type_digits\" > 0,
a.\"type_digits\" * 4, null) as int) as character_octet_length, cast(null as
varchar(1)) as character_set_catalog, cast(null as varchar(1)) as
character_set_schema, 'UTF-8' as character_set_name, cast(null as varchar(1))
as collation_catalog, cast(null as varchar(1)) as collation_schema, cast(null
as varchar(1)) as collation_name, cast(sys.ifthenelse(a.\"type\" in
('int','smallint','tinyint','bigint','hugeint','float','real','double','decimal','numeric','oid'),
a.\"type_digits\", null) as int) as numeric_precision,
cast(sys.ifthenelse(a.\"type\" in
('int','smallint','tinyint','bigint','hugeint','float','real','double','oid'),
2, sys.ifthenelse(a.\"type\" in ('decimal','numeric'), 10, null)) as int) as
numeric_precision_radix, cast(sys.ifthenelse(a.\"type\" in
('int','smallint','tinyint','bigint','hugeint','float','real','double','decimal
','numeric','oid'), a.\"type_scale\", null) as int) as numeric_scale,
cast(sys.ifthenelse(a.\"type\" in
('date','timestamp','timestamptz','time','timetz'), a.\"type_scale\" -1, null)
as int) as datetime_precision, cast(sys.ifthenelse(a.\"type\" in
('day_interval','month_interval','sec_interval'),
sys.\"sql_datatype\"(a.\"type\", a.\"type_digits\", a.\"type_scale\", true,
true), null) as varchar(40)) as interval_type, cast(case a.\"type\" when
'day_interval' then 0 when 'month_interval' then 0 when 'sec_interval' then
(sys.ifthenelse(a.\"type_digits\" in (7, 10, 12, 13),
sys.ifthenelse(a.\"type_scale\" > 0, a.\"type_scale\", 3), 0)) else null end as
int) as interval_precision, cast(null as varchar(1)) as type_udt_catalog,
cast(null as varchar(1)) as type_udt_schema, cast(null as varchar(1)) as
type_udt_name, cast(null as varchar(1)) as scope_catalog, cast(null as
varchar(1)) as scope_schema, cast(null as varchar(1)) as scope_name, cast(null
as int) as maximum_cardinality, cast(null a
s int) as dtd_identifier,
cast(sys.\"ifthenelse\"(sys.\"locate\"('begin',f.\"func\") > 0,
sys.\"ifthenelse\"(sys.\"endswith\"(f.\"func\",';'),
sys.\"substring\"(f.\"func\", sys.\"locate\"('begin',f.\"func\"),
sys.\"length\"(sys.\"substring\"(f.\"func\",
sys.\"locate\"('begin',f.\"func\")))-1), sys.\"substring\"(f.\"func\",
sys.\"locate\"('begin',f.\"func\"))), null) as varchar(8196)) as routine_body,
f.\"func\" as routine_definition,
cast(sys.\"ifthenelse\"(sys.\"locate\"('external name',f.\"func\") > 0,
sys.\"ifthenelse\"(sys.\"endswith\"(f.\"func\",';'),
sys.\"substring\"(f.\"func\", 14 + sys.\"locate\"('external name',f.\"func\"),
sys.\"length\"(sys.\"substring\"(f.\"func\", 14 + sys.\"locate\"('external
name',f.\"func\")))-1), sys.\"substring\"(f.\"func\", 14 +
sys.\"locate\"('external name',f.\"func\"))), null) as varchar(1024)) as
external_name, fl.\"language_keyword\" as external_language, 'GENERAL' as
parameter_style, 'YES' as is_deterministic, cast(sys.ifthenelse(f.\"side_e
ffect\", 'MODIFIES', 'READ') as varchar(10)) as sql_data_access, cast(case
f.\"type\" when 2 then null else 'NO' end as varchar(3)) as is_null_call,
cast(null as varchar(1)) as sql_path, cast(null as varchar(1)) as
schema_level_routine, cast(null as int) as max_dynamic_result_sets, cast(null
as varchar(1)) as is_user_defined_cast, cast(null as varchar(1)) as
is_implicitly_invocable, cast(null as varchar(1)) as security_type, cast(null
as varchar(1)) as to_sql_specific_catalog, cast(null as varchar(1)) as
to_sql_specific_schema, cast(null as varchar(1)) as to_sql_specific_name,
cast(null as varchar(1)) as as_locator, cast(null as timestamp) as created,
cast(null as timestamp) as last_altered, cast(null as varchar(1)) as
new_savepoint_level, cast(null as varchar(1)) as is_udt_dependent, cast(null as
varchar(1)) as result_cast_from_data_type, cast(null as varchar(1)) as
result_cast_as_locator, cast(null as int) as result_cast_char_max_length,
cast(null as int) as result_cast_char_octet
_length, cast(null as varchar(1)) as result_cast_char_set_catalog, cast(null
as varchar(1)) as result_cast_char_set_schema, cast(null as varchar(1)) as
result_cast_character_set_name, cast(null as varchar(1)) as
result_cast_collation_catalog, cast(null as varchar(1)) as
result_cast_collation_schema, cast(null as varchar(1)) as
result_cast_collation_name, cast(null as int) as result_cast_numeric_precision,
cast(null as int) as result_cast_numeric_radix, cast(null as int) as
result_cast_numeric_scale, cast(null as int) as result_cast_datetime_precision,
cast(null as varchar(1)) as result_cast_interval_type, cast(null as int) as
result_cast_interval_precision, cast(null as varchar(1)) as
result_cast_type_udt_catalog, cast(null as varchar(1)) as
result_cast_type_udt_schema, cast(null as varchar(1)) as
result_cast_type_udt_name, cast(null as varchar(1)) as
result_cast_scope_catalog, cast(null as varchar(1)) as
result_cast_scope_schema, cast(null as varchar(1)) as result_cast_scope_name,
cast(null as int) as result_cast_max_cardinality, cast(null as varchar(1)) as
result_cast_dtd_identifier, cast(null as varchar(1)) as declared_data_type,
cast(null as int) as declared_numeric_precision, cast(null as int) as
declared_numeric_scale, cast(null as varchar(1)) as
result_cast_from_declared_data_type, cast(null as int) as
result_cast_declared_numeric_precision, cast(null as int) as
result_cast_declared_numeric_scale, f.\"schema_id\" as schema_id, f.\"id\" as
function_id, f.\"type\" as function_type, f.\"language\" as function_language,
f.\"system\" as is_system, cm.\"remark\" as comments from sys.\"functions\" f
inner join sys.\"schemas\" s on s.\"id\" = f.\"schema_id\" inner join
sys.\"function_types\" ft on ft.\"function_type_id\" = f.\"type\" inner join
sys.\"function_languages\" fl on fl.\"language_id\" = f.\"language\" left outer
join sys.\"args\" a on a.\"func_id\" = f.\"id\" and a.\"inout\" = 0 and
a.\"number\" = 0 left outer join sys.\"comments\" cm on cm.\"id\" =
f.\"id\" where f.\"type\" in (1, 2, 5, 7) order by s.\"name\", f.\"name\";",
"VIEW", true, "COMMIT", "WRITABLE", NULL ]
+[ "sys._tables", "information_schema", "routines", "create view
information_schema.routines as select cast(null as varchar(1)) as
specific_catalog, s.\"name\" as specific_schema,
cast(f.\"name\"||'('||f.\"id\"||')' as varchar(270)) as specific_name,
cast(null as varchar(1)) as routine_catalog, s.\"name\" as routine_schema,
f.\"name\" as routine_name, ft.\"function_type_keyword\" as routine_type,
cast(null as varchar(1)) as module_catalog, cast(null as varchar(1)) as
module_schema, cast(f.\"mod\" as varchar(128)) as module_name, cast(null as
varchar(1)) as udt_catalog, cast(null as varchar(1)) as udt_schema, cast(null
as varchar(1)) as udt_name, cast(case f.\"type\" when 1 then
sys.\"sql_datatype\"(a.\"type\", a.\"type_digits\", a.\"type_scale\", true,
true) when 2 then null when 5 then 'TABLE' when 7 then 'TABLE' else null end as
varchar(1024)) as data_type, cast(sys.ifthenelse(a.\"type\" in
('varchar','clob','char','json','url','xml') and a.\"type_digits\" > 0,
a.\"type_digits\", n
ull) as int) as character_maximum_length, cast(sys.ifthenelse(a.\"type\" in
('varchar','clob','char','json','url','xml') and a.\"type_digits\" > 0,
sys.sql_min(4 * a.\"type_digits\", 2147483647), null) as int) as
character_octet_length, cast(null as varchar(1)) as character_set_catalog,
cast(null as varchar(1)) as character_set_schema, 'UTF-8' as
character_set_name, cast(null as varchar(1)) as collation_catalog, cast(null as
varchar(1)) as collation_schema, cast(null as varchar(1)) as collation_name,
cast(sys.ifthenelse(a.\"type\" in
('int','smallint','tinyint','bigint','hugeint','float','real','double','decimal','numeric','oid'),
a.\"type_digits\", null) as int) as numeric_precision,
cast(sys.ifthenelse(a.\"type\" in
('int','smallint','tinyint','bigint','hugeint','float','real','double','oid'),
2, sys.ifthenelse(a.\"type\" in ('decimal','numeric'), 10, null)) as int) as
numeric_precision_radix, cast(sys.ifthenelse(a.\"type\" in
('int','smallint','tinyint','bigint','hugeint','float'
,'real','double','decimal','numeric','oid'), a.\"type_scale\", null) as int)
as numeric_scale, cast(sys.ifthenelse(a.\"type\" in
('date','timestamp','timestamptz','time','timetz'), a.\"type_scale\" -1, null)
as int) as datetime_precision, cast(sys.ifthenelse(a.\"type\" in
('day_interval','month_interval','sec_interval'),
sys.\"sql_datatype\"(a.\"type\", a.\"type_digits\", a.\"type_scale\", true,
true), null) as varchar(40)) as interval_type, cast(case a.\"type\" when
'day_interval' then 0 when 'month_interval' then 0 when 'sec_interval' then
(sys.ifthenelse(a.\"type_digits\" in (7, 10, 12, 13),
sys.ifthenelse(a.\"type_scale\" > 0, a.\"type_scale\", 3), 0)) else null end as
int) as interval_precision, cast(null as varchar(1)) as type_udt_catalog,
cast(null as varchar(1)) as type_udt_schema, cast(null as varchar(1)) as
type_udt_name, cast(null as varchar(1)) as scope_catalog, cast(null as
varchar(1)) as scope_schema, cast(null as varchar(1)) as scope_name, cast(null
as int) as maximum
_cardinality, cast(null as int) as dtd_identifier,
cast(sys.\"ifthenelse\"(sys.\"locate\"('begin',f.\"func\") > 0,
sys.\"ifthenelse\"(sys.\"endswith\"(f.\"func\",';'),
sys.\"substring\"(f.\"func\", sys.\"locate\"('begin',f.\"func\"),
sys.\"length\"(sys.\"substring\"(f.\"func\",
sys.\"locate\"('begin',f.\"func\")))-1), sys.\"substring\"(f.\"func\",
sys.\"locate\"('begin',f.\"func\"))), null) as varchar(8196)) as routine_body,
f.\"func\" as routine_definition,
cast(sys.\"ifthenelse\"(sys.\"locate\"('external name',f.\"func\") > 0,
sys.\"ifthenelse\"(sys.\"endswith\"(f.\"func\",';'),
sys.\"substring\"(f.\"func\", 14 + sys.\"locate\"('external name',f.\"func\"),
sys.\"length\"(sys.\"substring\"(f.\"func\", 14 + sys.\"locate\"('external
name',f.\"func\")))-1), sys.\"substring\"(f.\"func\", 14 +
sys.\"locate\"('external name',f.\"func\"))), null) as varchar(1024)) as
external_name, fl.\"language_keyword\" as external_language, 'GENERAL' as
parameter_style, 'YES' as is_deterministic, cast(
sys.ifthenelse(f.\"side_effect\", 'MODIFIES', 'READ') as varchar(10)) as
sql_data_access, cast(case f.\"type\" when 2 then null else 'NO' end as
varchar(3)) as is_null_call, cast(null as varchar(1)) as sql_path, cast(null as
varchar(1)) as schema_level_routine, cast(null as int) as
max_dynamic_result_sets, cast(null as varchar(1)) as is_user_defined_cast,
cast(null as varchar(1)) as is_implicitly_invocable, cast(null as varchar(1))
as security_type, cast(null as varchar(1)) as to_sql_specific_catalog,
cast(null as varchar(1)) as to_sql_specific_schema, cast(null as varchar(1)) as
to_sql_specific_name, cast(null as varchar(1)) as as_locator, cast(null as
timestamp) as created, cast(null as timestamp) as last_altered, cast(null as
varchar(1)) as new_savepoint_level, cast(null as varchar(1)) as
is_udt_dependent, cast(null as varchar(1)) as result_cast_from_data_type,
cast(null as varchar(1)) as result_cast_as_locator, cast(null as int) as
result_cast_char_max_length, cast(null as int)
as result_cast_char_octet_length, cast(null as varchar(1)) as
result_cast_char_set_catalog, cast(null as varchar(1)) as
result_cast_char_set_schema, cast(null as varchar(1)) as
result_cast_character_set_name, cast(null as varchar(1)) as
result_cast_collation_catalog, cast(null as varchar(1)) as
result_cast_collation_schema, cast(null as varchar(1)) as
result_cast_collation_name, cast(null as int) as result_cast_numeric_precision,
cast(null as int) as result_cast_numeric_radix, cast(null as int) as
result_cast_numeric_scale, cast(null as int) as result_cast_datetime_precision,
cast(null as varchar(1)) as result_cast_interval_type, cast(null as int) as
result_cast_interval_precision, cast(null as varchar(1)) as
result_cast_type_udt_catalog, cast(null as varchar(1)) as
result_cast_type_udt_schema, cast(null as varchar(1)) as
result_cast_type_udt_name, cast(null as varchar(1)) as
result_cast_scope_catalog, cast(null as varchar(1)) as
result_cast_scope_schema, cast(null as varchar(1)) as
result_cast_scope_name, cast(null as int) as result_cast_max_cardinality,
cast(null as varchar(1)) as result_cast_dtd_identifier, cast(null as
varchar(1)) as declared_data_type, cast(null as int) as
declared_numeric_precision, cast(null as int) as declared_numeric_scale,
cast(null as varchar(1)) as result_cast_from_declared_data_type, cast(null as
int) as result_cast_declared_numeric_precision, cast(null as int) as
result_cast_declared_numeric_scale, f.\"schema_id\" as schema_id, f.\"id\" as
function_id, f.\"type\" as function_type, f.\"language\" as function_language,
f.\"system\" as is_system, cm.\"remark\" as comments from sys.\"functions\" f
inner join sys.\"schemas\" s on s.\"id\" = f.\"schema_id\" inner join
sys.\"function_types\" ft on ft.\"function_type_id\" = f.\"type\" inner join
sys.\"function_languages\" fl on fl.\"language_id\" = f.\"language\" left outer
join sys.\"args\" a on a.\"func_id\" = f.\"id\" and a.\"inout\" = 0 and
a.\"number\" = 0 left outer join sys.\"comm
ents\" cm on cm.\"id\" = f.\"id\" where f.\"type\" in (1, 2, 5, 7) order by
s.\"name\", f.\"name\";", "VIEW", true, "COMMIT", "WRITABLE", NULL
]
[ "sys._tables", "information_schema", "schemata", "create view
information_schema.schemata as select cast(null as varchar(1)) as catalog_name,
s.\"name\" as schema_name, a.\"name\" as schema_owner, cast(null as varchar(1))
as default_character_set_catalog, cast(null as varchar(1)) as
default_character_set_schema, cast('UTF-8' as varchar(16)) as
default_character_set_name, cast(null as varchar(1)) as sql_path, s.\"id\" as
schema_id, s.\"system\" as is_system, cm.\"remark\" as comments from
sys.\"schemas\" s inner join sys.\"auths\" a on s.\"owner\" = a.\"id\" left
outer join sys.\"comments\" cm on s.\"id\" = cm.\"id\" order by s.\"name\";",
"VIEW", true, "COMMIT", "WRITABLE", NULL ]
[ "sys._tables", "information_schema", "sequences", "create view
information_schema.sequences as select cast(null as varchar(1)) as
sequence_catalog, s.\"name\" as sequence_schema, sq.\"name\" as sequence_name,
cast('BIGINT' as varchar(16)) as data_type, cast(64 as smallint) as
numeric_precision, cast(2 as smallint) as numeric_precision_radix, cast(0 as
smallint) as numeric_scale, sq.\"start\" as start_value, sq.\"minvalue\" as
minimum_value, sq.\"maxvalue\" as maximum_value, sq.\"increment\" as increment,
cast(sys.ifthenelse(sq.\"cycle\", 'YES', 'NO') as varchar(3)) as cycle_option,
cast(null as varchar(16)) as declared_data_type, cast(null as smallint) as
declared_numeric_precision, cast(null as smallint) as declared_numeric_scale,
sq.\"schema_id\" as schema_id, sq.\"id\" as sequence_id,
get_value_for(s.\"name\", sq.\"name\") as current_value, sq.\"cacheinc\" as
cacheinc, cm.\"remark\" as comments from sys.\"sequences\" sq inner join
sys.\"schemas\" s on sq.\"schema_id\" = s.\"id\
" left outer join sys.\"comments\" cm on sq.\"id\" = cm.\"id\" order by
s.\"name\", sq.\"name\";", "VIEW", true, "COMMIT", "WRITABLE",
NULL ]
[ "sys._tables", "information_schema", "table_constraints", "create
view information_schema.table_constraints as select cast(null as varchar(1)) as
constraint_catalog, s.\"name\" as constraint_schema, k.\"name\" as
constraint_name, cast(null as varchar(1)) as table_catalog, s.\"name\" as
table_schema, t.\"name\" as table_name, cast(case k.\"type\" when 0 then
'PRIMARY KEY' when 1 then 'UNIQUE' when 2 then 'FOREIGN KEY' else null end as
varchar(16)) as constraint_type, cast('NO' as varchar(3)) as is_deferrable,
cast('NO' as varchar(3)) as initially_deferred, cast('YES' as varchar(3)) as
enforced, t.\"schema_id\" as schema_id, t.\"id\" as table_id, k.\"id\" as
key_id, k.\"type\" as key_type, t.\"system\" as is_system from (select
sk.\"id\", sk.\"table_id\", sk.\"name\", sk.\"type\" from sys.\"keys\" sk union
all select tk.\"id\", tk.\"table_id\", tk.\"name\", tk.\"type\" from
tmp.\"keys\" tk) k inner join (select st.\"id\", st.\"schema_id\", st.\"name\",
st.\"system\" from sys.\"_tabl
es\" st union all select tt.\"id\", tt.\"schema_id\", tt.\"name\",
tt.\"system\" from tmp.\"_tables\" tt) t on k.\"table_id\" = t.\"id\" inner
join sys.\"schemas\" s on t.\"schema_id\" = s.\"id\" order by s.\"name\",
t.\"name\", k.\"name\";", "VIEW", true, "COMMIT", "WRITABLE",
NULL ]
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
@@ -444,13 +444,13 @@ select 'null in fkeys.delete_action', de
% .%25, .s, .t, ., ., .t, ., ., . #
table_name
% %25, name, name, query, type, system, commit_action, access, comment
# name
% char, varchar, varchar, clob, varchar,
boolean, char, char, varchar # type
-% 11, 18, 34, 6793, 5, 5, 8, 10, 0 # length
+% 11, 18, 34, 6818, 5, 5, 8, 10, 0 # length
[ "sys._tables", "information_schema", "character_sets", "create
view information_schema.character_sets as select cast(null as varchar(1)) as
character_set_catalog, cast(null as varchar(1)) as character_set_schema,
cast('UTF-8' as varchar(16)) as character_set_name, cast('ISO/IEC 10646:2021'
as varchar(20)) as character_repertoire, cast('UTF-8' as varchar(16)) as
form_of_use, cast(null as varchar(1)) as default_collate_catalog, cast(null as
varchar(1)) as default_collate_schema, cast(null as varchar(1)) as
default_collate_name;", "VIEW", true, "COMMIT", "WRITABLE", NULL
]
[ "sys._tables", "information_schema", "check_constraints", "create
view information_schema.check_constraints as select cast(null as varchar(1)) as
constraint_catalog, cast(null as varchar(1024)) as constraint_schema, cast(null
as varchar(1024)) as constraint_name, cast(null as varchar(1024)) as
check_clause where 1=0;", "VIEW", true, "COMMIT", "WRITABLE", NULL
]
-[ "sys._tables", "information_schema", "columns", "create view
information_schema.columns as select cast(null as varchar(1)) as table_catalog,
s.\"name\" as table_schema, t.\"name\" as table_name, c.\"name\" as
column_name, cast(1 + c.\"number\" as int) as ordinal_position, c.\"default\"
as column_default, cast(sys.ifthenelse(c.\"null\", 'YES', 'NO') as varchar(3))
as is_nullable, cast(sys.\"sql_datatype\"(c.\"type\", c.\"type_digits\",
c.\"type_scale\", true, true) as varchar(1024)) as data_type,
cast(sys.ifthenelse(c.\"type\" in ('varchar','clob','char','json','url','xml')
and c.\"type_digits\" > 0, c.\"type_digits\", null) as int) as
character_maximum_length, cast(sys.ifthenelse(c.\"type\" in
('varchar','clob','char','json','url','xml') and c.\"type_digits\" > 0,
c.\"type_digits\" * 4, null) as int) as character_octet_length,
cast(sys.ifthenelse(c.\"type\" in
('int','smallint','tinyint','bigint','hugeint','float','real','double','decimal','numeric','oid'),
c.\"type_digits\", null
) as int) as numeric_precision, cast(sys.ifthenelse(c.\"type\" in
('int','smallint','tinyint','bigint','hugeint','float','real','double','oid'),
2, sys.ifthenelse(c.\"type\" in ('decimal','numeric'), 10, null)) as int) as
numeric_precision_radix, cast(sys.ifthenelse(c.\"type\" in
('int','smallint','tinyint','bigint','hugeint','float','real','double','decimal','numeric','oid'),
c.\"type_scale\", null) as int) as numeric_scale,
cast(sys.ifthenelse(c.\"type\" in
('date','timestamp','timestamptz','time','timetz'),
sys.ifthenelse(c.\"type_scale\" > 0, c.\"type_scale\" -1, 0), null) as int) as
datetime_precision, cast(sys.ifthenelse(c.\"type\" in
('day_interval','month_interval','sec_interval'),
sys.\"sql_datatype\"(c.\"type\", c.\"type_digits\", c.\"type_scale\", true,
true), null) as varchar(40)) as interval_type, cast(case c.\"type\" when
'day_interval' then 0 when 'month_interval' then 0 when 'sec_interval' then
(sys.ifthenelse(c.\"type_digits\" in (7, 10, 12, 13), sys.ifthenelse(c.\"
type_scale\" > 0, c.\"type_scale\", 3), 0)) else null end as int) as
interval_precision, cast(null as varchar(1)) as character_set_catalog,
cast(null as varchar(1)) as character_set_schema,
cast(sys.ifthenelse(c.\"type\" in ('varchar','clob','char','json','url','xml'),
'UTF-8', null) as varchar(16)) as character_set_name, cast(null as varchar(1))
as collation_catalog, cast(null as varchar(1)) as collation_schema, cast(null
as varchar(1)) as collation_name, cast(null as varchar(1)) as domain_catalog,
cast(null as varchar(1)) as domain_schema, cast(null as varchar(1)) as
domain_name, cast(null as varchar(1)) as udt_catalog, cast(null as varchar(1))
as udt_schema, cast(null as varchar(1)) as udt_name, cast(null as varchar(1))
as scope_catalog, cast(null as varchar(1)) as scope_schema, cast(null as
varchar(1)) as scope_name, cast(null as int) as maximum_cardinality, cast(null
as varchar(1)) as dtd_identifier, cast('NO' as varchar(3)) as
is_self_referencing, cast(sys.ifthenelse(seq.\"nam
e\" is null or c.\"null\", 'NO', 'YES') as varchar(3)) as is_identity,
seq.\"name\" as identity_generation, seq.\"start\" as identity_start,
seq.\"increment\" as identity_increment, seq.\"maxvalue\" as identity_maximum,
seq.\"minvalue\" as identity_minimum, cast(sys.ifthenelse(seq.\"name\" is null,
null, sys.ifthenelse(seq.\"cycle\", 'YES', 'NO')) as varchar(3)) as
identity_cycle, cast(sys.ifthenelse(seq.\"name\" is null, 'NO', 'YES') as
varchar(3)) as is_generated, cast(sys.ifthenelse(seq.\"name\" is null, null,
c.\"default\") as varchar(1024)) as generation_expression, cast('NO' as
varchar(3)) as is_system_time_period_start, cast('NO' as varchar(3)) as
is_system_time_period_end, cast('NO' as varchar(3)) as
system_time_period_timestamp_generation, cast(sys.ifthenelse(t.\"type\" in
(0,3,7,20,30), 'YES', 'NO') as varchar(3)) as is_updatable, cast(null as
varchar(1)) as declared_data_type, cast(null as int) as
declared_numeric_precision, cast(null as int) as declared_numeric_scale, t.
\"schema_id\" as schema_id, c.\"table_id\" as table_id, c.\"id\" as column_id,
seq.\"id\" as sequence_id, t.\"system\" as is_system, cm.\"remark\" as comments
from sys.\"columns\" c inner join sys.\"tables\" t on c.\"table_id\" = t.\"id\"
inner join sys.\"schemas\" s on t.\"schema_id\" = s.\"id\" left outer join
sys.\"comments\" cm on c.\"id\" = cm.\"id\" left outer join sys.\"sequences\"
seq on ((seq.\"name\"||'\"') = substring(c.\"default\", 3 +
sys.\"locate\"('\".\"seq_',c.\"default\",14))) order by s.\"name\", t.\"name\",
c.\"number\";", "VIEW", true, "COMMIT", "WRITABLE", NULL ]
-[ "sys._tables", "information_schema", "parameters", "create view
information_schema.parameters as select cast(null as varchar(1)) as
specific_catalog, s.\"name\" as specific_schema,
cast(f.\"name\"||'('||f.\"id\"||')' as varchar(270)) as specific_name,
cast(sys.ifthenelse((a.\"inout\" = 0 or f.\"type\" = 2), 1 + a.\"number\",
sys.ifthenelse(f.\"type\" = 1, a.\"number\", (1 + a.\"number\" -
f.count_out_cols))) as int) as ordinal_position,
cast(sys.ifthenelse(a.\"inout\" = 0, 'OUT', sys.ifthenelse(a.\"inout\" = 1,
'IN', 'INOUT')) as varchar(5)) as parameter_mode,
cast(sys.ifthenelse(a.\"inout\" = 0, 'YES', 'NO') as varchar(3)) as is_result,
cast(null as varchar(1)) as as_locator, a.\"name\" as parameter_name, cast(null
as varchar(1)) as from_sql_specific_catalog, cast(null as varchar(1)) as
from_sql_specific_schema, cast(null as varchar(1)) as from_sql_specific_name,
cast(null as varchar(1)) as to_sql_specific_catalog, cast(null as varchar(1))
as to_sql_specific_schema, cast(null as
varchar(1)) as to_sql_specific_name, cast(sys.\"sql_datatype\"(a.\"type\",
a.\"type_digits\", a.\"type_scale\", true, true) as varchar(1024)) as
data_type, cast(sys.ifthenelse(a.\"type\" in
('varchar','clob','char','json','url','xml') and a.\"type_digits\" > 0,
a.\"type_digits\", null) as int) as character_maximum_length,
cast(sys.ifthenelse(a.\"type\" in ('varchar','clob','char','json','url','xml')
and a.\"type_digits\" > 0, a.\"type_digits\" * 4, null) as int) as
character_octet_length, cast(null as varchar(1)) as character_set_catalog,
cast(null as varchar(1)) as character_set_schema,
cast(sys.ifthenelse(a.\"type\" in ('varchar','clob','char','json','url','xml'),
'UTF-8', null) as varchar(16)) as character_set_name, cast(null as varchar(1))
as collation_catalog, cast(null as varchar(1)) as collation_schema, cast(null
as varchar(1)) as collation_name, cast(sys.ifthenelse(a.\"type\" in
('int','smallint','tinyint','bigint','hugeint','float','real','double','decimal','numeric','oid')
, a.\"type_digits\", null) as int) as numeric_precision,
cast(sys.ifthenelse(a.\"type\" in
('int','smallint','tinyint','bigint','hugeint','float','real','double','oid'),
2, sys.ifthenelse(a.\"type\" in ('decimal','numeric'), 10, null)) as int) as
numeric_precision_radix, cast(sys.ifthenelse(a.\"type\" in
('int','smallint','tinyint','bigint','hugeint','float','real','double','decimal','numeric','oid'),
a.\"type_scale\", null) as int) as numeric_scale,
cast(sys.ifthenelse(a.\"type\" in
('date','timestamp','timestamptz','time','timetz'),
sys.ifthenelse(a.\"type_scale\" > 0, a.\"type_scale\" -1, 0), null) as int) as
datetime_precision, cast(sys.ifthenelse(a.\"type\" in
('day_interval','month_interval','sec_interval'),
sys.\"sql_datatype\"(a.\"type\", a.\"type_digits\", a.\"type_scale\", true,
true), null) as varchar(40)) as interval_type, cast(case a.\"type\" when
'day_interval' then 0 when 'month_interval' then 0 when 'sec_interval' then
(sys.ifthenelse(a.\"type_digits\" in (7, 10, 12,
13), sys.ifthenelse(a.\"type_scale\" > 0, a.\"type_scale\", 3), 0)) else null
end as int) as interval_precision, cast(null as varchar(1)) as udt_catalog,
cast(null as varchar(1)) as udt_schema, cast(null as varchar(1)) as udt_name,
cast(null as varchar(1)) as scope_catalog, cast(null as varchar(1)) as
scope_schema, cast(null as varchar(1)) as scope_name, cast(null as int) as
maximum_cardinality, cast(null as varchar(1)) as dtd_identifier, cast(null as
varchar(1)) as declared_data_type, cast(null as int) as
declared_numeric_precision, cast(null as int) as declared_numeric_scale,
cast(null as varchar(1)) as parameter_default, f.\"schema_id\" as schema_id,
f.\"id\" as function_id, a.\"id\" as arg_id, f.\"name\" as function_name,
f.\"type\" as function_type, f.\"system\" as is_system from sys.\"args\" a
inner join (select fun.*, (select count(*) from sys.args a0 where a0.inout = 0
and a0.func_id = fun.id) as count_out_cols from sys.\"functions\" fun where
fun.\"type\" in (1, 2, 5, 7))
f on f.\"id\" = a.\"func_id\" inner join sys.\"schemas\" s on s.\"id\" =
f.\"schema_id\" order by s.\"name\", f.\"name\", f.\"id\", a.\"inout\" desc,
a.\"number\";", "VIEW", true, "COMMIT", "WRITABLE", NULL ]
+[ "sys._tables", "information_schema", "columns", "create view
information_schema.columns as select cast(null as varchar(1)) as table_catalog,
s.\"name\" as table_schema, t.\"name\" as table_name, c.\"name\" as
column_name, cast(1 + c.\"number\" as int) as ordinal_position, c.\"default\"
as column_default, cast(sys.ifthenelse(c.\"null\", 'YES', 'NO') as varchar(3))
as is_nullable, cast(sys.\"sql_datatype\"(c.\"type\", c.\"type_digits\",
c.\"type_scale\", true, true) as varchar(1024)) as data_type,
cast(sys.ifthenelse(c.\"type\" in ('varchar','clob','char','json','url','xml')
and c.\"type_digits\" > 0, c.\"type_digits\", null) as int) as
character_maximum_length, cast(sys.ifthenelse(c.\"type\" in
('varchar','clob','char','json','url','xml') and c.\"type_digits\" > 0,
sys.sql_min(4 * c.\"type_digits\", 2147483647), null) as int) as
character_octet_length, cast(sys.ifthenelse(c.\"type\" in
('int','smallint','tinyint','bigint','hugeint','float','real','double','decimal','numeric','oid')
, c.\"type_digits\", null) as int) as numeric_precision,
cast(sys.ifthenelse(c.\"type\" in
('int','smallint','tinyint','bigint','hugeint','float','real','double','oid'),
2, sys.ifthenelse(c.\"type\" in ('decimal','numeric'), 10, null)) as int) as
numeric_precision_radix, cast(sys.ifthenelse(c.\"type\" in
('int','smallint','tinyint','bigint','hugeint','float','real','double','decimal','numeric','oid'),
c.\"type_scale\", null) as int) as numeric_scale,
cast(sys.ifthenelse(c.\"type\" in
('date','timestamp','timestamptz','time','timetz'),
sys.ifthenelse(c.\"type_scale\" > 0, c.\"type_scale\" -1, 0), null) as int) as
datetime_precision, cast(sys.ifthenelse(c.\"type\" in
('day_interval','month_interval','sec_interval'),
sys.\"sql_datatype\"(c.\"type\", c.\"type_digits\", c.\"type_scale\", true,
true), null) as varchar(40)) as interval_type, cast(case c.\"type\" when
'day_interval' then 0 when 'month_interval' then 0 when 'sec_interval' then
(sys.ifthenelse(c.\"type_digits\" in (7, 10, 12,
13), sys.ifthenelse(c.\"type_scale\" > 0, c.\"type_scale\", 3), 0)) else null
end as int) as interval_precision, cast(null as varchar(1)) as
character_set_catalog, cast(null as varchar(1)) as character_set_schema,
cast(sys.ifthenelse(c.\"type\" in ('varchar','clob','char','json','url','xml'),
'UTF-8', null) as varchar(16)) as character_set_name, cast(null as varchar(1))
as collation_catalog, cast(null as varchar(1)) as collation_schema, cast(null
as varchar(1)) as collation_name, cast(null as varchar(1)) as domain_catalog,
cast(null as varchar(1)) as domain_schema, cast(null as varchar(1)) as
domain_name, cast(null as varchar(1)) as udt_catalog, cast(null as varchar(1))
as udt_schema, cast(null as varchar(1)) as udt_name, cast(null as varchar(1))
as scope_catalog, cast(null as varchar(1)) as scope_schema, cast(null as
varchar(1)) as scope_name, cast(null as int) as maximum_cardinality, cast(null
as varchar(1)) as dtd_identifier, cast('NO' as varchar(3)) as
is_self_referencing, cast
(sys.ifthenelse(seq.\"name\" is null or c.\"null\", 'NO', 'YES') as
varchar(3)) as is_identity, seq.\"name\" as identity_generation, seq.\"start\"
as identity_start, seq.\"increment\" as identity_increment, seq.\"maxvalue\" as
identity_maximum, seq.\"minvalue\" as identity_minimum,
cast(sys.ifthenelse(seq.\"name\" is null, null, sys.ifthenelse(seq.\"cycle\",
'YES', 'NO')) as varchar(3)) as identity_cycle,
cast(sys.ifthenelse(seq.\"name\" is null, 'NO', 'YES') as varchar(3)) as
is_generated, cast(sys.ifthenelse(seq.\"name\" is null, null, c.\"default\") as
varchar(1024)) as generation_expression, cast('NO' as varchar(3)) as
is_system_time_period_start, cast('NO' as varchar(3)) as
is_system_time_period_end, cast('NO' as varchar(3)) as
system_time_period_timestamp_generation, cast(sys.ifthenelse(t.\"type\" in
(0,3,7,20,30), 'YES', 'NO') as varchar(3)) as is_updatable, cast(null as
varchar(1)) as declared_data_type, cast(null as int) as
declared_numeric_precision, cast(null as int) as d
eclared_numeric_scale, t.\"schema_id\" as schema_id, c.\"table_id\" as
table_id, c.\"id\" as column_id, seq.\"id\" as sequence_id, t.\"system\" as
is_system, cm.\"remark\" as comments from sys.\"columns\" c inner join
sys.\"tables\" t on c.\"table_id\" = t.\"id\" inner join sys.\"schemas\" s on
t.\"schema_id\" = s.\"id\" left outer join sys.\"comments\" cm on c.\"id\" =
cm.\"id\" left outer join sys.\"sequences\" seq on ((seq.\"name\"||'\"') =
substring(c.\"default\", 3 + sys.\"locate\"('\".\"seq_',c.\"default\",14)))
order by s.\"name\", t.\"name\", c.\"number\";", "VIEW", true, "COMMIT",
"WRITABLE", NULL ]
+[ "sys._tables", "information_schema", "parameters", "create view
information_schema.parameters as select cast(null as varchar(1)) as
specific_catalog, s.\"name\" as specific_schema,
cast(f.\"name\"||'('||f.\"id\"||')' as varchar(270)) as specific_name,
cast(sys.ifthenelse((a.\"inout\" = 0 or f.\"type\" = 2), 1 + a.\"number\",
sys.ifthenelse(f.\"type\" = 1, a.\"number\", (1 + a.\"number\" -
f.count_out_cols))) as int) as ordinal_position,
cast(sys.ifthenelse(a.\"inout\" = 0, 'OUT', sys.ifthenelse(a.\"inout\" = 1,
'IN', 'INOUT')) as varchar(5)) as parameter_mode,
cast(sys.ifthenelse(a.\"inout\" = 0, 'YES', 'NO') as varchar(3)) as is_result,
cast(null as varchar(1)) as as_locator, a.\"name\" as parameter_name, cast(null
as varchar(1)) as from_sql_specific_catalog, cast(null as varchar(1)) as
from_sql_specific_schema, cast(null as varchar(1)) as from_sql_specific_name,
cast(null as varchar(1)) as to_sql_specific_catalog, cast(null as varchar(1))
as to_sql_specific_schema, cast(null as
varchar(1)) as to_sql_specific_name, cast(sys.\"sql_datatype\"(a.\"type\",
a.\"type_digits\", a.\"type_scale\", true, true) as varchar(1024)) as
data_type, cast(sys.ifthenelse(a.\"type\" in
('varchar','clob','char','json','url','xml') and a.\"type_digits\" > 0,
a.\"type_digits\", null) as int) as character_maximum_length,
cast(sys.ifthenelse(a.\"type\" in ('varchar','clob','char','json','url','xml')
and a.\"type_digits\" > 0, sys.sql_min(4 * a.\"type_digits\", 2147483647),
null) as int) as character_octet_length, cast(null as varchar(1)) as
character_set_catalog, cast(null as varchar(1)) as character_set_schema,
cast(sys.ifthenelse(a.\"type\" in ('varchar','clob','char','json','url','xml'),
'UTF-8', null) as varchar(16)) as character_set_name, cast(null as varchar(1))
as collation_catalog, cast(null as varchar(1)) as collation_schema, cast(null
as varchar(1)) as collation_name, cast(sys.ifthenelse(a.\"type\" in
('int','smallint','tinyint','bigint','hugeint','float','real','double','
decimal','numeric','oid'), a.\"type_digits\", null) as int) as
numeric_precision, cast(sys.ifthenelse(a.\"type\" in
('int','smallint','tinyint','bigint','hugeint','float','real','double','oid'),
2, sys.ifthenelse(a.\"type\" in ('decimal','numeric'), 10, null)) as int) as
numeric_precision_radix, cast(sys.ifthenelse(a.\"type\" in
('int','smallint','tinyint','bigint','hugeint','float','real','double','decimal','numeric','oid'),
a.\"type_scale\", null) as int) as numeric_scale,
cast(sys.ifthenelse(a.\"type\" in
('date','timestamp','timestamptz','time','timetz'),
sys.ifthenelse(a.\"type_scale\" > 0, a.\"type_scale\" -1, 0), null) as int) as
datetime_precision, cast(sys.ifthenelse(a.\"type\" in
('day_interval','month_interval','sec_interval'),
sys.\"sql_datatype\"(a.\"type\", a.\"type_digits\", a.\"type_scale\", true,
true), null) as varchar(40)) as interval_type, cast(case a.\"type\" when
'day_interval' then 0 when 'month_interval' then 0 when 'sec_interval' then
(sys.ifthenelse(a.\"typ
e_digits\" in (7, 10, 12, 13), sys.ifthenelse(a.\"type_scale\" > 0,
a.\"type_scale\", 3), 0)) else null end as int) as interval_precision,
cast(null as varchar(1)) as udt_catalog, cast(null as varchar(1)) as
udt_schema, cast(null as varchar(1)) as udt_name, cast(null as varchar(1)) as
scope_catalog, cast(null as varchar(1)) as scope_schema, cast(null as
varchar(1)) as scope_name, cast(null as int) as maximum_cardinality, cast(null
as varchar(1)) as dtd_identifier, cast(null as varchar(1)) as
declared_data_type, cast(null as int) as declared_numeric_precision, cast(null
as int) as declared_numeric_scale, cast(null as varchar(1)) as
parameter_default, f.\"schema_id\" as schema_id, f.\"id\" as function_id,
a.\"id\" as arg_id, f.\"name\" as function_name, f.\"type\" as function_type,
f.\"system\" as is_system from sys.\"args\" a inner join (select fun.*, (select
count(*) from sys.args a0 where a0.inout = 0 and a0.func_id = fun.id) as
count_out_cols from sys.\"functions\" fun where fun.\
"type\" in (1, 2, 5, 7)) f on f.\"id\" = a.\"func_id\" inner join
sys.\"schemas\" s on s.\"id\" = f.\"schema_id\" order by s.\"name\",
f.\"name\", f.\"id\", a.\"inout\" desc, a.\"number\";", "VIEW", true,
"COMMIT", "WRITABLE", NULL ]
[ "sys._tables", "information_schema", "referential_constraints",
"create view information_schema.referential_constraints as select cast(null as
varchar(1)) as constraint_catalog, s.\"name\" as constraint_schema, fk.\"name\"
as constraint_name, cast(null as varchar(1)) as unique_constraint_catalog,
uks.\"name\" as unique_constraint_schema, uk.\"name\" as
unique_constraint_name, cast('FULL' as varchar(7)) as match_option,
fk.\"update_action\" as update_rule, fk.\"delete_action\" as delete_rule,
t.\"schema_id\" as fk_schema_id, t.\"id\" as fk_table_id, t.\"name\" as
fk_table_name, fk.\"id\" as fk_key_id, ukt.\"schema_id\" as uc_schema_id,
uk.\"table_id\" as uc_table_id, ukt.\"name\" as uc_table_name, uk.\"id\" as
uc_key_id from sys.\"fkeys\" fk inner join sys.\"tables\" t on t.\"id\" =
fk.\"table_id\" inner join sys.\"schemas\" s on s.\"id\" = t.\"schema_id\" left
outer join sys.\"keys\" uk on uk.\"id\" = fk.\"rkey\" left outer join
sys.\"tables\" ukt on ukt.\"id\" = uk.\"table_id\" l
eft outer join sys.\"schemas\" uks on uks.\"id\" = ukt.\"schema_id\" order by
s.\"name\", t.\"name\", fk.\"name\";", "VIEW", true, "COMMIT",
"WRITABLE", NULL ]
-[ "sys._tables", "information_schema", "routines", "create view
information_schema.routines as select cast(null as varchar(1)) as
specific_catalog, s.\"name\" as specific_schema,
cast(f.\"name\"||'('||f.\"id\"||')' as varchar(270)) as specific_name,
cast(null as varchar(1)) as routine_catalog, s.\"name\" as routine_schema,
f.\"name\" as routine_name, ft.\"function_type_keyword\" as routine_type,
cast(null as varchar(1)) as module_catalog, cast(null as varchar(1)) as
module_schema, cast(f.\"mod\" as varchar(128)) as module_name, cast(null as
varchar(1)) as udt_catalog, cast(null as varchar(1)) as udt_schema, cast(null
as varchar(1)) as udt_name, cast(case f.\"type\" when 1 then
sys.\"sql_datatype\"(a.\"type\", a.\"type_digits\", a.\"type_scale\", true,
true) when 2 then null when 5 then 'TABLE' when 7 then 'TABLE' else null end as
varchar(1024)) as data_type, cast(sys.ifthenelse(a.\"type\" in
('varchar','clob','char','json','url','xml') and a.\"type_digits\" > 0,
a.\"type_digits\", n
ull) as int) as character_maximum_length, cast(sys.ifthenelse(a.\"type\" in
('varchar','clob','char','json','url','xml') and a.\"type_digits\" > 0,
a.\"type_digits\" * 4, null) as int) as character_octet_length, cast(null as
varchar(1)) as character_set_catalog, cast(null as varchar(1)) as
character_set_schema, 'UTF-8' as character_set_name, cast(null as varchar(1))
as collation_catalog, cast(null as varchar(1)) as collation_schema, cast(null
as varchar(1)) as collation_name, cast(sys.ifthenelse(a.\"type\" in
('int','smallint','tinyint','bigint','hugeint','float','real','double','decimal','numeric','oid'),
a.\"type_digits\", null) as int) as numeric_precision,
cast(sys.ifthenelse(a.\"type\" in
('int','smallint','tinyint','bigint','hugeint','float','real','double','oid'),
2, sys.ifthenelse(a.\"type\" in ('decimal','numeric'), 10, null)) as int) as
numeric_precision_radix, cast(sys.ifthenelse(a.\"type\" in
('int','smallint','tinyint','bigint','hugeint','float','real','double','decimal
','numeric','oid'), a.\"type_scale\", null) as int) as numeric_scale,
cast(sys.ifthenelse(a.\"type\" in
('date','timestamp','timestamptz','time','timetz'), a.\"type_scale\" -1, null)
as int) as datetime_precision, cast(sys.ifthenelse(a.\"type\" in
('day_interval','month_interval','sec_interval'),
sys.\"sql_datatype\"(a.\"type\", a.\"type_digits\", a.\"type_scale\", true,
true), null) as varchar(40)) as interval_type, cast(case a.\"type\" when
'day_interval' then 0 when 'month_interval' then 0 when 'sec_interval' then
(sys.ifthenelse(a.\"type_digits\" in (7, 10, 12, 13),
sys.ifthenelse(a.\"type_scale\" > 0, a.\"type_scale\", 3), 0)) else null end as
int) as interval_precision, cast(null as varchar(1)) as type_udt_catalog,
cast(null as varchar(1)) as type_udt_schema, cast(null as varchar(1)) as
type_udt_name, cast(null as varchar(1)) as scope_catalog, cast(null as
varchar(1)) as scope_schema, cast(null as varchar(1)) as scope_name, cast(null
as int) as maximum_cardinality, cast(null a
s int) as dtd_identifier,
cast(sys.\"ifthenelse\"(sys.\"locate\"('begin',f.\"func\") > 0,
sys.\"ifthenelse\"(sys.\"endswith\"(f.\"func\",';'),
sys.\"substring\"(f.\"func\", sys.\"locate\"('begin',f.\"func\"),
sys.\"length\"(sys.\"substring\"(f.\"func\",
sys.\"locate\"('begin',f.\"func\")))-1), sys.\"substring\"(f.\"func\",
sys.\"locate\"('begin',f.\"func\"))), null) as varchar(8196)) as routine_body,
f.\"func\" as routine_definition,
cast(sys.\"ifthenelse\"(sys.\"locate\"('external name',f.\"func\") > 0,
sys.\"ifthenelse\"(sys.\"endswith\"(f.\"func\",';'),
sys.\"substring\"(f.\"func\", 14 + sys.\"locate\"('external name',f.\"func\"),
sys.\"length\"(sys.\"substring\"(f.\"func\", 14 + sys.\"locate\"('external
name',f.\"func\")))-1), sys.\"substring\"(f.\"func\", 14 +
sys.\"locate\"('external name',f.\"func\"))), null) as varchar(1024)) as
external_name, fl.\"language_keyword\" as external_language, 'GENERAL' as
parameter_style, 'YES' as is_deterministic, cast(sys.ifthenelse(f.\"side_e
ffect\", 'MODIFIES', 'READ') as varchar(10)) as sql_data_access, cast(case
f.\"type\" when 2 then null else 'NO' end as varchar(3)) as is_null_call,
cast(null as varchar(1)) as sql_path, cast(null as varchar(1)) as
schema_level_routine, cast(null as int) as max_dynamic_result_sets, cast(null
as varchar(1)) as is_user_defined_cast, cast(null as varchar(1)) as
is_implicitly_invocable, cast(null as varchar(1)) as security_type, cast(null
as varchar(1)) as to_sql_specific_catalog, cast(null as varchar(1)) as
to_sql_specific_schema, cast(null as varchar(1)) as to_sql_specific_name,
cast(null as varchar(1)) as as_locator, cast(null as timestamp) as created,
cast(null as timestamp) as last_altered, cast(null as varchar(1)) as
new_savepoint_level, cast(null as varchar(1)) as is_udt_dependent, cast(null as
varchar(1)) as result_cast_from_data_type, cast(null as varchar(1)) as
result_cast_as_locator, cast(null as int) as result_cast_char_max_length,
cast(null as int) as result_cast_char_octet
_length, cast(null as varchar(1)) as result_cast_char_set_catalog, cast(null
as varchar(1)) as result_cast_char_set_schema, cast(null as varchar(1)) as
result_cast_character_set_name, cast(null as varchar(1)) as
result_cast_collation_catalog, cast(null as varchar(1)) as
result_cast_collation_schema, cast(null as varchar(1)) as
result_cast_collation_name, cast(null as int) as result_cast_numeric_precision,
cast(null as int) as result_cast_numeric_radix, cast(null as int) as
result_cast_numeric_scale, cast(null as int) as result_cast_datetime_precision,
cast(null as varchar(1)) as result_cast_interval_type, cast(null as int) as
result_cast_interval_precision, cast(null as varchar(1)) as
result_cast_type_udt_catalog, cast(null as varchar(1)) as
result_cast_type_udt_schema, cast(null as varchar(1)) as
result_cast_type_udt_name, cast(null as varchar(1)) as
result_cast_scope_catalog, cast(null as varchar(1)) as
result_cast_scope_schema, cast(null as varchar(1)) as result_cast_scope_name,
cast(null as int) as result_cast_max_cardinality, cast(null as varchar(1)) as
result_cast_dtd_identifier, cast(null as varchar(1)) as declared_data_type,
cast(null as int) as declared_numeric_precision, cast(null as int) as
declared_numeric_scale, cast(null as varchar(1)) as
result_cast_from_declared_data_type, cast(null as int) as
result_cast_declared_numeric_precision, cast(null as int) as
result_cast_declared_numeric_scale, f.\"schema_id\" as schema_id, f.\"id\" as
function_id, f.\"type\" as function_type, f.\"language\" as function_language,
f.\"system\" as is_system, cm.\"remark\" as comments from sys.\"functions\" f
inner join sys.\"schemas\" s on s.\"id\" = f.\"schema_id\" inner join
sys.\"function_types\" ft on ft.\"function_type_id\" = f.\"type\" inner join
sys.\"function_languages\" fl on fl.\"language_id\" = f.\"language\" left outer
join sys.\"args\" a on a.\"func_id\" = f.\"id\" and a.\"inout\" = 0 and
a.\"number\" = 0 left outer join sys.\"comments\" cm on cm.\"id\" =
f.\"id\" where f.\"type\" in (1, 2, 5, 7) order by s.\"name\", f.\"name\";",
"VIEW", true, "COMMIT", "WRITABLE", NULL ]
+[ "sys._tables", "information_schema", "routines", "create view
information_schema.routines as select cast(null as varchar(1)) as
specific_catalog, s.\"name\" as specific_schema,
cast(f.\"name\"||'('||f.\"id\"||')' as varchar(270)) as specific_name,
cast(null as varchar(1)) as routine_catalog, s.\"name\" as routine_schema,
f.\"name\" as routine_name, ft.\"function_type_keyword\" as routine_type,
cast(null as varchar(1)) as module_catalog, cast(null as varchar(1)) as
module_schema, cast(f.\"mod\" as varchar(128)) as module_name, cast(null as
varchar(1)) as udt_catalog, cast(null as varchar(1)) as udt_schema, cast(null
as varchar(1)) as udt_name, cast(case f.\"type\" when 1 then
sys.\"sql_datatype\"(a.\"type\", a.\"type_digits\", a.\"type_scale\", true,
true) when 2 then null when 5 then 'TABLE' when 7 then 'TABLE' else null end as
varchar(1024)) as data_type, cast(sys.ifthenelse(a.\"type\" in
('varchar','clob','char','json','url','xml') and a.\"type_digits\" > 0,
a.\"type_digits\", n
ull) as int) as character_maximum_length, cast(sys.ifthenelse(a.\"type\" in
('varchar','clob','char','json','url','xml') and a.\"type_digits\" > 0,
sys.sql_min(4 * a.\"type_digits\", 2147483647), null) as int) as
character_octet_length, cast(null as varchar(1)) as character_set_catalog,
cast(null as varchar(1)) as character_set_schema, 'UTF-8' as
character_set_name, cast(null as varchar(1)) as collation_catalog, cast(null as
varchar(1)) as collation_schema, cast(null as varchar(1)) as collation_name,
cast(sys.ifthenelse(a.\"type\" in
('int','smallint','tinyint','bigint','hugeint','float','real','double','decimal','numeric','oid'),
a.\"type_digits\", null) as int) as numeric_precision,
cast(sys.ifthenelse(a.\"type\" in
('int','smallint','tinyint','bigint','hugeint','float','real','double','oid'),
2, sys.ifthenelse(a.\"type\" in ('decimal','numeric'), 10, null)) as int) as
numeric_precision_radix, cast(sys.ifthenelse(a.\"type\" in
('int','smallint','tinyint','bigint','hugeint','float'
,'real','double','decimal','numeric','oid'), a.\"type_scale\", null) as int)
as numeric_scale, cast(sys.ifthenelse(a.\"type\" in
('date','timestamp','timestamptz','time','timetz'), a.\"type_scale\" -1, null)
as int) as datetime_precision, cast(sys.ifthenelse(a.\"type\" in
('day_interval','month_interval','sec_interval'),
sys.\"sql_datatype\"(a.\"type\", a.\"type_digits\", a.\"type_scale\", true,
true), null) as varchar(40)) as interval_type, cast(case a.\"type\" when
'day_interval' then 0 when 'month_interval' then 0 when 'sec_interval' then
(sys.ifthenelse(a.\"type_digits\" in (7, 10, 12, 13),
sys.ifthenelse(a.\"type_scale\" > 0, a.\"type_scale\", 3), 0)) else null end as
int) as interval_precision, cast(null as varchar(1)) as type_udt_catalog,
cast(null as varchar(1)) as type_udt_schema, cast(null as varchar(1)) as
type_udt_name, cast(null as varchar(1)) as scope_catalog, cast(null as
varchar(1)) as scope_schema, cast(null as varchar(1)) as scope_name, cast(null
as int) as maximum
_cardinality, cast(null as int) as dtd_identifier,
cast(sys.\"ifthenelse\"(sys.\"locate\"('begin',f.\"func\") > 0,
sys.\"ifthenelse\"(sys.\"endswith\"(f.\"func\",';'),
sys.\"substring\"(f.\"func\", sys.\"locate\"('begin',f.\"func\"),
sys.\"length\"(sys.\"substring\"(f.\"func\",
sys.\"locate\"('begin',f.\"func\")))-1), sys.\"substring\"(f.\"func\",
sys.\"locate\"('begin',f.\"func\"))), null) as varchar(8196)) as routine_body,
f.\"func\" as routine_definition,
cast(sys.\"ifthenelse\"(sys.\"locate\"('external name',f.\"func\") > 0,
sys.\"ifthenelse\"(sys.\"endswith\"(f.\"func\",';'),
sys.\"substring\"(f.\"func\", 14 + sys.\"locate\"('external name',f.\"func\"),
sys.\"length\"(sys.\"substring\"(f.\"func\", 14 + sys.\"locate\"('external
name',f.\"func\")))-1), sys.\"substring\"(f.\"func\", 14 +
sys.\"locate\"('external name',f.\"func\"))), null) as varchar(1024)) as
external_name, fl.\"language_keyword\" as external_language, 'GENERAL' as
parameter_style, 'YES' as is_deterministic, cast(
sys.ifthenelse(f.\"side_effect\", 'MODIFIES', 'READ') as varchar(10)) as
sql_data_access, cast(case f.\"type\" when 2 then null else 'NO' end as
varchar(3)) as is_null_call, cast(null as varchar(1)) as sql_path, cast(null as
varchar(1)) as schema_level_routine, cast(null as int) as
max_dynamic_result_sets, cast(null as varchar(1)) as is_user_defined_cast,
cast(null as varchar(1)) as is_implicitly_invocable, cast(null as varchar(1))
as security_type, cast(null as varchar(1)) as to_sql_specific_catalog,
cast(null as varchar(1)) as to_sql_specific_schema, cast(null as varchar(1)) as
to_sql_specific_name, cast(null as varchar(1)) as as_locator, cast(null as
timestamp) as created, cast(null as timestamp) as last_altered, cast(null as
varchar(1)) as new_savepoint_level, cast(null as varchar(1)) as
is_udt_dependent, cast(null as varchar(1)) as result_cast_from_data_type,
cast(null as varchar(1)) as result_cast_as_locator, cast(null as int) as
result_cast_char_max_length, cast(null as int)
as result_cast_char_octet_length, cast(null as varchar(1)) as
result_cast_char_set_catalog, cast(null as varchar(1)) as
result_cast_char_set_schema, cast(null as varchar(1)) as
result_cast_character_set_name, cast(null as varchar(1)) as
result_cast_collation_catalog, cast(null as varchar(1)) as
result_cast_collation_schema, cast(null as varchar(1)) as
result_cast_collation_name, cast(null as int) as result_cast_numeric_precision,
cast(null as int) as result_cast_numeric_radix, cast(null as int) as
result_cast_numeric_scale, cast(null as int) as result_cast_datetime_precision,
cast(null as varchar(1)) as result_cast_interval_type, cast(null as int) as
result_cast_interval_precision, cast(null as varchar(1)) as
result_cast_type_udt_catalog, cast(null as varchar(1)) as
result_cast_type_udt_schema, cast(null as varchar(1)) as
result_cast_type_udt_name, cast(null as varchar(1)) as
result_cast_scope_catalog, cast(null as varchar(1)) as
result_cast_scope_schema, cast(null as varchar(1)) as
result_cast_scope_name, cast(null as int) as result_cast_max_cardinality,
cast(null as varchar(1)) as result_cast_dtd_identifier, cast(null as
varchar(1)) as declared_data_type, cast(null as int) as
declared_numeric_precision, cast(null as int) as declared_numeric_scale,
cast(null as varchar(1)) as result_cast_from_declared_data_type, cast(null as
int) as result_cast_declared_numeric_precision, cast(null as int) as
result_cast_declared_numeric_scale, f.\"schema_id\" as schema_id, f.\"id\" as
function_id, f.\"type\" as function_type, f.\"language\" as function_language,
f.\"system\" as is_system, cm.\"remark\" as comments from sys.\"functions\" f
inner join sys.\"schemas\" s on s.\"id\" = f.\"schema_id\" inner join
sys.\"function_types\" ft on ft.\"function_type_id\" = f.\"type\" inner join
sys.\"function_languages\" fl on fl.\"language_id\" = f.\"language\" left outer
join sys.\"args\" a on a.\"func_id\" = f.\"id\" and a.\"inout\" = 0 and
a.\"number\" = 0 left outer join sys.\"comm
ents\" cm on cm.\"id\" = f.\"id\" where f.\"type\" in (1, 2, 5, 7) order by
s.\"name\", f.\"name\";", "VIEW", true, "COMMIT", "WRITABLE", NULL
]
[ "sys._tables", "information_schema", "schemata", "create view
information_schema.schemata as select cast(null as varchar(1)) as catalog_name,
s.\"name\" as schema_name, a.\"name\" as schema_owner, cast(null as varchar(1))
as default_character_set_catalog, cast(null as varchar(1)) as
default_character_set_schema, cast('UTF-8' as varchar(16)) as
default_character_set_name, cast(null as varchar(1)) as sql_path, s.\"id\" as
schema_id, s.\"system\" as is_system, cm.\"remark\" as comments from
sys.\"schemas\" s inner join sys.\"auths\" a on s.\"owner\" = a.\"id\" left
outer join sys.\"comments\" cm on s.\"id\" = cm.\"id\" order by s.\"name\";",
"VIEW", true, "COMMIT", "WRITABLE", NULL ]
[ "sys._tables", "information_schema", "sequences", "create view
information_schema.sequences as select cast(null as varchar(1)) as
sequence_catalog, s.\"name\" as sequence_schema, sq.\"name\" as sequence_name,
cast('BIGINT' as varchar(16)) as data_type, cast(64 as smallint) as
numeric_precision, cast(2 as smallint) as numeric_precision_radix, cast(0 as
smallint) as numeric_scale, sq.\"start\" as start_value, sq.\"minvalue\" as
minimum_value, sq.\"maxvalue\" as maximum_value, sq.\"increment\" as increment,
cast(sys.ifthenelse(sq.\"cycle\", 'YES', 'NO') as varchar(3)) as cycle_option,
cast(null as varchar(16)) as declared_data_type, cast(null as smallint) as
declared_numeric_precision, cast(null as smallint) as declared_numeric_scale,
sq.\"schema_id\" as schema_id, sq.\"id\" as sequence_id,
get_value_for(s.\"name\", sq.\"name\") as current_value, sq.\"cacheinc\" as
cacheinc, cm.\"remark\" as comments from sys.\"sequences\" sq inner join
sys.\"schemas\" s on sq.\"schema_id\" = s.\"id\
" left outer join sys.\"comments\" cm on sq.\"id\" = cm.\"id\" order by
s.\"name\", sq.\"name\";", "VIEW", true, "COMMIT", "WRITABLE",
NULL ]
[ "sys._tables", "information_schema", "table_constraints", "create
view information_schema.table_constraints as select cast(null as varchar(1)) as
constraint_catalog, s.\"name\" as constraint_schema, k.\"name\" as
constraint_name, cast(null as varchar(1)) as table_catalog, s.\"name\" as
table_schema, t.\"name\" as table_name, cast(case k.\"type\" when 0 then
'PRIMARY KEY' when 1 then 'UNIQUE' when 2 then 'FOREIGN KEY' else null end as
varchar(16)) as constraint_type, cast('NO' as varchar(3)) as is_deferrable,
cast('NO' as varchar(3)) as initially_deferred, cast('YES' as varchar(3)) as
enforced, t.\"schema_id\" as schema_id, t.\"id\" as table_id, k.\"id\" as
key_id, k.\"type\" as key_type, t.\"system\" as is_system from (select
sk.\"id\", sk.\"table_id\", sk.\"name\", sk.\"type\" from sys.\"keys\" sk union
all select tk.\"id\", tk.\"table_id\", tk.\"name\", tk.\"type\" from
tmp.\"keys\" tk) k inner join (select st.\"id\", st.\"schema_id\", st.\"name\",
st.\"system\" from sys.\"_tabl
es\" st union all select tt.\"id\", tt.\"schema_id\", tt.\"name\",
tt.\"system\" from tmp.\"_tables\" tt) t on k.\"table_id\" = t.\"id\" inner
join sys.\"schemas\" s on t.\"schema_id\" = s.\"id\" order by s.\"name\",
t.\"name\", k.\"name\";", "VIEW", 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
@@ -444,13 +444,13 @@ select 'null in fkeys.delete_action', de
% .%25, .s, .t, ., ., .t, ., ., . #
table_name
% %25, name, name, query, type, system, commit_action, access, comment
# name
% char, varchar, varchar, clob, varchar,
boolean, char, char, varchar # type
-% 11, 18, 34, 6793, 5, 5, 8, 10, 0 # length
+% 11, 18, 34, 6818, 5, 5, 8, 10, 0 # length
[ "sys._tables", "information_schema", "character_sets", "create
view information_schema.character_sets as select cast(null as varchar(1)) as
character_set_catalog, cast(null as varchar(1)) as character_set_schema,
cast('UTF-8' as varchar(16)) as character_set_name, cast('ISO/IEC 10646:2021'
as varchar(20)) as character_repertoire, cast('UTF-8' as varchar(16)) as
form_of_use, cast(null as varchar(1)) as default_collate_catalog, cast(null as
varchar(1)) as default_collate_schema, cast(null as varchar(1)) as
default_collate_name;", "VIEW", true, "COMMIT", "WRITABLE", NULL
]
[ "sys._tables", "information_schema", "check_constraints", "create
view information_schema.check_constraints as select cast(null as varchar(1)) as
constraint_catalog, cast(null as varchar(1024)) as constraint_schema, cast(null
as varchar(1024)) as constraint_name, cast(null as varchar(1024)) as
check_clause where 1=0;", "VIEW", true, "COMMIT", "WRITABLE", NULL
]
-[ "sys._tables", "information_schema", "columns", "create view
information_schema.columns as select cast(null as varchar(1)) as table_catalog,
s.\"name\" as table_schema, t.\"name\" as table_name, c.\"name\" as
column_name, cast(1 + c.\"number\" as int) as ordinal_position, c.\"default\"
as column_default, cast(sys.ifthenelse(c.\"null\", 'YES', 'NO') as varchar(3))
as is_nullable, cast(sys.\"sql_datatype\"(c.\"type\", c.\"type_digits\",
c.\"type_scale\", true, true) as varchar(1024)) as data_type,
cast(sys.ifthenelse(c.\"type\" in ('varchar','clob','char','json','url','xml')
and c.\"type_digits\" > 0, c.\"type_digits\", null) as int) as
character_maximum_length, cast(sys.ifthenelse(c.\"type\" in
('varchar','clob','char','json','url','xml') and c.\"type_digits\" > 0,
c.\"type_digits\" * 4, null) as int) as character_octet_length,
cast(sys.ifthenelse(c.\"type\" in
('int','smallint','tinyint','bigint','hugeint','float','real','double','decimal','numeric','oid'),
c.\"type_digits\", null
) as int) as numeric_precision, cast(sys.ifthenelse(c.\"type\" in
('int','smallint','tinyint','bigint','hugeint','float','real','double','oid'),
2, sys.ifthenelse(c.\"type\" in ('decimal','numeric'), 10, null)) as int) as
numeric_precision_radix, cast(sys.ifthenelse(c.\"type\" in
('int','smallint','tinyint','bigint','hugeint','float','real','double','decimal','numeric','oid'),
c.\"type_scale\", null) as int) as numeric_scale,
cast(sys.ifthenelse(c.\"type\" in
('date','timestamp','timestamptz','time','timetz'),
sys.ifthenelse(c.\"type_scale\" > 0, c.\"type_scale\" -1, 0), null) as int) as
datetime_precision, cast(sys.ifthenelse(c.\"type\" in
('day_interval','month_interval','sec_interval'),
sys.\"sql_datatype\"(c.\"type\", c.\"type_digits\", c.\"type_scale\", true,
true), null) as varchar(40)) as interval_type, cast(case c.\"type\" when
'day_interval' then 0 when 'month_interval' then 0 when 'sec_interval' then
(sys.ifthenelse(c.\"type_digits\" in (7, 10, 12, 13), sys.ifthenelse(c.\"
type_scale\" > 0, c.\"type_scale\", 3), 0)) else null end as int) as
interval_precision, cast(null as varchar(1)) as character_set_catalog,
cast(null as varchar(1)) as character_set_schema,
cast(sys.ifthenelse(c.\"type\" in ('varchar','clob','char','json','url','xml'),
'UTF-8', null) as varchar(16)) as character_set_name, cast(null as varchar(1))
as collation_catalog, cast(null as varchar(1)) as collation_schema, cast(null
as varchar(1)) as collation_name, cast(null as varchar(1)) as domain_catalog,
cast(null as varchar(1)) as domain_schema, cast(null as varchar(1)) as
domain_name, cast(null as varchar(1)) as udt_catalog, cast(null as varchar(1))
as udt_schema, cast(null as varchar(1)) as udt_name, cast(null as varchar(1))
as scope_catalog, cast(null as varchar(1)) as scope_schema, cast(null as
varchar(1)) as scope_name, cast(null as int) as maximum_cardinality, cast(null
as varchar(1)) as dtd_identifier, cast('NO' as varchar(3)) as
is_self_referencing, cast(sys.ifthenelse(seq.\"nam
e\" is null or c.\"null\", 'NO', 'YES') as varchar(3)) as is_identity,
seq.\"name\" as identity_generation, seq.\"start\" as identity_start,
seq.\"increment\" as identity_increment, seq.\"maxvalue\" as identity_maximum,
seq.\"minvalue\" as identity_minimum, cast(sys.ifthenelse(seq.\"name\" is null,
null, sys.ifthenelse(seq.\"cycle\", 'YES', 'NO')) as varchar(3)) as
identity_cycle, cast(sys.ifthenelse(seq.\"name\" is null, 'NO', 'YES') as
varchar(3)) as is_generated, cast(sys.ifthenelse(seq.\"name\" is null, null,
c.\"default\") as varchar(1024)) as generation_expression, cast('NO' as
varchar(3)) as is_system_time_period_start, cast('NO' as varchar(3)) as
is_system_time_period_end, cast('NO' as varchar(3)) as
system_time_period_timestamp_generation, cast(sys.ifthenelse(t.\"type\" in
(0,3,7,20,30), 'YES', 'NO') as varchar(3)) as is_updatable, cast(null as
varchar(1)) as declared_data_type, cast(null as int) as
declared_numeric_precision, cast(null as int) as declared_numeric_scale, t.
\"schema_id\" as schema_id, c.\"table_id\" as table_id, c.\"id\" as column_id,
seq.\"id\" as sequence_id, t.\"system\" as is_system, cm.\"remark\" as comments
from sys.\"columns\" c inner join sys.\"tables\" t on c.\"table_id\" = t.\"id\"
inner join sys.\"schemas\" s on t.\"schema_id\" = s.\"id\" left outer join
sys.\"comments\" cm on c.\"id\" = cm.\"id\" left outer join sys.\"sequences\"
seq on ((seq.\"name\"||'\"') = substring(c.\"default\", 3 +
sys.\"locate\"('\".\"seq_',c.\"default\",14))) order by s.\"name\", t.\"name\",
c.\"number\";", "VIEW", true, "COMMIT", "WRITABLE", NULL ]
-[ "sys._tables", "information_schema", "parameters", "create view
information_schema.parameters as select cast(null as varchar(1)) as
specific_catalog, s.\"name\" as specific_schema,
cast(f.\"name\"||'('||f.\"id\"||')' as varchar(270)) as specific_name,
cast(sys.ifthenelse((a.\"inout\" = 0 or f.\"type\" = 2), 1 + a.\"number\",
sys.ifthenelse(f.\"type\" = 1, a.\"number\", (1 + a.\"number\" -
f.count_out_cols))) as int) as ordinal_position,
cast(sys.ifthenelse(a.\"inout\" = 0, 'OUT', sys.ifthenelse(a.\"inout\" = 1,
'IN', 'INOUT')) as varchar(5)) as parameter_mode,
cast(sys.ifthenelse(a.\"inout\" = 0, 'YES', 'NO') as varchar(3)) as is_result,
cast(null as varchar(1)) as as_locator, a.\"name\" as parameter_name, cast(null
as varchar(1)) as from_sql_specific_catalog, cast(null as varchar(1)) as
from_sql_specific_schema, cast(null as varchar(1)) as from_sql_specific_name,
cast(null as varchar(1)) as to_sql_specific_catalog, cast(null as varchar(1))
as to_sql_specific_schema, cast(null as
varchar(1)) as to_sql_specific_name, cast(sys.\"sql_datatype\"(a.\"type\",
a.\"type_digits\", a.\"type_scale\", true, true) as varchar(1024)) as
data_type, cast(sys.ifthenelse(a.\"type\" in
('varchar','clob','char','json','url','xml') and a.\"type_digits\" > 0,
a.\"type_digits\", null) as int) as character_maximum_length,
cast(sys.ifthenelse(a.\"type\" in ('varchar','clob','char','json','url','xml')
and a.\"type_digits\" > 0, a.\"type_digits\" * 4, null) as int) as
character_octet_length, cast(null as varchar(1)) as character_set_catalog,
cast(null as varchar(1)) as character_set_schema,
cast(sys.ifthenelse(a.\"type\" in ('varchar','clob','char','json','url','xml'),
'UTF-8', null) as varchar(16)) as character_set_name, cast(null as varchar(1))
as collation_catalog, cast(null as varchar(1)) as collation_schema, cast(null
as varchar(1)) as collation_name, cast(sys.ifthenelse(a.\"type\" in
('int','smallint','tinyint','bigint','hugeint','float','real','double','decimal','numeric','oid')
, a.\"type_digits\", null) as int) as numeric_precision,
cast(sys.ifthenelse(a.\"type\" in
('int','smallint','tinyint','bigint','hugeint','float','real','double','oid'),
2, sys.ifthenelse(a.\"type\" in ('decimal','numeric'), 10, null)) as int) as
numeric_precision_radix, cast(sys.ifthenelse(a.\"type\" in
('int','smallint','tinyint','bigint','hugeint','float','real','double','decimal','numeric','oid'),
a.\"type_scale\", null) as int) as numeric_scale,
cast(sys.ifthenelse(a.\"type\" in
('date','timestamp','timestamptz','time','timetz'),
sys.ifthenelse(a.\"type_scale\" > 0, a.\"type_scale\" -1, 0), null) as int) as
datetime_precision, cast(sys.ifthenelse(a.\"type\" in
('day_interval','month_interval','sec_interval'),
sys.\"sql_datatype\"(a.\"type\", a.\"type_digits\", a.\"type_scale\", true,
true), null) as varchar(40)) as interval_type, cast(case a.\"type\" when
'day_interval' then 0 when 'month_interval' then 0 when 'sec_interval' then
(sys.ifthenelse(a.\"type_digits\" in (7, 10, 12,
13), sys.ifthenelse(a.\"type_scale\" > 0, a.\"type_scale\", 3), 0)) else null
end as int) as interval_precision, cast(null as varchar(1)) as udt_catalog,
cast(null as varchar(1)) as udt_schema, cast(null as varchar(1)) as udt_name,
cast(null as varchar(1)) as scope_catalog, cast(null as varchar(1)) as
scope_schema, cast(null as varchar(1)) as scope_name, cast(null as int) as
maximum_cardinality, cast(null as varchar(1)) as dtd_identifier, cast(null as
varchar(1)) as declared_data_type, cast(null as int) as
declared_numeric_precision, cast(null as int) as declared_numeric_scale,
cast(null as varchar(1)) as parameter_default, f.\"schema_id\" as schema_id,
f.\"id\" as function_id, a.\"id\" as arg_id, f.\"name\" as function_name,
f.\"type\" as function_type, f.\"system\" as is_system from sys.\"args\" a
inner join (select fun.*, (select count(*) from sys.args a0 where a0.inout = 0
and a0.func_id = fun.id) as count_out_cols from sys.\"functions\" fun where
fun.\"type\" in (1, 2, 5, 7))
f on f.\"id\" = a.\"func_id\" inner join sys.\"schemas\" s on s.\"id\" =
f.\"schema_id\" order by s.\"name\", f.\"name\", f.\"id\", a.\"inout\" desc,
a.\"number\";", "VIEW", true, "COMMIT", "WRITABLE", NULL ]
+[ "sys._tables", "information_schema", "columns", "create view
information_schema.columns as select cast(null as varchar(1)) as table_catalog,
s.\"name\" as table_schema, t.\"name\" as table_name, c.\"name\" as
column_name, cast(1 + c.\"number\" as int) as ordinal_position, c.\"default\"
as column_default, cast(sys.ifthenelse(c.\"null\", 'YES', 'NO') as varchar(3))
as is_nullable, cast(sys.\"sql_datatype\"(c.\"type\", c.\"type_digits\",
c.\"type_scale\", true, true) as varchar(1024)) as data_type,
cast(sys.ifthenelse(c.\"type\" in ('varchar','clob','char','json','url','xml')
and c.\"type_digits\" > 0, c.\"type_digits\", null) as int) as
character_maximum_length, cast(sys.ifthenelse(c.\"type\" in
('varchar','clob','char','json','url','xml') and c.\"type_digits\" > 0,
sys.sql_min(4 * c.\"type_digits\", 2147483647), null) as int) as
character_octet_length, cast(sys.ifthenelse(c.\"type\" in
('int','smallint','tinyint','bigint','hugeint','float','real','double','decimal','numeric','oid')
, c.\"type_digits\", null) as int) as numeric_precision,
cast(sys.ifthenelse(c.\"type\" in
('int','smallint','tinyint','bigint','hugeint','float','real','double','oid'),
2, sys.ifthenelse(c.\"type\" in ('decimal','numeric'), 10, null)) as int) as
numeric_precision_radix, cast(sys.ifthenelse(c.\"type\" in
('int','smallint','tinyint','bigint','hugeint','float','real','double','decimal','numeric','oid'),
c.\"type_scale\", null) as int) as numeric_scale,
cast(sys.ifthenelse(c.\"type\" in
('date','timestamp','timestamptz','time','timetz'),
sys.ifthenelse(c.\"type_scale\" > 0, c.\"type_scale\" -1, 0), null) as int) as
datetime_precision, cast(sys.ifthenelse(c.\"type\" in
('day_interval','month_interval','sec_interval'),
sys.\"sql_datatype\"(c.\"type\", c.\"type_digits\", c.\"type_scale\", true,
true), null) as varchar(40)) as interval_type, cast(case c.\"type\" when
'day_interval' then 0 when 'month_interval' then 0 when 'sec_interval' then
(sys.ifthenelse(c.\"type_digits\" in (7, 10, 12,
13), sys.ifthenelse(c.\"type_scale\" > 0, c.\"type_scale\", 3), 0)) else null
end as int) as interval_precision, cast(null as varchar(1)) as
character_set_catalog, cast(null as varchar(1)) as character_set_schema,
cast(sys.ifthenelse(c.\"type\" in ('varchar','clob','char','json','url','xml'),
'UTF-8', null) as varchar(16)) as character_set_name, cast(null as varchar(1))
as collation_catalog, cast(null as varchar(1)) as collation_schema, cast(null
as varchar(1)) as collation_name, cast(null as varchar(1)) as domain_catalog,
cast(null as varchar(1)) as domain_schema, cast(null as varchar(1)) as
domain_name, cast(null as varchar(1)) as udt_catalog, cast(null as varchar(1))
as udt_schema, cast(null as varchar(1)) as udt_name, cast(null as varchar(1))
as scope_catalog, cast(null as varchar(1)) as scope_schema, cast(null as
varchar(1)) as scope_name, cast(null as int) as maximum_cardinality, cast(null
as varchar(1)) as dtd_identifier, cast('NO' as varchar(3)) as
is_self_referencing, cast
(sys.ifthenelse(seq.\"name\" is null or c.\"null\", 'NO', 'YES') as
varchar(3)) as is_identity, seq.\"name\" as identity_generation, seq.\"start\"
as identity_start, seq.\"increment\" as identity_increment, seq.\"maxvalue\" as
identity_maximum, seq.\"minvalue\" as identity_minimum,
cast(sys.ifthenelse(seq.\"name\" is null, null, sys.ifthenelse(seq.\"cycle\",
'YES', 'NO')) as varchar(3)) as identity_cycle,
cast(sys.ifthenelse(seq.\"name\" is null, 'NO', 'YES') as varchar(3)) as
is_generated, cast(sys.ifthenelse(seq.\"name\" is null, null, c.\"default\") as
varchar(1024)) as generation_expression, cast('NO' as varchar(3)) as
is_system_time_period_start, cast('NO' as varchar(3)) as
is_system_time_period_end, cast('NO' as varchar(3)) as
system_time_period_timestamp_generation, cast(sys.ifthenelse(t.\"type\" in
(0,3,7,20,30), 'YES', 'NO') as varchar(3)) as is_updatable, cast(null as
varchar(1)) as declared_data_type, cast(null as int) as
declared_numeric_precision, cast(null as int) as d
eclared_numeric_scale, t.\"schema_id\" as schema_id, c.\"table_id\" as
table_id, c.\"id\" as column_id, seq.\"id\" as sequence_id, t.\"system\" as
is_system, cm.\"remark\" as comments from sys.\"columns\" c inner join
sys.\"tables\" t on c.\"table_id\" = t.\"id\" inner join sys.\"schemas\" s on
t.\"schema_id\" = s.\"id\" left outer join sys.\"comments\" cm on c.\"id\" =
cm.\"id\" left outer join sys.\"sequences\" seq on ((seq.\"name\"||'\"') =
substring(c.\"default\", 3 + sys.\"locate\"('\".\"seq_',c.\"default\",14)))
order by s.\"name\", t.\"name\", c.\"number\";", "VIEW", true, "COMMIT",
"WRITABLE", NULL ]
+[ "sys._tables", "information_schema", "parameters", "create view
information_schema.parameters as select cast(null as varchar(1)) as
specific_catalog, s.\"name\" as specific_schema,
cast(f.\"name\"||'('||f.\"id\"||')' as varchar(270)) as specific_name,
cast(sys.ifthenelse((a.\"inout\" = 0 or f.\"type\" = 2), 1 + a.\"number\",
sys.ifthenelse(f.\"type\" = 1, a.\"number\", (1 + a.\"number\" -
f.count_out_cols))) as int) as ordinal_position,
cast(sys.ifthenelse(a.\"inout\" = 0, 'OUT', sys.ifthenelse(a.\"inout\" = 1,
'IN', 'INOUT')) as varchar(5)) as parameter_mode,
cast(sys.ifthenelse(a.\"inout\" = 0, 'YES', 'NO') as varchar(3)) as is_result,
cast(null as varchar(1)) as as_locator, a.\"name\" as parameter_name, cast(null
as varchar(1)) as from_sql_specific_catalog, cast(null as varchar(1)) as
from_sql_specific_schema, cast(null as varchar(1)) as from_sql_specific_name,
cast(null as varchar(1)) as to_sql_specific_catalog, cast(null as varchar(1))
as to_sql_specific_schema, cast(null as
varchar(1)) as to_sql_specific_name, cast(sys.\"sql_datatype\"(a.\"type\",
a.\"type_digits\", a.\"type_scale\", true, true) as varchar(1024)) as
data_type, cast(sys.ifthenelse(a.\"type\" in
('varchar','clob','char','json','url','xml') and a.\"type_digits\" > 0,
a.\"type_digits\", null) as int) as character_maximum_length,
cast(sys.ifthenelse(a.\"type\" in ('varchar','clob','char','json','url','xml')
and a.\"type_digits\" > 0, sys.sql_min(4 * a.\"type_digits\", 2147483647),
null) as int) as character_octet_length, cast(null as varchar(1)) as
character_set_catalog, cast(null as varchar(1)) as character_set_schema,
cast(sys.ifthenelse(a.\"type\" in ('varchar','clob','char','json','url','xml'),
'UTF-8', null) as varchar(16)) as character_set_name, cast(null as varchar(1))
as collation_catalog, cast(null as varchar(1)) as collation_schema, cast(null
as varchar(1)) as collation_name, cast(sys.ifthenelse(a.\"type\" in
('int','smallint','tinyint','bigint','hugeint','float','real','double','
decimal','numeric','oid'), a.\"type_digits\", null) as int) as
numeric_precision, cast(sys.ifthenelse(a.\"type\" in
('int','smallint','tinyint','bigint','hugeint','float','real','double','oid'),
2, sys.ifthenelse(a.\"type\" in ('decimal','numeric'), 10, null)) as int) as
numeric_precision_radix, cast(sys.ifthenelse(a.\"type\" in
('int','smallint','tinyint','bigint','hugeint','float','real','double','decimal','numeric','oid'),
a.\"type_scale\", null) as int) as numeric_scale,
cast(sys.ifthenelse(a.\"type\" in
('date','timestamp','timestamptz','time','timetz'),
sys.ifthenelse(a.\"type_scale\" > 0, a.\"type_scale\" -1, 0), null) as int) as
datetime_precision, cast(sys.ifthenelse(a.\"type\" in
('day_interval','month_interval','sec_interval'),
sys.\"sql_datatype\"(a.\"type\", a.\"type_digits\", a.\"type_scale\", true,
true), null) as varchar(40)) as interval_type, cast(case a.\"type\" when
'day_interval' then 0 when 'month_interval' then 0 when 'sec_interval' then
(sys.ifthenelse(a.\"typ
e_digits\" in (7, 10, 12, 13), sys.ifthenelse(a.\"type_scale\" > 0,
a.\"type_scale\", 3), 0)) else null end as int) as interval_precision,
cast(null as varchar(1)) as udt_catalog, cast(null as varchar(1)) as
udt_schema, cast(null as varchar(1)) as udt_name, cast(null as varchar(1)) as
scope_catalog, cast(null as varchar(1)) as scope_schema, cast(null as
varchar(1)) as scope_name, cast(null as int) as maximum_cardinality, cast(null
as varchar(1)) as dtd_identifier, cast(null as varchar(1)) as
declared_data_type, cast(null as int) as declared_numeric_precision, cast(null
as int) as declared_numeric_scale, cast(null as varchar(1)) as
parameter_default, f.\"schema_id\" as schema_id, f.\"id\" as function_id,
a.\"id\" as arg_id, f.\"name\" as function_name, f.\"type\" as function_type,
f.\"system\" as is_system from sys.\"args\" a inner join (select fun.*, (select
count(*) from sys.args a0 where a0.inout = 0 and a0.func_id = fun.id) as
count_out_cols from sys.\"functions\" fun where fun.\
"type\" in (1, 2, 5, 7)) f on f.\"id\" = a.\"func_id\" inner join
sys.\"schemas\" s on s.\"id\" = f.\"schema_id\" order by s.\"name\",
f.\"name\", f.\"id\", a.\"inout\" desc, a.\"number\";", "VIEW", true,
"COMMIT", "WRITABLE", NULL ]
[ "sys._tables", "information_schema", "referential_constraints",
"create view information_schema.referential_constraints as select cast(null as
varchar(1)) as constraint_catalog, s.\"name\" as constraint_schema, fk.\"name\"
as constraint_name, cast(null as varchar(1)) as unique_constraint_catalog,
uks.\"name\" as unique_constraint_schema, uk.\"name\" as
unique_constraint_name, cast('FULL' as varchar(7)) as match_option,
fk.\"update_action\" as update_rule, fk.\"delete_action\" as delete_rule,
t.\"schema_id\" as fk_schema_id, t.\"id\" as fk_table_id, t.\"name\" as
fk_table_name, fk.\"id\" as fk_key_id, ukt.\"schema_id\" as uc_schema_id,
uk.\"table_id\" as uc_table_id, ukt.\"name\" as uc_table_name, uk.\"id\" as
uc_key_id from sys.\"fkeys\" fk inner join sys.\"tables\" t on t.\"id\" =
fk.\"table_id\" inner join sys.\"schemas\" s on s.\"id\" = t.\"schema_id\" left
outer join sys.\"keys\" uk on uk.\"id\" = fk.\"rkey\" left outer join
sys.\"tables\" ukt on ukt.\"id\" = uk.\"table_id\" l
eft outer join sys.\"schemas\" uks on uks.\"id\" = ukt.\"schema_id\" order by
s.\"name\", t.\"name\", fk.\"name\";", "VIEW", true, "COMMIT",
"WRITABLE", NULL ]
-[ "sys._tables", "information_schema", "routines", "create view
information_schema.routines as select cast(null as varchar(1)) as
specific_catalog, s.\"name\" as specific_schema,
cast(f.\"name\"||'('||f.\"id\"||')' as varchar(270)) as specific_name,
cast(null as varchar(1)) as routine_catalog, s.\"name\" as routine_schema,
f.\"name\" as routine_name, ft.\"function_type_keyword\" as routine_type,
cast(null as varchar(1)) as module_catalog, cast(null as varchar(1)) as
module_schema, cast(f.\"mod\" as varchar(128)) as module_name, cast(null as
varchar(1)) as udt_catalog, cast(null as varchar(1)) as udt_schema, cast(null
as varchar(1)) as udt_name, cast(case f.\"type\" when 1 then
sys.\"sql_datatype\"(a.\"type\", a.\"type_digits\", a.\"type_scale\", true,
true) when 2 then null when 5 then 'TABLE' when 7 then 'TABLE' else null end as
varchar(1024)) as data_type, cast(sys.ifthenelse(a.\"type\" in
('varchar','clob','char','json','url','xml') and a.\"type_digits\" > 0,
a.\"type_digits\", n
ull) as int) as character_maximum_length, cast(sys.ifthenelse(a.\"type\" in
('varchar','clob','char','json','url','xml') and a.\"type_digits\" > 0,
a.\"type_digits\" * 4, null) as int) as character_octet_length, cast(null as
varchar(1)) as character_set_catalog, cast(null as varchar(1)) as
character_set_schema, 'UTF-8' as character_set_name, cast(null as varchar(1))
as collation_catalog, cast(null as varchar(1)) as collation_schema, cast(null
as varchar(1)) as collation_name, cast(sys.ifthenelse(a.\"type\" in
('int','smallint','tinyint','bigint','hugeint','float','real','double','decimal','numeric','oid'),
a.\"type_digits\", null) as int) as numeric_precision,
cast(sys.ifthenelse(a.\"type\" in
('int','smallint','tinyint','bigint','hugeint','float','real','double','oid'),
2, sys.ifthenelse(a.\"type\" in ('decimal','numeric'), 10, null)) as int) as
numeric_precision_radix, cast(sys.ifthenelse(a.\"type\" in
('int','smallint','tinyint','bigint','hugeint','float','real','double','decimal
','numeric','oid'), a.\"type_scale\", null) as int) as numeric_scale,
cast(sys.ifthenelse(a.\"type\" in
('date','timestamp','timestamptz','time','timetz'), a.\"type_scale\" -1, null)
as int) as datetime_precision, cast(sys.ifthenelse(a.\"type\" in
('day_interval','month_interval','sec_interval'),
sys.\"sql_datatype\"(a.\"type\", a.\"type_digits\", a.\"type_scale\", true,
true), null) as varchar(40)) as interval_type, cast(case a.\"type\" when
'day_interval' then 0 when 'month_interval' then 0 when 'sec_interval' then
(sys.ifthenelse(a.\"type_digits\" in (7, 10, 12, 13),
sys.ifthenelse(a.\"type_scale\" > 0, a.\"type_scale\", 3), 0)) else null end as
int) as interval_precision, cast(null as varchar(1)) as type_udt_catalog,
cast(null as varchar(1)) as type_udt_schema, cast(null as varchar(1)) as
type_udt_name, cast(null as varchar(1)) as scope_catalog, cast(null as
varchar(1)) as scope_schema, cast(null as varchar(1)) as scope_name, cast(null
as int) as maximum_cardinality, cast(null a
s int) as dtd_identifier,
cast(sys.\"ifthenelse\"(sys.\"locate\"('begin',f.\"func\") > 0,
sys.\"ifthenelse\"(sys.\"endswith\"(f.\"func\",';'),
sys.\"substring\"(f.\"func\", sys.\"locate\"('begin',f.\"func\"),
sys.\"length\"(sys.\"substring\"(f.\"func\",
sys.\"locate\"('begin',f.\"func\")))-1), sys.\"substring\"(f.\"func\",
sys.\"locate\"('begin',f.\"func\"))), null) as varchar(8196)) as routine_body,
f.\"func\" as routine_definition,
cast(sys.\"ifthenelse\"(sys.\"locate\"('external name',f.\"func\") > 0,
sys.\"ifthenelse\"(sys.\"endswith\"(f.\"func\",';'),
sys.\"substring\"(f.\"func\", 14 + sys.\"locate\"('external name',f.\"func\"),
sys.\"length\"(sys.\"substring\"(f.\"func\", 14 + sys.\"locate\"('external
name',f.\"func\")))-1), sys.\"substring\"(f.\"func\", 14 +
sys.\"locate\"('external name',f.\"func\"))), null) as varchar(1024)) as
external_name, fl.\"language_keyword\" as external_language, 'GENERAL' as
parameter_style, 'YES' as is_deterministic, cast(sys.ifthenelse(f.\"side_e
ffect\", 'MODIFIES', 'READ') as varchar(10)) as sql_data_access, cast(case
f.\"type\" when 2 then null else 'NO' end as varchar(3)) as is_null_call,
cast(null as varchar(1)) as sql_path, cast(null as varchar(1)) as
schema_level_routine, cast(null as int) as max_dynamic_result_sets, cast(null
as varchar(1)) as is_user_defined_cast, cast(null as varchar(1)) as
is_implicitly_invocable, cast(null as varchar(1)) as security_type, cast(null
as varchar(1)) as to_sql_specific_catalog, cast(null as varchar(1)) as
to_sql_specific_schema, cast(null as varchar(1)) as to_sql_specific_name,
cast(null as varchar(1)) as as_locator, cast(null as timestamp) as created,
cast(null as timestamp) as last_altered, cast(null as varchar(1)) as
new_savepoint_level, cast(null as varchar(1)) as is_udt_dependent, cast(null as
varchar(1)) as result_cast_from_data_type, cast(null as varchar(1)) as
result_cast_as_locator, cast(null as int) as result_cast_char_max_length,
cast(null as int) as result_cast_char_octet
_length, cast(null as varchar(1)) as result_cast_char_set_catalog, cast(null
as varchar(1)) as result_cast_char_set_schema, cast(null as varchar(1)) as
result_cast_character_set_name, cast(null as varchar(1)) as
result_cast_collation_catalog, cast(null as varchar(1)) as
result_cast_collation_schema, cast(null as varchar(1)) as
result_cast_collation_name, cast(null as int) as result_cast_numeric_precision,
cast(null as int) as result_cast_numeric_radix, cast(null as int) as
result_cast_numeric_scale, cast(null as int) as result_cast_datetime_precision,
cast(null as varchar(1)) as result_cast_interval_type, cast(null as int) as
result_cast_interval_precision, cast(null as varchar(1)) as
result_cast_type_udt_catalog, cast(null as varchar(1)) as
result_cast_type_udt_schema, cast(null as varchar(1)) as
result_cast_type_udt_name, cast(null as varchar(1)) as
result_cast_scope_catalog, cast(null as varchar(1)) as
result_cast_scope_schema, cast(null as varchar(1)) as result_cast_scope_name,
cast(null as int) as result_cast_max_cardinality, cast(null as varchar(1)) as
result_cast_dtd_identifier, cast(null as varchar(1)) as declared_data_type,
cast(null as int) as declared_numeric_precision, cast(null as int) as
declared_numeric_scale, cast(null as varchar(1)) as
result_cast_from_declared_data_type, cast(null as int) as
result_cast_declared_numeric_precision, cast(null as int) as
result_cast_declared_numeric_scale, f.\"schema_id\" as schema_id, f.\"id\" as
function_id, f.\"type\" as function_type, f.\"language\" as function_language,
f.\"system\" as is_system, cm.\"remark\" as comments from sys.\"functions\" f
inner join sys.\"schemas\" s on s.\"id\" = f.\"schema_id\" inner join
sys.\"function_types\" ft on ft.\"function_type_id\" = f.\"type\" inner join
sys.\"function_languages\" fl on fl.\"language_id\" = f.\"language\" left outer
join sys.\"args\" a on a.\"func_id\" = f.\"id\" and a.\"inout\" = 0 and
a.\"number\" = 0 left outer join sys.\"comments\" cm on cm.\"id\" =
f.\"id\" where f.\"type\" in (1, 2, 5, 7) order by s.\"name\", f.\"name\";",
"VIEW", true, "COMMIT", "WRITABLE", NULL ]
+[ "sys._tables", "information_schema", "routines", "create view
information_schema.routines as select cast(null as varchar(1)) as
specific_catalog, s.\"name\" as specific_schema,
cast(f.\"name\"||'('||f.\"id\"||')' as varchar(270)) as specific_name,
cast(null as varchar(1)) as routine_catalog, s.\"name\" as routine_schema,
f.\"name\" as routine_name, ft.\"function_type_keyword\" as routine_type,
cast(null as varchar(1)) as module_catalog, cast(null as varchar(1)) as
module_schema, cast(f.\"mod\" as varchar(128)) as module_name, cast(null as
varchar(1)) as udt_catalog, cast(null as varchar(1)) as udt_schema, cast(null
as varchar(1)) as udt_name, cast(case f.\"type\" when 1 then
sys.\"sql_datatype\"(a.\"type\", a.\"type_digits\", a.\"type_scale\", true,
true) when 2 then null when 5 then 'TABLE' when 7 then 'TABLE' else null end as
varchar(1024)) as data_type, cast(sys.ifthenelse(a.\"type\" in
('varchar','clob','char','json','url','xml') and a.\"type_digits\" > 0,
a.\"type_digits\", n
ull) as int) as character_maximum_length, cast(sys.ifthenelse(a.\"type\" in
('varchar','clob','char','json','url','xml') and a.\"type_digits\" > 0,
sys.sql_min(4 * a.\"type_digits\", 2147483647), null) as int) as
character_octet_length, cast(null as varchar(1)) as character_set_catalog,
cast(null as varchar(1)) as character_set_schema, 'UTF-8' as
character_set_name, cast(null as varchar(1)) as collation_catalog, cast(null as
varchar(1)) as collation_schema, cast(null as varchar(1)) as collation_name,
cast(sys.ifthenelse(a.\"type\" in
('int','smallint','tinyint','bigint','hugeint','float','real','double','decimal','numeric','oid'),
a.\"type_digits\", null) as int) as numeric_precision,
cast(sys.ifthenelse(a.\"type\" in
('int','smallint','tinyint','bigint','hugeint','float','real','double','oid'),
2, sys.ifthenelse(a.\"type\" in ('decimal','numeric'), 10, null)) as int) as
numeric_precision_radix, cast(sys.ifthenelse(a.\"type\" in
('int','smallint','tinyint','bigint','hugeint','float'
,'real','double','decimal','numeric','oid'), a.\"type_scale\", null) as int)
as numeric_scale, cast(sys.ifthenelse(a.\"type\" in
('date','timestamp','timestamptz','time','timetz'), a.\"type_scale\" -1, null)
as int) as datetime_precision, cast(sys.ifthenelse(a.\"type\" in
('day_interval','month_interval','sec_interval'),
sys.\"sql_datatype\"(a.\"type\", a.\"type_digits\", a.\"type_scale\", true,
true), null) as varchar(40)) as interval_type, cast(case a.\"type\" when
'day_interval' then 0 when 'month_interval' then 0 when 'sec_interval' then
(sys.ifthenelse(a.\"type_digits\" in (7, 10, 12, 13),
sys.ifthenelse(a.\"type_scale\" > 0, a.\"type_scale\", 3), 0)) else null end as
int) as interval_precision, cast(null as varchar(1)) as type_udt_catalog,
cast(null as varchar(1)) as type_udt_schema, cast(null as varchar(1)) as
type_udt_name, cast(null as varchar(1)) as scope_catalog, cast(null as
varchar(1)) as scope_schema, cast(null as varchar(1)) as scope_name, cast(null
as int) as maximum
_cardinality, cast(null as int) as dtd_identifier,
cast(sys.\"ifthenelse\"(sys.\"locate\"('begin',f.\"func\") > 0,
sys.\"ifthenelse\"(sys.\"endswith\"(f.\"func\",';'),
sys.\"substring\"(f.\"func\", sys.\"locate\"('begin',f.\"func\"),
sys.\"length\"(sys.\"substring\"(f.\"func\",
sys.\"locate\"('begin',f.\"func\")))-1), sys.\"substring\"(f.\"func\",
sys.\"locate\"('begin',f.\"func\"))), null) as varchar(8196)) as routine_body,
f.\"func\" as routine_definition,
cast(sys.\"ifthenelse\"(sys.\"locate\"('external name',f.\"func\") > 0,
sys.\"ifthenelse\"(sys.\"endswith\"(f.\"func\",';'),
sys.\"substring\"(f.\"func\", 14 + sys.\"locate\"('external name',f.\"func\"),
sys.\"length\"(sys.\"substring\"(f.\"func\", 14 + sys.\"locate\"('external
name',f.\"func\")))-1), sys.\"substring\"(f.\"func\", 14 +
sys.\"locate\"('external name',f.\"func\"))), null) as varchar(1024)) as
external_name, fl.\"language_keyword\" as external_language, 'GENERAL' as
parameter_style, 'YES' as is_deterministic, cast(
sys.ifthenelse(f.\"side_effect\", 'MODIFIES', 'READ') as varchar(10)) as
sql_data_access, cast(case f.\"type\" when 2 then null else 'NO' end as
varchar(3)) as is_null_call, cast(null as varchar(1)) as sql_path, cast(null as
varchar(1)) as schema_level_routine, cast(null as int) as
max_dynamic_result_sets, cast(null as varchar(1)) as is_user_defined_cast,
cast(null as varchar(1)) as is_implicitly_invocable, cast(null as varchar(1))
as security_type, cast(null as varchar(1)) as to_sql_specific_catalog,
cast(null as varchar(1)) as to_sql_specific_schema, cast(null as varchar(1)) as
to_sql_specific_name, cast(null as varchar(1)) as as_locator, cast(null as
timestamp) as created, cast(null as timestamp) as last_altered, cast(null as
varchar(1)) as new_savepoint_level, cast(null as varchar(1)) as
is_udt_dependent, cast(null as varchar(1)) as result_cast_from_data_type,
cast(null as varchar(1)) as result_cast_as_locator, cast(null as int) as
result_cast_char_max_length, cast(null as int)
as result_cast_char_octet_length, cast(null as varchar(1)) as
result_cast_char_set_catalog, cast(null as varchar(1)) as
result_cast_char_set_schema, cast(null as varchar(1)) as
result_cast_character_set_name, cast(null as varchar(1)) as
result_cast_collation_catalog, cast(null as varchar(1)) as
result_cast_collation_schema, cast(null as varchar(1)) as
result_cast_collation_name, cast(null as int) as result_cast_numeric_precision,
cast(null as int) as result_cast_numeric_radix, cast(null as int) as
result_cast_numeric_scale, cast(null as int) as result_cast_datetime_precision,
cast(null as varchar(1)) as result_cast_interval_type, cast(null as int) as
result_cast_interval_precision, cast(null as varchar(1)) as
result_cast_type_udt_catalog, cast(null as varchar(1)) as
result_cast_type_udt_schema, cast(null as varchar(1)) as
result_cast_type_udt_name, cast(null as varchar(1)) as
result_cast_scope_catalog, cast(null as varchar(1)) as
result_cast_scope_schema, cast(null as varchar(1)) as
result_cast_scope_name, cast(null as int) as result_cast_max_cardinality,
cast(null as varchar(1)) as result_cast_dtd_identifier, cast(null as
varchar(1)) as declared_data_type, cast(null as int) as
declared_numeric_precision, cast(null as int) as declared_numeric_scale,
cast(null as varchar(1)) as result_cast_from_declared_data_type, cast(null as
int) as result_cast_declared_numeric_precision, cast(null as int) as
result_cast_declared_numeric_scale, f.\"schema_id\" as schema_id, f.\"id\" as
function_id, f.\"type\" as function_type, f.\"language\" as function_language,
f.\"system\" as is_system, cm.\"remark\" as comments from sys.\"functions\" f
inner join sys.\"schemas\" s on s.\"id\" = f.\"schema_id\" inner join
sys.\"function_types\" ft on ft.\"function_type_id\" = f.\"type\" inner join
sys.\"function_languages\" fl on fl.\"language_id\" = f.\"language\" left outer
join sys.\"args\" a on a.\"func_id\" = f.\"id\" and a.\"inout\" = 0 and
a.\"number\" = 0 left outer join sys.\"comm
ents\" cm on cm.\"id\" = f.\"id\" where f.\"type\" in (1, 2, 5, 7) order by
s.\"name\", f.\"name\";", "VIEW", true, "COMMIT", "WRITABLE", NULL
]
[ "sys._tables", "information_schema", "schemata", "create view
information_schema.schemata as select cast(null as varchar(1)) as catalog_name,
s.\"name\" as schema_name, a.\"name\" as schema_owner, cast(null as varchar(1))
as default_character_set_catalog, cast(null as varchar(1)) as
default_character_set_schema, cast('UTF-8' as varchar(16)) as
default_character_set_name, cast(null as varchar(1)) as sql_path, s.\"id\" as
schema_id, s.\"system\" as is_system, cm.\"remark\" as comments from
sys.\"schemas\" s inner join sys.\"auths\" a on s.\"owner\" = a.\"id\" left
outer join sys.\"comments\" cm on s.\"id\" = cm.\"id\" order by s.\"name\";",
"VIEW", true, "COMMIT", "WRITABLE", NULL ]
[ "sys._tables", "information_schema", "sequences", "create view
information_schema.sequences as select cast(null as varchar(1)) as
sequence_catalog, s.\"name\" as sequence_schema, sq.\"name\" as sequence_name,
cast('BIGINT' as varchar(16)) as data_type, cast(64 as smallint) as
numeric_precision, cast(2 as smallint) as numeric_precision_radix, cast(0 as
smallint) as numeric_scale, sq.\"start\" as start_value, sq.\"minvalue\" as
minimum_value, sq.\"maxvalue\" as maximum_value, sq.\"increment\" as increment,
cast(sys.ifthenelse(sq.\"cycle\", 'YES', 'NO') as varchar(3)) as cycle_option,
cast(null as varchar(16)) as declared_data_type, cast(null as smallint) as
declared_numeric_precision, cast(null as smallint) as declared_numeric_scale,
sq.\"schema_id\" as schema_id, sq.\"id\" as sequence_id,
get_value_for(s.\"name\", sq.\"name\") as current_value, sq.\"cacheinc\" as
cacheinc, cm.\"remark\" as comments from sys.\"sequences\" sq inner join
sys.\"schemas\" s on sq.\"schema_id\" = s.\"id\
" left outer join sys.\"comments\" cm on sq.\"id\" = cm.\"id\" order by
s.\"name\", sq.\"name\";", "VIEW", true, "COMMIT", "WRITABLE",
NULL ]
[ "sys._tables", "information_schema", "table_constraints", "create
view information_schema.table_constraints as select cast(null as varchar(1)) as
constraint_catalog, s.\"name\" as constraint_schema, k.\"name\" as
constraint_name, cast(null as varchar(1)) as table_catalog, s.\"name\" as
table_schema, t.\"name\" as table_name, cast(case k.\"type\" when 0 then
'PRIMARY KEY' when 1 then 'UNIQUE' when 2 then 'FOREIGN KEY' else null end as
varchar(16)) as constraint_type, cast('NO' as varchar(3)) as is_deferrable,
cast('NO' as varchar(3)) as initially_deferred, cast('YES' as varchar(3)) as
enforced, t.\"schema_id\" as schema_id, t.\"id\" as table_id, k.\"id\" as
key_id, k.\"type\" as key_type, t.\"system\" as is_system from (select
sk.\"id\", sk.\"table_id\", sk.\"name\", sk.\"type\" from sys.\"keys\" sk union
all select tk.\"id\", tk.\"table_id\", tk.\"name\", tk.\"type\" from
tmp.\"keys\" tk) k inner join (select st.\"id\", st.\"schema_id\", st.\"name\",
st.\"system\" from sys.\"_tabl
es\" st union all select tt.\"id\", tt.\"schema_id\", tt.\"name\",
tt.\"system\" from tmp.\"_tables\" tt) t on k.\"table_id\" = t.\"id\" inner
join sys.\"schemas\" s on t.\"schema_id\" = s.\"id\" order by s.\"name\",
t.\"name\", k.\"name\";", "VIEW", true, "COMMIT", "WRITABLE",
NULL ]
diff --git a/sql/test/information-schema/Tests/columns.test
b/sql/test/information-schema/Tests/columns.test
--- a/sql/test/information-schema/Tests/columns.test
+++ b/sql/test/information-schema/Tests/columns.test
@@ -173,3 +173,48 @@ SELECT COUNT(*) AS duplicates, TABLE_CAT
HAVING COUNT(*) > 1
----
+
+statement ok
+create temp table tlargechar (c1 varchar(2147483647), c2 char(2147483646), c3
clob(2147483645), c4 json(2147483644), c5 url(2147483643))
+
+query TTTITII rowsort
+SELECT TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, ORDINAL_POSITION, DATA_TYPE,
CHARACTER_MAXIMUM_LENGTH, CHARACTER_OCTET_LENGTH
+ FROM INFORMATION_SCHEMA.COLUMNS
+ WHERE NOT is_system AND TABLE_NAME = 'tlargechar'
+----
+tmp
+tlargechar
+c1
+1
+VARCHAR
+2147483647
+2147483647
+tmp
+tlargechar
+c2
+2
+CHAR
+2147483646
+2147483647
+tmp
+tlargechar
+c3
+3
+CLOB
+2147483645
+2147483647
+tmp
+tlargechar
+c4
+4
+JSON
+2147483644
+2147483647
+tmp
+tlargechar
+c5
+5
+URL
+2147483643
+2147483647
+
_______________________________________________
checkin-list mailing list -- checkin-list@monetdb.org
To unsubscribe send an email to checkin-list-le...@monetdb.org
