Changeset: 02b10f3a1328 for MonetDB URL: http://dev.monetdb.org/hg/MonetDB?cmd=changeset;node=02b10f3a1328 Modified Files: sql/test/sys-schema/Tests/check_AlternateKey_uniqueness.sql sql/test/sys-schema/Tests/check_AlternateKey_uniqueness.stable.out Branch: Dec2016 Log Message:
Adding 3 missing alternate key checks: the combination of table_id and column number must be unique also. diffs (38 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 @@ -8,6 +8,9 @@ SELECT COUNT(*) AS duplicates, schema_id SELECT COUNT(*) AS duplicates, table_id, name FROM sys._columns GROUP BY table_id, name HAVING COUNT(*) > 1; SELECT COUNT(*) AS duplicates, table_id, name FROM tmp._columns GROUP BY table_id, name HAVING COUNT(*) > 1; SELECT COUNT(*) AS duplicates, table_id, name FROM sys.columns GROUP BY table_id, name HAVING COUNT(*) > 1; +SELECT COUNT(*) AS duplicates, table_id, number FROM sys._columns GROUP BY table_id, number HAVING COUNT(*) > 1; +SELECT COUNT(*) AS duplicates, table_id, number FROM tmp._columns GROUP BY table_id, number HAVING COUNT(*) > 1; +SELECT COUNT(*) AS duplicates, table_id, number FROM sys.columns GROUP BY table_id, number HAVING COUNT(*) > 1; -- The id values from sys.schemas, sys._tables, sys._columns and sys.functions combined must be exclusive (see FK from sys.privileges.obj_id) SELECT COUNT(*) AS duplicates, T.id FROM (SELECT id FROM sys.schemas UNION ALL SELECT id FROM sys._tables UNION ALL SELECT id FROM sys._columns UNION ALL SELECT id FROM sys.functions) T GROUP BY T.id 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 @@ -64,6 +64,21 @@ Ready. % duplicates, table_id, name # name % bigint, int, varchar # type % 1, 1, 0 # length +#SELECT COUNT(*) AS duplicates, table_id, number FROM sys._columns GROUP BY table_id, number HAVING COUNT(*) > 1; +% sys.L3, sys._columns, sys._columns # table_name +% duplicates, table_id, number # name +% bigint, int, int # type +% 1, 1, 1 # length +#SELECT COUNT(*) AS duplicates, table_id, number FROM tmp._columns GROUP BY table_id, number HAVING COUNT(*) > 1; +% tmp.L3, tmp._columns, tmp._columns # table_name +% duplicates, table_id, number # name +% bigint, int, int # type +% 1, 1, 1 # length +#SELECT COUNT(*) AS duplicates, table_id, number FROM sys.columns GROUP BY table_id, number HAVING COUNT(*) > 1; +% .L15, .columns, .columns # table_name +% duplicates, table_id, number # name +% bigint, int, int # type +% 1, 1, 1 # length #SELECT COUNT(*) AS duplicates, T.id FROM (SELECT id FROM sys.schemas UNION ALL SELECT id FROM sys._tables UNION ALL SELECT id FROM sys._columns UNION ALL SELECT id FROM sys.functions) T GROUP BY T.id HAVING COUNT(*) > 1; % .L26, .t # table_name % duplicates, id # name _______________________________________________ checkin-list mailing list [email protected] https://www.monetdb.org/mailman/listinfo/checkin-list
