This is an automated email from the ASF dual-hosted git repository. reshke pushed a commit to branch REL_2_STABLE in repository https://gitbox.apache.org/repos/asf/cloudberry.git
commit 7e30f9d15004c72a78c211c5a8c12fe196dc2537 Author: Tom Lane <[email protected]> AuthorDate: Thu Aug 4 11:11:22 2022 -0400 Add proper regression test for the recent SRFs-in-pathkeys problem. Remove the test case added by commit fac1b470a, which never actually worked to expose the problem it claimed to test. Replace it with a case that does expose the problem, and also covers the SRF-not- at-the-top deficiency repaired in 1aa8dad41. Richard Guo, with some editorialization by me Discussion: https://postgr.es/m/[email protected] --- src/test/regress/expected/incremental_sort.out | 12 ---------- .../expected/incremental_sort_optimizer.out | 14 ----------- src/test/regress/expected/select_parallel.out | 27 ++++++++++++++++++++++ src/test/regress/sql/incremental_sort.sql | 2 -- src/test/regress/sql/select_parallel.sql | 6 +++++ 5 files changed, 33 insertions(+), 28 deletions(-) diff --git a/src/test/regress/expected/incremental_sort.out b/src/test/regress/expected/incremental_sort.out index c5f52e1e97f..8a25141d8f5 100644 --- a/src/test/regress/expected/incremental_sort.out +++ b/src/test/regress/expected/incremental_sort.out @@ -1804,15 +1804,3 @@ order by 1, 2; -> Function Scan on generate_series (7 rows) --- Disallow pushing down sort when pathkey is an SRF. -explain (costs off) select unique1 from tenk1 order by unnest('{1,2}'::int[]); - QUERY PLAN -------------------------------------------------------------------------- - Gather Motion 3:1 (slice1; segments: 3) - Merge Key: (unnest('{1,2}'::anyarray)) - -> Sort - Sort Key: (unnest('{1,2}'::anyarray)) - -> ProjectSet - -> Index Only Scan using tenk1_unique1 on tenk1 -(6 rows) - diff --git a/src/test/regress/expected/incremental_sort_optimizer.out b/src/test/regress/expected/incremental_sort_optimizer.out index f5fd24f81ba..0648bb03799 100644 --- a/src/test/regress/expected/incremental_sort_optimizer.out +++ b/src/test/regress/expected/incremental_sort_optimizer.out @@ -1658,17 +1658,3 @@ order by 1, 2; Optimizer: Postgres query optimizer (8 rows) --- Disallow pushing down sort when pathkey is an SRF. -explain (costs off) select unique1 from tenk1 order by unnest('{1,2}'::int[]); - QUERY PLAN ------------------------------------------------------ - Result - -> Gather Motion 3:1 (slice1; segments: 3) - Merge Key: (unnest('{1,2}'::anyarray)) - -> Sort - Sort Key: (unnest('{1,2}'::anyarray)) - -> ProjectSet - -> Seq Scan on tenk1 - Optimizer: Pivotal Optimizer (GPORCA) -(8 rows) - diff --git a/src/test/regress/expected/select_parallel.out b/src/test/regress/expected/select_parallel.out index 4de01f4f632..db579197c3a 100644 --- a/src/test/regress/expected/select_parallel.out +++ b/src/test/regress/expected/select_parallel.out @@ -1217,6 +1217,33 @@ SELECT generate_series(1, two), array(select generate_series(1, two)) Optimizer: Postgres query optimizer (18 rows) +-- must disallow pushing sort below gather when pathkey contains an SRF +EXPLAIN (VERBOSE, COSTS OFF) +SELECT unnest(ARRAY[]::integer[]) + 1 AS pathkey + FROM tenk1 t1 JOIN tenk1 t2 ON TRUE + ORDER BY pathkey; + QUERY PLAN +------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- + Gather Motion 6:1 (slice1; segments: 6) + Output: (((unnest('{}'::integer[])) + 1)) + Merge Key: (((unnest('{}'::integer[])) + 1)) + -> Sort + Output: (((unnest('{}'::integer[])) + 1)) + Sort Key: (((unnest('{}'::integer[])) + 1)) + -> Result + Output: ((unnest('{}'::integer[])) + 1) + -> ProjectSet + Output: unnest('{}'::integer[]) + -> Nested Loop + -> Parallel Seq Scan on public.tenk1 t1 + Output: t1.unique1, t1.unique2, t1.two, t1.four, t1.ten, t1.twenty, t1.hundred, t1.thousand, t1.twothousand, t1.fivethous, t1.tenthous, t1.odd, t1.even, t1.stringu1, t1.stringu2, t1.string4 + -> Materialize + -> Broadcast Motion 3:6 (slice2; segments: 3) + -> Seq Scan on public.tenk1 t2 + Settings: enable_parallel = 'on', min_parallel_table_scan_size = '0', optimizer = 'off', parallel_setup_cost = '0', parallel_tuple_cost = '0' + Optimizer: Postgres query optimizer +(18 rows) + -- test passing expanded-value representations to workers CREATE FUNCTION make_some_array(int,int) returns int[] as $$declare x int[]; diff --git a/src/test/regress/sql/incremental_sort.sql b/src/test/regress/sql/incremental_sort.sql index afd1dab2045..648eced7e14 100644 --- a/src/test/regress/sql/incremental_sort.sql +++ b/src/test/regress/sql/incremental_sort.sql @@ -294,5 +294,3 @@ from tenk1, lateral (select tenk1.unique1 from generate_series(1, 1000)) as sub; explain (costs off) select sub.unique1, stringu1 || random()::text from tenk1, lateral (select tenk1.unique1 from generate_series(1, 1000)) as sub order by 1, 2; --- Disallow pushing down sort when pathkey is an SRF. -explain (costs off) select unique1 from tenk1 order by unnest('{1,2}'::int[]); diff --git a/src/test/regress/sql/select_parallel.sql b/src/test/regress/sql/select_parallel.sql index 846066fad05..4a22a001ef9 100644 --- a/src/test/regress/sql/select_parallel.sql +++ b/src/test/regress/sql/select_parallel.sql @@ -443,6 +443,12 @@ EXPLAIN (VERBOSE, COSTS OFF) SELECT generate_series(1, two), array(select generate_series(1, two)) FROM tenk1 ORDER BY tenthous; +-- must disallow pushing sort below gather when pathkey contains an SRF +EXPLAIN (VERBOSE, COSTS OFF) +SELECT unnest(ARRAY[]::integer[]) + 1 AS pathkey + FROM tenk1 t1 JOIN tenk1 t2 ON TRUE + ORDER BY pathkey; + -- test passing expanded-value representations to workers CREATE FUNCTION make_some_array(int,int) returns int[] as $$declare x int[]; --------------------------------------------------------------------- To unsubscribe, e-mail: [email protected] For additional commands, e-mail: [email protected]
