Eclipse Community Forums
Forum Search:

Search      Help    Register    Login    Home
Home » Eclipse Projects » EclipseLink » coalesce in where clause results in timeout(worry not, solved)
coalesce in where clause results in timeout [message #524550] Thu, 01 April 2010 11:42 Go to next message
Vasco Figueira is currently offline Vasco FigueiraFriend
Messages: 6
Registered: April 2010
Junior Member
Dear all,

We have a query along the lines of:

@NamedQuery(name = "Organizacao.findByConcelhoId", query =
    "SELECT co.contactoOrganizacaoPK.fkOrganizacao " +
    "FROM ContactoOrganizacao co, Contacto c, " +
    "Morada m, CodigoPostal cp " +
    "WHERE co.contactoOrganizacaoPK.fkContacto = c.id AND c.fkMorada = m.id " +
    "AND (m.fkCodigoPostal is null OR m.fkCodigoPostal = cp.id) " +
    "AND coalesce(m.fkConcelho, cp.fkConcelho) = :fkConcelho")


(Have to match given Id with either fkConcelho on Morada or fkConcelho on CodigoPostal "outer joined" with Morada).

The NamedQuery gets translated to the SQL:

SELECT t0.FK_ORGANIZACAO 
FROM CODIGO_POSTAL t3, MORADA t2, CONTACTO t1, CONTACTO_ORGANIZACAO t0 
WHERE ((
  ((t0.FK_CONTACTO = t1.ID) AND (t1.FK_MORADA = t2.ID)) 
  AND ((t2.FK_CODIGO_POSTAL IS NULL) OR (t2.FK_CODIGO_POSTAL = t3.ID))
  ) AND (COALESCE(t2.FK_CONCELHO,t3.FK_CONCELHO ) = '227'))


...that runs fine (as SQL, using an SQL client). However, querying via eclipselink takes too long and breaks with a timeout.

[EL Fine]: 2010-04-01 12:26:59.986--ServerSession(11738152)--Connection(64055960)--Thread(Thread[[ACTIVE] ExecuteThread: '19' for queue: 'weblogic.kernel.Default (self-tuning)',5,Pooled Threads])--SELECT t0.FK_ORGANIZACAO FROM CODIGO_POSTAL t3, MORADA t2, CONTACTO t1, CONTACTO_ORGANIZACAO t0 WHERE ((((t0.FK_CONTACTO = t1.ID) AND (t1.FK_MORADA = t2.ID)) AND ((t2.FK_CODIGO_POSTAL IS NULL) OR (t2.FK_CODIGO_POSTAL = t3.ID))) AND (COALESCE(t2.FK_CONCELHO,t3.FK_CONCELHO ) = ?))
	bind => [227]
[EL Finer]: 2010-04-01 12:27:48.825--UnitOfWork(64048396)--Thread(Thread[[ACTIVE] ExecuteThread: '0' for queue: 'weblogic.kernel.Default (self-tuning)',5,Pooled Threads])--TX afterCompletion callback, status=ROLLEDBACK
[EL Finer]: 2010-04-01 12:27:48.825--UnitOfWork(64048396)--Thread(Thread[[ACTIVE] ExecuteThread: '0' for queue: 'weblogic.kernel.Default (self-tuning)',5,Pooled Threads])--release unit of work
[EL Finer]: 2010-04-01 12:27:48.825--ClientSession(63235017)--Thread(Thread[[ACTIVE] ExecuteThread: '0' for queue: 'weblogic.kernel.Default (self-tuning)',5,Pooled Threads])--client released
javax.ejb.EJBException: Transaction Rolledback.: weblogic.transaction.internal.TimedOutException: Transaction timed out after 33 seconds 
BEA1-000687F032CA884F477B
	at weblogic.transaction.internal.ServerTransactionImpl.wakeUp(ServerTransactionImpl.java:1734)
	at weblogic.transaction.internal.ServerTransactionManagerImpl.processTimedOutTransactions(ServerTransactionManagerImpl.java:1607)
	at weblogic.transaction.internal.TransactionManagerImpl.wakeUp(TransactionManagerImpl.java:1879)
	at weblogic.transaction.internal.ServerTransactionManagerImpl.wakeUp(ServerTransactionManagerImpl.java:1517)
	at weblogic.transaction.internal.WLSTimer.timerExpired(WLSTimer.java:35)
	at weblogic.timers.internal.TimerImpl.run(TimerImpl.java:273)
	at weblogic.work.SelfTuningWorkManagerImpl$WorkAdapterImpl.run(SelfTuningWorkManagerImpl.java:516)
	at weblogic.work.ExecuteThread.execute(ExecuteThread.java:201)
	at weblogic.work.ExecuteThread.run(ExecuteThread.java:173)
; nested exception is: weblogic.transaction.internal.TimedOutException: Transaction timed out after 33 seconds 


The reason we don't simply use LEFT OUTER JOIN on the "optional" Freguesia table is mainly historical as we decided not to use relations annotations by fear of previously encountered problems with older versions of JPA providers.

But it's supposed to work like this, right? Should I file a bug? Any ideas?

Thanks in advance.

[Updated on: Thu, 01 April 2010 11:56]

Report message to a moderator

Re: coalesce in where clause results in timeout [message #524552 is a reply to message #524550] Thu, 01 April 2010 11:53 Go to previous message
Vasco Figueira is currently offline Vasco FigueiraFriend
Messages: 6
Registered: April 2010
Junior Member
Gosh, I need a vacation.

Offcourse it times out. It's not emulating a left outer join, and forces eclipselink to fetch all rows from CodigoPostal (which is huge).

Kindly forget this, will you?
Previous Topic:Issue with EclipseLink and Felix OSGi
Next Topic:PreUpdate not working
Goto Forum:
  


Current Time: Thu Nov 27 07:09:41 GMT 2014

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

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