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