Re: ntile() throws ERROR when hashagg is false

>>>>> "Rajkumar" == Rajkumar Raghuwanshi <rajkumar(dot)raghuwanshi(at)enterprisedb(dot)com> writes:

Rajkumar> Hi

Rajkumar> ntile() throws ERROR when hashagg is false, test case given
Rajkumar> below.

Rajkumar> postgres=# create table foo (a int, b int, c text);
Rajkumar> CREATE TABLE
Rajkumar> postgres=# insert into foo select i%20, i%30, to_char(i%12, 'FM0000') from
Rajkumar> generate_series(0, 36) i;
Rajkumar> INSERT 0 37
Rajkumar> postgres=# explain select ntile(a) OVER () from foo GROUP BY a;

This query isn't actually legal per the spec; the argument of ntile is
restricted to being a constant or parameter, so it can't change from row
to row. PG is more flexible, but that doesn't make the query any more
meaningful.

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).

--
Andrew (irc:RhodiumToad)