Re: ntile() throws ERROR when hashagg is false

On 14 June 2018 at 18:57, Andrew Gierth <andrew(at)tao11(dot)riddles(dot)org(dot)uk> wrote:
> What I think pg is actually doing is taking the value of the ntile()
> argument from the first row and using that for the whole partition.
> In your example, enabling or disabling hashagg changes the order of the
> input rows for the window function (since you've specified no ordering
> in the window definition), and with hashagg off, you get the smallest
> value of a first (which is 0 and thus an error).

Seems that's the case. I'd guess it was written that way so we could
allow PARAM_EXTERN Params rather than requiring the arg to be a Const.

I wonder if it would be worth adding a run-time check in
window_ntile() that causes an ERROR on first call if there are any
Vars or PARAM_EXEC Params in the function argument. An ERROR might be
better than doing something that the user does not expect.

Ideally, something would alert the user much sooner than the executor,
but I think doing it that way would be quite a bit more work.

--
David Rowley http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services