Date: Mon, 30 May 2022 13:27:53 +0300
From: Ilya Anfimov <ilan@tzirechnoy.com>
To: pgsql-bugs@lists.postgresql.org
Subject: Re: BUG #17502: View based on window functions returns wrong results
 when queried
Message-ID: <20220530102753.GA2725032@azor.tzirechnoy.ru>
Mail-Followup-To: Ilya Anfimov <ilan@tzirechnoy.com>,
	pgsql-bugs@lists.postgresql.org
References: <17502-281a7aaacfaa872a@postgresql.org>
 <CABUevEz7t3OWxaSSVSHOxa5KcYioOk=yRwoL3iMViyZQ8m2wAw@mail.gmail.com>
 <CAGckUK2GLF=d9J5ErEWgK5x8ECqCw4equnq3jEzrqtfJw+iHYw@mail.gmail.com>
 <CAApHDvoR4BXm7oxSrYhpEAFOZ-qMPkkYnn14y6sxtPf=5w5OOw@mail.gmail.com>
 <3722616.1653877139@sss.pgh.pa.us>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: <3722616.1653877139@sss.pgh.pa.us>
User-Agent: Mutt/1.10.1 (2018-07-13)
Archived-At: 
 <https://www.postgresql.org/message-id/20220530102753.GA2725032%40azor.tzirechnoy.ru>
Precedence: bulk

On Sun, May 29, 2022 at 10:18:59PM -0400, Tom Lane wrote:
> David Rowley <dgrowleyml@gmail.com> writes:
> > The problem seems to be down to the fact that
> > remove_unused_subquery_outputs() does not check if the to-be-removed
> > target entry references WindowClauses which contain set-returning
> > functions.
> 
> I was sort of wondering why we allow SRFs in this context in the
> first place.  The results don't seem terribly well-defined to me.
> In particular, a WindowFunc invocation is not supposed to change the
> number of rows in the query result, and yet this one is doing so.
 
 I  would like to note, that some SRFs may return only one row in
specific cases, like dblink() with the specific query.
 It may used much more often, than real multirow SRFs in PARTITION BY.

 While converting it to a LATERAL JOIN is not  a lot  of work  --
breaking it in previous versions may affect a lot of people.