Re: Why does aggregate query allow select of non-group by or aggregate values?

-----Original Message-----
From: pgsql-general-owner(at)postgresql(dot)org
[mailto:pgsql-general-owner(at)postgresql(dot)org] On Behalf Of Jack Christensen
Sent: Friday, December 09, 2011 5:48 PM
To: pgsql-general(at)postgresql(dot)org
Subject: [GENERAL] Why does aggregate query allow select of non-group by or
aggregate values?

CREATE TABLE people(
id serial PRIMARY KEY,
name varchar NOT NULL
);

INSERT INTO people(name) VALUES('Adam'), ('Adam'), ('Adam'), ('Bill'),
('Sam'), ('Joe'), ('Joe');

SELECT name, count(*), random()
FROM people
GROUP BY name;

I would expect this query to cause an error because of random(). I ran into
this using an array produced by a subquery as a column in the select of an
aggregate query, but I was able to boil it down to this contrived example.
Shouldn't any expression that is not in the group by or an aggregate
function be rejected?

What am I not understanding?

Thanks.

----------------------------------------------------------------------------
-------

Functions are evaluated once for each row that it generated by the
surrounding query. This is particularly useful if the function in question
takes an aggregate as an input:

SELECT col1, array_processing_function( ARRAY_AGG( col2 ) )
FROM table
GROUP BY col1;

Without this particular behavior you would need to sub-query.

From a layman's perspective the reason why you cannot use non-aggregates
outside of GROUP BY it that it is ambiguous as to what value to output; with
an uncorrelated function call that is not the case.

David J.