Re: BUG #17495: Regression in 15beta1 when filtering subquery including row_number window function

On Wed, May 25, 2022 at 10:17 AM David Rowley <dgrowleyml@gmail.com> wrote:

On Wed, 25 May 2022 at 12:51, Michael Paquier <michael@paquier.xyz> wrote:
>
> On Tue, May 24, 2022 at 04:17:35PM +0000, PG Bug reporting form wrote:
> > From testing with 15beta1, I think there is a regression in filtering a
> > subquery using the result of the row_number window function.  Here is the
> > code:
> >
> > This was discovered by the tests for Sequel, a Ruby database access
> > library.
>
> Inconsistency reproduced here, thanks for the detailed report and the
> test case!  I have added an open item:
> https://wiki.postgresql.org/wiki/PostgreSQL_15_Open_Items

Thanks for adding. I've put myself as the owner of this and will look
at it soon.

This should be introduced in by 9d9c02cc. The rel of the subquery loses

its baserestrictinfo, which is (x_sequel_row_number_x <= 2), when
generating SubqueryScan access paths for it.  As a result, the
WindowFunc target entry is removed as an unused targetlist item.

To be more detailed, set_subquery_pathlist believes the restriction
clause of the subquery's rel is useful to use for the WindowAgg's run
condition, and the run condition will handle all of the required
filtering. So the restriction clause is removed from the
rel->baserestrictinfo. Then when we try to remove subquery's unused
targetlist items, we find the WindowFunc target entry is not needed
either in the reltarget, or in the restriction clauses. So we proceed to
remove it from the subquery's targetList. And that makes
grouping_planner think there are no active Windows in the subquery. As a
result, no WindowAgg node would be generated in the final plan.

Thanks