Strange primary key constraint influence to grouping

From: Gražvydas Valeika <gvaleika(at)gmail(dot)com>
To: pgsql-hackers(at)postgresql(dot)org
Subject: Strange primary key constraint influence to grouping
Date: 2012-01-18 23:25:02
Message-ID: CAO6eJZo_D20Tmxa1e1DEbc49atkiiXT3oR-kDHjhui2YKecQrA@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

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

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Andreas Karlsson 2012-01-18 23:41:41 Re: Strange primary key constraint influence to grouping
Previous Message Robert Haas 2012-01-18 23:17:59 Re: how to create a non-inherited CHECK constraint in CREATE TABLE