Changeset: 9258cfce7ec0 for MonetDB URL: https://dev.monetdb.org/hg/MonetDB?cmd=changeset;node=9258cfce7ec0 Modified Files: sql/test/sys-schema/Tests/check_ForeignKey_referential_integrity.sql sql/test/sys-schema/Tests/check_ForeignKey_referential_integrity.stable.out sql/test/sys-schema/Tests/check_Not_Nullable_columns.sql sql/test/sys-schema/Tests/check_Not_Nullable_columns.stable.out Branch: Apr2019 Log Message:
Update integrity checks for sys.table_partitions, sys.range_partitions and sys.value_partitions based on tests and code inspections. diffs (109 lines): diff --git a/sql/test/sys-schema/Tests/check_ForeignKey_referential_integrity.sql b/sql/test/sys-schema/Tests/check_ForeignKey_referential_integrity.sql --- a/sql/test/sys-schema/Tests/check_ForeignKey_referential_integrity.sql +++ b/sql/test/sys-schema/Tests/check_ForeignKey_referential_integrity.sql @@ -141,7 +141,7 @@ SELECT schema, table, rowcount, columnsi -- new tables introduced in 2019 SELECT * FROM sys.table_partitions WHERE "table_id" NOT IN (SELECT id FROM sys._tables); SELECT * FROM sys.table_partitions WHERE "column_id" IS NOT NULL AND "column_id" NOT IN (SELECT id FROM sys._columns); -SELECT * FROM sys.table_partitions WHERE "type" NOT IN (SELECT id FROM sys.types); +SELECT * FROM sys.table_partitions WHERE "type" NOT IN (5,6,9,10); -- 5=By Column Range (1+4), 6=By Expression Range (2+4), 9=By Column Value (1+8), 10=By Expression Value (2+8), see sql_catalog.h #define PARTITION_* SELECT * FROM sys.range_partitions WHERE "table_id" NOT IN (SELECT id FROM sys._tables); SELECT * FROM sys.range_partitions WHERE "partition_id" NOT IN (SELECT id FROM sys.table_partitions); diff --git a/sql/test/sys-schema/Tests/check_ForeignKey_referential_integrity.stable.out b/sql/test/sys-schema/Tests/check_ForeignKey_referential_integrity.stable.out --- a/sql/test/sys-schema/Tests/check_ForeignKey_referential_integrity.stable.out +++ b/sql/test/sys-schema/Tests/check_ForeignKey_referential_integrity.stable.out @@ -529,7 +529,7 @@ Ready. % id, table_id, column_id, expression, type # name % int, int, int, varchar, tinyint # type % 1, 1, 1, 0, 1 # length -#SELECT * FROM sys.table_partitions WHERE "type" NOT IN (SELECT id FROM sys.types); +#SELECT * FROM sys.table_partitions WHERE "type" NOT IN (5,6,9,10); % sys.table_partitions, sys.table_partitions, sys.table_partitions, sys.table_partitions, sys.table_partitions # table_name % id, table_id, column_id, expression, type # name % int, int, int, varchar, tinyint # type diff --git a/sql/test/sys-schema/Tests/check_Not_Nullable_columns.sql b/sql/test/sys-schema/Tests/check_Not_Nullable_columns.sql --- a/sql/test/sys-schema/Tests/check_Not_Nullable_columns.sql +++ b/sql/test/sys-schema/Tests/check_Not_Nullable_columns.sql @@ -138,8 +138,8 @@ SELECT "privileges", * FROM "sys"."privi SELECT "grantor", * FROM "sys"."privileges" WHERE "grantor" IS NULL; SELECT "grantable", * FROM "sys"."privileges" WHERE "grantable" IS NULL; -SELECT "maximum", * FROM "sys"."range_partitions" WHERE "maximum" IS NULL; -SELECT "minimum", * FROM "sys"."range_partitions" WHERE "minimum" IS NULL; +-- SELECT "maximum", * FROM "sys"."range_partitions" WHERE "maximum" IS NULL; -- Can be null when WITH NULL VALUES is specified. +-- SELECT "minimum", * FROM "sys"."range_partitions" WHERE "minimum" IS NULL; -- Can be null when WITH NULL VALUES is specified. SELECT "partition_id", * FROM "sys"."range_partitions" WHERE "partition_id" IS NULL; SELECT "table_id", * FROM "sys"."range_partitions" WHERE "table_id" IS NULL; SELECT "with_nulls", * FROM "sys"."range_partitions" WHERE "with_nulls" IS NULL; @@ -186,7 +186,8 @@ SELECT "id", * FROM "sys"."table_partiti SELECT "table_id", * FROM "sys"."table_partitions" WHERE "table_id" IS NULL; SELECT "type", * FROM "sys"."table_partitions" WHERE "type" IS NULL; -- either column_id or expression must be populated -SELECT "column_id", "expression", * FROM "sys"."table_partitions" WHERE "column_id" IS NULL AND "expression" IS NULL; +SELECT "column_id", "expression", 'Missing either column_id or expression' AS violation, * FROM "sys"."table_partitions" WHERE "column_id" IS NULL AND "expression" IS NULL; +SELECT "column_id", "expression", 'column_id and expression may not both be populated. One of them must be NULL' AS violation, * FROM "sys"."table_partitions" WHERE "column_id" IS NOT NULL AND "expression" IS NOT NULL; SELECT "id", * FROM "sys"."triggers" WHERE "id" IS NULL; SELECT "name", * FROM "sys"."triggers" WHERE "name" IS NULL; @@ -210,5 +211,5 @@ SELECT "role_id", * FROM "sys"."user_rol SELECT "partition_id", * FROM "sys"."value_partitions" WHERE "partition_id" IS NULL; SELECT "table_id", * FROM "sys"."value_partitions" WHERE "table_id" IS NULL; -SELECT "value", * FROM "sys"."value_partitions" WHERE "value" IS NULL; +-- SELECT "value", * FROM "sys"."value_partitions" WHERE "value" IS NULL; -- Can be null when WITH NULL VALUES is specified. diff --git a/sql/test/sys-schema/Tests/check_Not_Nullable_columns.stable.out b/sql/test/sys-schema/Tests/check_Not_Nullable_columns.stable.out --- a/sql/test/sys-schema/Tests/check_Not_Nullable_columns.stable.out +++ b/sql/test/sys-schema/Tests/check_Not_Nullable_columns.stable.out @@ -474,16 +474,6 @@ Ready. % grantable, obj_id, auth_id, privileges, grantor, grantable # name % int, int, int, int, int, int # type % 1, 1, 1, 1, 1, 1 # length -#SELECT "maximum", * FROM "sys"."range_partitions" WHERE "maximum" IS NULL; -% sys.range_partitions, sys.range_partitions, sys.range_partitions, sys.range_partitions, sys.range_partitions, sys.range_partitions # table_name -% maximum, table_id, partition_id, minimum, maximum, with_nulls # name -% varchar, int, int, varchar, varchar, boolean # type -% 0, 1, 1, 0, 0, 5 # length -#SELECT "minimum", * FROM "sys"."range_partitions" WHERE "minimum" IS NULL; -% sys.range_partitions, sys.range_partitions, sys.range_partitions, sys.range_partitions, sys.range_partitions, sys.range_partitions # table_name -% minimum, table_id, partition_id, minimum, maximum, with_nulls # name -% varchar, int, int, varchar, varchar, boolean # type -% 0, 1, 1, 0, 0, 5 # length #SELECT "partition_id", * FROM "sys"."range_partitions" WHERE "partition_id" IS NULL; % sys.range_partitions, sys.range_partitions, sys.range_partitions, sys.range_partitions, sys.range_partitions, sys.range_partitions # table_name % partition_id, table_id, partition_id, minimum, maximum, with_nulls # name @@ -644,11 +634,16 @@ Ready. % type, id, table_id, column_id, expression, type # name % tinyint, int, int, int, varchar, tinyint # type % 1, 1, 1, 1, 0, 1 # length -#SELECT "column_id", "expression", * FROM "sys"."table_partitions" WHERE "column_id" IS NULL AND "expression" IS NULL; -% sys.table_partitions, sys.table_partitions, sys.table_partitions, sys.table_partitions, sys.table_partitions, sys.table_partitions, sys.table_partitions # table_name -% column_id, expression, id, table_id, column_id, expression, type # name -% int, varchar, int, int, int, varchar, tinyint # type -% 1, 0, 1, 1, 1, 0, 1 # length +#SELECT "column_id", "expression", 'Missing either column_id or expression' AS violation, * FROM "sys"."table_partitions" WHERE "column_id" IS NULL AND "expression" IS NULL; +% sys.table_partitions, sys.table_partitions, ., sys.table_partitions, sys.table_partitions, sys.table_partitions, sys.table_partitions, sys.table_partitions # table_name +% column_id, expression, violation, id, table_id, column_id, expression, type # name +% int, varchar, char, int, int, int, varchar, tinyint # type +% 1, 0, 38, 1, 1, 1, 0, 1 # length +#SELECT "column_id", "expression", 'column_id and expression may not both be populated. One of them must be NULL' AS violation, * FROM "sys"."table_partitions" WHERE "column_id" IS NOT NULL AND "expression" IS NOT NULL; +% sys.table_partitions, sys.table_partitions, ., sys.table_partitions, sys.table_partitions, sys.table_partitions, sys.table_partitions, sys.table_partitions # table_name +% column_id, expression, violation, id, table_id, column_id, expression, type # name +% int, varchar, char, int, int, int, varchar, tinyint # type +% 1, 0, 76, 1, 1, 1, 0, 1 # length #SELECT "id", * FROM "sys"."triggers" WHERE "id" IS NULL; % sys.triggers, sys.triggers, sys.triggers, sys.triggers, sys.triggers, sys.triggers, sys.triggers, sys.triggers, sys.triggers, sys.triggers, sys.triggers # table_name % id, id, name, table_id, time, orientation, event, old_name, new_name, condition, statement # name @@ -744,11 +739,6 @@ Ready. % table_id, table_id, partition_id, value # name % int, int, int, varchar # type % 1, 1, 1, 0 # length -#SELECT "value", * FROM "sys"."value_partitions" WHERE "value" IS NULL; -% sys.value_partitions, sys.value_partitions, sys.value_partitions, sys.value_partitions # table_name -% value, table_id, partition_id, value # name -% varchar, int, int, varchar # type -% 0, 1, 1, 0 # length # 18:07:03 > # 18:07:03 > "Done." _______________________________________________ checkin-list mailing list checkin-list@monetdb.org https://www.monetdb.org/mailman/listinfo/checkin-list
