jpa query with join, causes sql exception

[wegmueller]

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

[Mike Kienenberger]

I think it's this issue:

https://bugs.eclipse.org/bugs/show_bug.cgi?id=259384


On Thu, Aug 13, 2009 at 3:10 AM, wegmueller<wegmueller@...> wrote:

>
> 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.
>
> _______________________________________________
> eclipselink-users mailing list
> eclipselink-users@...
> https://dev.eclipse.org/mailman/listinfo/eclipselink-users
>

_______________________________________________
eclipselink-users mailing list
eclipselink-users@...
https://dev.eclipse.org/mailman/listinfo/eclipselink-users

[wegmueller]

that's it, thank you for the link!

Regards,
Max