Eclipse Community Forums
Forum Search:

Search      Help    Register    Login    Home
Home » Eclipse Projects » EclipseLink » EclipseLink - ordering of tables for join statement
EclipseLink - ordering of tables for join statement [message #479718] Wed, 12 August 2009 07:40 Go to next message
Ricky is currently offline Ricky
Messages: 204
Registered: July 2009
Senior Member
Hi all,

I have encountered a problem when using EclipseLink 1.1.1 to translate the
following JPA query into native query.

"SELECT e FROM Employee e, Department d WHERE d.mgrId = e.id and
d.mgrCountryId = e.countryId"

It was translated into the following query.

"SELECT el_employee.id, el_employee.name ... FROM el_department t0,
el_employee t1 WHERE ((t0.mgr_id = t1.id) AND (t0.mgr_country_id =
t1.country_id))"

Instead of using "t1" as prefixes in the SELECT clause, table name
"el_employee" was used as prefixes.

It is interesting that if I change the ordering of the tables (reverse the
order) in the FROM clause as follow,

"SELECT e FROM Department d, Employee e WHERE d.mgrId = e.id and
d.mgrCountryId = e.countryId"

The query was correctly translated.

"SELECT t0.id, t0.name ... FROM el_employee t0, el_department t1
WHERE((t1.mgr_id = t0.id) AND (t1.mgr_country_id = t0.country_id))"

The query was also correctly translated if the SELECT clause was changed
to

"SELECT e.id FROM Employee e, Department d WHERE d.mgrId = e.id and
d.mgrCountryId = e.countryId"

It was translated into,

"SELECT t0.id FROM el_employee t0, el_department t1 WHERE ((t1.mgr_id =
t0.id) AND (t1.mgr_country_id = t0.country_id))"

It seems that the problem only occurred when the ordering of the tables in
FROM clause is different from the ordering of the prefixes in the WHERE
clause (join condition). Is it a bug in EclipseLink or I have missed out
something?

Best Regards,
Ricky
Re: EclipseLink - ordering of tables for join statement [message #480797 is a reply to message #479718] Tue, 18 August 2009 14:06 Go to previous message
James Sutherland is currently offline James Sutherland
Messages: 1939
Registered: July 2009
Location: Ottawa, Canada
Senior Member

This is a bug, please log the issue.

James : Wiki : Book : Blog : Twitter
Previous Topic:ManyToOne only have the IDs in a collection not the whole object
Next Topic:loader constraint violation
Goto Forum:
  


Current Time: Tue Sep 30 22:05:48 GMT 2014

Powered by FUDForum. Page generated in 0.01605 seconds
.:: Contact :: Home ::.

Powered by: FUDforum 3.0.2.
Copyright ©2001-2010 FUDforum Bulletin Board Software