Re: Aggregate error message

On Fri, 24 May 2019 at 18:17, Vik Fearing <vik(dot)fearing(at)2ndquadrant(dot)com> wrote:
>
> With a sample query such as
>
> SELECT x, avg(x)
> FROM (VALUES (1), (2), (3)) AS v (x);
>
> We give the error message "column "v.x" must appear in the GROUP BY
> clause or be used in an aggregate function".
>
> This is correct but incomplete. Attached is a trivial patch to also
> suggest that the user might have been trying to use a window function.

I think you might have misthought this one. If there's an aggregate
function in the SELECT or HAVING clause, then anything else in the
SELECT clause is going to have to be either in the GROUP BY clause, be
functionally dependent on the GROUP BY clause, or be in an aggregate
function. Putting it into a window function won't help the situation.

postgres=# select sum(x) over(),avg(x) FROM (VALUES (1), (2), (3)) AS v (x);
psql: ERROR: column "v.x" must appear in the GROUP BY clause or be
used in an aggregate function
LINE 1: select sum(x) over(),avg(x) FROM (VALUES (1), (2), (3)) AS v...
^

If there's any change to make to the error message then it would be to
add the functional dependency part, but since we're pretty bad at
detecting that, I don't think we should.

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