Skip site navigation (1) Skip section navigation (2)

Re: [HACKERS] SQL compatibility reminder: MySQL vs PostgreSQL

From: Joshua Waihi <josh(at)catalyst(dot)net(dot)nz>
To: Josh Berkus <josh(at)agliodbs(dot)com>
Cc: Mark Kirkwood <mark(dot)kirkwood(at)catalyst(dot)net(dot)nz>, francois(dot)perou(at)free(dot)fr, Craig Ringer <craig(at)postnewspapers(dot)com(dot)au>, Andrew Dunstan <andrew(at)dunslane(dot)net>, PostgreSQL Advocacy <pgsql-advocacy(at)postgresql(dot)org>, dpage(at)pgadmin(dot)org
Subject: Re: [HACKERS] SQL compatibility reminder: MySQL vs PostgreSQL
Date: 2010-03-08 00:34:21
Message-ID: 4B94460D.8090401@catalyst.net.nz (view raw or flat)
Thread:
Lists: pgsql-advocacypgsql-hackers
Josh Berkus wrote:
> 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
>   
Views is trying to make MySQL and PostgreSQL compliant code. So the goal 
is to make MySQL and PostgreSQL return the same results from the same 
views configuration. http://drupal.org/node/607418#comment-2677468 is a 
good example of what Views is doing to PostgreSQL to make it work.

Be wary that often the SQL isn't the best written SQL because it is 
dynamically generated and designed to be altered by the views framework.

So your saying SELECT DISTINCT(field) is different to SELECT DISTINCT ON 
field?

Apparently the problem is that when MySQL uses group by clauses like 
pgsql does, it doesn't return the same results (I'm not 100% sure on 
this) and thats the reason why Views doesn't want to use group by clauses.

-- 
Joshua Waihi // Drupal Architect

Catalyst.Net Limited,
Level 6, Catalyst House,
150 Willis Street, Wellington.
P.O.Box 11053, Manners Street,
Wellington 6142

DDI: +64 4 803 2228
Mob: +64 21 979 794
Tel: +64 4 499 2267
Web: http://catalyst.net.nz


In response to

pgsql-hackers by date

Next:From: Bruce MomjianDate: 2010-03-08 00:47:42
Subject: Re: Re: incorrect exit code from psql with single transaction + violation of deferred FK constraint
Previous:From: Bruce MomjianDate: 2010-03-08 00:30:18
Subject: Re: Re: incorrect exit code from psql with single transaction + violation of deferred FK constraint

pgsql-advocacy by date

Next:From: Paragon CorporationDate: 2010-03-08 04:16:50
Subject: Re: [HACKERS] SQL compatibility reminder: MySQL vs PostgreSQL
Previous:From: Josh BerkusDate: 2010-03-07 23:05:29
Subject: Re: [HACKERS] SQL compatibility reminder: MySQL vs PostgreSQL

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group