Changeset: 98b735520ae0 for MonetDB
URL: https://dev.monetdb.org/hg/MonetDB/rev/98b735520ae0
Modified Files:
sql/test/information-schema/Tests/character_sets.test
sql/test/information-schema/Tests/columns.test
sql/test/information-schema/Tests/schemata.test
sql/test/information-schema/Tests/sequences.test
sql/test/information-schema/Tests/tables.test
sql/test/information-schema/Tests/views.test
Branch: Dec2023
Log Message:
Extend tests on information_schema views.
Added NOT NULL or empty string tests, entity and referential integrity tests
and allowed values checks.
diffs (truncated from 771 to 300 lines):
diff --git a/sql/test/information-schema/Tests/character_sets.test
b/sql/test/information-schema/Tests/character_sets.test
--- a/sql/test/information-schema/Tests/character_sets.test
+++ b/sql/test/information-schema/Tests/character_sets.test
@@ -19,6 +19,8 @@ NULL
NULL
NULL
+
+-- entity integrity checks
query ITTT rowsort
SELECT COUNT(*) AS duplicates, CHARACTER_SET_CATALOG, CHARACTER_SET_SCHEMA,
CHARACTER_SET_NAME
FROM INFORMATION_SCHEMA.CHARACTER_SETS
@@ -26,3 +28,19 @@ SELECT COUNT(*) AS duplicates, CHARACTER
HAVING COUNT(*) > 1
----
+-- as CHARACTER_SET_CATALOG is always NULL leave it out of the check
+query ITT rowsort
+SELECT COUNT(*) AS duplicates, CHARACTER_SET_SCHEMA, CHARACTER_SET_NAME
+ FROM INFORMATION_SCHEMA.CHARACTER_SETS
+ GROUP BY CHARACTER_SET_SCHEMA, CHARACTER_SET_NAME
+ HAVING COUNT(*) > 1
+----
+
+-- as CHARACTER_SET_CATALOG and CHARACTER_SET_SCHEMA are always NULL leave it
out of the check
+query IT rowsort
+SELECT COUNT(*) AS duplicates, CHARACTER_SET_NAME
+ FROM INFORMATION_SCHEMA.CHARACTER_SETS
+ GROUP BY CHARACTER_SET_NAME
+ HAVING COUNT(*) > 1
+----
+
diff --git a/sql/test/information-schema/Tests/columns.test
b/sql/test/information-schema/Tests/columns.test
--- a/sql/test/information-schema/Tests/columns.test
+++ b/sql/test/information-schema/Tests/columns.test
@@ -104,6 +104,7 @@ NULL
NULL
NULL
+-- check for NULL and empty string value violations on NOT NULL columns
query TTTTITTTIIIIIITITTTTTTTTTTTTTTTITTTTIIIITTTTTTTTIIIIIIIT rowsort
SELECT
TABLE_CATALOG,
@@ -163,17 +164,21 @@ SELECT
is_system,
comments
FROM INFORMATION_SCHEMA.COLUMNS
-WHERE TABLE_SCHEMA = '' OR TABLE_NAME = '' OR COLUMN_NAME = ''
+WHERE TABLE_SCHEMA IS NULL
+ OR TABLE_SCHEMA = ''
+ OR TABLE_NAME IS NULL
+ OR TABLE_NAME = ''
+ OR COLUMN_NAME IS NULL
+ OR COLUMN_NAME = ''
+ OR DATA_TYPE IS NULL
+ OR DATA_TYPE = ''
+ OR ORDINAL_POSITION IS NULL
+ OR schema_id IS NULL
+ OR table_id IS NULL
+ OR column_id IS NULL
+ OR is_system IS NULL
----
-query ITTTT rowsort
-SELECT COUNT(*) AS duplicates, TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME,
COLUMN_NAME
- FROM INFORMATION_SCHEMA.COLUMNS
- GROUP BY TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME
- HAVING COUNT(*) > 1
-----
-
-
statement ok
CREATE TEMP TABLE tlargechar (c1 varchar(2147483647), c2 char(2147483646), c3
clob(2147483645), c4 json(2147483644), c5 url(2147483643)) ON COMMIT PRESERVE
ROWS
@@ -218,3 +223,172 @@ URL
2147483643
8589934572
+
+-- entity integrity checks
+query ITTTT rowsort
+SELECT COUNT(*) AS duplicates, TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME,
COLUMN_NAME
+ FROM INFORMATION_SCHEMA.COLUMNS
+ GROUP BY TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME
+ HAVING COUNT(*) > 1
+----
+
+-- as TABLE_CATALOG is always NULL the TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME
combo should be unique also
+query ITTT rowsort
+SELECT COUNT(*) AS duplicates, TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME
+ FROM INFORMATION_SCHEMA.COLUMNS
+ GROUP BY TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME
+ HAVING COUNT(*) > 1
+----
+
+-- it should also be unique when using schema_id instead of TABLE_SCHEMA
+query IITT rowsort
+SELECT COUNT(*) AS duplicates, schema_id, TABLE_NAME, COLUMN_NAME
+ FROM INFORMATION_SCHEMA.COLUMNS
+ GROUP BY schema_id, TABLE_NAME, COLUMN_NAME
+ HAVING COUNT(*) > 1
+----
+
+-- it should also be unique when using table_id instead of TABLE_SCHEMA,
TABLE_NAME
+query IIT rowsort
+SELECT COUNT(*) AS duplicates, table_id, COLUMN_NAME
+ FROM INFORMATION_SCHEMA.COLUMNS
+ GROUP BY table_id, COLUMN_NAME
+ HAVING COUNT(*) > 1
+----
+
+-- column_id alone should be unique also
+query II rowsort
+SELECT COUNT(*) AS duplicates, column_id
+ FROM INFORMATION_SCHEMA.COLUMNS
+ GROUP BY column_id
+ HAVING COUNT(*) > 1
+----
+
+
+-- referential integrity checks
+query TTTT rowsort
+SELECT TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME
+ FROM INFORMATION_SCHEMA.COLUMNS
+ WHERE (TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME)
+ NOT IN (SELECT TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME FROM
INFORMATION_SCHEMA.TABLES)
+----
+
+-- as TABLE_CATALOG is always NULL leave it out of the check
+query TTT rowsort
+SELECT TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME
+ FROM INFORMATION_SCHEMA.COLUMNS
+ WHERE (TABLE_SCHEMA, TABLE_NAME)
+ NOT IN (SELECT TABLE_SCHEMA, TABLE_NAME FROM INFORMATION_SCHEMA.TABLES)
+----
+
+-- it should also be correct when using schema_id instead of TABLE_SCHEMA
+query ITTT rowsort
+SELECT schema_id, TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME
+ FROM INFORMATION_SCHEMA.COLUMNS
+ WHERE (schema_id, TABLE_NAME)
+ NOT IN (SELECT schema_id, TABLE_NAME FROM INFORMATION_SCHEMA.TABLES)
+----
+
+-- it should also be correct when using table_id instead of TABLE_SCHEMA,
TABLE_NAME
+query TITT rowsort
+SELECT TABLE_SCHEMA, table_id, TABLE_NAME, COLUMN_NAME
+ FROM INFORMATION_SCHEMA.COLUMNS
+ WHERE (table_id)
+ NOT IN (SELECT table_id FROM INFORMATION_SCHEMA.TABLES)
+----
+
+-- check schema_id reference
+query TTTI rowsort
+SELECT TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, schema_id
+ FROM INFORMATION_SCHEMA.COLUMNS
+ WHERE (schema_id) NOT IN (SELECT id FROM sys.schemas)
+----
+
+-- check table_id reference
+query TTTI rowsort
+SELECT TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, table_id
+ FROM INFORMATION_SCHEMA.COLUMNS
+ WHERE (table_id) NOT IN (SELECT id FROM sys.tables)
+----
+
+-- check column_id reference
+query TTTI rowsort
+SELECT TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, column_id
+ FROM INFORMATION_SCHEMA.COLUMNS
+ WHERE (column_id) NOT IN (SELECT id FROM sys.columns)
+----
+
+-- check sequence_id reference
+query TTTI rowsort
+SELECT TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, sequence_id
+ FROM INFORMATION_SCHEMA.COLUMNS
+ WHERE (sequence_id) NOT IN (SELECT id FROM sys.sequences)
+ AND (sequence_id IS NOT NULL OR IS_IDENTITY = 'YES')
+----
+
+
+-- check ORDINAL_POSITION allowed values, should be NOT NULL and >= 1
+query TTTT rowsort
+SELECT TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, ORDINAL_POSITION
+ FROM INFORMATION_SCHEMA.COLUMNS
+ WHERE ORDINAL_POSITION IS NULL
+ OR ORDINAL_POSITION < 1
+----
+
+-- check DATA_TYPE, should be NOT NULL and not empty
+query TTTT rowsort
+SELECT TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, DATA_TYPE
+ FROM INFORMATION_SCHEMA.COLUMNS
+ WHERE DATA_TYPE IS NULL
+ OR LENGTH(DATA_TYPE) < 1
+----
+
+-- check IS_NULLABLE allowed values
+query TTTT rowsort
+SELECT TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, IS_NULLABLE
+ FROM INFORMATION_SCHEMA.COLUMNS
+ WHERE (IS_NULLABLE) NOT IN ('NO', 'YES')
+----
+
+-- check IS_SELF_REFERENCING allowed values
+query TTTT rowsort
+SELECT TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, IS_SELF_REFERENCING
+ FROM INFORMATION_SCHEMA.COLUMNS
+ WHERE (IS_SELF_REFERENCING) NOT IN ('NO', 'YES')
+----
+
+-- check IS_IDENTITY allowed values
+query TTTT rowsort
+SELECT TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, IS_IDENTITY
+ FROM INFORMATION_SCHEMA.COLUMNS
+ WHERE (IS_IDENTITY) NOT IN ('NO', 'YES')
+----
+
+-- check IDENTITY_GENERATION allowed values
+query TTTT rowsort
+SELECT TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, IDENTITY_GENERATION
+ FROM INFORMATION_SCHEMA.COLUMNS
+ WHERE (IDENTITY_GENERATION) NOT IN ('NO', 'YES')
+----
+
+-- check IS_GENERATED allowed values
+query TTTT rowsort
+SELECT TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, IS_GENERATED
+ FROM INFORMATION_SCHEMA.COLUMNS
+ WHERE (IS_GENERATED) NOT IN ('NO', 'YES')
+----
+
+-- check IS_UPDATABLE allowed values
+query TTTT rowsort
+SELECT TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, IS_UPDATABLE
+ FROM INFORMATION_SCHEMA.COLUMNS
+ WHERE (IS_UPDATABLE) NOT IN ('NO', 'YES')
+----
+
+-- check is_system allowed boolean values
+query TTTI rowsort
+SELECT TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, is_system
+ FROM INFORMATION_SCHEMA.COLUMNS
+ WHERE (is_system) NOT IN (FALSE, TRUE)
+----
+
diff --git a/sql/test/information-schema/Tests/schemata.test
b/sql/test/information-schema/Tests/schemata.test
--- a/sql/test/information-schema/Tests/schemata.test
+++ b/sql/test/information-schema/Tests/schemata.test
@@ -18,6 +18,7 @@ NULL
UTF-8
NULL
+-- check for NULL and empty string value violations on NOT NULL columns
query TTTTTTTIIT rowsort
SELECT
CATALOG_NAME,
@@ -31,9 +32,18 @@ SELECT
is_system,
comments
FROM INFORMATION_SCHEMA.SCHEMATA
-WHERE SCHEMA_NAME = ''
+WHERE SCHEMA_NAME IS NULL
+ OR SCHEMA_NAME = ''
+ OR SCHEMA_OWNER IS NULL
+ OR SCHEMA_OWNER = ''
+ OR DEFAULT_CHARACTER_SET_NAME IS NULL
+ OR DEFAULT_CHARACTER_SET_NAME = ''
+ OR schema_id IS NULL
+ OR is_system IS NULL
----
+
+-- entity integrity checks
query ITT rowsort
SELECT COUNT(*) AS duplicates, CATALOG_NAME, SCHEMA_NAME
FROM INFORMATION_SCHEMA.SCHEMATA
@@ -41,3 +51,59 @@ SELECT COUNT(*) AS duplicates, CATALOG_N
HAVING COUNT(*) > 1
----
+-- as CATALOG_NAME is always NULL, SCHEMA_NAME alone should be unique also
+query IT rowsort
+SELECT COUNT(*) AS duplicates, SCHEMA_NAME
+ FROM INFORMATION_SCHEMA.SCHEMATA
+ GROUP BY SCHEMA_NAME
+ HAVING COUNT(*) > 1
+----
+
+-- schema_id alone should be unique also
+query II rowsort
+SELECT COUNT(*) AS duplicates, schema_id
+ FROM INFORMATION_SCHEMA.SCHEMATA
+ GROUP BY schema_id
+ HAVING COUNT(*) > 1
+----
+
+
+-- referential integrity checks
_______________________________________________
checkin-list mailing list -- [email protected]
To unsubscribe send an email to [email protected]
