Re: join group by etc

Peter Jackson <tasmaniac@iprimus.com.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