Re: [HACKERS] SQL compatibility reminder: MySQL vs PostgreSQL
От | Paragon Corporation |
---|---|
Тема | Re: [HACKERS] SQL compatibility reminder: MySQL vs PostgreSQL |
Дата | |
Msg-id | 674F31537E484E08869F9EAD82FD065D@J обсуждение исходный текст |
Ответ на | Re: [HACKERS] SQL compatibility reminder: MySQL vs PostgreSQL (Josh Berkus <josh@agliodbs.com>) |
Список | pgsql-advocacy |
FWIW I've heard this complaint from MySQL users switching to PostgreSQL and SQL Server before. Even heard it from FoxPro users which also seems to have this non-standard behavior of group by. A simple DISTINCT ON doesn't quite do it for them. I think you need to wrap it in a subselect to get the equivalent meaning. If I am not mistaken in MySQL when you have a construct such as SELECT u.username, n.subject,n.note, n.post_date FROM users As u INNER JOIN notes As n ON u.user_id = n.user_id GROUP BY u.username ORDER BY n.post_date DESC It essentially means return the username, subject and note of the last note for each user and sort by the last post date The problem they say with DISTINCT ON is that it forces sorting by the key first, which is not what they want. So the equivalent I think would be SELECT * FROM (SELECT DISTINCT ON(u.username) u.username, n.subject, n.note, n.post_date FROM users As u INNER JOIN notes As n ON u.user_id = n.user_id ORDER BY u.username, n.post_date DESC) As foo ORDER BY foo.post_date DESC Hope that helps, Regina -----Original Message----- From: pgsql-advocacy-owner@postgresql.org [mailto:pgsql-advocacy-owner@postgresql.org] On Behalf Of Josh Berkus Sent: Sunday, March 07, 2010 6:05 PM To: Joshua Waihi Cc: Mark Kirkwood; francois.perou@free.fr; Craig Ringer; Andrew Dunstan; PostgreSQL Advocacy; dpage@pgadmin.org Subject: Re: [pgsql-advocacy] [HACKERS] SQL compatibility reminder: MySQL vs PostgreSQL Joshua, I've moved this discussion to pgsql-advocacy where it belongs. > Aside from Drupal core, its too soon to know if there will be problems > with D7 contrib but there are a few major bugs about other D6 contrib > modules. The biggest one, which I think need movement to get fixed is > in the Views module. The Views module has a bit of a hard time trying > to please both databases and its surrounding the use of DISTINCT and > restricting duplicate results. They've opted for a solution that > really hits hard on PostgreSQL's performance. Bascially, when a > DISTINCT clause is used, all other fields being selected get a custom > functional called FIRST rapped around them: SELECT DISTINCT(nid), > FIRST(title), FIRST(body), ..... The function merely returns the first > value when two values are present for that row. This is the alternate > instead of grouping by each field. Its stupid and needs to be fixed. > The issue is > here: http://drupal.org/node/460838 What's the obstacle to fixing it? It seems like just changing to SELECT DISTINCT ON ... would do the trick, and would allow cutting code. --Josh Berkus -- Sent via pgsql-advocacy mailing list (pgsql-advocacy@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-advocacy
В списке pgsql-advocacy по дате отправления: