[eclipselink-users] jpa query with join, causes sql exception

[wegmueller <wegmueller@xxxxxxxxx>]

Hi,

currently we do have a problem, how JPA queries over 2 tables are translated
to sql.
The joined attributes (Person.nID = PersonCEQ.personId) of this 2 objects to
not have defined a reference in the entity.

Query query = entityManager.createQuery("SELECT p" + "  FROM Person p," + "
PersonCEQ pq" + " WHERE pq.nID = p.personId" + " ORDER BY pq.dfCQ DESC");

This query is translated into:
SELECT
	PERSON.PERSON_ID AS PERSON_ID1,
	PERSON.PERSON_KEY AS PERSON_KEY2,
	PERSON.SCREEN_NAME AS SCREEN_NAME3,
	PERSON.SURNAME AS SURNAME4,
	PERSON.GIVEN_NAME AS GIVEN_NAME5,
	PERSON.HOMEPAGE AS HOMEPAGE6,
	PERSON.CQ AS CQ7,
	PERSON.CREATED_ON AS CREATED_ON8,
	PERSON.PICTURE_URL AS PICTURE_URL9,
	PERSON.PEQ AS PEQ10,
	PERSON.E_MAIL AS E_MAIL11,
	PERSON.UPDATED_ON AS UPDATED_ON12,
	PERSON.PQ AS PQ13,
	PERSON.COUNTRY_CODE AS COUNTRY_CODE14
FROM
	PERSON_CEQ t0,
	PERSON t1
WHERE (
	t0.PERSON_ID = t1.PERSON_ID
)
ORDER BY
	t0.PERSON_CQ DESC
LIMIT
	?, ?

Now, the problem is that EclipseLink _aliases_ the table *PERSON* as *t1*.
However, in the field list, the table is referred to by its full name,
e.g. *PERSON*.PERSON_ID instead of *t1*.PERSON_ID. Whilst some databases
may support this type of SQL, MySQL does not. In MySQL, a table is either
referred to by its full name and not aliased, _or_ it is aliased and
referred to by its alias only. However, it is not allowed to alias a table
_and_ refer to it by its full name. 

Environment: glassfish v3 (nightly build), MySQL 5.1.34, Java 6
(updating the eclipselink librarary to the latest version did not help)

Is this a known problem? Any ideas are welcome.

Thanks,
Max
-- 
View this message in context: http://www.nabble.com/jpa-query-with-join%2C-causes-sql-exception-tp24919786p24919786.html
Sent from the EclipseLink - Users mailing list archive at Nabble.com.