diff --git a/contrib/postgres_fdw/deparse.c b/contrib/postgres_fdw/deparse.c index 0876589..6ada941 100644 --- a/contrib/postgres_fdw/deparse.c +++ b/contrib/postgres_fdw/deparse.c @@ -178,7 +178,7 @@ static void appendGroupByClause(List *tlist, deparse_expr_cxt *context); static void appendAggOrderBy(List *orderList, List *targetList, deparse_expr_cxt *context); static void appendFunctionName(Oid funcid, deparse_expr_cxt *context); -static Node *deparseSortGroupClause(Index ref, List *tlist, +static Node *deparseSortGroupClause(Index ref, List *tlist, bool force_colno, deparse_expr_cxt *context); /* @@ -2853,7 +2853,7 @@ appendAggOrderBy(List *orderList, List *targetList, deparse_expr_cxt *context) first = false; sortexpr = deparseSortGroupClause(srt->tleSortGroupRef, targetList, - context); + false, context); sortcoltype = exprType(sortexpr); /* See whether operator is default < or > for datatype */ typentry = lookup_type_cache(sortcoltype, @@ -2960,7 +2960,7 @@ appendGroupByClause(List *tlist, deparse_expr_cxt *context) appendStringInfoString(buf, ", "); first = false; - deparseSortGroupClause(grp->tleSortGroupRef, tlist, context); + deparseSortGroupClause(grp->tleSortGroupRef, tlist, true, context); } } @@ -3047,7 +3047,8 @@ appendFunctionName(Oid funcid, deparse_expr_cxt *context) * need not find it again. */ static Node * -deparseSortGroupClause(Index ref, List *tlist, deparse_expr_cxt *context) +deparseSortGroupClause(Index ref, List *tlist, bool force_colno, + deparse_expr_cxt *context) { StringInfo buf = context->buf; TargetEntry *tle; @@ -3056,7 +3057,13 @@ deparseSortGroupClause(Index ref, List *tlist, deparse_expr_cxt *context) tle = get_sortgroupref_tle(ref, tlist); expr = tle->expr; - if (expr && IsA(expr, Const)) + /* Use column-number form if requested by caller. */ + if (force_colno) + { + Assert(!tle->resjunk); + appendStringInfo(buf, "%d", tle->resno); + } + else if (expr && IsA(expr, Const)) { /* * Force a typecast here so that we don't emit something like "GROUP diff --git a/contrib/postgres_fdw/expected/postgres_fdw.out b/contrib/postgres_fdw/expected/postgres_fdw.out index 4339bbf..cbf1c7b 100644 --- a/contrib/postgres_fdw/expected/postgres_fdw.out +++ b/contrib/postgres_fdw/expected/postgres_fdw.out @@ -2462,8 +2462,8 @@ DROP ROLE regress_view_owner; -- Simple aggregates explain (verbose, costs off) select count(c6), sum(c1), avg(c1), min(c2), max(c1), stddev(c2), sum(c1) * (random() <= 1)::int as sum2 from ft1 where c2 < 5 group by c2 order by 1, 2; - QUERY PLAN -------------------------------------------------------------------------------------------------------------------------------------------------------- + QUERY PLAN +------------------------------------------------------------------------------------------------------------------------------------------------------ Result Output: (count(c6)), (sum(c1)), (avg(c1)), (min(c2)), (max(c1)), (stddev(c2)), ((sum(c1)) * ((random() <= '1'::double precision))::integer), c2 -> Sort @@ -2472,7 +2472,7 @@ select count(c6), sum(c1), avg(c1), min(c2), max(c1), stddev(c2), sum(c1) * (ran -> Foreign Scan Output: (count(c6)), (sum(c1)), (avg(c1)), (min(c2)), (max(c1)), (stddev(c2)), c2 Relations: Aggregate on (public.ft1) - Remote SQL: SELECT count(c6), sum("C 1"), avg("C 1"), min(c2), max("C 1"), stddev(c2), c2 FROM "S 1"."T 1" WHERE ((c2 < 5)) GROUP BY c2 + Remote SQL: SELECT count(c6), sum("C 1"), avg("C 1"), min(c2), max("C 1"), stddev(c2), c2 FROM "S 1"."T 1" WHERE ((c2 < 5)) GROUP BY 7 (9 rows) select count(c6), sum(c1), avg(c1), min(c2), max(c1), stddev(c2), sum(c1) * (random() <= 1)::int as sum2 from ft1 where c2 < 5 group by c2 order by 1, 2; @@ -2531,15 +2531,15 @@ select sum(t1.c1), count(t2.c1) from ft1 t1 inner join ft2 t2 on (t1.c1 = t2.c1) -- GROUP BY clause having expressions explain (verbose, costs off) select c2/2, sum(c2) * (c2/2) from ft1 group by c2/2 order by c2/2; - QUERY PLAN ------------------------------------------------------------------------------------------------- + QUERY PLAN +--------------------------------------------------------------------------------------- Sort Output: ((c2 / 2)), ((sum(c2) * (c2 / 2))) Sort Key: ((ft1.c2 / 2)) -> Foreign Scan Output: ((c2 / 2)), ((sum(c2) * (c2 / 2))) Relations: Aggregate on (public.ft1) - Remote SQL: SELECT (c2 / 2), (sum(c2) * (c2 / 2)) FROM "S 1"."T 1" GROUP BY ((c2 / 2)) + Remote SQL: SELECT (c2 / 2), (sum(c2) * (c2 / 2)) FROM "S 1"."T 1" GROUP BY 1 (7 rows) select c2/2, sum(c2) * (c2/2) from ft1 group by c2/2 order by c2/2; @@ -2555,8 +2555,8 @@ select c2/2, sum(c2) * (c2/2) from ft1 group by c2/2 order by c2/2; -- Aggregates in subquery are pushed down. explain (verbose, costs off) select count(x.a), sum(x.a) from (select c2 a, sum(c1) b from ft1 group by c2, sqrt(c1) order by 1, 2) x; - QUERY PLAN ----------------------------------------------------------------------------------------------------------- + QUERY PLAN +--------------------------------------------------------------------------------------------- Aggregate Output: count(ft1.c2), sum(ft1.c2) -> Sort @@ -2565,7 +2565,7 @@ select count(x.a), sum(x.a) from (select c2 a, sum(c1) b from ft1 group by c2, s -> Foreign Scan Output: ft1.c2, (sum(ft1.c1)), (sqrt((ft1.c1)::double precision)) Relations: Aggregate on (public.ft1) - Remote SQL: SELECT c2, sum("C 1"), sqrt("C 1") FROM "S 1"."T 1" GROUP BY c2, (sqrt("C 1")) + Remote SQL: SELECT c2, sum("C 1"), sqrt("C 1") FROM "S 1"."T 1" GROUP BY 1, 3 (9 rows) select count(x.a), sum(x.a) from (select c2 a, sum(c1) b from ft1 group by c2, sqrt(c1) order by 1, 2) x; @@ -2585,7 +2585,7 @@ select c2 * (random() <= 1)::int as sum1, sum(c1) * c2 as sum2 from ft1 group by -> Foreign Scan Output: (c2 * ((random() <= '1'::double precision))::integer), ((sum(c1) * c2)), c2 Relations: Aggregate on (public.ft1) - Remote SQL: SELECT (sum("C 1") * c2), c2 FROM "S 1"."T 1" GROUP BY c2 + Remote SQL: SELECT (sum("C 1") * c2), c2 FROM "S 1"."T 1" GROUP BY 2 (7 rows) select c2 * (random() <= 1)::int as sum1, sum(c1) * c2 as sum2 from ft1 group by c2 order by 1, 2; @@ -2622,15 +2622,15 @@ select c2 * (random() <= 1)::int as c2 from ft2 group by c2 * (random() <= 1)::i -- GROUP BY clause in various forms, cardinal, alias and constant expression explain (verbose, costs off) select count(c2) w, c2 x, 5 y, 7.0 z from ft1 group by 2, y, 9.0::int order by 2; - QUERY PLAN ----------------------------------------------------------------------------------------------------------- + QUERY PLAN +--------------------------------------------------------------------------------------- Sort Output: (count(c2)), c2, 5, 7.0, 9 Sort Key: ft1.c2 -> Foreign Scan Output: (count(c2)), c2, 5, 7.0, 9 Relations: Aggregate on (public.ft1) - Remote SQL: SELECT count(c2), c2, 5, 7.0, 9 FROM "S 1"."T 1" GROUP BY c2, 5::integer, 9::integer + Remote SQL: SELECT count(c2), c2, 5, 7.0, 9 FROM "S 1"."T 1" GROUP BY 2, 3, 5 (7 rows) select count(c2) w, c2 x, 5 y, 7.0 z from ft1 group by 2, y, 9.0::int order by 2; @@ -2648,18 +2648,41 @@ select count(c2) w, c2 x, 5 y, 7.0 z from ft1 group by 2, y, 9.0::int order by 2 100 | 9 | 5 | 7.0 (10 rows) +-- GROUP BY clause referring to same column multiple times +-- Also, ORDER BY contains an aggregate function +explain (verbose, costs off) +select c2, c2 from ft1 where c2 > 6 group by 1, 2 order by sum(c1); + QUERY PLAN +----------------------------------------------------------------------------------------------- + Sort + Output: c2, c2, (sum(c1)) + Sort Key: (sum(ft1.c1)) + -> Foreign Scan + Output: c2, c2, (sum(c1)) + Relations: Aggregate on (public.ft1) + Remote SQL: SELECT c2, c2, sum("C 1") FROM "S 1"."T 1" WHERE ((c2 > 6)) GROUP BY 1, 2 +(7 rows) + +select c2, c2 from ft1 where c2 > 6 group by 1, 2 order by sum(c1); + c2 | c2 +----+---- + 7 | 7 + 8 | 8 + 9 | 9 +(3 rows) + -- Testing HAVING clause shippability explain (verbose, costs off) select c2, sum(c1) from ft2 group by c2 having avg(c1) < 500 and sum(c1) < 49800 order by c2; - QUERY PLAN ----------------------------------------------------------------------------------------------------------------------------------------- + QUERY PLAN +--------------------------------------------------------------------------------------------------------------------------------------- Sort Output: c2, (sum(c1)) Sort Key: ft2.c2 -> Foreign Scan Output: c2, (sum(c1)) Relations: Aggregate on (public.ft2) - Remote SQL: SELECT c2, sum("C 1") FROM "S 1"."T 1" GROUP BY c2 HAVING ((avg("C 1") < 500::numeric)) AND ((sum("C 1") < 49800)) + Remote SQL: SELECT c2, sum("C 1") FROM "S 1"."T 1" GROUP BY 1 HAVING ((avg("C 1") < 500::numeric)) AND ((sum("C 1") < 49800)) (7 rows) select c2, sum(c1) from ft2 group by c2 having avg(c1) < 500 and sum(c1) < 49800 order by c2; @@ -2672,15 +2695,15 @@ select c2, sum(c1) from ft2 group by c2 having avg(c1) < 500 and sum(c1) < 49800 -- Unshippable HAVING clause will be evaluated locally, and other qual in HAVING clause is pushed down explain (verbose, costs off) select count(*) from (select c5, count(c1) from ft1 group by c5, sqrt(c2) having (avg(c1) / avg(c1)) * random() <= 1 and avg(c1) < 500) x; - QUERY PLAN -------------------------------------------------------------------------------------------------------------------------------------------------- + QUERY PLAN +--------------------------------------------------------------------------------------------------------------------------------------- Aggregate Output: count(*) -> Foreign Scan Output: ft1.c5, NULL::bigint, (sqrt((ft1.c2)::double precision)) Filter: (((((avg(ft1.c1)) / (avg(ft1.c1))))::double precision * random()) <= '1'::double precision) Relations: Aggregate on (public.ft1) - Remote SQL: SELECT c5, NULL::bigint, sqrt(c2), avg("C 1") FROM "S 1"."T 1" GROUP BY c5, (sqrt(c2)) HAVING ((avg("C 1") < 500::numeric)) + Remote SQL: SELECT c5, NULL::bigint, sqrt(c2), avg("C 1") FROM "S 1"."T 1" GROUP BY 1, 3 HAVING ((avg("C 1") < 500::numeric)) (7 rows) select count(*) from (select c5, count(c1) from ft1 group by c5, sqrt(c2) having (avg(c1) / avg(c1)) * random() <= 1 and avg(c1) < 500) x; @@ -2710,15 +2733,15 @@ select sum(c1) from ft1 group by c2 having avg(c1 * (random() <= 1)::int) > 100 -- ORDER BY within aggregate, same column used to order explain (verbose, costs off) select array_agg(c1 order by c1) from ft1 where c1 < 100 group by c2 order by 1; - QUERY PLAN ----------------------------------------------------------------------------------------------------------------------------------- + QUERY PLAN +--------------------------------------------------------------------------------------------------------------------------------- Sort Output: (array_agg(c1 ORDER BY c1)), c2 Sort Key: (array_agg(ft1.c1 ORDER BY ft1.c1)) -> Foreign Scan Output: (array_agg(c1 ORDER BY c1)), c2 Relations: Aggregate on (public.ft1) - Remote SQL: SELECT array_agg("C 1" ORDER BY "C 1" ASC NULLS LAST), c2 FROM "S 1"."T 1" WHERE (("C 1" < 100)) GROUP BY c2 + Remote SQL: SELECT array_agg("C 1" ORDER BY "C 1" ASC NULLS LAST), c2 FROM "S 1"."T 1" WHERE (("C 1" < 100)) GROUP BY 2 (7 rows) select array_agg(c1 order by c1) from ft1 where c1 < 100 group by c2 order by 1; @@ -2756,15 +2779,15 @@ select array_agg(c5 order by c1 desc) from ft2 where c2 = 6 and c1 < 50; -- DISTINCT within aggregate explain (verbose, costs off) select array_agg(distinct (t1.c1)%5) from ft4 t1 full join ft5 t2 on (t1.c1 = t2.c1) where t1.c1 < 20 or (t1.c1 is null and t2.c1 < 5) group by (t2.c1)%3 order by 1; - QUERY PLAN ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- + QUERY PLAN +---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Sort Output: (array_agg(DISTINCT (t1.c1 % 5))), ((t2.c1 % 3)) Sort Key: (array_agg(DISTINCT (t1.c1 % 5))) -> Foreign Scan Output: (array_agg(DISTINCT (t1.c1 % 5))), ((t2.c1 % 3)) Relations: Aggregate on ((public.ft4 t1) FULL JOIN (public.ft5 t2)) - Remote SQL: SELECT array_agg(DISTINCT (r1.c1 % 5)), (r2.c1 % 3) FROM ("S 1"."T 3" r1 FULL JOIN "S 1"."T 4" r2 ON (((r1.c1 = r2.c1)))) WHERE (((r1.c1 < 20) OR ((r1.c1 IS NULL) AND (r2.c1 < 5)))) GROUP BY ((r2.c1 % 3)) + Remote SQL: SELECT array_agg(DISTINCT (r1.c1 % 5)), (r2.c1 % 3) FROM ("S 1"."T 3" r1 FULL JOIN "S 1"."T 4" r2 ON (((r1.c1 = r2.c1)))) WHERE (((r1.c1 < 20) OR ((r1.c1 IS NULL) AND (r2.c1 < 5)))) GROUP BY 2 (7 rows) select array_agg(distinct (t1.c1)%5) from ft4 t1 full join ft5 t2 on (t1.c1 = t2.c1) where t1.c1 < 20 or (t1.c1 is null and t2.c1 < 5) group by (t2.c1)%3 order by 1; @@ -2777,15 +2800,15 @@ select array_agg(distinct (t1.c1)%5) from ft4 t1 full join ft5 t2 on (t1.c1 = t2 -- DISTINCT combined with ORDER BY within aggregate explain (verbose, costs off) select array_agg(distinct (t1.c1)%5 order by (t1.c1)%5) from ft4 t1 full join ft5 t2 on (t1.c1 = t2.c1) where t1.c1 < 20 or (t1.c1 is null and t2.c1 < 5) group by (t2.c1)%3 order by 1; - QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- + QUERY PLAN +------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ Sort Output: (array_agg(DISTINCT (t1.c1 % 5) ORDER BY (t1.c1 % 5))), ((t2.c1 % 3)) Sort Key: (array_agg(DISTINCT (t1.c1 % 5) ORDER BY (t1.c1 % 5))) -> Foreign Scan Output: (array_agg(DISTINCT (t1.c1 % 5) ORDER BY (t1.c1 % 5))), ((t2.c1 % 3)) Relations: Aggregate on ((public.ft4 t1) FULL JOIN (public.ft5 t2)) - Remote SQL: SELECT array_agg(DISTINCT (r1.c1 % 5) ORDER BY ((r1.c1 % 5)) ASC NULLS LAST), (r2.c1 % 3) FROM ("S 1"."T 3" r1 FULL JOIN "S 1"."T 4" r2 ON (((r1.c1 = r2.c1)))) WHERE (((r1.c1 < 20) OR ((r1.c1 IS NULL) AND (r2.c1 < 5)))) GROUP BY ((r2.c1 % 3)) + Remote SQL: SELECT array_agg(DISTINCT (r1.c1 % 5) ORDER BY ((r1.c1 % 5)) ASC NULLS LAST), (r2.c1 % 3) FROM ("S 1"."T 3" r1 FULL JOIN "S 1"."T 4" r2 ON (((r1.c1 = r2.c1)))) WHERE (((r1.c1 < 20) OR ((r1.c1 IS NULL) AND (r2.c1 < 5)))) GROUP BY 2 (7 rows) select array_agg(distinct (t1.c1)%5 order by (t1.c1)%5) from ft4 t1 full join ft5 t2 on (t1.c1 = t2.c1) where t1.c1 < 20 or (t1.c1 is null and t2.c1 < 5) group by (t2.c1)%3 order by 1; @@ -2797,15 +2820,15 @@ select array_agg(distinct (t1.c1)%5 order by (t1.c1)%5) from ft4 t1 full join ft explain (verbose, costs off) select array_agg(distinct (t1.c1)%5 order by (t1.c1)%5 desc nulls last) from ft4 t1 full join ft5 t2 on (t1.c1 = t2.c1) where t1.c1 < 20 or (t1.c1 is null and t2.c1 < 5) group by (t2.c1)%3 order by 1; - QUERY PLAN -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- + QUERY PLAN +------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Sort Output: (array_agg(DISTINCT (t1.c1 % 5) ORDER BY (t1.c1 % 5) DESC NULLS LAST)), ((t2.c1 % 3)) Sort Key: (array_agg(DISTINCT (t1.c1 % 5) ORDER BY (t1.c1 % 5) DESC NULLS LAST)) -> Foreign Scan Output: (array_agg(DISTINCT (t1.c1 % 5) ORDER BY (t1.c1 % 5) DESC NULLS LAST)), ((t2.c1 % 3)) Relations: Aggregate on ((public.ft4 t1) FULL JOIN (public.ft5 t2)) - Remote SQL: SELECT array_agg(DISTINCT (r1.c1 % 5) ORDER BY ((r1.c1 % 5)) DESC NULLS LAST), (r2.c1 % 3) FROM ("S 1"."T 3" r1 FULL JOIN "S 1"."T 4" r2 ON (((r1.c1 = r2.c1)))) WHERE (((r1.c1 < 20) OR ((r1.c1 IS NULL) AND (r2.c1 < 5)))) GROUP BY ((r2.c1 % 3)) + Remote SQL: SELECT array_agg(DISTINCT (r1.c1 % 5) ORDER BY ((r1.c1 % 5)) DESC NULLS LAST), (r2.c1 % 3) FROM ("S 1"."T 3" r1 FULL JOIN "S 1"."T 4" r2 ON (((r1.c1 = r2.c1)))) WHERE (((r1.c1 < 20) OR ((r1.c1 IS NULL) AND (r2.c1 < 5)))) GROUP BY 2 (7 rows) select array_agg(distinct (t1.c1)%5 order by (t1.c1)%5 desc nulls last) from ft4 t1 full join ft5 t2 on (t1.c1 = t2.c1) where t1.c1 < 20 or (t1.c1 is null and t2.c1 < 5) group by (t2.c1)%3 order by 1; @@ -2818,15 +2841,15 @@ select array_agg(distinct (t1.c1)%5 order by (t1.c1)%5 desc nulls last) from ft4 -- FILTER within aggregate explain (verbose, costs off) select sum(c1) filter (where c1 < 100 and c2 > 5) from ft1 group by c2 order by 1 nulls last; - QUERY PLAN --------------------------------------------------------------------------------------------------------------------- + QUERY PLAN +------------------------------------------------------------------------------------------------------------------- Sort Output: (sum(c1) FILTER (WHERE ((c1 < 100) AND (c2 > 5)))), c2 Sort Key: (sum(ft1.c1) FILTER (WHERE ((ft1.c1 < 100) AND (ft1.c2 > 5)))) -> Foreign Scan Output: (sum(c1) FILTER (WHERE ((c1 < 100) AND (c2 > 5)))), c2 Relations: Aggregate on (public.ft1) - Remote SQL: SELECT sum("C 1") FILTER (WHERE (("C 1" < 100) AND (c2 > 5))), c2 FROM "S 1"."T 1" GROUP BY c2 + Remote SQL: SELECT sum("C 1") FILTER (WHERE (("C 1" < 100) AND (c2 > 5))), c2 FROM "S 1"."T 1" GROUP BY 2 (7 rows) select sum(c1) filter (where c1 < 100 and c2 > 5) from ft1 group by c2 order by 1 nulls last; @@ -2847,12 +2870,12 @@ select sum(c1) filter (where c1 < 100 and c2 > 5) from ft1 group by c2 order by -- DISTINCT, ORDER BY and FILTER within aggregate explain (verbose, costs off) select sum(c1%3), sum(distinct c1%3 order by c1%3) filter (where c1%3 < 2), c2 from ft1 where c2 = 6 group by c2; - QUERY PLAN -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- + QUERY PLAN +------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ Foreign Scan Output: (sum((c1 % 3))), (sum(DISTINCT (c1 % 3) ORDER BY (c1 % 3)) FILTER (WHERE ((c1 % 3) < 2))), c2 Relations: Aggregate on (public.ft1) - Remote SQL: SELECT sum(("C 1" % 3)), sum(DISTINCT ("C 1" % 3) ORDER BY (("C 1" % 3)) ASC NULLS LAST) FILTER (WHERE (("C 1" % 3) < 2)), c2 FROM "S 1"."T 1" WHERE ((c2 = 6)) GROUP BY c2 + Remote SQL: SELECT sum(("C 1" % 3)), sum(DISTINCT ("C 1" % 3) ORDER BY (("C 1" % 3)) ASC NULLS LAST) FILTER (WHERE (("C 1" % 3) < 2)), c2 FROM "S 1"."T 1" WHERE ((c2 = 6)) GROUP BY 3 (4 rows) select sum(c1%3), sum(distinct c1%3 order by c1%3) filter (where c1%3 < 2), c2 from ft1 where c2 = 6 group by c2; @@ -2948,15 +2971,15 @@ select sum(c2) filter (where c2 in (select c2 from ft1 where c2 < 5)) from ft1; -- Ordered-sets within aggregate explain (verbose, costs off) select c2, rank('10'::varchar) within group (order by c6), percentile_cont(c2/10::numeric) within group (order by c1) from ft1 where c2 < 10 group by c2 having percentile_cont(c2/10::numeric) within group (order by c1) < 500 order by c2; - QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ + QUERY PLAN +---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Sort Output: c2, (rank('10'::character varying) WITHIN GROUP (ORDER BY c6)), (percentile_cont((((c2)::numeric / '10'::numeric))::double precision) WITHIN GROUP (ORDER BY ((c1)::double precision))) Sort Key: ft1.c2 -> Foreign Scan Output: c2, (rank('10'::character varying) WITHIN GROUP (ORDER BY c6)), (percentile_cont((((c2)::numeric / '10'::numeric))::double precision) WITHIN GROUP (ORDER BY ((c1)::double precision))) Relations: Aggregate on (public.ft1) - Remote SQL: SELECT c2, rank('10'::character varying) WITHIN GROUP (ORDER BY c6 ASC NULLS LAST), percentile_cont((c2 / 10::numeric)) WITHIN GROUP (ORDER BY ("C 1") ASC NULLS LAST) FROM "S 1"."T 1" WHERE ((c2 < 10)) GROUP BY c2 HAVING ((percentile_cont((c2 / 10::numeric)) WITHIN GROUP (ORDER BY ("C 1") ASC NULLS LAST) < 500::double precision)) + Remote SQL: SELECT c2, rank('10'::character varying) WITHIN GROUP (ORDER BY c6 ASC NULLS LAST), percentile_cont((c2 / 10::numeric)) WITHIN GROUP (ORDER BY ("C 1") ASC NULLS LAST) FROM "S 1"."T 1" WHERE ((c2 < 10)) GROUP BY 1 HAVING ((percentile_cont((c2 / 10::numeric)) WITHIN GROUP (ORDER BY ("C 1") ASC NULLS LAST) < 500::double precision)) (7 rows) select c2, rank('10'::varchar) within group (order by c6), percentile_cont(c2/10::numeric) within group (order by c1) from ft1 where c2 < 10 group by c2 having percentile_cont(c2/10::numeric) within group (order by c1) < 500 order by c2; @@ -2972,12 +2995,12 @@ select c2, rank('10'::varchar) within group (order by c6), percentile_cont(c2/10 -- Using multiple arguments within aggregates explain (verbose, costs off) select c1, rank(c1, c2) within group (order by c1, c2) from ft1 group by c1, c2 having c1 = 6 order by 1; - QUERY PLAN -------------------------------------------------------------------------------------------------------------------------------------------------------------------------- + QUERY PLAN +-------------------------------------------------------------------------------------------------------------------------------------------------------------------- Foreign Scan Output: c1, (rank(c1, c2) WITHIN GROUP (ORDER BY c1, c2)), c2 Relations: Aggregate on (public.ft1) - Remote SQL: SELECT "C 1", rank("C 1", c2) WITHIN GROUP (ORDER BY "C 1" ASC NULLS LAST, c2 ASC NULLS LAST), c2 FROM "S 1"."T 1" WHERE (("C 1" = 6)) GROUP BY "C 1", c2 + Remote SQL: SELECT "C 1", rank("C 1", c2) WITHIN GROUP (ORDER BY "C 1" ASC NULLS LAST, c2 ASC NULLS LAST), c2 FROM "S 1"."T 1" WHERE (("C 1" = 6)) GROUP BY 1, 3 (4 rows) select c1, rank(c1, c2) within group (order by c1, c2) from ft1 group by c1, c2 having c1 = 6 order by 1; @@ -3015,15 +3038,15 @@ alter server loopback options (set extensions 'postgres_fdw'); -- Now aggregate will be pushed. Aggregate will display VARIADIC argument. explain (verbose, costs off) select c2, least_agg(c1) from ft1 where c2 < 100 group by c2 order by c2; - QUERY PLAN ------------------------------------------------------------------------------------------------------------------------- + QUERY PLAN +----------------------------------------------------------------------------------------------------------------------- Sort Output: c2, (least_agg(VARIADIC ARRAY[c1])) Sort Key: ft1.c2 -> Foreign Scan Output: c2, (least_agg(VARIADIC ARRAY[c1])) Relations: Aggregate on (public.ft1) - Remote SQL: SELECT c2, public.least_agg(VARIADIC ARRAY["C 1"]) FROM "S 1"."T 1" WHERE ((c2 < 100)) GROUP BY c2 + Remote SQL: SELECT c2, public.least_agg(VARIADIC ARRAY["C 1"]) FROM "S 1"."T 1" WHERE ((c2 < 100)) GROUP BY 1 (7 rows) select c2, least_agg(c1) from ft1 where c2 < 100 group by c2 order by c2; @@ -3115,12 +3138,12 @@ alter server loopback options (set extensions 'postgres_fdw'); -- Now this will be pushed as sort operator is part of the extension. explain (verbose, costs off) select array_agg(c1 order by c1 using operator(public.<^)) from ft2 where c2 = 6 and c1 < 100 group by c2; - QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------------------------------------------ + QUERY PLAN +---------------------------------------------------------------------------------------------------------------------------------------------------------------- Foreign Scan Output: (array_agg(c1 ORDER BY c1 USING <^ NULLS LAST)), c2 Relations: Aggregate on (public.ft2) - Remote SQL: SELECT array_agg("C 1" ORDER BY "C 1" USING OPERATOR(public.<^) NULLS LAST), c2 FROM "S 1"."T 1" WHERE (("C 1" < 100)) AND ((c2 = 6)) GROUP BY c2 + Remote SQL: SELECT array_agg("C 1" ORDER BY "C 1" USING OPERATOR(public.<^) NULLS LAST), c2 FROM "S 1"."T 1" WHERE (("C 1" < 100)) AND ((c2 = 6)) GROUP BY 2 (4 rows) select array_agg(c1 order by c1 using operator(public.<^)) from ft2 where c2 = 6 and c1 < 100 group by c2; @@ -3179,8 +3202,8 @@ select count(t1.c3) from ft1 t1, ft1 t2 where t1.c1 = postgres_fdw_abs(t1.c2); -- Subquery in FROM clause having aggregate explain (verbose, costs off) select count(*), x.b from ft1, (select c2 a, sum(c1) b from ft1 group by c2) x where ft1.c2 = x.a group by x.b order by 1, 2; - QUERY PLAN ------------------------------------------------------------------------------------------------- + QUERY PLAN +----------------------------------------------------------------------------------------------- Sort Output: (count(*)), x.b Sort Key: (count(*)), x.b @@ -3201,7 +3224,7 @@ select count(*), x.b from ft1, (select c2 a, sum(c1) b from ft1 group by c2) x w -> Foreign Scan Output: ft1_1.c2, (sum(ft1_1.c1)) Relations: Aggregate on (public.ft1) - Remote SQL: SELECT c2, sum("C 1") FROM "S 1"."T 1" GROUP BY c2 + Remote SQL: SELECT c2, sum("C 1") FROM "S 1"."T 1" GROUP BY 1 (21 rows) select count(*), x.b from ft1, (select c2 a, sum(c1) b from ft1 group by c2) x where ft1.c2 = x.a group by x.b order by 1, 2; @@ -3222,15 +3245,15 @@ select count(*), x.b from ft1, (select c2 a, sum(c1) b from ft1 group by c2) x w -- FULL join with IS NULL check in HAVING explain (verbose, costs off) select avg(t1.c1), sum(t2.c1) from ft4 t1 full join ft5 t2 on (t1.c1 = t2.c1) group by t2.c1 having (avg(t1.c1) is null and sum(t2.c1) < 10) or sum(t2.c1) is null order by 1 nulls last, 2; - QUERY PLAN -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- + QUERY PLAN +--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Sort Output: (avg(t1.c1)), (sum(t2.c1)), t2.c1 Sort Key: (avg(t1.c1)), (sum(t2.c1)) -> Foreign Scan Output: (avg(t1.c1)), (sum(t2.c1)), t2.c1 Relations: Aggregate on ((public.ft4 t1) FULL JOIN (public.ft5 t2)) - Remote SQL: SELECT avg(r1.c1), sum(r2.c1), r2.c1 FROM ("S 1"."T 3" r1 FULL JOIN "S 1"."T 4" r2 ON (((r1.c1 = r2.c1)))) GROUP BY r2.c1 HAVING ((((avg(r1.c1) IS NULL) AND (sum(r2.c1) < 10)) OR (sum(r2.c1) IS NULL))) + Remote SQL: SELECT avg(r1.c1), sum(r2.c1), r2.c1 FROM ("S 1"."T 3" r1 FULL JOIN "S 1"."T 4" r2 ON (((r1.c1 = r2.c1)))) GROUP BY 3 HAVING ((((avg(r1.c1) IS NULL) AND (sum(r2.c1) < 10)) OR (sum(r2.c1) IS NULL))) (7 rows) select avg(t1.c1), sum(t2.c1) from ft4 t1 full join ft5 t2 on (t1.c1 = t2.c1) group by t2.c1 having (avg(t1.c1) is null and sum(t2.c1) < 10) or sum(t2.c1) is null order by 1 nulls last, 2; @@ -3284,8 +3307,8 @@ select sum(c2) * (random() <= 1)::int as sum from ft1 order by 1; set enable_hashagg to false; explain (verbose, costs off) select c2, sum from "S 1"."T 1" t1, lateral (select sum(t2.c1 + t1."C 1") sum from ft2 t2 group by t2.c1) qry where t1.c2 * 2 = qry.sum and t1.c2 < 3 and t1."C 1" < 100 order by 1; - QUERY PLAN ----------------------------------------------------------------------------------------------------------- + QUERY PLAN +------------------------------------------------------------------------------------------------------ Sort Output: t1.c2, qry.sum Sort Key: t1.c2 @@ -3301,7 +3324,7 @@ select c2, sum from "S 1"."T 1" t1, lateral (select sum(t2.c1 + t1."C 1") sum fr -> Foreign Scan Output: (sum((t2.c1 + t1."C 1"))), t2.c1 Relations: Aggregate on (public.ft2 t2) - Remote SQL: SELECT sum(("C 1" + $1::integer)), "C 1" FROM "S 1"."T 1" GROUP BY "C 1" + Remote SQL: SELECT sum(("C 1" + $1::integer)), "C 1" FROM "S 1"."T 1" GROUP BY 2 (16 rows) select c2, sum from "S 1"."T 1" t1, lateral (select sum(t2.c1 + t1."C 1") sum from ft2 t2 group by t2.c1) qry where t1.c2 * 2 = qry.sum and t1.c2 < 3 and t1."C 1" < 100 order by 1; @@ -3447,8 +3470,8 @@ select c2, sum(c1), grouping(c2) from ft1 where c2 < 3 group by c2 order by 1 nu -- DISTINCT itself is not pushed down, whereas underneath aggregate is pushed explain (verbose, costs off) select distinct sum(c1)/1000 s from ft2 where c2 < 6 group by c2 order by 1; - QUERY PLAN --------------------------------------------------------------------------------------------------------- + QUERY PLAN +------------------------------------------------------------------------------------------------------- Unique Output: ((sum(c1) / 1000)), c2 -> Sort @@ -3457,7 +3480,7 @@ select distinct sum(c1)/1000 s from ft2 where c2 < 6 group by c2 order by 1; -> Foreign Scan Output: ((sum(c1) / 1000)), c2 Relations: Aggregate on (public.ft2) - Remote SQL: SELECT (sum("C 1") / 1000), c2 FROM "S 1"."T 1" WHERE ((c2 < 6)) GROUP BY c2 + Remote SQL: SELECT (sum("C 1") / 1000), c2 FROM "S 1"."T 1" WHERE ((c2 < 6)) GROUP BY 2 (9 rows) select distinct sum(c1)/1000 s from ft2 where c2 < 6 group by c2 order by 1; @@ -3470,8 +3493,8 @@ select distinct sum(c1)/1000 s from ft2 where c2 < 6 group by c2 order by 1; -- WindowAgg explain (verbose, costs off) select c2, sum(c2), count(c2) over (partition by c2%2) from ft2 where c2 < 10 group by c2 order by 1; - QUERY PLAN -------------------------------------------------------------------------------------------------------------- + QUERY PLAN +------------------------------------------------------------------------------------------------------------ Sort Output: c2, (sum(c2)), (count(c2) OVER (?)), ((c2 % 2)) Sort Key: ft2.c2 @@ -3483,7 +3506,7 @@ select c2, sum(c2), count(c2) over (partition by c2%2) from ft2 where c2 < 10 gr -> Foreign Scan Output: c2, ((c2 % 2)), (sum(c2)) Relations: Aggregate on (public.ft2) - Remote SQL: SELECT c2, (c2 % 2), sum(c2) FROM "S 1"."T 1" WHERE ((c2 < 10)) GROUP BY c2 + Remote SQL: SELECT c2, (c2 % 2), sum(c2) FROM "S 1"."T 1" WHERE ((c2 < 10)) GROUP BY 1 (12 rows) select c2, sum(c2), count(c2) over (partition by c2%2) from ft2 where c2 < 10 group by c2 order by 1; @@ -3503,8 +3526,8 @@ select c2, sum(c2), count(c2) over (partition by c2%2) from ft2 where c2 < 10 gr explain (verbose, costs off) select c2, array_agg(c2) over (partition by c2%2 order by c2 desc) from ft1 where c2 < 10 group by c2 order by 1; - QUERY PLAN ----------------------------------------------------------------------------------------------------- + QUERY PLAN +--------------------------------------------------------------------------------------------------- Sort Output: c2, (array_agg(c2) OVER (?)), ((c2 % 2)) Sort Key: ft1.c2 @@ -3516,7 +3539,7 @@ select c2, array_agg(c2) over (partition by c2%2 order by c2 desc) from ft1 wher -> Foreign Scan Output: c2, ((c2 % 2)) Relations: Aggregate on (public.ft1) - Remote SQL: SELECT c2, (c2 % 2) FROM "S 1"."T 1" WHERE ((c2 < 10)) GROUP BY c2 + Remote SQL: SELECT c2, (c2 % 2) FROM "S 1"."T 1" WHERE ((c2 < 10)) GROUP BY 1 (12 rows) select c2, array_agg(c2) over (partition by c2%2 order by c2 desc) from ft1 where c2 < 10 group by c2 order by 1; @@ -3536,8 +3559,8 @@ select c2, array_agg(c2) over (partition by c2%2 order by c2 desc) from ft1 wher explain (verbose, costs off) select c2, array_agg(c2) over (partition by c2%2 order by c2 range between current row and unbounded following) from ft1 where c2 < 10 group by c2 order by 1; - QUERY PLAN ----------------------------------------------------------------------------------------------------- + QUERY PLAN +--------------------------------------------------------------------------------------------------- Sort Output: c2, (array_agg(c2) OVER (?)), ((c2 % 2)) Sort Key: ft1.c2 @@ -3549,7 +3572,7 @@ select c2, array_agg(c2) over (partition by c2%2 order by c2 range between curre -> Foreign Scan Output: c2, ((c2 % 2)) Relations: Aggregate on (public.ft1) - Remote SQL: SELECT c2, (c2 % 2) FROM "S 1"."T 1" WHERE ((c2 < 10)) GROUP BY c2 + Remote SQL: SELECT c2, (c2 % 2) FROM "S 1"."T 1" WHERE ((c2 < 10)) GROUP BY 1 (12 rows) select c2, array_agg(c2) over (partition by c2%2 order by c2 range between current row and unbounded following) from ft1 where c2 < 10 group by c2 order by 1; diff --git a/contrib/postgres_fdw/postgres_fdw.c b/contrib/postgres_fdw/postgres_fdw.c index fb65e2e..0ad32a5 100644 --- a/contrib/postgres_fdw/postgres_fdw.c +++ b/contrib/postgres_fdw/postgres_fdw.c @@ -4591,7 +4591,7 @@ static bool foreign_grouping_ok(PlannerInfo *root, RelOptInfo *grouped_rel) { Query *query = root->parse; - PathTarget *grouping_target; + PathTarget *grouping_target = root->upper_targets[UPPERREL_GROUP_AGG]; PgFdwRelationInfo *fpinfo = (PgFdwRelationInfo *) grouped_rel->fdw_private; PgFdwRelationInfo *ofpinfo; List *aggvars; @@ -4615,16 +4615,6 @@ foreign_grouping_ok(PlannerInfo *root, RelOptInfo *grouped_rel) return false; /* - * The targetlist expected from this node and the targetlist pushed down - * to the foreign server may be different. The latter requires - * sortgrouprefs to be set to push down GROUP BY clause, but should not - * have those arising from ORDER BY clause. These sortgrouprefs may be - * different from those in the plan's targetlist. Use a copy of path - * target to record the new sortgrouprefs. - */ - grouping_target = copy_pathtarget(root->upper_targets[UPPERREL_GROUP_AGG]); - - /* * Evaluate grouping targets and check whether they are safe to push down * to the foreign side. All GROUP BY expressions will be part of the * grouping target and thus there is no need to evaluate it separately. @@ -4641,6 +4631,8 @@ foreign_grouping_ok(PlannerInfo *root, RelOptInfo *grouped_rel) /* Check whether this expression is part of GROUP BY clause */ if (sgref && get_sortgroupref_clause_noerr(sgref, query->groupClause)) { + TargetEntry *tle; + /* * If any of the GROUP BY expression is not shippable we can not * push down aggregation to the foreign server. @@ -4648,8 +4640,18 @@ foreign_grouping_ok(PlannerInfo *root, RelOptInfo *grouped_rel) if (!is_foreign_expr(root, grouped_rel, expr)) return false; - /* Pushable, add to tlist */ - tlist = add_to_flat_tlist(tlist, list_make1(expr)); + /* + * Pushable, add to tlist. We need to create a tle for this + * expression and need to transfer the sortgroupref too. We cannot + * use add_to_flat_tlist() here as it avoids the duplicate entries + * in the targetlist but here we want those duplicate entries as + * there can be multiple GROUP BY expressions pointing to the same + * column at different positions. + */ + tle = makeTargetEntry((Expr *) expr, list_length(tlist) + 1, NULL, + false); + tle->ressortgroupref = sgref; + tlist = lappend(tlist, tle); } else { @@ -4661,14 +4663,6 @@ foreign_grouping_ok(PlannerInfo *root, RelOptInfo *grouped_rel) } else { - /* - * If we have sortgroupref set, then it means that we have an - * ORDER BY entry pointing to this expression. Since we are - * not pushing ORDER BY with GROUP BY, clear it. - */ - if (sgref) - grouping_target->sortgrouprefs[i] = 0; - /* Not matched exactly, pull the var with aggregates then */ aggvars = pull_var_clause((Node *) expr, PVC_INCLUDE_AGGREGATES); @@ -4771,9 +4765,6 @@ foreign_grouping_ok(PlannerInfo *root, RelOptInfo *grouped_rel) } } - /* Transfer any sortgroupref data to the replacement tlist */ - apply_pathtarget_labeling_to_tlist(tlist, grouping_target); - /* Store generated targetlist */ fpinfo->grouped_tlist = tlist; diff --git a/contrib/postgres_fdw/sql/postgres_fdw.sql b/contrib/postgres_fdw/sql/postgres_fdw.sql index ddfec79..81d2329 100644 --- a/contrib/postgres_fdw/sql/postgres_fdw.sql +++ b/contrib/postgres_fdw/sql/postgres_fdw.sql @@ -636,6 +636,12 @@ explain (verbose, costs off) select count(c2) w, c2 x, 5 y, 7.0 z from ft1 group by 2, y, 9.0::int order by 2; select count(c2) w, c2 x, 5 y, 7.0 z from ft1 group by 2, y, 9.0::int order by 2; +-- GROUP BY clause referring to same column multiple times +-- Also, ORDER BY contains an aggregate function +explain (verbose, costs off) +select c2, c2 from ft1 where c2 > 6 group by 1, 2 order by sum(c1); +select c2, c2 from ft1 where c2 > 6 group by 1, 2 order by sum(c1); + -- Testing HAVING clause shippability explain (verbose, costs off) select c2, sum(c1) from ft2 group by c2 having avg(c1) < 500 and sum(c1) < 49800 order by c2;