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.