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

[Grzegorz Olędzki (JIRA)]

     [ http://opensource.atlassian.com/projects/hibernate/browse/HHH-5168?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]

Grzegorz Olędzki updated HHH-5168:
----------------------------------

    Attachment: HHH5168Project.zip

There you go. I've created a standalone JPA Eclipse project. There are two entities defined: Book and Category (many-to-one). A simple query is executed to filter books based on some information from their categories. The query fails on DB2 using standard dialect. 

The zip file contains a Eclipse project. Run the hhh5168.HHH5168 to see the error message. The zip file doesn't contain all the required libraries.

Expected message is:
- either a proper result (if you have some entries in the database)
- or a {{javax.persistence.NoResultException: No entity found for query}} exception if your db is empty.

> 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
>         Attachments: 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