Changeset: 2f502b976a93 for MonetDB
URL: https://dev.monetdb.org/hg/MonetDB/rev/2f502b976a93
Modified Files:
sql/backends/monet5/sql_upgrades.c
sql/scripts/52_describe.sql
sql/scripts/76_dump.sql
sql/test/BugTracker-2026/Tests/All
sql/test/Dependencies/Tests/dependency_owner_schema_3.test
sql/test/emptydb-previous-upgrade-chain-hge/Tests/upgrade.stable.out.int128
sql/test/emptydb-previous-upgrade-chain/Tests/upgrade.stable.out
sql/test/emptydb-previous-upgrade-chain/Tests/upgrade.stable.out.int128
sql/test/emptydb-previous-upgrade-hge/Tests/upgrade.stable.out.int128
sql/test/emptydb-previous-upgrade/Tests/upgrade.stable.out
sql/test/emptydb-previous-upgrade/Tests/upgrade.stable.out.int128
sql/test/emptydb-upgrade-chain-hge/Tests/upgrade.stable.out.int128
sql/test/emptydb-upgrade-chain/Tests/upgrade.stable.out
sql/test/emptydb-upgrade-chain/Tests/upgrade.stable.out.int128
sql/test/emptydb-upgrade-hge/Tests/upgrade.stable.out.int128
sql/test/emptydb-upgrade/Tests/upgrade.stable.out
sql/test/emptydb-upgrade/Tests/upgrade.stable.out.int128
sql/test/emptydb/Tests/check.stable.out
sql/test/emptydb/Tests/check.stable.out.int128
sql/test/sql_dump/Tests/dump.test
sql/test/testdb-previous-upgrade-chain-hge/Tests/upgrade.stable.out.int128
sql/test/testdb-previous-upgrade-chain/Tests/upgrade.stable.out
sql/test/testdb-previous-upgrade-chain/Tests/upgrade.stable.out.int128
sql/test/testdb-previous-upgrade-hge/Tests/upgrade.stable.out.int128
sql/test/testdb-previous-upgrade/Tests/upgrade.stable.out
sql/test/testdb-previous-upgrade/Tests/upgrade.stable.out.int128
sql/test/testdb-upgrade-chain-hge/Tests/upgrade.stable.out.int128
sql/test/testdb-upgrade-chain/Tests/upgrade.stable.out
sql/test/testdb-upgrade-chain/Tests/upgrade.stable.out.int128
sql/test/testdb-upgrade-hge/Tests/upgrade.stable.out.int128
sql/test/testdb-upgrade/Tests/upgrade.stable.out
sql/test/testdb-upgrade/Tests/upgrade.stable.out.int128
Branch: default
Log Message:
Use ORDER BY in group_concat calls used in sys.dump_database.
Fixes #7976.
diffs (truncated from 6719 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
@@ -5413,10 +5413,31 @@ sql_update_default(Client c, mvc *sql, s
t->system = 0; /* make it non-system else the drop view
will fail */
t = mvc_bind_table(sql, s, "describe_tables");
t->system = 0; /* make it non-system else the drop view
will fail */
+ t = mvc_bind_table(sql, s, "describe_constraints");
+ t->system = 0; /* make it non-system else the drop view
will fail */
+ t = mvc_bind_table(sql, s, "describe_indices");
+ t->system = 0; /* make it non-system else the drop view
will fail */
+ t = mvc_bind_table(sql, s, "describe_partition_tables");
+ t->system = 0; /* make it non-system else the drop view
will fail */
+ t = mvc_bind_table(sql, s,
"dump_table_constraint_type");
+ t->system = 0; /* make it non-system else the drop view
will fail */
+ t = mvc_bind_table(sql, s, "dump_indices");
+ t->system = 0; /* make it non-system else the drop view
will fail */
+ t = mvc_bind_table(sql, s, "dump_partition_tables");
+ t->system = 0; /* make it non-system else the drop view
will fail */
+ t = mvc_bind_table(sql, s, "dump_foreign_keys");
+ t->system = 0; /* make it non-system else the drop view
will fail */
const char query[] =
"drop function sys.dump_database;\n"
"drop view sys.dump_tables;\n"
+ "drop view sys.dump_foreign_keys;\n"
+ "drop view sys.dump_table_constraint_type;\n"
+ "drop view sys.dump_indices;\n"
+ "drop view sys.dump_partition_tables;\n"
"drop view sys.describe_tables;\n"
+ "drop view sys.describe_constraints;\n"
+ "drop view sys.describe_indices;\n"
+ "drop view sys.describe_partition_tables;\n"
"CREATE VIEW sys.describe_tables AS\n"
" SELECT\n"
" t.id o,\n"
@@ -5428,7 +5449,8 @@ sql_update_default(Client c, mvc *sql, s
" GROUP_CONCAT(\n"
" sys.DQ(c.name) || ' ' ||\n"
" sys.describe_type(c.type, c.type_digits,
c.type_scale) ||\n"
- " ifthenelse(c.\"null\" = false, ' NOT NULL',
'')\n"
+ " ifthenelse(c.\"null\" = false, ' NOT NULL',
''\n"
+ " ORDER BY c.number)\n"
" , ', ') || ')'\n"
" FROM sys._columns c\n"
" WHERE c.table_id = t.id) col,\n"
@@ -5449,6 +5471,149 @@ sql_update_default(Client c, mvc *sql, s
" AND ts.table_type_id = t.type\n"
" AND s.name <> 'tmp';\n"
"GRANT SELECT ON sys.describe_tables TO
PUBLIC;\n"
+ "CREATE VIEW sys.describe_constraints AS\n"
+ " SELECT\n"
+ " s.name sch,\n"
+ " t.name tbl,\n"
+ " kc.name col,\n"
+ " k.name con,\n"
+ " CASE k.type WHEN 0 THEN 'PRIMARY KEY' WHEN 1
THEN 'UNIQUE' END tpe,\n"
+ " kc.nr nr\n"
+ " FROM sys.schemas s, sys._tables t,
sys.objects kc, sys.keys k\n"
+ " WHERE kc.id = k.id\n"
+ " AND k.table_id = t.id\n"
+ " AND s.id = t.schema_id\n"
+ " AND t.system = FALSE\n"
+ " AND k.type in (0, 1);\n"
+ "GRANT SELECT ON sys.describe_constraints TO
PUBLIC;\n"
+ "CREATE VIEW sys.describe_indices AS\n"
+ " WITH it (id, idx) AS (VALUES (0, 'INDEX'),
(4, 'IMPRINTS INDEX'), (5, 'ORDERED INDEX')) --UNIQUE INDEX wraps to INDEX.\n"
+ " SELECT\n"
+ " i.name ind,\n"
+ " s.name sch,\n"
+ " t.name tbl,\n"
+ " c.name col,\n"
+ " it.idx tpe,\n"
+ " kc.nr nr\n"
+ " FROM\n"
+ " sys.idxs AS i LEFT JOIN sys.keys AS k ON
i.name = k.name,\n"
+ " sys.objects AS kc,\n"
+ " sys._columns AS c,\n"
+ " sys.schemas s,\n"
+ " sys._tables AS t,\n"
+ " it\n"
+ " WHERE\n"
+ " i.table_id = t.id\n"
+ " AND i.id = kc.id\n"
+ " AND kc.name = c.name\n"
+ " AND t.id = c.table_id\n"
+ " AND t.schema_id = s.id\n"
+ " AND k.type IS NULL\n"
+ " AND i.type = it.id\n"
+ " ORDER BY i.name, kc.nr;\n"
+ "GRANT SELECT ON sys.describe_indices TO
PUBLIC;\n"
+ "CREATE VIEW sys.describe_partition_tables AS\n"
+ " SELECT\n"
+ " m_sch,\n"
+ " m_tbl,\n"
+ " p_sch,\n"
+ " p_tbl,\n"
+ " CASE\n"
+ " WHEN p_raw_type IS NULL THEN 'READ ONLY'\n"
+ " WHEN (p_raw_type = 'VALUES' AND pvalues IS
NULL) OR (p_raw_type = 'RANGE' AND minimum IS NULL AND maximum IS NULL AND
with_nulls) THEN 'FOR NULLS'\n"
+ " ELSE p_raw_type\n"
+ " END AS tpe,\n"
+ " pvalues,\n"
+ " minimum,\n"
+ " maximum,\n"
+ " with_nulls\n"
+ " FROM\n"
+ " (WITH\n"
+ " tp(\"type\", table_id) AS\n"
+ " (SELECT ifthenelse((table_partitions.\"type\"
& 2) = 2, 'VALUES', 'RANGE'), table_partitions.table_id FROM
sys.table_partitions),\n"
+ " subq(m_tid, p_mid, \"type\", m_sch, m_tbl,
p_sch, p_tbl) AS\n"
+ " (SELECT m_t.id, p_m.id, m_t.\"type\",
m_s.name, m_t.name, p_s.name, p_m.name\n"
+ " FROM sys.schemas m_s, sys._tables m_t,
sys.dependencies d, sys.schemas p_s, sys._tables p_m\n"
+ " WHERE m_t.\"type\" IN (3, 6)\n"
+ " AND m_t.schema_id = m_s.id\n"
+ " AND m_s.name <> 'tmp'\n"
+ " AND m_t.system = FALSE\n"
+ " AND m_t.id = d.depend_id\n"
+ " AND d.id = p_m.id\n"
+ " AND p_m.schema_id = p_s.id\n"
+ " ORDER BY m_t.id, p_m.id),\n"
+ " vals(id,vals) as\n"
+ " (SELECT vp.table_id, GROUP_CONCAT(vp.value,
',' ORDER BY vp.value) FROM sys.value_partitions vp GROUP BY vp.table_id)\n"
+ " SELECT\n"
+ " subq.m_sch,\n"
+ " subq.m_tbl,\n"
+ " subq.p_sch,\n"
+ " subq.p_tbl,\n"
+ " tp.\"type\" AS p_raw_type,\n"
+ " CASE WHEN tp.\"type\" = 'VALUES'\n"
+ " THEN (SELECT vals.vals FROM vals WHERE
vals.id = subq.p_mid)\n"
+ " ELSE NULL\n"
+ " END AS pvalues,\n"
+ " CASE WHEN tp.\"type\" = 'RANGE'\n"
+ " THEN (SELECT minimum FROM
sys.range_partitions rp WHERE rp.table_id = subq.p_mid)\n"
+ " ELSE NULL\n"
+ " END AS minimum,\n"
+ " CASE WHEN tp.\"type\" = 'RANGE'\n"
+ " THEN (SELECT maximum FROM
sys.range_partitions rp WHERE rp.table_id = subq.p_mid)\n"
+ " ELSE NULL\n"
+ " END AS maximum,\n"
+ " CASE WHEN tp.\"type\" = 'VALUES'\n"
+ " THEN EXISTS(SELECT vp.value FROM
sys.value_partitions vp WHERE vp.table_id = subq.p_mid AND vp.value IS NULL)\n"
+ " ELSE (SELECT rp.with_nulls FROM
sys.range_partitions rp WHERE rp.table_id = subq.p_mid)\n"
+ " END AS with_nulls\n"
+ " FROM\n"
+ " subq LEFT OUTER JOIN tp\n"
+ " ON subq.m_tid = tp.table_id) AS tmp_pi;\n"
+ "GRANT SELECT ON sys.describe_partition_tables
TO PUBLIC;\n"
+ "CREATE VIEW sys.dump_table_constraint_type
AS\n"
+ " SELECT\n"
+ " 'ALTER TABLE ' || sys.FQN(sch, tbl) || ' ADD
CONSTRAINT ' || sys.DQ(con) || ' '||\n"
+ " tpe || ' (' || GROUP_CONCAT(sys.DQ(col), ', '
ORDER BY nr) || ');' stmt,\n"
+ " sch schema_name,\n"
+ " tbl table_name,\n"
+ " con constraint_name\n"
+ " FROM sys.describe_constraints GROUP BY sch,
tbl, con, tpe;\n"
+ "CREATE VIEW sys.dump_indices AS\n"
+ " SELECT\n"
+ " 'CREATE ' || tpe || ' ' || sys.DQ(ind) || '
ON ' || sys.FQN(sch, tbl) || '(' || GROUP_CONCAT(col, ',' ORDER BY nr) || ');'
stmt,\n"
+ " sch schema_name,\n"
+ " tbl table_name,\n"
+ " ind index_name\n"
+ " FROM sys.describe_indices GROUP BY ind, tpe,
sch, tbl;\n"
+ "CREATE VIEW sys.dump_foreign_keys AS\n"
+ " SELECT\n"
+ " 'ALTER TABLE ' || sys.FQN(fk_s, fk_t) || '
ADD CONSTRAINT ' || sys.DQ(fk) || ' ' ||\n"
+ " 'FOREIGN KEY(' || GROUP_CONCAT(sys.DQ(fk_c),
',' ORDER BY o) ||') ' ||\n"
+ " 'REFERENCES ' || sys.FQN(pk_s, pk_t) || '('
|| GROUP_CONCAT(sys.DQ(pk_c), ',' ORDER BY o) || ') ' ||\n"
+ " 'ON DELETE ' || on_delete || ' ON UPDATE ' ||
on_update ||\n"
+ " ';' stmt,\n"
+ " fk_s foreign_schema_name,\n"
+ " fk_t foreign_table_name,\n"
+ " pk_s primary_schema_name,\n"
+ " pk_t primary_table_name,\n"
+ " fk key_name\n"
+ " FROM sys.describe_foreign_keys GROUP BY fk_s,
fk_t, pk_s, pk_t, fk, on_delete, on_update;\n"
+ "CREATE VIEW sys.dump_partition_tables AS\n"
+ " SELECT\n"
+ " 'ALTER TABLE ' || sys.FQN(m_sch, m_tbl) || '
ADD TABLE ' || sys.FQN(p_sch, p_tbl) ||\n"
+ " CASE\n"
+ " WHEN tpe = 'VALUES' THEN ' AS PARTITION IN ('
|| pvalues || ')'\n"
+ " WHEN tpe = 'RANGE' THEN ' AS PARTITION FROM '
|| ifthenelse(minimum IS NOT NULL, sys.SQ(minimum), 'RANGE MINVALUE') || ' TO '
|| ifthenelse(maximum IS NOT NULL, sys.SQ(maximum), 'RANGE MAXVALUE')\n"
+ " WHEN tpe = 'FOR NULLS' THEN ' AS PARTITION
FOR NULL VALUES'\n"
+ " ELSE '' --'READ ONLY'\n"
+ " END ||\n"
+ " CASE WHEN tpe in ('VALUES', 'RANGE') AND
with_nulls THEN ' WITH NULL VALUES' ELSE '' END ||\n"
+ " ';' stmt,\n"
+ " m_sch merge_schema_name,\n"
+ " m_tbl merge_table_name,\n"
+ " p_sch partition_schema_name,\n"
+ " p_tbl partition_table_name\n"
+ " FROM sys.describe_partition_tables;\n"
"CREATE VIEW sys.dump_tables AS\n"
" SELECT\n"
" t.o o,\n"
@@ -5498,7 +5663,7 @@ sql_update_default(Client c, mvc *sql, s
" RETURN sys.dump_statements;\n"
"END;\n"
"update sys.functions set system = true where
not system and schema_id = 2000 and name = 'dump_database' and type = 5;\n" /*
F_UNION == 5 */
- "update sys._tables set system = true where not
system and schema_id = 2000 and name in ('dump_tables', 'describe_tables');\n";
+ "update sys._tables set system = true where not
system and schema_id = 2000 and name in ('describe_constraints',
'describe_indices', 'describe_partition_tables', 'describe_tables',
'dump_foreign_keys', 'dump_indices', 'dump_partition_tables',
'dump_table_constraint_type', 'dump_tables');\n";
printf("Running database upgrade commands:\n%s\n",
query);
fflush(stdout);
err = SQLstatementIntern(c, query, "update", true,
false, NULL);
diff --git a/sql/scripts/52_describe.sql b/sql/scripts/52_describe.sql
--- a/sql/scripts/52_describe.sql
+++ b/sql/scripts/52_describe.sql
@@ -90,7 +90,8 @@ CREATE VIEW sys.describe_constraints AS
t.name tbl,
kc.name col,
k.name con,
- CASE k.type WHEN 0 THEN 'PRIMARY KEY' WHEN 1 THEN 'UNIQUE' END
tpe
+ CASE k.type WHEN 0 THEN 'PRIMARY KEY' WHEN 1 THEN 'UNIQUE' END
tpe,
+ kc.nr nr
FROM sys.schemas s, sys._tables t, sys.objects kc, sys.keys k
WHERE kc.id = k.id
AND k.table_id = t.id
@@ -105,7 +106,8 @@ CREATE VIEW sys.describe_indices AS
s.name sch,
t.name tbl,
c.name col,
- it.idx tpe
+ it.idx tpe,
+ kc.nr nr
FROM
sys.idxs AS i LEFT JOIN sys.keys AS k ON i.name = k.name,
sys.objects AS kc,
@@ -210,7 +212,8 @@ CREATE VIEW sys.describe_tables AS
GROUP_CONCAT(
sys.DQ(c.name) || ' ' ||
sys.describe_type(c.type, c.type_digits,
c.type_scale) ||
- ifthenelse(c."null" = false, ' NOT NULL', '')
+ ifthenelse(c."null" = false, ' NOT NULL', ''
+ ORDER BY c.number)
, ', ') || ')'
FROM sys._columns c
WHERE c.table_id = t.id) col,
@@ -366,7 +369,7 @@ CREATE VIEW sys.describe_partition_table
AND p_m.schema_id = p_s.id
ORDER BY m_t.id, p_m.id),
vals(id,vals) as
- (SELECT vp.table_id, GROUP_CONCAT(vp.value, ',') FROM
sys.value_partitions vp GROUP BY vp.table_id)
+ (SELECT vp.table_id, GROUP_CONCAT(vp.value, ',' ORDER BY
vp.value) FROM sys.value_partitions vp GROUP BY vp.table_id)
SELECT
subq.m_sch,
subq.m_tbl,
diff --git a/sql/scripts/76_dump.sql b/sql/scripts/76_dump.sql
--- a/sql/scripts/76_dump.sql
+++ b/sql/scripts/76_dump.sql
@@ -54,7 +54,7 @@ CREATE VIEW sys.dump_grant_user_privileg
CREATE VIEW sys.dump_table_constraint_type AS
SELECT
'ALTER TABLE ' || sys.FQN(sch, tbl) || ' ADD CONSTRAINT ' || sys.DQ(con)
|| ' '||
- tpe || ' (' || GROUP_CONCAT(sys.DQ(col), ', ') || ');' stmt,
+ tpe || ' (' || GROUP_CONCAT(sys.DQ(col), ', ' ORDER BY nr) || ');' stmt,
sch schema_name,
tbl table_name,
con constraint_name
@@ -137,7 +137,7 @@ CREATE VIEW sys.dump_function_grants AS
CREATE VIEW sys.dump_indices AS
SELECT
- 'CREATE ' || tpe || ' ' || sys.DQ(ind) || ' ON ' || sys.FQN(sch, tbl) ||
'(' || GROUP_CONCAT(col) || ');' stmt,
+ 'CREATE ' || tpe || ' ' || sys.DQ(ind) || ' ON ' || sys.FQN(sch, tbl) ||
'(' || GROUP_CONCAT(col, ',' ORDER BY nr) || ');' stmt,
sch schema_name,
tbl table_name,
ind index_name
@@ -153,8 +153,8 @@ CREATE VIEW sys.dump_column_defaults AS
CREATE VIEW sys.dump_foreign_keys AS
SELECT
'ALTER TABLE ' || sys.FQN(fk_s, fk_t) || ' ADD CONSTRAINT ' || sys.DQ(fk)
|| ' ' ||
- 'FOREIGN KEY(' || GROUP_CONCAT(sys.DQ(fk_c), ',') ||') ' ||
- 'REFERENCES ' || sys.FQN(pk_s, pk_t) || '(' ||
GROUP_CONCAT(sys.DQ(pk_c), ',') || ') ' ||
+ 'FOREIGN KEY(' || GROUP_CONCAT(sys.DQ(fk_c), ',' ORDER BY o) ||') ' ||
+ 'REFERENCES ' || sys.FQN(pk_s, pk_t) || '(' ||
GROUP_CONCAT(sys.DQ(pk_c), ',' ORDER BY o) || ') ' ||
'ON DELETE ' || on_delete || ' ON UPDATE ' || on_update ||
';' stmt,
fk_s foreign_schema_name,
diff --git a/sql/test/BugTracker-2026/Tests/All
b/sql/test/BugTracker-2026/Tests/All
--- a/sql/test/BugTracker-2026/Tests/All
+++ b/sql/test/BugTracker-2026/Tests/All
@@ -161,7 +161,7 @@ PIPELINE?7966-optimize-select-distinct-f
7973-groupby-list-null-row-bug
KNOWNFAIL?7974-muli-column-not-in-subquery-bug
7975-cleanup-join-exps
-KNOWNFAIL?7976-dump_database-composite-key-order-bug
+7976-dump_database-composite-key-order-bug
7977-prepare-optimize
7978-name-leak-prepare-error-recompile
7979-prepare-error-exec-113
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
@@ -15,791 +15,273 @@ DROP SCHEMA test_2
query TTT nosort
SELECT s.name, u.name, 'DEP_USER' from sys.schemas as s, users u where
u.default_schema = s.id order by s.name, u.name
----
-sys
-.snapshot
-DEP_USER
_______________________________________________
checkin-list mailing list -- [email protected]
To unsubscribe send an email to [email protected]
