Re: BUG #19106: Potential regression with CTE materialization planning in Postgres 18

Richard Guo <guofenglinux@gmail.com> writes:
> However, I still find this behavior somewhat confusing.  For example,
> one might expect that an inlined CTE should be semantically equivalent
> to a subquery, yet the following two queries can produce different
> results.

> create table t (a int);
> insert into t values (1), (2);

> with ss as not materialized (select * from t)
> select (select sum((select a from ss where a = t.a limit 1))) from t;
>  sum
> -----
>    1
>    2
> (2 rows)

> select (select sum((select a from (select * from t) ss where a = t.a
> limit 1))) from t;
>  sum
> -----
>    3
> (1 row)

That's with my v1 patch?  I agree it's pretty bogus.

The more I think about this, the more I like the other idea of just
throwing an error rather than trying to fix up cases like bug #19055.
I don't think we have much evidence that anyone is trying to do that
in the real world (otherwise reports would have surfaced years ago).
And this discussion is making it clear that fixing it up is harder
than it sounds.

Also: if we throw an error, and someone shows up with a real-world
example that triggers the error, we'd then have some more evidence
about what would be a plausible interpretation.

So now I'm thinking about something more like the attached.  It
causes the #19055 query to throw an error, and restores our current
test query to working (I added said query to the test so we can't
break it again).

Producing a decently localized error turned out to be harder than
I expected, because RangeTableEntrys don't have location fields so
it's impossible to put an error cursor on the troublesome reference.
I ended up putting the cursor on the aggregate function itself
and giving the name of the CTE in errdetail.

			regards, tom lane