Re: Exception in Query when mixing explicit join and implicit

Tom Lane schrieb:

>Sebastian Hennebrueder <usenet(at)laliluna(dot)de> writes:
>
>
>>-- the following query has a inner join and an implicit join and does
>>not work.
>>select a.name as act_name,
>>u.name as trainer
>>from
>>activity a,
>>localgroup lg,
>>sponsor spon
>>inner join tuser u on a.fk_trainer=u.id
>>where spon.name='Jimmy Rich'
>>and
>>spon.fk_localgroup=lg.id
>>and lg.fk_activity=a.id
>>
>>
>
>I think you were brought up on MySQL, which has only a vague
>relationship to actual SQL :-(. Per the SQL standard, what you
>wrote is equivalent to
>
> ... from
> ((activity a cross join localgroup lg)
> cross join
> (sponsor spon inner join tuser u on a.fk_trainer=u.id))
> where ...
>
>Notice the parenthesization. Table a is not part of the spon/u join
>and so the reference to it from the spon/u ON condition is invalid.
>
>MySQL, unfortunately, seems to render the above syntax as
>
> ... from
> (((activity a cross join localgroup lg)
> cross join sponsor spon)
> inner join tuser u on a.fk_trainer=u.id)
> where ...
>
>in which case the reference from the ON condition is valid. Basically
>they think that comma and JOIN in the FROM list have the same precedence
>and associate left-to-right ... but any casual glance at the spec's
>syntax rules shows this is wrong. JOIN is supposed to bind more tightly
>than comma.
>
> regards, tom lane
>
>
>
>
Thank you very much for the feedback.

The program I try to make runnable on PostgreSQL has MySQL as main
supported database. So you are right here.
Interesting IMHO:
Object Relational Mapping solutions like Hibernate are having their own
QL language which should make the db code portable across differnet dbs
But if you do not code your Hibernate QL queries properly this
intention of ORM might fail.

--
Best Regards / Viele Grüße

Sebastian Hennebrueder