Re: Re: [COMMITTERS] pgsql: Recognize functional dependency on primary keys.

Tom Lane wrote:
> Stephen Frost <sfrost@snowman.net> writes:
> > * Bruce Momjian (bruce@momjian.us) wrote:
> >> My guess is our new 9.1 functionality will reduce requests for this
> >> features, so we can just not list it anymore.  If they still ask, we can
> >> re-added this not-wanted item.
> 
> > I'm not so sure...  I expect we're going to get people complaining that
> > it doesn't work the way MySQL's does now instead of complaints we don't
> > have it.
> 
> Yes.  Please compare PG HEAD with mysql 5.1.48 (ok, it's last month's
> version):
> 
> regression=# create table t1 (f1 int primary key, f2 int, f3 int);
> NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "t1_pkey" for table "t1"
> CREATE TABLE
> regression=# select * from t1 group by f1;
>  f1 | f2 | f3 
> ----+----+----
> (0 rows)
> 
> regression=# select * from t1 group by f2;
> ERROR:  column "t1.f1" must appear in the GROUP BY clause or be used in an aggregate function
> LINE 1: select * from t1 group by f2;
>                ^
> 
> 
> 
> mysql> create table t1 (f1 int primary key, f2 int, f3 int);
> Query OK, 0 rows affected (0.07 sec)
> 
> mysql> select * from t1 group by f1;
> Empty set (0.00 sec)
> 
> mysql> select * from t1 group by f2;
> Empty set (0.00 sec)
> 
> 
> I'm not sure whether there is any clear rule for what rows you get when
> grouping by a non-PK column in mysql, but it'll let you do it.

I understand this.  The issue is how many people who complained about
our GROUP BY behavior were grouping by something that was a primary key,
and how many were not using a primary key?  The former will no longer
complain.

--  Bruce Momjian  <bruce@momjian.us>        http://momjian.us EnterpriseDB
http://enterprisedb.com
 + It's impossible for everything to be true. +