pgsql: Recognize functional dependency on primary keys.

Log Message:
-----------
Recognize functional dependency on primary keys. This allows a table's
other columns to be referenced without listing them in GROUP BY, so long as
the primary key column(s) are listed in GROUP BY.

Eventually we should also allow functional dependency on a UNIQUE constraint
when the columns are marked NOT NULL, but that has to wait until NOT NULL
constraints are represented in pg_constraint, because we need to have
pg_constraint OIDs for all the conditions needed to ensure functional
dependency.

Peter Eisentraut, reviewed by Alex Hunsaker and Tom Lane

Modified Files:
--------------
pgsql/doc/src/sgml:
queries.sgml (r1.58 -> r1.59)
(http://anoncvs.postgresql.org/cvsweb.cgi/pgsql/doc/src/sgml/queries.sgml?r1=1.58&r2=1.59)
pgsql/doc/src/sgml/ref:
select.sgml (r1.131 -> r1.132)
(http://anoncvs.postgresql.org/cvsweb.cgi/pgsql/doc/src/sgml/ref/select.sgml?r1=1.131&r2=1.132)
pgsql/src/backend/catalog:
dependency.c (r1.96 -> r1.97)
(http://anoncvs.postgresql.org/cvsweb.cgi/pgsql/src/backend/catalog/dependency.c?r1=1.96&r2=1.97)
pg_constraint.c (r1.54 -> r1.55)
(http://anoncvs.postgresql.org/cvsweb.cgi/pgsql/src/backend/catalog/pg_constraint.c?r1=1.54&r2=1.55)
pgsql/src/backend/nodes:
copyfuncs.c (r1.466 -> r1.467)
(http://anoncvs.postgresql.org/cvsweb.cgi/pgsql/src/backend/nodes/copyfuncs.c?r1=1.466&r2=1.467)
equalfuncs.c (r1.386 -> r1.387)
(http://anoncvs.postgresql.org/cvsweb.cgi/pgsql/src/backend/nodes/equalfuncs.c?r1=1.386&r2=1.387)
outfuncs.c (r1.387 -> r1.388)
(http://anoncvs.postgresql.org/cvsweb.cgi/pgsql/src/backend/nodes/outfuncs.c?r1=1.387&r2=1.388)
readfuncs.c (r1.232 -> r1.233)
(http://anoncvs.postgresql.org/cvsweb.cgi/pgsql/src/backend/nodes/readfuncs.c?r1=1.232&r2=1.233)
pgsql/src/backend/parser:
parse_agg.c (r1.93 -> r1.94)
(http://anoncvs.postgresql.org/cvsweb.cgi/pgsql/src/backend/parser/parse_agg.c?r1=1.93&r2=1.94)
pgsql/src/include/catalog:
catversion.h (r1.590 -> r1.591)
(http://anoncvs.postgresql.org/cvsweb.cgi/pgsql/src/include/catalog/catversion.h?r1=1.590&r2=1.591)
pg_constraint.h (r1.40 -> r1.41)
(http://anoncvs.postgresql.org/cvsweb.cgi/pgsql/src/include/catalog/pg_constraint.h?r1=1.40&r2=1.41)
pgsql/src/include/nodes:
parsenodes.h (r1.433 -> r1.434)
(http://anoncvs.postgresql.org/cvsweb.cgi/pgsql/src/include/nodes/parsenodes.h?r1=1.433&r2=1.434)
pgsql/src/test/regress:
parallel_schedule (r1.61 -> r1.62)
(http://anoncvs.postgresql.org/cvsweb.cgi/pgsql/src/test/regress/parallel_schedule?r1=1.61&r2=1.62)
serial_schedule (r1.56 -> r1.57)
(http://anoncvs.postgresql.org/cvsweb.cgi/pgsql/src/test/regress/serial_schedule?r1=1.56&r2=1.57)

Added Files:
-----------
pgsql/src/test/regress/expected:
functional_deps.out (r1.1)
(http://anoncvs.postgresql.org/cvsweb.cgi/pgsql/src/test/regress/expected/functional_deps.out?rev=1.1&content-type=text/x-cvsweb-markup)
pgsql/src/test/regress/sql:
functional_deps.sql (r1.1)
(http://anoncvs.postgresql.org/cvsweb.cgi/pgsql/src/test/regress/sql/functional_deps.sql?rev=1.1&content-type=text/x-cvsweb-markup)

Tom Lane wrote:
> Log Message:
> -----------
> Recognize functional dependency on primary keys. This allows a table's
> other columns to be referenced without listing them in GROUP BY, so long as
> the primary key column(s) are listed in GROUP BY.
>
> Eventually we should also allow functional dependency on a UNIQUE constraint
> when the columns are marked NOT NULL, but that has to wait until NOT NULL
> constraints are represented in pg_constraint, because we need to have
> pg_constraint OIDs for all the conditions needed to ensure functional
> dependency.
>
> Peter Eisentraut, reviewed by Alex Hunsaker and Tom Lane

Because of this commit, I am removing this "we do not want" TODO item:

{{TodoItem
|Indeterminate behavior for the GROUP BY clause (not wanted)
|At least one other database product allows specification of a subset of
the result columns which GROUP BY would need to be able to provide
predictable results; the server is free to return any value from the
group. This is not viewed as a desirable feature.
* [http://archives.postgresql.org/pgsql-hackers/2010-03/msg00297.php
<nowiki>Re: SQL compatibility reminder: MySQL vs PostgreSQL</nowiki>]
}}

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.

--
Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ It's impossible for everything to be true. +

Bruce,

* Bruce Momjian (bruce(at)momjian(dot)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. Not sure what value there is in removing it as a "feature we're
not going to implement but realize others have"?

Thanks,

Stephen

Stephen Frost wrote:
-- Start of PGP signed section.
> Bruce,
>
> * Bruce Momjian (bruce(at)momjian(dot)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. Not sure what value there is in removing it as a "feature we're
> not going to implement but realize others have"?

Well, as worded, it says we have to group by everything, which is not
true in 9.1, so I figured let's see what feedback we get and we can add
a new one if we want, but our old argument is invalid, since we did
implement part of what we said we wouldn't. ;-)

--
Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ It's impossible for everything to be true. +

Bruce Momjian <bruce(at)momjian(dot)us> writes:
> Well, as worded, it says we have to group by everything, which is not
> true in 9.1, so I figured let's see what feedback we get and we can add
> a new one if we want, but our old argument is invalid, since we did
> implement part of what we said we wouldn't. ;-)

Uh, no. What we said we wouldn't implement is "Indeterminate behavior
for the GROUP BY clause". We haven't implemented any part of that.

regards, tom lane

Stephen Frost <sfrost(at)snowman(dot)net> writes:
> * Bruce Momjian (bruce(at)momjian(dot)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.

regards, tom lane

Tom Lane wrote:
> Stephen Frost <sfrost(at)snowman(dot)net> writes:
> > * Bruce Momjian (bruce(at)momjian(dot)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(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ It's impossible for everything to be true. +

Bruce Momjian <bruce(at)momjian(dot)us> writes:
> Tom Lane wrote:
>> 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.

No doubt, but the TODO entry you removed is still 100% accurately
worded, and what's more the archive entry it links to clearly describes
exactly the point at issue, namely that grouping by a PK *isn't*
indeterminate. You were wrong to remove it.

regards, tom lane

Tom Lane wrote:
> Bruce Momjian <bruce(at)momjian(dot)us> writes:
> > Tom Lane wrote:
> >> 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.
>
> No doubt, but the TODO entry you removed is still 100% accurately
> worded, and what's more the archive entry it links to clearly describes
> exactly the point at issue, namely that grouping by a PK *isn't*
> indeterminate. You were wrong to remove it.

OK, I put it back, but I still feel we might not need it anymore.

--
Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ It's impossible for everything to be true. +

Bruce Momjian <bruce(at)momjian(dot)us> writes:
> Tom Lane wrote:
>> No doubt, but the TODO entry you removed is still 100% accurately
>> worded, and what's more the archive entry it links to clearly describes
>> exactly the point at issue, namely that grouping by a PK *isn't*
>> indeterminate. You were wrong to remove it.

> OK, I put it back, but I still feel we might not need it anymore.

Even if you're willing to believe that the questions will stop once
we have this feature, that won't happen for more than a year.

regards, tom lane

Tom Lane wrote:
> Bruce Momjian <bruce(at)momjian(dot)us> writes:
> > Tom Lane wrote:
> >> No doubt, but the TODO entry you removed is still 100% accurately
> >> worded, and what's more the archive entry it links to clearly describes
> >> exactly the point at issue, namely that grouping by a PK *isn't*
> >> indeterminate. You were wrong to remove it.
>
> > OK, I put it back, but I still feel we might not need it anymore.
>
> Even if you're willing to believe that the questions will stop once
> we have this feature, that won't happen for more than a year.

OK, I updated the TODO text with:

PostgreSQL 9.1 will allow result columns that are not referenced by
GROUP BY if a primary key for the same table is referenced in GROUP BY.

Hopefully we can reevaluate this for 9.2. This is an unusual case
because it is a not-wanted TODO entry (which always come across as
harsh), and we didn't complete it (so we can't mark it as done).

--
Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ It's impossible for everything to be true. +

Tom Lane wrote:
> Bruce Momjian <bruce(at)momjian(dot)us> writes:
>
>> OK, I put it back, but I still feel we might not need it anymore.
>>
>
> Even if you're willing to believe that the questions will stop once
> we have this feature, that won't happen for more than a year.
>

As a general comment on this, I've gotten two rounds of complaints about
MySQL migrations bit by this problem in the last year, and I found it
handy to point them to the FAQ entry. Even if one of the forms starts
to work in 9.1 eventually, I'd like to see a comment about this issue
hang around somewhere for future reference. Note that in both cases the
whole operation involved was rather brain dead and returning silly
indeterminate results in MySQL, but they didn't realize it. No
objections to the PostgreSQL "limitation" once they understood it was
fixing a subtle bug in the original too.

I was thinking of adding this one as an example for my next MySQL vs.
PostgreSQL paper update, it's a great example of the focus on
correctness differences between the two databases.

--
Greg Smith 2ndQuadrant US Baltimore, MD
PostgreSQL Training, Services and Support
greg(at)2ndQuadrant(dot)com www.2ndQuadrant.us