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

[Grzegorz Olędzki (JIRA)]

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
         Environment: Hibernate  3.5.0-Final, using hibernate-jpa-2.0-api-1.0.0.Final.jar, DB2/NT SQL09013

            Reporter: Grzegorz Olędzki


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