Re: Add proper planner support for ORDER BY / DISTINCT aggregates

On Tue, Jan 17, 2023 at 3:05 PM Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> Richard Guo <guofenglinux(at)gmail(dot)com> writes:
> > BTW, I wonder if we should have checked CoercionForm before
> > fix_upper_expr_mutator steps into CoerceViaIO->arg to adjust the expr
> > there.
>
> I will just quote what it says in primnodes.h:
>
> * NB: equal() ignores CoercionForm fields, therefore this *must* not carry
> * any semantically significant information.
>
> If you think the planner should act differently for different values of
> CoercionForm, you are mistaken. Maybe this is evidence of some
> previous bit of brain-fade, but if so we need to fix that.

According to this comment in primnodes.h, the planner is not supposed to
treat implicit and explicit casts differently. In this case
set_plan_references is doing its job correctly, to adjust both random()
FuncExprs in targetlist to refer to subplan's output for query 2. As a
consequence we would get a sorted output.

I'm still confused that when the same scenario happens with ORDER BY in
an aggregate function, like in query 1, the result is different in that
we would get an unsorted output.

I wonder if we should avoid this inconsistent behavior.

Thanks
Richard