Обсуждение: Exception in Query when mixing explicit join and implicit join

Hallo,

in an existing application I got an exception and tracked it down to a
generated query from the Java OR mapping solution Hibernate
Actually I  was suprised that the query does not work and created a
simple use case to explain the problem (see and of this email).

The postgre SQL exception is:
ERROR:  relation a does not exist

It happens when a implicit join and a inner join is mixed. Do I have to
except this as a fact or is there a logical explication for this behaviour.

--
Best Regards / Viele Grüße

Sebastian Hennebrueder

----

http://www.laliluna.de

Tutorials for JSP, JavaServer Faces, Struts, Hibernate and EJB


-- 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


-- implicit join passing two tables works
select a.name as act_name
from
activity a,
localgroup lg,
sponsor spon

where spon.name='Jimmy Rich'
and
spon.fk_localgroup=lg.id
and lg.fk_activity=a.id

--
Best Regards / Viele Grüße

Sebastian Hennebrueder

----

http://www.laliluna.de

Tutorials for JSP, JavaServer Faces, Struts, Hibernate and EJB

Get support, education and consulting for these technologies - uncomplicated and cheap.

Sebastian Hennebrueder <usenet@laliluna.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

Tom Lane schrieb:

>Sebastian Hennebrueder <usenet@laliluna.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