Some Eclipse Foundation services are deprecated, or will be soon. Please ensure you've read this important communication.

Bug 277682

Summary:	ReadAllQuery doesn't use the table alias in select
Product:	z_Archived	Reporter:	uygar yuzsuren <uygaryuzsuren>
Component:	Eclipselink	Assignee:	James Sutherland <jamesssss>
Status:	RESOLVED FIXED	QA Contact:
Severity:	blocker
Priority:	P2	CC:	eclipse_bugs, jamesssss, tom.ware
Version:	unspecified
Target Milestone:	---
Hardware:	PC
OS:	Mac OS X - Carbon (unsup.)
Whiteboard:

Description uygar yuzsuren

2009-05-25 08:44:30 EDT

I have just started to migrate my toplink-essentials jpa implementation to ecipselink jpa. When I run my application, it causes my postgresql database to throw the following exception:

Internal Exception: org.postgresql.util.PSQLException: ERROR: invalid reference to FROM-clause entry for table "gameincrements"

I copied the query to pgAdmin and ran it without eclipselink. The postgresql database still threw the same exception. The query generated by eclipselink was the below one:

'SELECT GAMEINCREMENTS.ID, GAMEINCREMENTS.ENDTYPE, GAMEINCREMENTS.TOTALWIN, GAMEINCREMENTS.STARTTIME, GAMEINCREMENTS.TOTALLOST, GAMEINCREMENTS.STATE, GAMEINCREMENTS.TOTALBET, GAMEINCREMENTS.GAMERCOUNT, GAMEINCREMENTS.ENDTIME, GAMEINCREMENTS.INCREMENTNO, GAMEINCREMENTS.BETCOUNT, GAMEINCREMENTS.WINNERNO, GAMEINCREMENTS.livegameid FROM LIVEGAME t3, LIVEGAME t2, GAMETABLE t1, GAMEINCREMENTS t0 WHERE (((t1.ID = 1) AND (t3.ID = t2.ID)) AND ((t1.ID = t2.GAMETABLE_ID) AND (t3.ID = t0.livegameid))) ORDER BY t0.ENDTIME DESC'

Afterwards, I changed GAMEINCREMENTS to t0 in select section and it started to work.

The problem here is that, in my NamedQueries I never use the alias t0, but I use as follows:

'select g from Gameincrements g, Livegame l where l.id=g.livegame.id and l.id=:liveGameId'

Eclipselink creates aliases such as t0, t1, ... But, as can be seen in the above query, it is not used in the select section of the query, causing the database not being able to identify their being the same table. On the other hand, in the NamedQuery it is a provided information, which is represented as the alias g in the above NamedQuery.

What should be the correct strategy would be to use the alias provided by the user whenever possible, or identify t0 as being the same table in the select section and create the query as:

'SELECT t0.ID, t0.ENDTYPE, t0. ...FROM TABLENAME t0 ....'

This situation occurs in ReadAllQuery, but it may be the case in other queries, too. So, this bug may apply to other select queries.


Thanks in advance.

Comment 1 Tom Ware

2009-05-27 11:53:57 EDT

If you have a mapping for a relationship between Gameincrements and Livegame, you should be able to work around this issue by changing your query to something like the following:

select g from Gameincrements g join g.livegame l where l.id=:liveGameId

Please update the bug with a code/xml that shows the key mappings in Gameincrements and Livegame.

Comment 2 uygar yuzsuren

2009-05-27 12:50:39 EDT

Okay, it is very nice to have a workaround. But in this case one will have to modify all of his queries:(

Anyways, if it is the right way of doing this in EJB QL, and it is going to work for any persistence implementation other than EclipseLink, then no problem. You know, one of the advantages of using specifications is their implementation-independence, isn't it?

But, if the specification allows such a usage (without explicit join), then I suggest that it should be implemented.

Thank you very much.

Comment 3 Tom Ware

2009-05-27 13:40:52 EDT

The suggestion above is the way you are expected to write this query in JPA.  Writing your query this way allows a more efficient build of the query itself.

Additionally, it is more flexible since if you change your mappings, you will not have to change your queries to reflect new join criteria.

Having said that, you are right, although it is not the recommended way to write this kind of query, the JPQL you have written is allowed by the spec and we should fix this issue.

If you are on one of our 1.x builds, you might want to try a nightly build from our 2.0 stream as there have been some fixes in that area in 2.0.

Setting target and priority.  Details of what this means can be found here.

http://wiki.eclipse.org/EclipseLink/Development/Bugs/Guidelines

Comment 4 Tom Ware

2009-05-28 15:51:21 EDT

Another workaround that may solve the issue is to alter the order of the items in the FROM clause:

'select g from Livegame l, Gameincrements g where l.id=g.livegame.id and
l.id=:liveGameId'

Comment 5 Tom Ware

2009-09-23 13:10:59 EDT

*** Bug 289677 has been marked as a duplicate of this bug. ***

Comment 6 Ernie Rael

2009-09-23 14:16:24 EDT

It is surprising to me that an SQL syntax error '(t0.ID = )' ((from the dup'd  Bug 289677)) is the same as this issue, but I'm sometimes easily surprised.

Comment 7 James Sutherland

2009-10-27 11:46:31 EDT

Cannot recreate in main, 2.0 (10/27/2009)

I assume also fixed in 1.2.

Comment 8 Eclipse Webmaster

2022-06-09 10:05:34 EDT

The Eclipselink project has moved to Github: https://github.com/eclipse-ee4j/eclipselink

Comment 9 Eclipse Webmaster

2022-06-09 10:09:06 EDT

The Eclipselink project has moved to Github: https://github.com/eclipse-ee4j/eclipselink