Re: join group by etc

Regina
  Thank you that returned the result that the scripts expected.
   But naturally broke it for mysql. Oh well.

Tom
  To make matters worse I had 'simplified' it a bit.

I'm trying to get TikiWiki working nicely with postgres. So far I have
basic stuff working now I need to try and get the rest working.
Unfortunately my chances of them implementing the changes are slim when
it breaks their preferred db.


Tom Lane wrote:
> 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
>