[hibernate-issues] [Hibernate-JIRA] Commented: (HHH-5072) Invalid SQL produced when using FetchMode.SUBSELECT with SQLServerDialect

[Brian R. Jackson (JIRA)]

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

Brian R. Jackson commented on HHH-5072:
---------------------------------------

I experienced this too and looked into the code to see if I could implement a dialect-specific fix.  The root of the cause is that SQL Server (as of 2008) doesn't fully support row value constructor syntax (read more here http://www.sqlmag.com/article/sql-server/Row-Value-Constructor.aspx), and Hibernate doesn't generate correlated subqueries. If you could get Hibernate (see org.hibernate.loader.collection.CollectionJoinWalker) to generate correlated subqueries using WHERE EXISTS, you could do an efficient subselect in SQL Server.

> Invalid SQL produced when using FetchMode.SUBSELECT with SQLServerDialect
> -------------------------------------------------------------------------
>
>                 Key: HHH-5072
>                 URL: http://opensource.atlassian.com/projects/hibernate/browse/HHH-5072
>             Project: Hibernate Core
>          Issue Type: Bug
>          Components: core
>    Affects Versions: 3.5.0-Final
>         Environment: Hibernate core 3.5, JPA 2.0, SQL Server 2005, JDK 1.6
>            Reporter: kabram
>            Priority: Blocker
>
> I have a parent entity with child entities being loaded using SubSelect and eager fetching. I execute a query to retrieve all parents and get the following query which SQL Server 2005 does not support:
> select
> 	childtab0_.CONFIG_KEY as CONFIG5_23_1_, 
> 	childtab0_.QUALIFIER as QUALIFIER23_1_, 
> 	childtab0_.CONFIG_VALUE_ID as CONFIG1_1_, 
> 	childtab0_.CONFIG_VALUE_ID as CONFIG1_24_0_, 
> 	childtab0_.CONFIG_KEY as CONFIG5_24_0_, 
> 	childtab0_.QUALIFIER as QUALIFIER24_0_, 
> 	childtab0_.CONFIG_VALUE as CONFIG2_24_0_, 
> 	childtab0_.IS_ENCRYPTED as IS3_24_0_, 
> 	childtab0_.LIST_ORDER as LIST4_24_0_ 
> from
> 	TEST_CHILD_TABLE childtab0_
> where 
> 	(childtab0_.CONFIG_KEY, childtab0_.QUALIFIER) in 
> 	(select parenttab0_.CONFIG_KEY, parenttab0_.QUALIFIER 
> 	from RSS_NEO_CANYON_CONFIG_KEY parenttab0_ )
> order by 
> 	childtab0_.LIST_ORDER asc
> SQL Server does not like the tuple in the where clause.

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