From e15e56713702c45402d2728f3d7d68df96b2fbbe Mon Sep 17 00:00:00 2001 From: Richard Guo Date: Tue, 26 Nov 2024 11:12:57 +0900 Subject: [PATCH] Fix test case from a8ccf4e93 Commit a8ccf4e93 uses the same table name "distinct_tbl" in both select_distinct.sql and select_distinct_on.sql, which could cause conflicts when these two test scripts are run in parallel. Fix by renaming the table in select_distinct_on.sql to "distinct_on_tbl". Per buildfarm (via Tom Lane) Discussion: https://postgr.es/m/1572004.1732583549@sss.pgh.pa.us --- .../regress/expected/select_distinct_on.out | 52 +++++++++---------- src/test/regress/sql/select_distinct_on.sql | 26 +++++----- 2 files changed, 39 insertions(+), 39 deletions(-) diff --git a/src/test/regress/expected/select_distinct_on.out b/src/test/regress/expected/select_distinct_on.out index 69e0cf3959c..75b1e7d300f 100644 --- a/src/test/regress/expected/select_distinct_on.out +++ b/src/test/regress/expected/select_distinct_on.out @@ -125,23 +125,23 @@ SELECT DISTINCT ON (four) four,hundred -- Test the planner's ability to reorder the distinctClause Pathkeys to match -- the input path's ordering -- -CREATE TABLE distinct_tbl (x int, y int, z int); -INSERT INTO distinct_tbl SELECT i%10, i%10, i%10 FROM generate_series(1, 1000) AS i; -CREATE INDEX distinct_tbl_x_y_idx ON distinct_tbl (x, y); -ANALYZE distinct_tbl; +CREATE TABLE distinct_on_tbl (x int, y int, z int); +INSERT INTO distinct_on_tbl SELECT i%10, i%10, i%10 FROM generate_series(1, 1000) AS i; +CREATE INDEX distinct_on_tbl_x_y_idx ON distinct_on_tbl (x, y); +ANALYZE distinct_on_tbl; -- Produce results with sorting. SET enable_hashagg TO OFF; -- Ensure we avoid the need to re-sort by reordering the distinctClause -- Pathkeys to match the ordering of the input path EXPLAIN (COSTS OFF) -SELECT DISTINCT ON (y, x) x, y FROM distinct_tbl; - QUERY PLAN ------------------------------------------------------------------- +SELECT DISTINCT ON (y, x) x, y FROM distinct_on_tbl; + QUERY PLAN +------------------------------------------------------------------------ Unique - -> Index Only Scan using distinct_tbl_x_y_idx on distinct_tbl + -> Index Only Scan using distinct_on_tbl_x_y_idx on distinct_on_tbl (2 rows) -SELECT DISTINCT ON (y, x) x, y FROM distinct_tbl; +SELECT DISTINCT ON (y, x) x, y FROM distinct_on_tbl; x | y ---+--- 0 | 0 @@ -159,18 +159,18 @@ SELECT DISTINCT ON (y, x) x, y FROM distinct_tbl; -- Ensure we leverage incremental-sort by reordering the distinctClause -- Pathkeys to partially match the ordering of the input path EXPLAIN (COSTS OFF) -SELECT DISTINCT ON (y, x) x, y FROM (SELECT * FROM distinct_tbl ORDER BY x) s; - QUERY PLAN ------------------------------------------------------------------------------- +SELECT DISTINCT ON (y, x) x, y FROM (SELECT * FROM distinct_on_tbl ORDER BY x) s; + QUERY PLAN +------------------------------------------------------------------------------------ Unique -> Incremental Sort Sort Key: s.x, s.y Presorted Key: s.x -> Subquery Scan on s - -> Index Only Scan using distinct_tbl_x_y_idx on distinct_tbl + -> Index Only Scan using distinct_on_tbl_x_y_idx on distinct_on_tbl (6 rows) -SELECT DISTINCT ON (y, x) x, y FROM (SELECT * FROM distinct_tbl ORDER BY x) s; +SELECT DISTINCT ON (y, x) x, y FROM (SELECT * FROM distinct_on_tbl ORDER BY x) s; x | y ---+--- 0 | 0 @@ -188,16 +188,16 @@ SELECT DISTINCT ON (y, x) x, y FROM (SELECT * FROM distinct_tbl ORDER BY x) s; -- Ensure we reorder the distinctClause Pathkeys to match the ordering of the -- input path even if there is ORDER BY clause EXPLAIN (COSTS OFF) -SELECT DISTINCT ON (y, x) x, y FROM distinct_tbl ORDER BY y; - QUERY PLAN ------------------------------------------------------------------------- +SELECT DISTINCT ON (y, x) x, y FROM distinct_on_tbl ORDER BY y; + QUERY PLAN +------------------------------------------------------------------------------ Sort Sort Key: y -> Unique - -> Index Only Scan using distinct_tbl_x_y_idx on distinct_tbl + -> Index Only Scan using distinct_on_tbl_x_y_idx on distinct_on_tbl (4 rows) -SELECT DISTINCT ON (y, x) x, y FROM distinct_tbl ORDER BY y; +SELECT DISTINCT ON (y, x) x, y FROM distinct_on_tbl ORDER BY y; x | y ---+--- 0 | 0 @@ -214,9 +214,9 @@ SELECT DISTINCT ON (y, x) x, y FROM distinct_tbl ORDER BY y; -- Ensure the resulting pathkey list matches the initial distinctClause Pathkeys EXPLAIN (COSTS OFF) -SELECT DISTINCT ON (y, x) x, y FROM (select * from distinct_tbl order by x, z, y) s ORDER BY y, x, z; - QUERY PLAN ------------------------------------------------------------------------------------- +SELECT DISTINCT ON (y, x) x, y FROM (select * from distinct_on_tbl order by x, z, y) s ORDER BY y, x, z; + QUERY PLAN +--------------------------------------------------------------------------------------------- Sort Sort Key: s.y, s.x, s.z -> Unique @@ -225,11 +225,11 @@ SELECT DISTINCT ON (y, x) x, y FROM (select * from distinct_tbl order by x, z, y Presorted Key: s.x -> Subquery Scan on s -> Sort - Sort Key: distinct_tbl.x, distinct_tbl.z, distinct_tbl.y - -> Seq Scan on distinct_tbl + Sort Key: distinct_on_tbl.x, distinct_on_tbl.z, distinct_on_tbl.y + -> Seq Scan on distinct_on_tbl (10 rows) -SELECT DISTINCT ON (y, x) x, y FROM (select * from distinct_tbl order by x, z, y) s ORDER BY y, x, z; +SELECT DISTINCT ON (y, x) x, y FROM (select * from distinct_on_tbl order by x, z, y) s ORDER BY y, x, z; x | y ---+--- 0 | 0 @@ -245,4 +245,4 @@ SELECT DISTINCT ON (y, x) x, y FROM (select * from distinct_tbl order by x, z, y (10 rows) RESET enable_hashagg; -DROP TABLE distinct_tbl; +DROP TABLE distinct_on_tbl; diff --git a/src/test/regress/sql/select_distinct_on.sql b/src/test/regress/sql/select_distinct_on.sql index d79dace94c4..8680749e49a 100644 --- a/src/test/regress/sql/select_distinct_on.sql +++ b/src/test/regress/sql/select_distinct_on.sql @@ -48,10 +48,10 @@ SELECT DISTINCT ON (four) four,hundred -- the input path's ordering -- -CREATE TABLE distinct_tbl (x int, y int, z int); -INSERT INTO distinct_tbl SELECT i%10, i%10, i%10 FROM generate_series(1, 1000) AS i; -CREATE INDEX distinct_tbl_x_y_idx ON distinct_tbl (x, y); -ANALYZE distinct_tbl; +CREATE TABLE distinct_on_tbl (x int, y int, z int); +INSERT INTO distinct_on_tbl SELECT i%10, i%10, i%10 FROM generate_series(1, 1000) AS i; +CREATE INDEX distinct_on_tbl_x_y_idx ON distinct_on_tbl (x, y); +ANALYZE distinct_on_tbl; -- Produce results with sorting. SET enable_hashagg TO OFF; @@ -59,26 +59,26 @@ SET enable_hashagg TO OFF; -- Ensure we avoid the need to re-sort by reordering the distinctClause -- Pathkeys to match the ordering of the input path EXPLAIN (COSTS OFF) -SELECT DISTINCT ON (y, x) x, y FROM distinct_tbl; -SELECT DISTINCT ON (y, x) x, y FROM distinct_tbl; +SELECT DISTINCT ON (y, x) x, y FROM distinct_on_tbl; +SELECT DISTINCT ON (y, x) x, y FROM distinct_on_tbl; -- Ensure we leverage incremental-sort by reordering the distinctClause -- Pathkeys to partially match the ordering of the input path EXPLAIN (COSTS OFF) -SELECT DISTINCT ON (y, x) x, y FROM (SELECT * FROM distinct_tbl ORDER BY x) s; -SELECT DISTINCT ON (y, x) x, y FROM (SELECT * FROM distinct_tbl ORDER BY x) s; +SELECT DISTINCT ON (y, x) x, y FROM (SELECT * FROM distinct_on_tbl ORDER BY x) s; +SELECT DISTINCT ON (y, x) x, y FROM (SELECT * FROM distinct_on_tbl ORDER BY x) s; -- Ensure we reorder the distinctClause Pathkeys to match the ordering of the -- input path even if there is ORDER BY clause EXPLAIN (COSTS OFF) -SELECT DISTINCT ON (y, x) x, y FROM distinct_tbl ORDER BY y; -SELECT DISTINCT ON (y, x) x, y FROM distinct_tbl ORDER BY y; +SELECT DISTINCT ON (y, x) x, y FROM distinct_on_tbl ORDER BY y; +SELECT DISTINCT ON (y, x) x, y FROM distinct_on_tbl ORDER BY y; -- Ensure the resulting pathkey list matches the initial distinctClause Pathkeys EXPLAIN (COSTS OFF) -SELECT DISTINCT ON (y, x) x, y FROM (select * from distinct_tbl order by x, z, y) s ORDER BY y, x, z; -SELECT DISTINCT ON (y, x) x, y FROM (select * from distinct_tbl order by x, z, y) s ORDER BY y, x, z; +SELECT DISTINCT ON (y, x) x, y FROM (select * from distinct_on_tbl order by x, z, y) s ORDER BY y, x, z; +SELECT DISTINCT ON (y, x) x, y FROM (select * from distinct_on_tbl order by x, z, y) s ORDER BY y, x, z; RESET enable_hashagg; -DROP TABLE distinct_tbl; +DROP TABLE distinct_on_tbl; -- 2.30.2