Hi
ntile() throws ERROR when hashagg is false, test case given below.
postgres=# create table foo (a int, b int, c text);
CREATE TABLE
postgres=# insert into foo select i%20, i%30, to_char(i%12, 'FM0000') from generate_series(0, 36) i;
INSERT 0 37
postgres=# explain select ntile(a) OVER () from foo GROUP BY a;
QUERY PLAN
-------------------------------------------------------------------
WindowAgg (cost=25.00..29.50 rows=200 width=8)
-> HashAggregate (cost=25.00..27.00 rows=200 width=4)
Group Key: a
-> Seq Scan on foo (cost=0.00..22.00 rows=1200 width=4)
(4 rows)
postgres=# select ntile(a) OVER () from foo GROUP BY a;
ntile
-------
1
1
2
2
3
3
4
4
5
5
6
6
7
7
8
8
9
9
10
11
(20 rows)
postgres=# set enable_hashagg to false;
SET
postgres=# explain select ntile(a) OVER () from foo GROUP BY a;
QUERY PLAN
-------------------------------------------------------------------------
WindowAgg (cost=83.37..91.87 rows=200 width=8)
-> Group (cost=83.37..89.37 rows=200 width=4)
Group Key: a
-> Sort (cost=83.37..86.37 rows=1200 width=4)
Sort Key: a
-> Seq Scan on foo (cost=0.00..22.00 rows=1200 width=4)
(6 rows)
postgres=# select ntile(a) OVER () from foo GROUP BY a;
ERROR: argument of ntile must be greater than zero
Thanks & Regards,
Rajkumar Raghuwanshi
QMG, EnterpriseDB Corporation