Обсуждение: Aggregate error message

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.
-- 
Vik Fearing                                          +33 6 46 75 15 36
http://2ndQuadrant.fr     PostgreSQL : Expertise, Formation et Support

On Fri, 24 May 2019 at 18:17, Vik Fearing <vik.fearing@2ndquadrant.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

David Rowley <david.rowley@2ndquadrant.com> writes:
> On Fri, 24 May 2019 at 18:17, Vik Fearing <vik.fearing@2ndquadrant.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.

Yeah.  Also, even if the problem really is that avg(x) should have had
an OVER clause, the fact that the error cursor will not be pointing
at avg(x) means that Vik's wording is still not that helpful.

This is a bit outside our usual error-writing practice, but I wonder
if we could phrase it like "since this query uses aggregation, column
"v.x" must appear in the GROUP BY clause or be used in an aggregate
function".  With that, perhaps the user would realize "oh, I didn't
mean to aggregate" when faced with Vik's example.  But this phrasing
doesn't cover the GROUP-BY-without-aggregate case, and I'm not sure
how to do that without making the message even longer and more unwieldy.

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

Yeah, that's another thing we're failing to cover in the message ...
but it seems unrelated to Vik's example.

            regards, tom lane