Re: Query results vary depending on the plan cache mode used - Mailing list pgsql-hackers

On Thu, Aug 1, 2024 at 10:34 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Richard Guo <guofenglinux@gmail.com> writes:
> > While working on the grouping sets patches for queries with GROUP BY
> > items that are constants, I noticed $subject on master.  As an
> > example, consider
>
> This particular example seems like it's just an illustration of
> our known bugs with grouping sets using non-Var columns.  I see
> no reason to blame the plan cache for it.  Do you have any
> examples that don't depend on such a bug?

Yeah, it's not the fault of the plan cache.  I noticed this because in
check_ungrouped_columns, both Const and Param are treated as always
acceptable.  However, in setrefs.c these two expression types are
handled differently: Const is never matched to the lower tlist, while
Param is always attempted to be matched to the lower tlist.  This
discrepancy causes the query in the example to behave differently
depending on the plan cache mode.

I'm unsure which behavior is the expected one.


Another related question I have is about the behavior of the following
query:

select 3 as c1, 3 as c2 from generate_series(1,2) t group by
rollup(c1) having 3 = 3;

Should the target entry 'c2', as well as the Const expressions in
havingQual, be replaced with references to the grouping key?

Thanks
Richard