Обсуждение: Re: [COMMITTERS] pgsql: Recognize functional dependency on primary keys.
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
specificationof a subset ofthe result columns which GROUP BY would need to be able to providepredictable results; the
serveris free to return any value from thegroup. 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. +
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
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. +
Bruce Momjian <bruce@momjian.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@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.
regards, tom lane
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. +
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.
regards, tom lane
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. +
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.
regards, tom lane
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 byGROUP BY if a primary key for the same table is referencedin 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. +
Tom Lane wrote: <blockquote cite="mid:7305.1281792213@sss.pgh.pa.us" type="cite"><pre wrap="">Bruce Momjian <a class="moz-txt-link-rfc2396E"href="mailto:bruce@momjian.us"><bruce@momjian.us></a> writes: </pre><blockquote type="cite"><prewrap="">OK, I put it back, but I still feel we might not need it anymore. </pre></blockquote><pre wrap=""> 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. </pre></blockquote><br /> As a general comment on this, I'vegotten two rounds of complaints about MySQL migrations bit by this problem in the last year, and I found it handy topoint 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 aboutthis issue hang around somewhere for future reference. Note that in both cases the whole operation involved was ratherbrain 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.<br /><br /> I was thinking ofadding this one as an example for my next MySQL vs. PostgreSQL paper update, it's a great example of the focus on correctnessdifferences between the two databases.<br /><br /><pre class="moz-signature" cols="72">-- Greg Smith 2ndQuadrant US Baltimore, MD PostgreSQL Training, Services and Support <a class="moz-txt-link-abbreviated" href="mailto:greg@2ndQuadrant.com">greg@2ndQuadrant.com</a> <a class="moz-txt-link-abbreviated"href="http://www.2ndQuadrant.us">www.2ndQuadrant.us</a> </pre>