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

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}