Re: [HACKERS] Re: [COMMITTERS] pgsql: Recognize functional dependency on primary keys.
Tom Lane wrote: Bruce Momjian br...@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 g...@2ndquadrant.com www.2ndQuadrant.us
Re: [HACKERS] Re: [COMMITTERS] pgsql: Recognize functional dependency on primary keys.
Tom Lane wrote: Bruce Momjian br...@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 br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Re: [COMMITTERS] pgsql: Recognize functional dependency on primary keys.
Bruce Momjian br...@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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Re: [COMMITTERS] pgsql: Recognize functional dependency on primary keys.
Tom Lane wrote: Bruce Momjian br...@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 br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] 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 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
nowikiRe: 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 br...@momjian.ushttp://momjian.us
EnterpriseDB http://enterprisedb.com
+ It's impossible for everything to be true. +
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Re: [COMMITTERS] pgsql: Recognize functional dependency on primary keys.
Bruce, * Bruce Momjian (br...@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 signature.asc Description: Digital signature
Re: [HACKERS] Re: [COMMITTERS] pgsql: Recognize functional dependency on primary keys.
Stephen Frost wrote: -- Start of PGP signed section. Bruce, * Bruce Momjian (br...@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 br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Re: [COMMITTERS] pgsql: Recognize functional dependency on primary keys.
Bruce Momjian br...@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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Re: [COMMITTERS] pgsql: Recognize functional dependency on primary keys.
Stephen Frost sfr...@snowman.net writes: * Bruce Momjian (br...@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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Re: [COMMITTERS] pgsql: Recognize functional dependency on primary keys.
Tom Lane wrote: Stephen Frost sfr...@snowman.net writes: * Bruce Momjian (br...@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 br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Re: [COMMITTERS] pgsql: Recognize functional dependency on primary keys.
Bruce Momjian br...@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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
