On Mon, 13 Mar 2023 at 10:43, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>
> David Rowley <dgrowleyml@gmail.com> writes:
> > It is only possible to either leave such quals in the outer query in
> > the WHERE clause or make them run conditions of some WindowClause in
> > the subquery. They can never be legally in the WHERE clause of the
> > subquery as window functions cannot be evaluated in the WHERE clause.
>
> Ah, got it. So basically, the quals that we want are excluded by
> check_output_expressions' point 4 (since they reference window
> function output columns rather than partitioning columns). I think
> we need them to satisfy every other property that's checked in this
> code, though. Now I agree that we need to refactor a bit -- we
> don't want to have to re-check all of these conditions.
OK, patch attached.
I've coded it so the only safety hazard that we can ignore for run
conditions is the column is in all WindowClause PARTITION BYs. I
believe the DISTINCT ON column not in the sort list is still a hazard.
The attached also fixes the issue with making a run condition when the
window func contains a volatile func, e.g:
explain select * from (select oid,count(random()) over (order by oid)
c from pg_class) c where c < 10;
I think this is ok to back patch to v15 as the pushdown_safety_info
struct is static. I think it'd be ok to widen that type to int
instead of unsigned char, I just didn't.
I probably need to do a bit more indenting work. I'll look again tomorrow.
David
