On 18/09/2026 01:58, Richard Guo wrote:
> On Fri, Sep 18, 2026 at 12:55 AM Robert Haas <[email protected]> wrote:
>> On Thu, Sep 17, 2026 at 10:58 AM Richard Guo <[email protected]> wrote:
>>> As for the division-by-zero case, I agree it's user-visible, but I
>>> think eager aggregation should follow the same rules as our existing
>>> transformations, which already evaluate expressions on rows that a
>>> join removes.
>
>> OK, good. So this isn't really doing something that is fundamentally
>> new, which is what I was fearing. Do you think this section should be
>> adjusted at all?
>
> Yes, I think it's worth saying that an expression can be evaluated on
> rows that don't appear in the result.
As far as I can see, the documentation still doesn’t clarify this point, and the
SQL:2023 standard doesn’t settle it explicitly either. I ran into the same issue
with pushing a Sort below a join, both in my proposal [1] and in the extension
that backports it.
But it isn’t specific to new optimisations: the existing Gather Merge and Merge
Append paths can already compute a sort key on rows that the join later removes,
so a text-to-integer cast in ORDER BY can fail on master (see the SQL script in
the attachment).
Attached is a draft paragraph for the “Expression Evaluation Rules” section.
Since it describes behaviour that already exists, it might be worth
back-patching to v19 together with the eager aggregation fixes.
[1] Try a presorted outer path when referenced by an ORDER BY prefix
https://www.postgresql.org/message-id/19a9265c-c441-4a43-bc0d-dac533438da0%40gmail.com
--
regards, Andrei Lepikhov,
pgEdge
From 2a8900e4818dda1349d70df69a72155b590e934e Mon Sep 17 00:00:00 2001
From: "Andrei V. Lepikhov" <[email protected]>
Date: Mon, 2 Nov 2026 13:58:30 +0100
Subject: [PATCH] Explain that expressions may be evaluated on rows a join
removes
---
doc/src/sgml/syntax.sgml | 24 ++++++++++++++++++++++++
1 file changed, 24 insertions(+)
diff --git a/doc/src/sgml/syntax.sgml b/doc/src/sgml/syntax.sgml
index a697218c8b3..ef77b4ae028 100644
--- a/doc/src/sgml/syntax.sgml
+++ b/doc/src/sgml/syntax.sgml
@@ -2517,6 +2517,30 @@ SELECT somefunc() OR true;
in any manner allowed by the laws of Boolean algebra.
</para>
+ <para>
+ The same applies across the clauses of a query. The logical order in
+ which a query is described (<literal>FROM</literal> and joins, then
+ <literal>WHERE</literal>, then <literal>GROUP BY</literal> and
+ <literal>HAVING</literal>, then the output list and
+ <literal>ORDER BY</literal>) defines the query's result, not the order
+ in which its expressions are evaluated.
+ <productname>PostgreSQL</productname> does not guarantee that an
+ expression is evaluated only for rows that satisfy the query's join and
+ <literal>WHERE</literal> conditions. The planner may evaluate an
+ expression below a join, and therefore on rows that the join will
+ eliminate and that never appear in the result. For example,
+ a <literal>WHERE</literal> condition that mentions only one table can be
+ checked while scanning that table; a sort required by
+ <literal>ORDER BY</literal> can be performed on one input of a join,
+ computing the sort key for every row of that input; and aggregation can
+ be partially performed before a join (see
+ <xref linkend="guc-enable-eager-aggregate"/>), so that aggregate
+ arguments are evaluated, and aggregate transition functions are called,
+ for rows that the join will remove. If such an expression can fail for
+ some input values, the query can raise an error even though none of the
+ offending rows would have been part of its result.
+ </para>
+
<para>
When it is essential to force evaluation order, a <literal>CASE</literal>
construct (see <xref linkend="functions-conditional"/>) can be
--
2.56.0
-- CASE: Gather Merge
CREATE TABLE t1 (id int, s text);
CREATE TABLE t2 (id int primary key);
INSERT INTO t1 SELECT g, CASE WHEN g = 10 THEN 'x' ELSE g::text END
FROM generate_series(1, 10) g;
INSERT INTO t2 SELECT generate_series(1, 9);
ANALYZE t1, t2;
SET parallel_setup_cost = 0;
SET parallel_tuple_cost = 0;
SET min_parallel_table_scan_size = 0;
EXPLAIN (COSTS OFF)
SELECT t1.s::int FROM t1 JOIN t2 USING (id)
ORDER BY t1.s::int
LIMIT 1;
SELECT t1.s::int FROM t1 JOIN t2 USING (id)
ORDER BY t1.s::int
LIMIT 1;
-- CASE: Merge Append
CREATE TABLE p (id int, s text) PARTITION BY RANGE (id);
CREATE TABLE p1 PARTITION of p FOR VALUES FROM (1) TO (6);
CREATE TABLE p2 PARTITION of p FOR VALUES FROM (6) TO (11);
INSERT INTO p (id,s)
SELECT g, CASE WHEN g = 10 THEN 'x' ELSE g::text END
FROM generate_series(1, 10) g;
CREATE INDEX ON p1 ((s::int));
DROP TABLE IF EXISTS t2;
CREATE TABLE t2 (id int PRIMARY KEY);
INSERT INTO t2 SELECT generate_series(1, 9);
ANALYZE p, t2;
EXPLAIN (COSTS OFF)
SELECT p.s::int FROM p JOIN t2 USING (id) ORDER BY p.s::int LIMIT 1;
SELECT p.s::int FROM p JOIN t2 USING (id) ORDER BY p.s::int LIMIT 1;
-- CASE: Eager Aggregation
CREATE TABLE a (id int PRIMARY KEY, g int);
CREATE TABLE b (id int, s text);
INSERT INTO a SELECT g, g % 2 FROM generate_series(1, 10) g;
INSERT INTO b SELECT g % 10 + 1, '1' FROM generate_series(1, 100) g;
INSERT INTO b VALUES (11, 'x');
ANALYZE a, b;
EXPLAIN (COSTS OFF)
SELECT a.g, sum(b.s::int) FROM a JOIN b USING (id) GROUP BY a.g;
SELECT a.g, sum(b.s::int) FROM a JOIN b USING (id) GROUP BY a.g;
