Changeset: d624ab51d772 for MonetDB
URL: https://dev.monetdb.org/hg/MonetDB/rev/d624ab51d772
Modified Files:
sql/backends/monet5/sql_upgrades.c
sql/scripts/91_information_schema.sql
sql/test/Dependencies/Tests/dependency_owner_schema_3.test
sql/test/bugs/Tests/groupby_having-bug-sf-947600.test
sql/test/bugs/Tests/innerjoin_multiple-bug-sf-943661.test
sql/test/emptydb/Tests/check.stable.out
sql/test/emptydb/Tests/check.stable.out.32bit
sql/test/emptydb/Tests/check.stable.out.int128
Branch: default
Log Message:
Improved implementation of view INFORMATION_SCHEMA.COLUMNS for columns
DATETIME_PRECISION, IS_IDENTITY, IDENTITY_GENERATION, IDENTITY_START,
IDENTITY_INCREMENT, IDENTITY_MAXIMUM, IDENTITY_MINIMUM, IDENTITY_CYCLE,
IS_GENERATED, GENERATION_EXPRESSION.
The identity column information is pulled from the sequence definition which is
generated when using a serial data type.
diffs (truncated from 418 to 300 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
@@ -6073,7 +6073,6 @@ sql_update_default(Client c, mvc *sql, s
" cast(NULL AS varchar(1)) AS DEFAULT_CHARACTER_SET_SCHEMA,\n"
" cast('UTF-8' AS varchar(16)) AS
DEFAULT_CHARACTER_SET_NAME,\n"
" cast(NULL AS varchar(1)) AS SQL_PATH,\n"
- " -- MonetDB column extensions\n"
" s.\"id\" AS schema_id,\n"
" s.\"system\" AS is_system,\n"
" cm.\"remark\" AS comments\n"
@@ -6096,7 +6095,6 @@ sql_update_default(Client c, mvc *sql, s
" cast(sys.ifthenelse((t.\"type\" IN (0, 3, 7, 20, 30) AND
t.\"access\" IN (0, 2)), 'YES', 'NO') AS varchar(3)) AS IS_INSERTABLE_INTO,\n"
" cast('NO' AS varchar(3)) AS IS_TYPED,\n"
" cast((CASE t.\"commit_action\" WHEN 1 THEN 'DELETE' WHEN 2
THEN 'PRESERVE' WHEN 3 THEN 'DROP' ELSE NULL END) AS varchar(10)) AS
COMMIT_ACTION,\n"
- " -- MonetDB column extensions\n"
" t.\"schema_id\" AS schema_id,\n"
" t.\"id\" AS table_id,\n"
" t.\"type\" AS table_type_id,\n"
@@ -6124,7 +6122,6 @@ sql_update_default(Client c, mvc *sql, s
" cast('NO' AS varchar(3)) AS IS_TRIGGER_UPDATABLE,\n"
" cast('NO' AS varchar(3)) AS IS_TRIGGER_DELETABLE,\n"
" cast('NO' AS varchar(3)) AS IS_TRIGGER_INSERTABLE_INTO,\n"
- " -- MonetDB column extensions\n"
" t.\"schema_id\" AS schema_id,\n"
" t.\"id\" AS table_id,\n"
" cast(sys.ifthenelse(t.\"system\", t.\"type\" + 10 ,
t.\"type\") AS smallint) AS table_type_id,\n"
@@ -6151,7 +6148,7 @@ sql_update_default(Client c, mvc *sql, s
" 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"
- " cast(sys.ifthenelse(c.\"type\" IN
('date','timestamp','timestamptz','time','timetz'), c.\"type_scale\" -1, NULL)
AS int) AS DATETIME_PRECISION,\n"
+ " 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,\n"
" cast(CASE c.\"type\" WHEN 'day_interval' THEN 'interval day'
WHEN 'month_interval' THEN (CASE c.\"type_digits\" WHEN 1 THEN 'interval year'
WHEN 2 THEN 'interval year to month' WHEN 3 THEN 'interval month' ELSE NULL
END)"
" WHEN 'sec_interval' THEN (CASE c.\"type_digits\" WHEN 5 THEN
'interval day to hour' WHEN 6 THEN 'interval day to minute' WHEN 7 THEN
'interval day to second'"
" WHEN 8 THEN 'interval hour' WHEN 9 THEN 'interval hour to
minute' WHEN 10 THEN 'interval hour to second' WHEN 11 THEN 'interval minute'
WHEN 12 THEN 'interval minute to second'"
@@ -6175,15 +6172,15 @@ sql_update_default(Client c, mvc *sql, s
" cast(NULL AS int) AS MAXIMUM_CARDINALITY,\n"
" cast(NULL AS varchar(1)) AS DTD_IDENTIFIER,\n"
" cast('NO' AS varchar(3)) AS IS_SELF_REFERENCING,\n"
- " cast(CASE WHEN c.\"default\" LIKE 'next value for %' THEN
'YES' ELSE 'NO' END AS varchar(3)) AS IS_IDENTITY,\n"
- " cast(NULL AS varchar(10)) AS IDENTITY_GENERATION,\n"
- " cast(NULL AS int) AS IDENTITY_START,\n"
- " cast(NULL AS int) AS IDENTITY_INCREMENT,\n"
- " cast(NULL AS int) AS IDENTITY_MAXIMUM,\n"
- " cast(NULL AS int) AS IDENTITY_MINIMUM,\n"
- " cast(NULL AS varchar(3)) AS IDENTITY_CYCLE,\n"
- " cast('NO' AS varchar(3)) AS IS_GENERATED,\n"
- " cast(NULL AS varchar(1)) AS GENERATION_EXPRESSION,\n"
+ " cast(sys.ifthenelse(seq.\"name\" IS NULL OR c.\"null\",
'NO', 'YES') AS varchar(3)) AS IS_IDENTITY,\n"
+ " seq.\"name\" AS IDENTITY_GENERATION,\n"
+ " seq.\"start\" AS IDENTITY_START,\n"
+ " seq.\"increment\" AS IDENTITY_INCREMENT,\n"
+ " seq.\"maxvalue\" AS IDENTITY_MAXIMUM,\n"
+ " seq.\"minvalue\" AS IDENTITY_MINIMUM,\n"
+ " cast(sys.ifthenelse(seq.\"name\" IS NULL, NULL,
sys.ifthenelse(seq.\"cycle\", 'YES', 'NO')) AS varchar(3)) AS IDENTITY_CYCLE,\n"
+ " cast(sys.ifthenelse(seq.\"name\" IS NULL, 'NO', 'YES') AS
varchar(3)) AS IS_GENERATED,\n"
+ " cast(sys.ifthenelse(seq.\"name\" IS NULL, NULL,
c.\"default\") AS varchar(1024)) AS GENERATION_EXPRESSION,\n"
" cast('NO' AS varchar(3)) AS IS_SYSTEM_TIME_PERIOD_START,\n"
" cast('NO' AS varchar(3)) AS IS_SYSTEM_TIME_PERIOD_END,\n"
" cast('NO' AS varchar(3)) AS
SYSTEM_TIME_PERIOD_TIMESTAMP_GENERATION,\n"
@@ -6191,16 +6188,17 @@ sql_update_default(Client c, mvc *sql, s
" cast(NULL AS varchar(1)) AS DECLARED_DATA_TYPE,\n"
" cast(NULL AS int) AS DECLARED_NUMERIC_PRECISION,\n"
" cast(NULL AS int) AS DECLARED_NUMERIC_SCALE,\n"
- " -- MonetDB column extensions\n"
" t.\"schema_id\" AS schema_id,\n"
" c.\"table_id\" AS table_id,\n"
" c.\"id\" AS column_id,\n"
+ " seq.\"id\" AS sequence_id,\n"
" t.\"system\" AS is_system,\n"
" cm.\"remark\" AS comments\n"
" FROM sys.\"columns\" c\n"
" INNER JOIN sys.\"tables\" t ON c.\"table_id\" = t.\"id\"\n"
" INNER JOIN sys.\"schemas\" s ON t.\"schema_id\" = s.\"id\"\n"
" LEFT OUTER JOIN sys.\"comments\" cm ON c.\"id\" = cm.\"id\"\n"
+ " LEFT OUTER JOIN sys.\"sequences\" seq ON
((seq.\"name\"||'\"') = substring(c.\"default\", 3 +
sys.\"locate\"('\".\"seq_',c.\"default\",14)))\n"
" ORDER BY s.\"name\", t.\"name\", c.\"number\";\n"
"GRANT SELECT ON TABLE INFORMATION_SCHEMA.COLUMNS TO PUBLIC
WITH GRANT OPTION;\n"
@@ -6223,7 +6221,6 @@ sql_update_default(Client c, mvc *sql, s
" cast('NO' AS varchar(3)) AS IS_DEFERRABLE,\n"
" cast('NO' AS varchar(3)) AS INITIALLY_DEFERRED,\n"
" cast('YES' AS varchar(3)) AS ENFORCED,\n"
- " -- MonetDB column extensions\n"
" t.\"schema_id\" AS schema_id,\n"
" t.\"id\" AS table_id,\n"
" k.\"id\" AS key_id,\n"
@@ -6246,7 +6243,6 @@ sql_update_default(Client c, mvc *sql, s
" cast('FULL' AS varchar(7)) AS MATCH_OPTION,\n"
" fk.\"update_action\" AS UPDATE_RULE,\n"
" fk.\"delete_action\" AS DELETE_RULE,\n"
- " -- MonetDB column extensions\n"
" t.\"schema_id\" AS fk_schema_id,\n"
" t.\"id\" AS fk_table_id,\n"
" t.\"name\" AS fk_table_name,\n"
@@ -6353,7 +6349,6 @@ sql_update_default(Client c, mvc *sql, s
" cast(NULL AS varchar(1)) AS
RESULT_CAST_FROM_DECLARED_DATA_TYPE,\n"
" cast(NULL AS int) AS
RESULT_CAST_DECLARED_NUMERIC_PRECISION,\n"
" cast(NULL AS int) AS RESULT_CAST_DECLARED_NUMERIC_SCALE,\n"
- " -- MonetDB column extensions\n"
" f.\"schema_id\" AS schema_id,\n"
" f.\"id\" AS function_id,\n"
" f.\"type\" AS function_type,\n"
@@ -6386,7 +6381,6 @@ sql_update_default(Client c, mvc *sql, s
" cast(NULL AS varchar(16)) AS DECLARED_DATA_TYPE,\n"
" cast(NULL AS SMALLINT) AS DECLARED_NUMERIC_PRECISION,\n"
" cast(NULL AS SMALLINT) AS DECLARED_NUMERIC_SCALE,\n"
- " -- MonetDB column extensions\n"
" sq.\"schema_id\" AS schema_id,\n"
" sq.\"id\" AS sequence_id,\n"
" get_value_for(s.\"name\", sq.\"name\") AS current_value,\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
@@ -135,7 +135,7 @@ CREATE VIEW INFORMATION_SCHEMA.COLUMNS A
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'), c."type_scale" -1, NULL) AS
int) AS DATETIME_PRECISION,
+ 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(CASE c."type" WHEN 'day_interval' THEN 'interval day' WHEN
'month_interval' THEN (CASE c."type_digits" WHEN 1 THEN 'interval year' WHEN 2
THEN 'interval year to month' WHEN 3 THEN 'interval month' ELSE NULL END) WHEN
'sec_interval' THEN (CASE c."type_digits" WHEN 5 THEN 'interval day to hour'
WHEN 6 THEN 'interval day to minute' WHEN 7 THEN 'interval day to second' WHEN
8 THEN 'interval hour' WHEN 9 THEN 'interval hour to minute' WHEN 10 THEN
'interval hour to second' WHEN 11 THEN 'interval minute' WHEN 12 THEN 'interval
minute to second' WHEN 13 THEN 'interval second' ELSE NULL END) ELSE NULL END
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,
@@ -156,15 +156,15 @@ CREATE VIEW INFORMATION_SCHEMA.COLUMNS A
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(CASE WHEN c."default" LIKE 'next value for %' THEN 'YES' ELSE 'NO' END
AS varchar(3)) AS IS_IDENTITY,
- cast(NULL AS varchar(10)) AS IDENTITY_GENERATION,
- cast(NULL AS int) AS IDENTITY_START,
- cast(NULL AS int) AS IDENTITY_INCREMENT,
- cast(NULL AS int) AS IDENTITY_MAXIMUM,
- cast(NULL AS int) AS IDENTITY_MINIMUM,
- cast(NULL AS varchar(3)) AS IDENTITY_CYCLE,
- cast('NO' AS varchar(3)) AS IS_GENERATED,
- cast(NULL AS varchar(1)) AS GENERATION_EXPRESSION,
+ 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,
@@ -176,12 +176,14 @@ CREATE VIEW INFORMATION_SCHEMA.COLUMNS A
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))) --
match only sequences of generated identity columns
ORDER BY s."name", t."name", c."number";
GRANT SELECT ON TABLE INFORMATION_SCHEMA.COLUMNS TO PUBLIC WITH GRANT OPTION;
diff --git a/sql/test/Dependencies/Tests/dependency_owner_schema_3.test
b/sql/test/Dependencies/Tests/dependency_owner_schema_3.test
--- a/sql/test/Dependencies/Tests/dependency_owner_schema_3.test
+++ b/sql/test/Dependencies/Tests/dependency_owner_schema_3.test
@@ -615,6 +615,9 @@ schemas
views
DEP_VIEW
sequences
+columns
+DEP_VIEW
+sequences
describe_comments
DEP_VIEW
sequences
@@ -868,7 +871,7 @@ DEP_FUNC
query TTT rowsort
SELECT distinct c.name, v.name, 'DEP_VIEW' from sys.columns as c, sys.tables
as v, sys.dependencies as dep where c.id = dep.id AND v.id = dep.depend_id AND
dep.depend_type = 5 AND v.type in (1, 11, 21, 31) order by c.name, v.name
----
-1755 values hashing to ce914cc8151df86cb7ff91fb677843fa
+1770 values hashing to 5410dd601c567b97e738aeee54fab229
query TTT rowsort
SELECT c.name, k.name, 'DEP_KEY' from sys.columns as c, sys.objects as kc,
sys.keys as k where kc."name" = c.name AND kc.id = k.id AND k.table_id =
c.table_id AND k.rkey = -1 order by c.name, k.name
diff --git a/sql/test/bugs/Tests/groupby_having-bug-sf-947600.test
b/sql/test/bugs/Tests/groupby_having-bug-sf-947600.test
--- a/sql/test/bugs/Tests/groupby_having-bug-sf-947600.test
+++ b/sql/test/bugs/Tests/groupby_having-bug-sf-947600.test
@@ -36,7 +36,7 @@ columns
1
columns
sequences
-9
+10
functions
args
3
diff --git a/sql/test/bugs/Tests/innerjoin_multiple-bug-sf-943661.test
b/sql/test/bugs/Tests/innerjoin_multiple-bug-sf-943661.test
--- a/sql/test/bugs/Tests/innerjoin_multiple-bug-sf-943661.test
+++ b/sql/test/bugs/Tests/innerjoin_multiple-bug-sf-943661.test
@@ -11,7 +11,7 @@ select schemas.name, tables.name, column
'objects', 'keys', 'modules', 'sequences')
order by schemas.name, tables.name, columns.name
----
-426 values hashing to f0d023420e53dd0beb9114710beaa71f
+429 values hashing to 6d7dbf36ca46001022af9219632baa04
query TTT rowsort
select s.name, t.name, c.name from
@@ -26,5 +26,5 @@ select s.name, t.name, c.name from
'objects', 'keys', 'modules', 'sequences')
order by s.name, t.name, c.name
----
-426 values hashing to f0d023420e53dd0beb9114710beaa71f
+429 values hashing to 6d7dbf36ca46001022af9219632baa04
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
@@ -447,7 +447,7 @@ select 'null in fkeys.delete_action', de
% 11, 18, 34, 6750, 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.\"describe_type\"(c.\"type\", c.\"type_digits\",
c.\"type_scale\") 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) a
s 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'), c.\"type_scale\" -1, null)
as int) as datetime_precision, cast(case c.\"type\" when 'day_interval' then
'interval day' when 'month_interval' then (case c.\"type_digits\" when 1 then
'interval year' when 2 then 'interval year to month' when 3 then 'interval
month' else null end) when 'sec_interval' then (case c.\"type_digits\" when 5
then 'interval day to hour' when 6 then 'interval day to minute' when 7 then
'interval day to second' when 8 then 'interval hour' when 9 then 'interval hour
to minute' when 10 then 'interval hour to second' when 11 then 'interval
minute' when 12 then 'interval minute to second' when 13 then 'interval second'
else null end) else null end 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 varc
har(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(case when
c.\"default\" like 'next value for %' then 'YES' else 'NO' end as varchar(3))
as is_identity, cast(null as varchar(10)) as identity_generation, cast(null as
int) as identity_start, cast(null as int) as identity_increment, cast(null as
int) as identity_maximum, cast(null as int) as identity_minimum, cast(null as
varchar(3)) as identity_cycle, cast('NO' as varchar(3)) as is_generated,
cast(null as varchar(1)) 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, ca
st(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, 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\" order by s.\"name\", t.\"name\",
c.\"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.\"describe_type\"(c.\"type\", c.\"type_digits\",
c.\"type_scale\") 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) a
s 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(case c.\"type\" when 'day_interval' then 'interval
day' when 'month_interval' then (case c.\"type_digits\" when 1 then 'interval
year' when 2 then 'interval year to month' when 3 then 'interval month' else
null end) when 'sec_interval' then (case c.\"type_digits\" when 5 then
'interval day to hour' when 6 then 'interval day to minute' when 7 then
'interval day to second' when 8 then 'i
nterval hour' when 9 then 'interval hour to minute' when 10 then 'interval
hour to second' when 11 then 'interval minute' when 12 then 'interval minute to
second' when 13 then 'interval second' else null end) else null end 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 varc
har(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 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, "CO
MMIT", "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.\"id\" as varchar(10))
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.\"describe_type\"(a.\"type\", a.\"type_digits\", a.\"type_scale\")
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(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.\"describe_type\"(a.\"type\", a.\"type_digits\", a.\"type_scale\"), 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.\"ifthenel
se\"(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_c
ast_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_cardinal
ity, 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) ord
er 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 ]
@@ -637,14 +637,14 @@ select 'null in fkeys.delete_action', de
[ "sys._columns", "information_schema", "columns",
"dtd_identifier", "varchar", 1, 0, NULL, true, 32,
NULL, NULL ]
[ "sys._columns", "information_schema", "columns",
"is_self_referencing", "varchar", 3, 0, NULL, true, 33,
NULL, NULL ]
[ "sys._columns", "information_schema", "columns", "is_identity",
"varchar", 3, 0, NULL, true, 34, NULL, NULL ]
-[ "sys._columns", "information_schema", "columns",
"identity_generation", "varchar", 10, 0, NULL, true, 35,
NULL, NULL ]
-[ "sys._columns", "information_schema", "columns",
"identity_start", "int", 32, 0, NULL, true, 36, NULL,
NULL ]
-[ "sys._columns", "information_schema", "columns",
"identity_increment", "int", 32, 0, NULL, true, 37, NULL,
NULL ]
-[ "sys._columns", "information_schema", "columns",
"identity_maximum", "int", 32, 0, NULL, true, 38, NULL,
NULL ]
-[ "sys._columns", "information_schema", "columns",
"identity_minimum", "int", 32, 0, NULL, true, 39, NULL,
NULL ]
+[ "sys._columns", "information_schema", "columns",
"identity_generation", "varchar", 256, 0, NULL, true, 35,
NULL, NULL ]
+[ "sys._columns", "information_schema", "columns",
"identity_start", "bigint", 64, 0, NULL, true, 36,
NULL, NULL ]
+[ "sys._columns", "information_schema", "columns",
"identity_increment", "bigint", 64, 0, NULL, true, 37,
NULL, NULL ]
+[ "sys._columns", "information_schema", "columns",
"identity_maximum", "bigint", 64, 0, NULL, true, 38,
NULL, NULL ]
+[ "sys._columns", "information_schema", "columns",
"identity_minimum", "bigint", 64, 0, NULL, true, 39,
NULL, NULL ]
[ "sys._columns", "information_schema", "columns",
"identity_cycle", "varchar", 3, 0, NULL, true, 40,
NULL, NULL ]
[ "sys._columns", "information_schema", "columns", "is_generated",
"varchar", 3, 0, NULL, true, 41, NULL, NULL ]
-[ "sys._columns", "information_schema", "columns",
"generation_expression", "varchar", 1, 0, NULL, true,
42, NULL, NULL ]
+[ "sys._columns", "information_schema", "columns",
"generation_expression", "varchar", 1024, 0, NULL, true,
42, NULL, NULL ]
[ "sys._columns", "information_schema", "columns",
"is_system_time_period_start", "varchar", 3, 0, NULL, true,
43, NULL, NULL ]
[ "sys._columns", "information_schema", "columns",
"is_system_time_period_end", "varchar", 3, 0, NULL, true,
44, NULL, NULL ]
[ "sys._columns", "information_schema", "columns",
"system_time_period_timestamp_generation", "varchar", 3, 0,
NULL, true, 45, NULL, NULL ]
@@ -655,8 +655,9 @@ select 'null in fkeys.delete_action', de
[ "sys._columns", "information_schema", "columns", "schema_id",
"int", 32, 0, NULL, true, 50, NULL, NULL ]
[ "sys._columns", "information_schema", "columns", "table_id",
"int", 32, 0, NULL, true, 51, NULL, NULL ]
[ "sys._columns", "information_schema", "columns", "column_id",
"int", 32, 0, NULL, true, 52, NULL, NULL ]
-[ "sys._columns", "information_schema", "columns", "is_system",
"boolean", 1, 0, NULL, true, 53, NULL, NULL ]
-[ "sys._columns", "information_schema", "columns", "comments",
"varchar", 65000, 0, NULL, true, 54, NULL, NULL ]
+[ "sys._columns", "information_schema", "columns", "sequence_id",
"int", 32, 0, NULL, true, 53, NULL, NULL ]
+[ "sys._columns", "information_schema", "columns", "is_system",
"boolean", 1, 0, NULL, true, 54, NULL, NULL ]
+[ "sys._columns", "information_schema", "columns", "comments",
"varchar", 65000, 0, NULL, true, 55, NULL, NULL ]
[ "sys._columns", "information_schema", "referential_constraints",
"constraint_catalog", "varchar", 1, 0, NULL, true, 0,
NULL, NULL ]
[ "sys._columns", "information_schema", "referential_constraints",
"constraint_schema", "varchar", 1024, 0, NULL, true, 1,
NULL, NULL ]
[ "sys._columns", "information_schema", "referential_constraints",
"constraint_name", "varchar", 1024, 0, NULL, true, 2,
NULL, NULL ]
@@ -3225,6 +3226,7 @@ select 'null in fkeys.delete_action', de
[ "table used by view", "sys", "columns", "information_schema",
"columns", "VIEW" ]
[ "table used by view", "sys", "comments", "information_schema",
"columns", "VIEW" ]
[ "table used by view", "sys", "schemas", "information_schema",
"columns", "VIEW" ]
+[ "table used by view", "sys", "sequences", "information_schema",
"columns", "VIEW" ]
[ "table used by view", "sys", "tables", "information_schema",
"columns", "VIEW" ]
[ "table used by view", "sys", "fkeys", "information_schema",
"referential_constraints", "VIEW" ]
[ "table used by view", "sys", "keys", "information_schema",
"referential_constraints", "VIEW" ]
@@ -3494,6 +3496,13 @@ select 'null in fkeys.delete_action', de
[ "column used by view", "sys", "comments", "remark",
"information_schema", "columns", "VIEW" ]
[ "column used by view", "sys", "schemas", "id",
"information_schema", "columns", "VIEW" ]
[ "column used by view", "sys", "schemas", "name",
"information_schema", "columns", "VIEW" ]
+[ "column used by view", "sys", "sequences", "cycle",
"information_schema", "columns", "VIEW" ]
+[ "column used by view", "sys", "sequences", "id",
"information_schema", "columns", "VIEW" ]
+[ "column used by view", "sys", "sequences", "increment",
"information_schema", "columns", "VIEW" ]
+[ "column used by view", "sys", "sequences", "maxvalue",
"information_schema", "columns", "VIEW" ]
+[ "column used by view", "sys", "sequences", "minvalue",
"information_schema", "columns", "VIEW" ]
+[ "column used by view", "sys", "sequences", "name",
"information_schema", "columns", "VIEW" ]
+[ "column used by view", "sys", "sequences", "start",
"information_schema", "columns", "VIEW" ]
[ "column used by view", "sys", "tables", "id",
"information_schema", "columns", "VIEW" ]
[ "column used by view", "sys", "tables", "name",
"information_schema", "columns", "VIEW" ]
[ "column used by view", "sys", "tables", "schema_id",
"information_schema", "columns", "VIEW" ]
diff --git a/sql/test/emptydb/Tests/check.stable.out.32bit
b/sql/test/emptydb/Tests/check.stable.out.32bit
--- a/sql/test/emptydb/Tests/check.stable.out.32bit
+++ b/sql/test/emptydb/Tests/check.stable.out.32bit
@@ -447,7 +447,7 @@ select 'null in fkeys.delete_action', de
% 11, 18, 34, 6750, 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.\"describe_type\"(c.\"type\", c.\"type_digits\",
c.\"type_scale\") 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) a
s 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'), c.\"type_scale\" -1, null)
as int) as datetime_precision, cast(case c.\"type\" when 'day_interval' then
'interval day' when 'month_interval' then (case c.\"type_digits\" when 1 then
'interval year' when 2 then 'interval year to month' when 3 then 'interval
month' else null end) when 'sec_interval' then (case c.\"type_digits\" when 5
then 'interval day to hour' when 6 then 'interval day to minute' when 7 then
'interval day to second' when 8 then 'interval hour' when 9 then 'interval hour
to minute' when 10 then 'interval hour to second' when 11 then 'interval
minute' when 12 then 'interval minute to second' when 13 then 'interval second'
else null end) else null end 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 varc
har(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(case when
c.\"default\" like 'next value for %' then 'YES' else 'NO' end as varchar(3))
as is_identity, cast(null as varchar(10)) as identity_generation, cast(null as
int) as identity_start, cast(null as int) as identity_increment, cast(null as
int) as identity_maximum, cast(null as int) as identity_minimum, cast(null as
varchar(3)) as identity_cycle, cast('NO' as varchar(3)) as is_generated,
cast(null as varchar(1)) 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, ca
st(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, 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\" order by s.\"name\", t.\"name\",
c.\"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.\"describe_type\"(c.\"type\", c.\"type_digits\",
c.\"type_scale\") 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) a
s 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(case c.\"type\" when 'day_interval' then 'interval
day' when 'month_interval' then (case c.\"type_digits\" when 1 then 'interval
year' when 2 then 'interval year to month' when 3 then 'interval month' else
null end) when 'sec_interval' then (case c.\"type_digits\" when 5 then
'interval day to hour' when 6 then 'interval day to minute' when 7 then
'interval day to second' when 8 then 'i
nterval hour' when 9 then 'interval hour to minute' when 10 then 'interval
hour to second' when 11 then 'interval minute' when 12 then 'interval minute to
second' when 13 then 'interval second' else null end) else null end 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 varc
har(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 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, "CO
MMIT", "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.\"id\" as varchar(10))
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.\"describe_type\"(a.\"type\", a.\"type_digits\", a.\"type_scale\")
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(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.\"describe_type\"(a.\"type\", a.\"type_digits\", a.\"type_scale\"), 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.\"ifthenel
se\"(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_c
ast_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_cardinal
ity, 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) ord
er 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 ]
@@ -635,14 +635,14 @@ select 'null in fkeys.delete_action', de
[ "sys._columns", "information_schema", "columns",
"dtd_identifier", "varchar", 1, 0, NULL, true, 32,
NULL, NULL ]
[ "sys._columns", "information_schema", "columns",
"is_self_referencing", "varchar", 3, 0, NULL, true, 33,
NULL, NULL ]
[ "sys._columns", "information_schema", "columns", "is_identity",
"varchar", 3, 0, NULL, true, 34, NULL, NULL ]
_______________________________________________
checkin-list mailing list -- checkin-list@monetdb.org
To unsubscribe send an email to checkin-list-le...@monetdb.org
