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@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com

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

Search Discussions

  • Stephen Frost at Aug 14, 2010 at 2:28 am
    Bruce,

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

    Thanks,

    Stephen
  • Bruce Momjian at Aug 14, 2010 at 2:54 am
    Stephen Frost wrote:
    -- Start of PGP signed section.
    Bruce,

    * 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. 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@momjian.us> http://momjian.us
    EnterpriseDB http://enterprisedb.com

    + It's impossible for everything to be true. +
  • Tom Lane at Aug 14, 2010 at 3:10 am

    Bruce Momjian 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
  • Tom Lane at Aug 14, 2010 at 3:22 am

    Stephen Frost 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.

    regards, tom lane
  • Bruce Momjian at Aug 14, 2010 at 3:29 am

    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. +
  • Tom Lane at Aug 14, 2010 at 3:38 am

    Bruce Momjian 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
  • Bruce Momjian at Aug 14, 2010 at 1:02 pm

    Tom Lane wrote:
    Bruce Momjian <bruce@momjian.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@momjian.us> http://momjian.us
    EnterpriseDB http://enterprisedb.com

    + It's impossible for everything to be true. +
  • Tom Lane at Aug 14, 2010 at 1:23 pm

    Bruce Momjian 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
  • Bruce Momjian at Aug 14, 2010 at 1:48 pm

    Tom Lane wrote:
    Bruce Momjian <bruce@momjian.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@momjian.us> http://momjian.us
    EnterpriseDB http://enterprisedb.com

    + It's impossible for everything to be true. +
  • Greg Smith at Aug 16, 2010 at 7:29 am

    Tom Lane wrote:
    Bruce Momjian <bruce@momjian.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@2ndQuadrant.com www.2ndQuadrant.us

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouppgsql-hackers @
categoriespostgresql
postedAug 14, '10 at 2:25a
activeAug 16, '10 at 7:29a
posts11
users4
websitepostgresql.org...
irc#postgresql

People

Translate

site design / logo © 2021 Grokbase