Changeset: 9a7d7704186c for MonetDB
URL: https://dev.monetdb.org/hg/MonetDB?cmd=changeset;node=9a7d7704186c
Modified Files:
sql/test/sys-schema/Tests/check_AlternateKey_uniqueness.sql
sql/test/sys-schema/Tests/check_AlternateKey_uniqueness.stable.out
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: default
Log Message:
Fixed data integrity checks for tables: table_partitions, range_partitions and
value_partitions.
Note that sys.table_partitions.type column is not related sys.types.id column,
and sys.value_partitions.value column is nullable.
diffs (93 lines):
diff --git a/sql/test/sys-schema/Tests/check_AlternateKey_uniqueness.sql
b/sql/test/sys-schema/Tests/check_AlternateKey_uniqueness.sql
--- a/sql/test/sys-schema/Tests/check_AlternateKey_uniqueness.sql
+++ b/sql/test/sys-schema/Tests/check_AlternateKey_uniqueness.sql
@@ -50,5 +50,6 @@ SELECT COUNT(*) AS duplicates, def FROM
-- new tables introduced in 2019
SELECT COUNT(*) AS duplicates, table_id, column_id FROM sys.table_partitions
WHERE "column_id" IS NOT NULL GROUP BY table_id, column_id HAVING COUNT(*) >1;
SELECT COUNT(*) AS duplicates, table_id, "expression" FROM
sys.table_partitions WHERE "expression" IS NOT NULL GROUP BY table_id,
"expression" HAVING COUNT(*) >1;
-SELECT COUNT(*) AS duplicates, table_id, partition_id, maximum FROM
sys.range_partitions GROUP BY table_id, partition_id, maximum HAVING COUNT(*)
>1;
+SELECT COUNT(*) AS duplicates, table_id, partition_id, "maximum" FROM
sys.range_partitions GROUP BY table_id, partition_id, "maximum" HAVING COUNT(*)
>1;
+SELECT COUNT(*) AS duplicates, table_id, partition_id, "minimum" FROM
sys.range_partitions GROUP BY table_id, partition_id, "minimum" HAVING
COUNT(*) >1;
diff --git a/sql/test/sys-schema/Tests/check_AlternateKey_uniqueness.stable.out
b/sql/test/sys-schema/Tests/check_AlternateKey_uniqueness.stable.out
--- a/sql/test/sys-schema/Tests/check_AlternateKey_uniqueness.stable.out
+++ b/sql/test/sys-schema/Tests/check_AlternateKey_uniqueness.stable.out
@@ -199,6 +199,11 @@ Ready.
% duplicates, table_id, partition_id, maximum # name
% bigint, int, int, varchar # type
% 1, 1, 1, 0 # length
+#SELECT COUNT(*) AS duplicates, table_id, partition_id, "minimum" FROM
sys.range_partitions GROUP BY table_id, partition_id, "minimum" HAVING
COUNT(*) >1;
+% sys.L3, sys.range_partitions, sys.range_partitions,
sys.range_partitions # table_name
+% duplicates, table_id, partition_id, minimum # name
+% bigint, int, int, varchar # type
+% 1, 1, 1, 0 # length
# 16:31:36 >
# 16:31:36 > "Done."
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
@@ -142,11 +142,9 @@ 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.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);
SELECT * FROM sys.value_partitions WHERE "table_id" NOT IN (SELECT id FROM
sys._tables);
SELECT * FROM sys.value_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
@@ -534,11 +534,6 @@ 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);
-% 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
-% 1, 1, 1, 0, 1 # length
#SELECT * FROM sys.range_partitions WHERE "table_id" NOT IN (SELECT id FROM
sys._tables);
% sys.range_partitions, sys.range_partitions, sys.range_partitions,
sys.range_partitions, sys.range_partitions # table_name
% table_id, partition_id, minimum, maximum, with_nulls #
name
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
@@ -184,9 +184,10 @@ SELECT "unique", * FROM "sys"."storagemo
SELECT "id", * FROM "sys"."table_partitions" WHERE "id" IS NULL;
SELECT "table_id", * FROM "sys"."table_partitions" WHERE "table_id" IS NULL;
-SELECT "type", * FROM "sys"."table_partitions" WHERE "type" IS NULL;
+SELECT "type", * FROM "sys"."table_partitions" WHERE "type" IS NULL OR "type"
NOT IN (5,6,9,10);
-- 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", * FROM "sys"."table_partitions"
+WHERE ("column_id" IS NULL AND "expression" IS NULL) OR ("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,3 @@ 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;
-
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
@@ -754,11 +754,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
[email protected]
https://www.monetdb.org/mailman/listinfo/checkin-list
