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

Re: join group by etc

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Peter Jackson <tasmaniac(at)iprimus(dot)com(dot)au>
Cc: pgsql <pgsql-novice(at)postgresql(dot)org>
Subject: Re: join group by etc
Date: 2008-08-08 13:57:32
Message-ID: 8644.1218203852@sss.pgh.pa.us (view raw or flat)
Thread:
Lists: pgsql-novice
Peter Jackson <tasmaniac(at)iprimus(dot)com(dot)au> writes:
> mysql query

> SELECT T1.*, T2.ttC, T3.tthD, toD as sort FROM table_one T1 INNER JOIN 
> table_two T2 ON T1.iId = T2.iId INNER JOIN table_three T3 ON T3.fId = 
> T2.fId WHERE T1.tId = '9' and T1.toC = 'o' GROUP BY T1.iId ORDER BY sort 
> asc;

> Which in mysql returns 1 row but fails in pg due to the group by.

Egad.  *Which* row does it return, and why?  Anyone reading the SQL
spec would have to say that this query's behavior is undefined:
there are multiple t2 and t3 rows joining to each T1 row, hence
no principled way to decide which ttC and tthD values to output
for a given T1.iId value.

(One of mysql's more unlovely behaviors is their willingness to
return some randomly-chosen result for underspecified queries.)

			regards, tom lane

In response to

Responses

pgsql-novice by date

Next:From: Peter JacksonDate: 2008-08-08 14:32:22
Subject: Re: join group by etc
Previous:From: Brian HurtDate: 2008-08-08 13:54:07
Subject: Re: join group by etc

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