[hibernate-issues] [Hibernate-JIRA] Commented: (HHH-4907) "id in ..." with EmbeddedId and criteria API fails on ms sql server

[Steve Ebersole (JIRA)]

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

Steve Ebersole commented on HHH-4907:
-------------------------------------

We really need to decide if we are going to support this.  Its not trivial.  If the underlying database does not support tuples (row value constructor) as part of an IN predicate, we need to seriously rearrange this SQL.  For example, if a database does not like tuple syntax
{code:sql} 
...
where (a,b) in ( (1,2), (3,4) )
{code} 

really needs to become
{code:sql} 
...
where ( (a=1 AND b=2) OR ( (a=3 AND b=4) )
{code} 

Note, specifically, that the following is not at all the same semantic
{code:sql} 
...
where a in (1,3) AND b in (2,4)
{code} 

So this is *possible* for parameter lists and explicit lists.  It is completely unreasonable for subqueries.

> "id in ..." with EmbeddedId and criteria API fails on ms sql server
> -------------------------------------------------------------------
>
>                 Key: HHH-4907
>                 URL: http://opensource.atlassian.com/projects/hibernate/browse/HHH-4907
>             Project: Hibernate Core
>          Issue Type: Bug
>          Components: core
>    Affects Versions: 3.5.0-CR-1
>         Environment: ms sql server, sybase, db2
>            Reporter: Strong Liu
>            Assignee: Strong Liu
>   Original Estimate: 0 minutes
>  Remaining Estimate: 0 minutes
>
> [http://hudson.qa.jboss.com/hudson/view/Hibernate%20Community/job/hibernate-core-trunk/database=mssql2005,jdk=java15_default,label=hibernate/lastCompletedBuild/testReport/org.hibernate.test.annotations.cid/CompositeIdTest/testQueryInAndComposite/ ]
> org.hibernate.exception.SQLGrammarException: could not execute query
> 	at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:91)
> 	at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:66)
> 	at org.hibernate.loader.Loader.doList(Loader.java:2276)
> 	at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2151)
> 	at org.hibernate.loader.Loader.list(Loader.java:2146)
> 	at org.hibernate.loader.criteria.CriteriaLoader.list(CriteriaLoader.java:118)
> 	at org.hibernate.impl.SessionImpl.list(SessionImpl.java:1706)
> 	at org.hibernate.impl.CriteriaImpl.list(CriteriaImpl.java:347)
> 	at org.hibernate.test.annotations.cid.CompositeIdTest.testQueryInAndComposite(CompositeIdTest.java:306)
> Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: Incorrect syntax near ','.
> 	at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(SQLServerException.java:156)
> 	at com.microsoft.sqlserver.jdbc.SQLServerStatement.getNextResult(SQLServerStatement.java:1373)
> 	at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.doExecutePreparedStatement(SQLServerPreparedStatement.java:371)
> 	at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement$PrepStmtExecCmd.doExecute(SQLServerPreparedStatement.java:322)
> 	at com.microsoft.sqlserver.jdbc.TDSCommand.execute(IOBuffer.java:4003)
> 	at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(SQLServerConnection.java:1550)
> 	at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeCommand(SQLServerStatement.java:160)
> 	at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeStatement(SQLServerStatement.java:133)
> 	at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.executeQuery(SQLServerPreparedStatement.java:265)
> 	at org.hibernate.jdbc.AbstractBatcher.getResultSet(AbstractBatcher.java:208)
> 	at org.hibernate.loader.Loader.getResultSet(Loader.java:1832)
> 	at org.hibernate.loader.Loader.doQuery(Loader.java:719)
> 	at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:271)
> 	at org.hibernate.loader.Loader.doList(Loader.java:2273)
> 	... 32 more

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