Hi All, If group by clause has primary key, the targetlist may have columns which are not part of the aggregate and not part of group by clause. The relevant commit is e49ae8d3bc588294d07ce1a1272b31718cfca5ef and relevant mail thread has subject Functional dependencies and GROUP BY.
As a result, for following set of commands, the last SELECT statement does not throw error. CREATE TEMP TABLE products (product_id int, name text, price numeric); CREATE TEMP TABLE sales (product_id int, units int); ALTER TABLE products ADD PRIMARY KEY (product_id); SELECT product_id, p.name, (sum(s.units) * p.price) AS sales FROM products p LEFT JOIN sales s USING (product_id) GROUP BY product_id; But, if I rewrite the query using views as follows create view sel_product as SELECT p.product_id, p.name, p.price FROM products p; create view sel_sales as SELECT s.units, s.product_id FROM ONLY sales s; SELECT p.product_id, p.name, (sum(s.units) * p.price) FROM sel_product p LEFT JOIN sel_sales s using(product_id) GROUP BY p.product_id; The last SELECT statement gives error ERROR: column "p.name" must appear in the GROUP BY clause or be used in an aggregate function LINE 1: SELECT p.product_id, p.name, (sum(s.units) * p.price) FROM s... The reason being, it doesn't look into the subqueries (in FROM clause) to infer that p.product_id is essentially product.product_id which is a primary key. Attached find a crude patch to infer the same by traversing subqueries. As I said the patch is crude and needs a better shape. If community is willing to accept the extension, I can work on it further. -- Best Wishes, Ashutosh Bapat EntepriseDB Corporation The Postgres Database Company
gb_subquery_pk.patch
Description: Binary data
-- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
