[hibernate-issues] [Hibernate-JIRA] Commented: (HHH-3690) Sybase - FumTest.testCompositeKeyPathExpressions() fails due to subquery with more than one column in select list

[Strong Liu (JIRA)]

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

Strong Liu commented on HHH-3690:
---------------------------------

Gail, I'd think we can resolve this as won't fix, WDYT?
----------------------------------------------------------------------
Sybase does not support multiple columns in the exists clause. There are alternative ways to write this query in HQL.
"from Fum fum1 where exists elements(fum1.friends)"
Which gets translated to 
select
        fum0_.string_ as string1_38_,
        fum0_.short_ as short2_38_,
        fum0_.date_ as date3_38_,
        fum0_.vid as vid38_,
        fum0_.fum as fum38_,
        fum0_.TString as TString38_,
        fum0_.fo_string as fo7_38_,
        fum0_.fo_short as fo8_38_,
        fum0_.fo_date as fo9_38_,
        fum0_.count_ as count10_38_ 
    from
        Fum fum0_ 
    where
        (
            exists(
                select
                    friends1_.string_,
                    friends1_.short_,
                    friends1_.date_ 
                from
                    Fum friends1_ 
                where
                    fum0_.string_=friends1_.fr_string_ 
                    and fum0_.short_=friends1_.fr_short_ 
                    and fum0_.date_=friends1_.fr_date_
            )
        )
Other way to write this query is use native query as, this will work in Sybase.
select
        fum0_.string_ as string1_38_,
        fum0_.short_ as short2_38_,
        fum0_.date_ as date3_38_,
        fum0_.vid as vid38_,
        fum0_.fum as fum38_,
        fum0_.TString as TString38_,
        fum0_.fo_string as fo7_38_,
        fum0_.fo_short as fo8_38_,
        fum0_.fo_date as fo9_38_,
        fum0_.count_ as count10_38_ 
    from
        Fum fum0_ 
    where
        (
            exists(
                select *
                    
                from
                    Fum friends1_ 
                where
                    fum0_.string_=friends1_.fr_string_ 
                    and fum0_.short_=friends1_.fr_short_ 
                    and fum0_.date_=friends1_.fr_date_
            )
        )

> Sybase - FumTest.testCompositeKeyPathExpressions() fails due to subquery with more than one column in select list
> -----------------------------------------------------------------------------------------------------------------
>
>                 Key: HHH-3690
>                 URL: http://opensource.atlassian.com/projects/hibernate/browse/HHH-3690
>             Project: Hibernate Core
>          Issue Type: Bug
>          Components: core
>            Reporter: Gail Badner
>
> Sybase only allows one column in a subquery select list. The only exception is that a subquery in an EXISTS() predicate can have '*' as the select list. This  is documented at:
> http://infocenter.sybase.com/help/topic/com.sybase.infocenter.dc32300.1502/html/sqlug/sqlug181.htm
> FumTest.testCompositeKeyPathExpressions() fails with "SybSQLException: Incorrect syntax near ','."
> The HQL that fails is: "from Fum fum1 where exists elements(fum1.friends)". It fails becuase the elements have a composite ID.
> The fix described in HHH-3032 should fix this issue since the subquery is used in an EXISTS() predicate..

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