Thread: Strange primary key constraint influence to grouping
Hi,
accidentally found a our sql typo, which runs without errors on pg 9.1, but raises error on 9.0. It seems to me, that 9.0 behaviour was correct.
Reproducing case:
create table aaa(id integer NOT NULL, something double precision, constraint pk_aaa primary key (id));
insert into aaa values (1, 1), (2, null);
select
id,
case
when something is not null then 'something'
else 'something is null'
end as status
from
aaa
group by id;
drop table aaa cascade;
In PG 9.0 this script complains that: column "aaa.something" must appear in the GROUP BY clause or be used in an aggregate function. Sorry, don't have 9.0 at my hands, but error message is similar to quoted.
Same error is raised in 9.1 when ', constraint pk_aaa primary key (id)' is commented out.
With PK constraint in place, this script runs happily, without complaints.
Version with observerd behaviour: "PostgreSQL 9.1.1, compiled by Visual C++ build 1500, 32-bit"
Best regards,
Grazvydas
On 2012-01-19 00:25, Gražvydas Valeika wrote: > In PG 9.0 this script complains that: column "aaa.something" must appear > in the GROUP BY clause or be used in an aggregate function. Sorry, don't > have 9.0 at my hands, but error message is similar to quoted. > Same error is raised in 9.1 when ', constraint pk_aaa primary key (id)' > is commented out. > > With PK constraint in place, this script runs happily, without complaints. Hi, This is because PostgreSQL 9.1 added the feature of simple checking of functional dependencies for GROUP BY. The manual of 9.1 explains quite well when PostgreSQL considers there to be a functional dependency. "When GROUP BY is present, it is not valid for the SELECT list expressions to refer to ungrouped columns except within aggregate functions or if the ungrouped column is functionally dependent on the grouped columns, since there would otherwise be more than one possible value to return for an ungrouped column. A functional dependency exists if the grouped columns (or a subset thereof) are the primary key of the table containing the ungrouped column." http://www.postgresql.org/docs/9.1/interactive/sql-select.html#SQL-GROUPBY Best regards, Andreas -- Andreas Karlsson
This is because PostgreSQL 9.1 added the feature of simple checking of functional dependencies for GROUP BY. The manual of 9.1 explains quite well when PostgreSQL considers there to be a functional dependency.
"When GROUP BY is present, it is not valid for the SELECT list expressions to refer to ungrouped columns except within aggregate functions or if the ungrouped column is functionally dependent on the grouped columns, since there would otherwise be more than one possible value to return for an ungrouped column. A functional dependency exists if the grouped columns (or a subset thereof) are the primary key of the table containing the ungrouped column."
I completely agree with documentation.
But my case shows that "not valid" expression which refers to column which is ungrouped still works in 9.1.
G.