This is an automated email from the ASF dual-hosted git repository.
chenjinbao1989 pushed a commit to branch cbdb-postgres-merge
in repository https://gitbox.apache.org/repos/asf/cloudberry.git
The following commit(s) were added to refs/heads/cbdb-postgres-merge by this
push:
new 6ed77487573 Fix errors for aggregate with limit
6ed77487573 is described below
commit 6ed7748757311400cfbeeaab0523ab69e9a9e04c
Author: Jinbao Chen <[email protected]>
AuthorDate: Sun Nov 16 22:09:38 2025 +0800
Fix errors for aggregate with limit
---
src/backend/cdb/cdbgroupingpaths.c | 2 +-
src/backend/optimizer/plan/planner.c | 108 +++++------
src/test/regress/expected/aggregates.out | 232 +++++++++++------------
src/test/regress/expected/select_distinct.out | 77 ++++----
src/test/regress/expected/select_distinct_on.out | 69 +++++--
src/test/regress/expected/subselect.out | 119 +-----------
src/test/regress/expected/union.out | 3 +-
src/test/regress/serial_schedule | 4 +-
src/test/regress/sql/aggregates.sql | 4 +-
9 files changed, 273 insertions(+), 345 deletions(-)
diff --git a/src/backend/cdb/cdbgroupingpaths.c
b/src/backend/cdb/cdbgroupingpaths.c
index 623afd4a371..d87af54f52b 100644
--- a/src/backend/cdb/cdbgroupingpaths.c
+++ b/src/backend/cdb/cdbgroupingpaths.c
@@ -617,7 +617,7 @@ cdb_create_twostage_distinct_paths(PlannerInfo *root,
ctx.hasDistinctOn = true;
ctx.groupingSets = NIL;
ctx.havingQual = NULL;
- ctx.groupClause = root->processed_distinctClause;
+ ctx.groupClause = parse->distinctClause;
ctx.group_tles = get_common_group_tles(target, parse->distinctClause,
NIL);
ctx.final_groupClause = ctx.groupClause;
ctx.final_group_tles = ctx.group_tles;
diff --git a/src/backend/optimizer/plan/planner.c
b/src/backend/optimizer/plan/planner.c
index 1dfe3cf0f11..bae6946da84 100644
--- a/src/backend/optimizer/plan/planner.c
+++ b/src/backend/optimizer/plan/planner.c
@@ -5798,7 +5798,7 @@ create_final_distinct_paths(PlannerInfo *root, RelOptInfo
*input_rel,
* the other.)
*/
List *needed_pathkeys;
- double limittuples = root->distinct_pathkeys == NIL ?
1.0 : -1.0;
+// double limittuples = root->distinct_pathkeys == NIL ?
1.0 : -1.0;
if (parse->hasDistinctOn &&
list_length(root->distinct_pathkeys) <
@@ -5820,38 +5820,38 @@ create_final_distinct_paths(PlannerInfo *root,
RelOptInfo *input_rel,
if (is_sorted)
sorted_path = input_path;
- else
- {
- /*
- * Try at least sorting the cheapest path and
also try
- * incrementally sorting any path which is
partially sorted
- * already (no need to deal with paths which
have presorted
- * keys when incremental sort is disabled
unless it's the
- * cheapest input path).
- */
- if (input_path != cheapest_input_path &&
- (presorted_keys == 0 ||
!enable_incremental_sort))
- continue;
-
- /*
- * We've no need to consider both a sort and
incremental sort.
- * We'll just do a sort if there are no
presorted keys and an
- * incremental sort when there are presorted
keys.
- */
- if (presorted_keys == 0 ||
!enable_incremental_sort)
- sorted_path = (Path *)
create_sort_path(root,
-
distinct_rel,
-
input_path,
-
needed_pathkeys,
-
limittuples);
- else
- sorted_path = (Path *)
create_incremental_sort_path(root,
-
distinct_rel,
-
input_path,
-
needed_pathkeys,
-
presorted_keys,
-
limittuples);
- }
+// else
+// {
+// /*
+// * Try at least sorting the cheapest path and
also try
+// * incrementally sorting any path which is
partially sorted
+// * already (no need to deal with paths which
have presorted
+// * keys when incremental sort is disabled
unless it's the
+// * cheapest input path).
+// */
+// if (input_path != cheapest_input_path &&
+// (presorted_keys == 0 ||
!enable_incremental_sort))
+// continue;
+//
+// /*
+// * We've no need to consider both a sort and
incremental sort.
+// * We'll just do a sort if there are no
presorted keys and an
+// * incremental sort when there are presorted
keys.
+// */
+// if (presorted_keys == 0 ||
!enable_incremental_sort)
+// sorted_path = (Path *)
create_sort_path(root,
+//
distinct_rel,
+//
input_path,
+//
needed_pathkeys,
+//
limittuples);
+// else
+// sorted_path = (Path *)
create_incremental_sort_path(root,
+//
distinct_rel,
+//
input_path,
+//
needed_pathkeys,
+//
presorted_keys,
+//
limittuples);
+// }
/*
* distinct_pathkeys may have become empty if all of
the pathkeys
@@ -5865,26 +5865,26 @@ create_final_distinct_paths(PlannerInfo *root,
RelOptInfo *input_rel,
* list, so we must still only do this with paths which
are
* correctly sorted by sort_pathkeys.
*/
- if (root->distinct_pathkeys == NIL)
- {
- Node *limitCount;
-
- limitCount = (Node *) makeConst(INT8OID, -1,
InvalidOid,
-
sizeof(int64),
-
Int64GetDatum(1), false,
-
FLOAT8PASSBYVAL);
-
- /*
- * If the query already has a LIMIT clause,
then we could end
- * up with a duplicate LimitPath in the final
plan. That does
- * not seem worth troubling over too much.
- */
- add_path(distinct_rel, (Path *)
- create_limit_path(root,
distinct_rel, sorted_path,
-
NULL, limitCount,
-
LIMIT_OPTION_COUNT, 0, 1), root);
- }
- else
+// if (root->distinct_pathkeys == NIL)
+// {
+// Node *limitCount;
+//
+// limitCount = (Node *) makeConst(INT8OID, -1,
InvalidOid,
+//
sizeof(int64),
+//
Int64GetDatum(1), false,
+//
FLOAT8PASSBYVAL);
+//
+// /*
+// * If the query already has a LIMIT clause,
then we could end
+// * up with a duplicate LimitPath in the final
plan. That does
+// * not seem worth troubling over too much.
+// */
+// add_path(distinct_rel, (Path *)
+// create_limit_path(root,
distinct_rel, sorted_path,
+//
NULL, limitCount,
+//
LIMIT_OPTION_COUNT, 0, 1), root);
+// }
+ if (is_sorted)
{
path = cdb_prepare_path_for_sorted_agg(root,
true, /* is_sorted */
@@ -5909,7 +5909,7 @@ create_final_distinct_paths(PlannerInfo *root, RelOptInfo
*input_rel,
add_path(distinct_rel, (Path *)
create_upper_unique_path(root,
distinct_rel,
-
sorted_path,
+
path,
list_length(root->distinct_pathkeys),
numDistinctRows),
root);
diff --git a/src/test/regress/expected/aggregates.out
b/src/test/regress/expected/aggregates.out
index e20769638fb..acead353f5d 100644
--- a/src/test/regress/expected/aggregates.out
+++ b/src/test/regress/expected/aggregates.out
@@ -1314,18 +1314,19 @@ set local enable_sort = off;
explain (costs off)
select f1, (select distinct min(t1.f1) from int4_tbl t1 where t1.f1 = t0.f1)
from int4_tbl t0;
- QUERY PLAN
----------------------------------------------------------------------
- Seq Scan on int4_tbl t0
- SubPlan 2
- -> HashAggregate
- Group Key: $1
- InitPlan 1 (returns $1)
- -> Limit
- -> Seq Scan on int4_tbl t1
- Filter: ((f1 IS NOT NULL) AND (f1 = t0.f1))
- -> Result
-(9 rows)
+ QUERY PLAN
+-----------------------------------------------------------------------------
+ Gather Motion 3:1 (slice1; segments: 3)
+ -> Seq Scan on int4_tbl t0
+ SubPlan 1
+ -> Aggregate
+ -> Result
+ Filter: (t1.f1 = t0.f1)
+ -> Materialize
+ -> Broadcast Motion 3:3 (slice2; segments: 3)
+ -> Seq Scan on int4_tbl t1
+ Optimizer: Postgres query optimizer
+(10 rows)
select f1, (select distinct min(t1.f1) from int4_tbl t1 where t1.f1 = t0.f1)
from int4_tbl t0;
@@ -1390,12 +1391,11 @@ explain (costs off) select a,c from t1 group by a,c,d;
explain (costs off) select *
from t1 inner join t2 on t1.a = t2.x and t1.b = t2.y
group by t1.a,t1.b,t1.c,t1.d,t2.x,t2.y,t2.z;
-<<<<<<< HEAD
QUERY PLAN
------------------------------------------------------------
Gather Motion 3:1 (slice1; segments: 3)
-> HashAggregate
- Group Key: t1.a, t1.b, t2.x, t2.y
+ Group Key: t1.a, t1.b
-> Hash Join
Hash Cond: ((t2.x = t1.a) AND (t2.y = t1.b))
-> Seq Scan on t2
@@ -1403,29 +1403,16 @@ group by t1.a,t1.b,t1.c,t1.d,t2.x,t2.y,t2.z;
-> Seq Scan on t1
Optimizer: Postgres query optimizer
(9 rows)
-=======
- QUERY PLAN
-------------------------------------------------------
- HashAggregate
- Group Key: t1.a, t1.b
- -> Hash Join
- Hash Cond: ((t2.x = t1.a) AND (t2.y = t1.b))
- -> Seq Scan on t2
- -> Hash
- -> Seq Scan on t1
-(7 rows)
->>>>>>> REL_16_9
-- Test case where t1 can be optimized but not t2
explain (costs off) select t1.*,t2.x,t2.z
from t1 inner join t2 on t1.a = t2.x and t1.b = t2.y
group by t1.a,t1.b,t1.c,t1.d,t2.x,t2.z;
-<<<<<<< HEAD
QUERY PLAN
------------------------------------------------------------
Gather Motion 3:1 (slice1; segments: 3)
-> HashAggregate
- Group Key: t1.a, t1.b, t2.x, t2.z
+ Group Key: t1.a, t1.b, t2.z
-> Hash Join
Hash Cond: ((t2.x = t1.a) AND (t2.y = t1.b))
-> Seq Scan on t2
@@ -1433,18 +1420,6 @@ group by t1.a,t1.b,t1.c,t1.d,t2.x,t2.z;
-> Seq Scan on t1
Optimizer: Postgres query optimizer
(9 rows)
-=======
- QUERY PLAN
-------------------------------------------------------
- HashAggregate
- Group Key: t1.a, t1.b, t2.z
- -> Hash Join
- Hash Cond: ((t2.x = t1.a) AND (t2.y = t1.b))
- -> Seq Scan on t2
- -> Hash
- -> Seq Scan on t1
-(7 rows)
->>>>>>> REL_16_9
-- Cannot optimize when PK is deferrable
explain (costs off) select * from t3 group by a,b,c;
@@ -1558,11 +1533,10 @@ drop table t1, t2;
explain (costs off)
select sum(two order by two),max(four order by four), min(four order by four)
from tenk1;
- QUERY PLAN
--------------------------------
+ QUERY PLAN
+------------------------------------------------
Aggregate
- -> Sort
- Sort Key: four
+ -> Gather Motion 3:1 (slice1; segments: 3)
-> Seq Scan on tenk1
(4 rows)
@@ -1573,11 +1547,10 @@ select
sum(two order by two), max(four order by four),
min(four order by four), max(two order by two)
from tenk1;
- QUERY PLAN
--------------------------------
+ QUERY PLAN
+------------------------------------------------
Aggregate
- -> Sort
- Sort Key: two
+ -> Gather Motion 3:1 (slice1; segments: 3)
-> Seq Scan on tenk1
(4 rows)
@@ -1587,11 +1560,10 @@ select
max(four order by four), sum(two order by two),
min(four order by four), max(two order by two)
from tenk1;
- QUERY PLAN
--------------------------------
+ QUERY PLAN
+------------------------------------------------
Aggregate
- -> Sort
- Sort Key: four
+ -> Gather Motion 3:1 (slice1; segments: 3)
-> Seq Scan on tenk1
(4 rows)
@@ -1603,11 +1575,10 @@ select
min(four order by four), max(two order by two),
sum(ten order by ten), min(ten order by ten), max(ten order by ten)
from tenk1;
- QUERY PLAN
--------------------------------
+ QUERY PLAN
+------------------------------------------------
Aggregate
- -> Sort
- Sort Key: ten
+ -> Gather Motion 3:1 (slice1; segments: 3)
-> Seq Scan on tenk1
(4 rows)
@@ -1620,14 +1591,18 @@ select
sum(unique1 order by two, four)
from tenk1
group by ten;
- QUERY PLAN
-----------------------------------
- GroupAggregate
- Group Key: ten
- -> Sort
- Sort Key: ten, two, four
- -> Seq Scan on tenk1
-(5 rows)
+ QUERY PLAN
+------------------------------------------------------------------
+ Gather Motion 3:1 (slice1; segments: 3)
+ -> GroupAggregate
+ Group Key: ten
+ -> Sort
+ Sort Key: ten, two, four
+ -> Redistribute Motion 3:3 (slice2; segments: 3)
+ Hash Key: ten
+ -> Seq Scan on tenk1
+ Optimizer: Postgres query optimizer
+(9 rows)
-- Ensure that we never choose to provide presorted input to an Aggref with
-- a volatile function in the ORDER BY / DISTINCT clause. We want to ensure
@@ -1639,14 +1614,18 @@ select
sum(unique1 order by two, random(), random() + 1)
from tenk1
group by ten;
- QUERY PLAN
-----------------------------------
- GroupAggregate
- Group Key: ten
- -> Sort
- Sort Key: ten, four, two
- -> Seq Scan on tenk1
-(5 rows)
+ QUERY PLAN
+------------------------------------------------------------------
+ Gather Motion 3:1 (slice1; segments: 3)
+ -> GroupAggregate
+ Group Key: ten
+ -> Sort
+ Sort Key: ten, four, two
+ -> Redistribute Motion 3:3 (slice2; segments: 3)
+ Hash Key: ten
+ -> Seq Scan on tenk1
+ Optimizer: Postgres query optimizer
+(9 rows)
-- Ensure consecutive NULLs are properly treated as distinct from each other
select array_agg(distinct val)
@@ -1660,11 +1639,13 @@ from (select null as val from generate_series(1, 2));
set enable_presorted_aggregate to off;
explain (costs off)
select sum(two order by two) from tenk1;
- QUERY PLAN
--------------------------
+ QUERY PLAN
+------------------------------------------------
Aggregate
- -> Seq Scan on tenk1
-(2 rows)
+ -> Gather Motion 3:1 (slice1; segments: 3)
+ -> Seq Scan on tenk1
+ Optimizer: Postgres query optimizer
+(4 rows)
reset enable_presorted_aggregate;
--
@@ -1673,11 +1654,10 @@ reset enable_presorted_aggregate;
-- Ensure we presort when the aggregate contains plain Vars
explain (costs off)
select sum(two order by two) filter (where two > 1) from tenk1;
- QUERY PLAN
--------------------------------
+ QUERY PLAN
+------------------------------------------------
Aggregate
- -> Sort
- Sort Key: two
+ -> Gather Motion 3:1 (slice1; segments: 3)
-> Seq Scan on tenk1
(4 rows)
@@ -1685,24 +1665,27 @@ select sum(two order by two) filter (where two > 1)
from tenk1;
explain (costs off)
select string_agg(distinct f1, ',') filter (where length(f1) > 1)
from varchar_tbl;
- QUERY PLAN
--------------------------------------
- Aggregate
- -> Sort
- Sort Key: f1
- -> Seq Scan on varchar_tbl
-(4 rows)
+ QUERY PLAN
+------------------------------------------------
+ Finalize Aggregate
+ -> Gather Motion 3:1 (slice1; segments: 3)
+ -> Partial Aggregate
+ -> Seq Scan on varchar_tbl
+ Optimizer: Postgres query optimizer
+(5 rows)
-- Ensure we don't presort when the aggregate's argument contains an
-- explicit cast.
explain (costs off)
select string_agg(distinct f1::varchar(2), ',') filter (where length(f1) > 1)
from varchar_tbl;
- QUERY PLAN
--------------------------------
+ QUERY PLAN
+------------------------------------------------
Aggregate
- -> Seq Scan on varchar_tbl
-(2 rows)
+ -> Gather Motion 3:1 (slice1; segments: 3)
+ -> Seq Scan on varchar_tbl
+ Optimizer: Postgres query optimizer
+(4 rows)
--
-- Test combinations of DISTINCT and/or ORDER BY
@@ -2168,14 +2151,14 @@ from generate_series(1,5) x,
(values (0::float8),(0.1),(0.25),(0.4),(0.5),(0.6),(0.75),(0.9),(1)) v(p)
group by p order by p;
ERROR: sum is not an ordered-set aggregate, so it cannot have WITHIN GROUP
-LINE 1: select p, sum() within group (order by x::float8)
+LINE 1: select p, sum() within group (order by x::float8) -- error
^
select p, percentile_cont(p,p) -- error
from generate_series(1,5) x,
(values (0::float8),(0.1),(0.25),(0.4),(0.5),(0.6),(0.75),(0.9),(1)) v(p)
group by p order by p;
ERROR: WITHIN GROUP is required for ordered-set aggregate percentile_cont
-LINE 1: select p, percentile_cont(p,p)
+LINE 1: select p, percentile_cont(p,p) -- error
^
select percentile_cont(0.5) within group (order by b) from aggtest;
percentile_cont
@@ -2367,15 +2350,15 @@ select ten,
from tenk1
group by ten order by ten;
select pg_get_viewdef('aggordview1');
- pg_get_viewdef
--------------------------------------------------------------------------------------------------------------------------------
- SELECT tenk1.ten,
+
- percentile_disc((0.5)::double precision) WITHIN GROUP (ORDER BY
tenk1.thousand) AS p50, +
- percentile_disc((0.5)::double precision) WITHIN GROUP (ORDER BY
tenk1.thousand) FILTER (WHERE (tenk1.hundred = 1)) AS px,+
- rank(5, 'AZZZZ'::name, 50) WITHIN GROUP (ORDER BY tenk1.hundred,
tenk1.string4 DESC, tenk1.hundred) AS rank +
- FROM tenk1
+
- GROUP BY tenk1.ten
+
- ORDER BY tenk1.ten;
+ pg_get_viewdef
+-------------------------------------------------------------------------------------------------------------------
+ SELECT ten,
+
+ percentile_disc((0.5)::double precision) WITHIN GROUP (ORDER BY thousand)
AS p50, +
+ percentile_disc((0.5)::double precision) WITHIN GROUP (ORDER BY thousand)
FILTER (WHERE (hundred = 1)) AS px,+
+ rank(5, 'AZZZZ'::name, 50) WITHIN GROUP (ORDER BY hundred, string4 DESC,
hundred) AS rank +
+ FROM tenk1
+
+ GROUP BY ten
+
+ ORDER BY ten;
(1 row)
select * from aggordview1 order by ten;
@@ -2500,22 +2483,25 @@ select * from v_pagg_test order by y;
-- Ensure parallel aggregation is actually being used.
explain (costs off) select * from v_pagg_test order by y;
- QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------
- GroupAggregate
- Group Key: pagg_test.y
- -> Sort
- Sort Key: pagg_test.y,
(((unnest(regexp_split_to_array((string_agg((pagg_test.x)::text, ','::text)),
','::text))))::integer)
- -> Result
- -> ProjectSet
- -> Finalize HashAggregate
- Group Key: pagg_test.y
- -> Gather
- Workers Planned: 2
- -> Partial HashAggregate
- Group Key: pagg_test.y
- -> Parallel Seq Scan on pagg_test
-(13 rows)
+ QUERY PLAN
+--------------------------------------------------------------------------------------------------------------------------------------------
+ Gather Motion 3:1 (slice1; segments: 3)
+ Merge Key: pagg_test.y
+ -> GroupAggregate
+ Group Key: pagg_test.y
+ -> Sort
+ Sort Key: pagg_test.y,
(((unnest(regexp_split_to_array((string_agg((pagg_test.x)::text, ','::text)),
','::text))))::integer)
+ -> Result
+ -> ProjectSet
+ -> Finalize HashAggregate
+ Group Key: pagg_test.y
+ -> Redistribute Motion 3:3 (slice2;
segments: 3)
+ Hash Key: pagg_test.y
+ -> Partial HashAggregate
+ Group Key: pagg_test.y
+ -> Seq Scan on pagg_test
+ Optimizer: Postgres query optimizer
+(16 rows)
-- Ensure results are the same without parallel aggregation.
set max_parallel_workers_per_gather = 0;
@@ -2538,12 +2524,12 @@ select * from v_pagg_test order by y;
set max_parallel_workers_per_gather = 2;
explain (costs off)
select array_dims(array_agg(s)) from (select * from pagg_test) s;
- QUERY PLAN
---------------------------------------------
+ QUERY PLAN
+------------------------------------------------
Aggregate
- -> Gather
- Workers Planned: 2
- -> Parallel Seq Scan on pagg_test
+ -> Gather Motion 3:1 (slice1; segments: 3)
+ -> Seq Scan on pagg_test
+ Optimizer: Postgres query optimizer
(4 rows)
select array_dims(array_agg(s)) from (select * from pagg_test) s;
diff --git a/src/test/regress/expected/select_distinct.out
b/src/test/regress/expected/select_distinct.out
index 660f03f3df5..c5c85101d1c 100644
--- a/src/test/regress/expected/select_distinct.out
+++ b/src/test/regress/expected/select_distinct.out
@@ -140,13 +140,13 @@ SELECT count(*) FROM
Output: PARTIAL count(*)
-> HashAggregate
Output: tenk1.two, tenk1.four, tenk1.two
- Group Key: tenk1.two, tenk1.four
+ Group Key: tenk1.two, tenk1.four, tenk1.two
-> Redistribute Motion 3:3 (slice2; segments: 3)
Output: tenk1.two, tenk1.four, tenk1.two
Hash Key: tenk1.two, tenk1.four, tenk1.two
-> HashAggregate
Output: tenk1.two, tenk1.four, tenk1.two
- Group Key: tenk1.two, tenk1.four
+ Group Key: tenk1.two, tenk1.four, tenk1.two
-> Seq Scan on public.tenk1
Output: tenk1.two, tenk1.four, tenk1.two
Optimizer: Postgres query optimizer
@@ -188,18 +188,22 @@ SET enable_seqscan = 0;
-- Check to see we get an incremental sort plan
EXPLAIN (costs off)
SELECT DISTINCT hundred, two FROM tenk1;
- QUERY PLAN
-------------------------------------------------------------
- Gather Motion 3:1 (slice1; segments: 3)
- Merge Key: hundred, two
- -> Unique
- Group Key: hundred, two
+ QUERY PLAN
+------------------------------------------------------------------------
+ GroupAggregate
+ Group Key: hundred, two
+ -> Gather Motion 3:1 (slice1; segments: 3)
+ Merge Key: hundred, two
-> Sort
Sort Key: hundred, two
- -> Bitmap Heap Scan on tenk1
- -> Bitmap Index Scan on tenk1_hundred
+ -> GroupAggregate
+ Group Key: hundred, two
+ -> Sort
+ Sort Key: hundred, two
+ -> Bitmap Heap Scan on tenk1
+ -> Bitmap Index Scan on tenk1_hundred
Optimizer: Postgres query optimizer
-(9 rows)
+(13 rows)
RESET enable_seqscan;
SET enable_hashagg=TRUE;
@@ -329,14 +333,17 @@ RESET parallel_tuple_cost;
-- Ensure we get a plan with a Limit 1
EXPLAIN (COSTS OFF)
SELECT DISTINCT four FROM tenk1 WHERE four = 0;
- QUERY PLAN
-------------------------------------------
- Gather Motion 3:1 (slice1; segments: 3)
- -> Limit
- -> Seq Scan on tenk1
- Filter: (four = 0)
+ QUERY PLAN
+------------------------------------------------
+ GroupAggregate
+ Group Key: four
+ -> Gather Motion 3:1 (slice1; segments: 3)
+ -> GroupAggregate
+ Group Key: four
+ -> Seq Scan on tenk1
+ Filter: (four = 0)
Optimizer: Postgres query optimizer
-(5 rows)
+(8 rows)
-- Ensure the above gives us the correct result
SELECT DISTINCT four FROM tenk1 WHERE four = 0;
@@ -348,14 +355,17 @@ SELECT DISTINCT four FROM tenk1 WHERE four = 0;
-- Ensure we get a plan with a Limit 1
EXPLAIN (COSTS OFF)
SELECT DISTINCT four FROM tenk1 WHERE four = 0 AND two <> 0;
- QUERY PLAN
----------------------------------------------------
- Gather Motion 3:1 (slice1; segments: 3)
- -> Limit
- -> Seq Scan on tenk1
- Filter: ((two <> 0) AND (four = 0))
+ QUERY PLAN
+---------------------------------------------------------
+ GroupAggregate
+ Group Key: four
+ -> Gather Motion 3:1 (slice1; segments: 3)
+ -> GroupAggregate
+ Group Key: four
+ -> Seq Scan on tenk1
+ Filter: ((two <> 0) AND (four = 0))
Optimizer: Postgres query optimizer
-(5 rows)
+(8 rows)
-- Ensure no rows are returned
SELECT DISTINCT four FROM tenk1 WHERE four = 0 AND two <> 0;
@@ -366,14 +376,17 @@ SELECT DISTINCT four FROM tenk1 WHERE four = 0 AND two <>
0;
-- Ensure we get a plan with a Limit 1 when the SELECT list contains constants
EXPLAIN (COSTS OFF)
SELECT DISTINCT four,1,2,3 FROM tenk1 WHERE four = 0;
- QUERY PLAN
-------------------------------------------
- Gather Motion 3:1 (slice1; segments: 3)
- -> Limit
- -> Seq Scan on tenk1
- Filter: (four = 0)
+ QUERY PLAN
+------------------------------------------------
+ GroupAggregate
+ Group Key: four, 1, 2, 3
+ -> Gather Motion 3:1 (slice1; segments: 3)
+ -> GroupAggregate
+ Group Key: four, 1, 2, 3
+ -> Seq Scan on tenk1
+ Filter: (four = 0)
Optimizer: Postgres query optimizer
-(5 rows)
+(8 rows)
-- Ensure we only get 1 row
SELECT DISTINCT four,1,2,3 FROM tenk1 WHERE four = 0;
diff --git a/src/test/regress/expected/select_distinct_on.out
b/src/test/regress/expected/select_distinct_on.out
index b2978c1114a..363591af133 100644
--- a/src/test/regress/expected/select_distinct_on.out
+++ b/src/test/regress/expected/select_distinct_on.out
@@ -81,13 +81,17 @@ select distinct on (1) floor(random()) as r, f1 from
int4_tbl order by 1,2;
EXPLAIN (COSTS OFF)
SELECT DISTINCT ON (four) four,two
FROM tenk1 WHERE four = 0 ORDER BY 1;
- QUERY PLAN
-----------------------------------
- Result
- -> Limit
- -> Seq Scan on tenk1
- Filter: (four = 0)
-(4 rows)
+ QUERY PLAN
+------------------------------------------------
+ GroupAggregate
+ Group Key: four
+ -> Gather Motion 3:1 (slice1; segments: 3)
+ -> GroupAggregate
+ Group Key: four
+ -> Seq Scan on tenk1
+ Filter: (four = 0)
+ Optimizer: Postgres query optimizer
+(8 rows)
-- and check the result of the above query is correct
SELECT DISTINCT ON (four) four,two
@@ -101,25 +105,50 @@ SELECT DISTINCT ON (four) four,two
EXPLAIN (COSTS OFF)
SELECT DISTINCT ON (four) four,two
FROM tenk1 WHERE four = 0 ORDER BY 1,2;
- QUERY PLAN
-----------------------------------
- Limit
+ QUERY PLAN
+------------------------------------------------------------------------
+ Gather Motion 3:1 (slice1; segments: 3)
+ Merge Key: two
-> Sort
Sort Key: two
- -> Seq Scan on tenk1
- Filter: (four = 0)
-(5 rows)
+ -> GroupAggregate
+ Group Key: four
+ -> Sort
+ Sort Key: two
+ -> Redistribute Motion 3:3 (slice2; segments: 3)
+ Hash Key: four
+ -> GroupAggregate
+ Group Key: four
+ -> Sort
+ Sort Key: two
+ -> Seq Scan on tenk1
+ Filter: (four = 0)
+ Optimizer: Postgres query optimizer
+(17 rows)
-- Same again but use a column that is indexed so that we get an index scan
-- then a limit
EXPLAIN (COSTS OFF)
SELECT DISTINCT ON (four) four,hundred
FROM tenk1 WHERE four = 0 ORDER BY 1,2;
- QUERY PLAN
------------------------------------------------------
- Result
- -> Limit
- -> Index Scan using tenk1_hundred on tenk1
- Filter: (four = 0)
-(4 rows)
+ QUERY PLAN
+------------------------------------------------------------------------
+ Gather Motion 3:1 (slice1; segments: 3)
+ Merge Key: hundred
+ -> Sort
+ Sort Key: hundred
+ -> GroupAggregate
+ Group Key: four
+ -> Sort
+ Sort Key: hundred
+ -> Redistribute Motion 3:3 (slice2; segments: 3)
+ Hash Key: four
+ -> GroupAggregate
+ Group Key: four
+ -> Sort
+ Sort Key: hundred
+ -> Seq Scan on tenk1
+ Filter: (four = 0)
+ Optimizer: Postgres query optimizer
+(17 rows)
diff --git a/src/test/regress/expected/subselect.out
b/src/test/regress/expected/subselect.out
index 73cc16f740b..75648050d52 100644
--- a/src/test/regress/expected/subselect.out
+++ b/src/test/regress/expected/subselect.out
@@ -367,11 +367,11 @@ select * from int4_tbl o where not exists
QUERY PLAN
------------------------------------------
Gather Motion 3:1 (slice1; segments: 3)
- -> Hash Anti Join
- Hash Cond: (o.f1 = i.f1)
- -> Seq Scan on int4_tbl o
+ -> Hash Right Anti Join
+ Hash Cond: (i.f1 = o.f1)
+ -> Seq Scan on int4_tbl i
-> Hash
- -> Seq Scan on int4_tbl i
+ -> Seq Scan on int4_tbl o
Optimizer: Postgres query optimizer
(7 rows)
@@ -864,22 +864,17 @@ select 'foo'::text in (select 'bar'::name union all
select 'bar'::name);
--
explain (verbose, costs off)
select row(row(row(1))) = any (select row(row(1)));
- QUERY PLAN
--------------------------------------------
+ QUERY PLAN
+-------------------------------------
Result
Output: (SubPlan 1)
SubPlan 1
-> Materialize
-<<<<<<< HEAD
Output: (ROW(ROW(1)))
-> Result
Output: ROW(ROW(1))
-=======
- Output: '("(1)")'::record
- -> Result
- Output: '("(1)")'::record
->>>>>>> REL_16_9
-(7 rows)
+ Optimizer: Postgres query optimizer
+(8 rows)
select row(row(row(1))) = any (select row(row(1)));
?column?
@@ -1043,7 +1038,6 @@ analyze exists_tbl;
explain (costs off)
select * from exists_tbl t1
where (exists(select 1 from exists_tbl t2 where t1.c1 = t2.c2) or c3 < 0);
-<<<<<<< HEAD
QUERY PLAN
------------------------------------------------------------------------------
Gather Motion 3:1 (slice1; segments: 3)
@@ -1070,26 +1064,6 @@ select * from exists_tbl t1
-> Seq Scan on exists_tbl_def t2_5
Optimizer: Postgres query optimizer
(23 rows)
-=======
- QUERY PLAN
-------------------------------------------------------
- Append
- -> Seq Scan on exists_tbl_null t1_1
- Filter: ((SubPlan 1) OR (c3 < 0))
- SubPlan 1
- -> Append
- -> Seq Scan on exists_tbl_null t2_1
- Filter: (t1_1.c1 = c2)
- -> Seq Scan on exists_tbl_def t2_2
- Filter: (t1_1.c1 = c2)
- -> Seq Scan on exists_tbl_def t1_2
- Filter: ((hashed SubPlan 2) OR (c3 < 0))
- SubPlan 2
- -> Append
- -> Seq Scan on exists_tbl_null t2_4
- -> Seq Scan on exists_tbl_def t2_5
-(15 rows)
->>>>>>> REL_16_9
select * from exists_tbl t1
where (exists(select 1 from exists_tbl t2 where t1.c1 = t2.c2) or c3 < 0);
@@ -1199,28 +1173,7 @@ select sum(ss.tst::int) from
random() as r
from onek i where i.unique1 = o.unique1 ) ss
where o.ten = 0;
-<<<<<<< HEAD
ERROR: correlated subquery with skip-level correlations is not supported
-=======
-
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
- Aggregate
- Output: sum((((hashed SubPlan 1)))::integer)
- -> Nested Loop
- Output: ((hashed SubPlan 1))
- -> Seq Scan on public.onek o
- Output: o.unique1, o.unique2, o.two, o.four, o.ten, o.twenty,
o.hundred, o.thousand, o.twothousand, o.fivethous, o.tenthous, o.odd, o.even,
o.stringu1, o.stringu2, o.string4
- Filter: (o.ten = 0)
- -> Index Scan using onek_unique1 on public.onek i
- Output: (hashed SubPlan 1), random()
- Index Cond: (i.unique1 = o.unique1)
- SubPlan 1
- -> Seq Scan on public.int4_tbl
- Output: int4_tbl.f1
- Filter: (int4_tbl.f1 <= o.hundred)
-(14 rows)
-
->>>>>>> REL_16_9
select sum(ss.tst::int) from
onek o cross join lateral (
select i.ten in (select f1 from int4_tbl where f1 <= o.hundred) as tst,
@@ -1291,9 +1244,8 @@ select sum(o.four), sum(ss.a) from
select * from x
) ss
where o.ten = 1;
- QUERY PLAN
----------------------------------------------------------
-<<<<<<< HEAD
+ QUERY PLAN
+---------------------------------------------------------------
Finalize Aggregate
-> Gather Motion 3:1 (slice1; segments: 3)
-> Partial Aggregate
@@ -1310,22 +1262,6 @@ where o.ten = 1;
Filter: (a < 10)
Optimizer: Postgres query optimizer
(15 rows)
-=======
- Aggregate
- -> Nested Loop
- -> Seq Scan on onek o
- Filter: (ten = 1)
- -> Memoize
- Cache Key: o.four
- Cache Mode: binary
- -> CTE Scan on x
- CTE x
- -> Recursive Union
- -> Result
- -> WorkTable Scan on x x_1
- Filter: (a < 10)
-(13 rows)
->>>>>>> REL_16_9
select sum(o.four), sum(ss.a) from
onek o cross join lateral (
@@ -1466,41 +1402,6 @@ select * from int4_tbl where
f1
----
0
-(1 row)
-
---
--- Check for incorrect optimization when IN subquery contains a SRF
---
-explain (verbose, costs off)
-select * from int4_tbl o where (f1, f1) in
- (select f1, generate_series(1,50) / 10 g from int4_tbl i group by f1);
- QUERY PLAN
--------------------------------------------------------------------------
- Gather Motion 3:1 (slice1; segments: 3)
- Output: o.f1
- -> Hash Semi Join
- Output: o.f1
- Hash Cond: (o.f1 = "ANY_subquery".f1)
- -> Seq Scan on public.int4_tbl o
- Output: o.f1
- -> Hash
- Output: "ANY_subquery".f1, "ANY_subquery".g
- -> Subquery Scan on "ANY_subquery"
- Output: "ANY_subquery".f1, "ANY_subquery".g
- Filter: ("ANY_subquery".f1 = "ANY_subquery".g)
- -> Result
- Output: i.f1, ((generate_series(1, 50)) / 10)
- -> ProjectSet
- Output: generate_series(1, 50), i.f1
- -> Seq Scan on public.int4_tbl i
- Output: i.f1
- Optimizer: Postgres query optimizer
-(20 rows)
-
-select * from int4_tbl o where (f1, f1) in
- (select f1, generate_series(1,50) / 10 g from int4_tbl i group by f1);
- f1
-----
0
(1 row)
diff --git a/src/test/regress/expected/union.out
b/src/test/regress/expected/union.out
index 34febf92964..bbbb2215589 100644
--- a/src/test/regress/expected/union.out
+++ b/src/test/regress/expected/union.out
@@ -316,7 +316,8 @@ SELECT q1 FROM int8_tbl EXCEPT ALL SELECT DISTINCT q2 FROM
int8_tbl ORDER BY 1;
------------------
123
4567890123456789
-(2 rows)
+ 4567890123456789
+(3 rows)
SELECT q1 FROM int8_tbl EXCEPT ALL SELECT q1 FROM int8_tbl FOR NO KEY UPDATE;
ERROR: FOR NO KEY UPDATE is not allowed with UNION/INTERSECT/EXCEPT
diff --git a/src/test/regress/serial_schedule b/src/test/regress/serial_schedule
index 713785ad73e..84bbfc2b24d 100644
--- a/src/test/regress/serial_schedule
+++ b/src/test/regress/serial_schedule
@@ -92,8 +92,8 @@ test: subselect
test: incremental_sort
test: union
test: case
-# test: join
-# test: aggregates
+test: join
+test: aggregates
# test: transactions
# ignore: random
# test: random
diff --git a/src/test/regress/sql/aggregates.sql
b/src/test/regress/sql/aggregates.sql
index a544d71d104..bcd1f5ed7b4 100644
--- a/src/test/regress/sql/aggregates.sql
+++ b/src/test/regress/sql/aggregates.sql
@@ -2,14 +2,12 @@
-- AGGREGATES
--
-<<<<<<< HEAD
-- start_ignore
SET optimizer_trace_fallback to on;
-- end_ignore
-=======
+
-- directory paths are passed to us in environment variables
\getenv abs_srcdir PG_ABS_SRCDIR
->>>>>>> REL_16_9
-- avoid bit-exact output here because operations may not be bit-exact.
SET extra_float_digits = 0;
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
