[hibernate-issues] [Hibernate-JIRA] Commented: (HHH-5168) DB2Dialect generates CROSS JOINs which aren't supported

[Shay Sofer (JIRA)]

    [ http://opensource.atlassian.com/projects/hibernate/browse/HHH-5168?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=37626#action_37626 ] 

Shay Sofer commented on HHH-5168:
---------------------------------

It wasn't fixed in 3.5.3 - In the meanwhile, the solution Grzegorz offered works. I sure hope it will be fixed soon...

> DB2Dialect generates CROSS JOINs which aren't supported
> -------------------------------------------------------
>
>                 Key: HHH-5168
>                 URL: http://opensource.atlassian.com/projects/hibernate/browse/HHH-5168
>             Project: Hibernate Core
>          Issue Type: Bug
>    Affects Versions: 3.5.0-Final, 3.5.1
>         Environment: Hibernate  3.5.0-Final, using hibernate-jpa-2.0-api-1.0.0.Final.jar, DB2/NT SQL09013
>            Reporter: Grzegorz Olędzki
>         Attachments: console.log, HHH5168Project.zip
>
>
> When executing a simple JPQL query:
> {code}
> SELECT st FROM ScheduledTask st WHERE (NOT st.status = 'COMPLETED') AND st.active = TRUE AND st.task.group = :group
> {code}
> Hibernate generates the following SQL query:
> {code}
> select scheduledt0_.QSID as col_0_0_ 
> from SCHSCHEDTASK_QS scheduledt0_ cross join SCHTASK_QT task1_ 
> where scheduledt0_.QTID=task1_.QTID and scheduledt0_.QSSTATUS<>'COMPLETED' and scheduledt0_.QSACTIVE=1 and task1_.QTGROUP=?
> {code}
> which crashes with:
> {code}
> 2010-04-28 10:14:18,551 WARN  [org.hibernate.util.JDBCExceptionReporter]/[org.hibernate.util.JDBCExceptionReporter] 
> SQL Error: -104, SQLState: 42601
> 2010-04-28 10:14:18,552 ERROR [org.hibernate.util.JDBCExceptionReporter]/[org.hibernate.util.JDBCExceptionReporter] 
> DB2 SQL error: SQLCODE: -104, SQLSTATE: 42601, SQLERRMC: cross;TASK_QS scheduledt0_;<space>
> 2010-04-28 10:14:18,552 WARN  [org.hibernate.util.JDBCExceptionReporter]/[org.hibernate.util.JDBCExceptionReporter] 
> SQL Error: -727, SQLState: 56098
> 2010-04-28 10:14:18,553 ERROR [org.hibernate.util.JDBCExceptionReporter]/[org.hibernate.util.JDBCExceptionReporter] 
> DB2 SQL error: SQLCODE: -727, SQLSTATE: 56098, SQLERRMC: 2;-104;42601;cross|TASK_QS scheduledt0_|<space>
> 2010-04-28 10:14:18,554 WARN  [org.hibernate.util.JDBCExceptionReporter]/[org.hibernate.util.JDBCExceptionReporter] 
> SQL Error: -727, SQLState: 56098
> 2010-04-28 10:14:18,554 ERROR [org.hibernate.util.JDBCExceptionReporter]/[org.hibernate.util.JDBCExceptionReporter] 
> DB2 SQL error: SQLCODE: -727, SQLSTATE: 56098, SQLERRMC: 2;-104;42601;cross|TASK_QS scheduledt0_|<space>
> {code}
> The very same JPQL query works on MySQL database (the actual SQL query uses CROSS JOIN too).
> When trying to run the same SQL on DB2 manually in a database client the error message is the same (-104, 42601). 
> A subtle change in the query, i.e. replacing CROSS JOIN with a comma (,) seems to fix the problem - both manually and using Hibernate-based application.
> Using the following DB2Dialect seems to help:
> {code}
> public class DB2Dialect extends org.hibernate.dialect.DB2Dialect {
> 	@Override
> 	public String getCrossJoinSeparator() {
> 		return ", ";
> 	}
> }
> {code}

-- 
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators: http://opensource.atlassian.com/projects/hibernate/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira