Re: [eclipselink-users] Duplicate alias in generated SQL

[James Sutherland <jamesssss@xxxxxxxxx>]

This is a bug in EclipseLink 2.0, and has been fixed in trunk/2.1.

A workaround in 2.0 would be to not use the maxRows in the query.


Michael Simons wrote:
> 
> Hello,
> 
> As to be seen in the stack trace below, EL uses the same alias (t1) twice.
> Does anybody know whether this is fixed in EL 2.0.2?
> Any hints on what's going wrong here or what might be the reason would be
> appreciated.
> Might this be a bug in EL 2.0.0?
> 
> Kind Regards, Michael
> 
> Caused by: Exception [EclipseLink-4002] (Eclipse Persistence Services -
> 2.0.0.v20091127-r5931):
> org.eclipse.persistence.exceptions.DatabaseException
> Internal Exception:
> com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Not unique
> table/alias: 't1'
> Error Code: 1066
> Call: SELECT t0.ordr_id AS ordr_id1, t0.dtype AS dtype2, ..., t3.ordr_id
> AS ordr_id69,
> t4.ordr_id AS ordr_id70, t1.link_id AS link_id71
> FROM ordr t0
> LEFT OUTER JOIN customer_order t1 ON (t1.ordr_id = t0.ordr_id)
> LEFT OUTER JOIN via_order t2 ON (t2.ordr_id = t0.ordr_id)
> LEFT OUTER JOIN preparation_order t3 ON (t3.ordr_id = t0.ordr_id)
> LEFT OUTER JOIN storage_supply_order t4 ON (t4.ordr_id = t0.ordr_id)
> LEFT OUTER JOIN customer_order t1 ON (t1.ordr_id = t0.ordr_id),
> customer_order t5
> WHERE (t0.ext_id = ?) LIMIT ?, ?
> 	bind => [FS_AT_TEST_O1, 0, 1]
> Query: ReadAllQuery(referenceClass=Order sql="SELECT t0.ordr_id AS
> ordr_id1, t0.dtype AS
> dtype2,... AS ordr_id69, t4.ordr_id AS ordr_id70, t1.link_id AS link_id71
> FROM ordr t0
> LEFT OUTER JOIN customer_order t1 ON (t1.ordr_id = t0.ordr_id)
> LEFT OUTER JOIN via_order t2 ON (t2.ordr_id = t0.ordr_id)
> LEFT OUTER JOIN preparation_order t3 ON (t3.ordr_id = t0.ordr_id)
> LEFT OUTER JOIN storage_supply_order t4 ON (t4.ordr_id = t0.ordr_id)
> LEFT OUTER JOIN customer_order t1 ON (t1.ordr_id = t0.ordr_id),
> customer_order t5
> WHERE (t0.ext_id = ?) LIMIT ?, ?")
> 	at
> org.eclipse.persistence.exceptions.DatabaseException.sqlException(DatabaseException.java:333)
> 	at
> org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.basicExecuteCall(DatabaseAccessor.java:686)
> 	at
> org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.executeCall(DatabaseAccessor.java:529)
> 	at
> org.eclipse.persistence.internal.sessions.AbstractSession.executeCall(AbstractSession.java:914)
> 	at
> org.eclipse.persistence.internal.queries.DatasourceCallQueryMechanism.executeCall(DatasourceCallQueryMechanism.java:205)
> 	at
> org.eclipse.persistence.internal.queries.DatasourceCallQueryMechanism.executeCall(DatasourceCallQueryMechanism.java:191)
> 	at
> org.eclipse.persistence.internal.queries.DatasourceCallQueryMechanism.executeSelectCall(DatasourceCallQueryMechanism.java:262)
> 	at
> org.eclipse.persistence.internal.queries.DatasourceCallQueryMechanism.selectAllRows(DatasourceCallQueryMechanism.java:618)
> 	at
> org.eclipse.persistence.internal.queries.ExpressionQueryMechanism.selectAllRowsFromTable(ExpressionQueryMechanism.java:2537)
> 	at
> org.eclipse.persistence.internal.queries.ExpressionQueryMechanism.selectAllRows(ExpressionQueryMechanism.java:2496)
> 	at
> org.eclipse.persistence.queries.ReadAllQuery.executeObjectLevelReadQuery(ReadAllQuery.java:455)
> 	at
> org.eclipse.persistence.queries.ObjectLevelReadQuery.executeDatabaseQuery(ObjectLevelReadQuery.java:997)
> 	at
> org.eclipse.persistence.queries.DatabaseQuery.execute(DatabaseQuery.java:675)
> 	at
> org.eclipse.persistence.queries.ObjectLevelReadQuery.execute(ObjectLevelReadQuery.java:958)
> 	at
> org.eclipse.persistence.queries.ReadAllQuery.execute(ReadAllQuery.java:432)
> 	at
> org.eclipse.persistence.queries.ObjectLevelReadQuery.executeInUnitOfWork(ObjectLevelReadQuery.java:1021)
> 	at
> org.eclipse.persistence.internal.sessions.UnitOfWorkImpl.internalExecuteQuery(UnitOfWorkImpl.java:2863)
> 	at
> org.eclipse.persistence.internal.sessions.AbstractSession.executeQuery(AbstractSession.java:1225)
> 	at
> org.eclipse.persistence.internal.sessions.AbstractSession.executeQuery(AbstractSession.java:1207)
> 	at
> org.eclipse.persistence.internal.sessions.AbstractSession.executeQuery(AbstractSession.java:1181)
> 	at
> org.eclipse.persistence.internal.jpa.EJBQueryImpl.executeReadQuery(EJBQueryImpl.java:453)
> 	at
> org.eclipse.persistence.internal.jpa.EJBQueryImpl.getResultList(EJBQueryImpl.java:669)
> 	at
> de.optitool.biz.AbstractManager.retrieveSingle(AbstractManager.java:174)
> 
> 
> 


-----
http://wiki.eclipse.org/User:James.sutherland.oracle.com James Sutherland 
http://www.eclipse.org/eclipselink/
 EclipseLink ,  http://www.oracle.com/technology/products/ias/toplink/
TopLink 
Wiki:  http://wiki.eclipse.org/EclipseLink EclipseLink , 
http://wiki.oracle.com/page/TopLink TopLink 
Forums:  http://forums.oracle.com/forums/forum.jspa?forumID=48 TopLink , 
http://www.nabble.com/EclipseLink-f26430.html EclipseLink 
Book:  http://en.wikibooks.org/wiki/Java_Persistence Java Persistence 
-- 
View this message in context: http://old.nabble.com/Duplicate-alias-in-generated-SQL-tp28594453p28599694.html
Sent from the EclipseLink - Users mailing list archive at Nabble.com.