Re: issues with eager aggregation

On 18/09/2026 01:58, Richard Guo wrote:
> On Fri, Sep 18, 2026 at 12:55 AM Robert Haas <robertmhaas(at)gmail(dot)com> wrote:
>> On Thu, Sep 17, 2026 at 10:58 AM Richard Guo <guofenglinux(at)gmail(dot)com> 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