[hibernate-issues] [Hibernate-JIRA] Commented: (HHH-3637) Union in where doesn't works for Sybase, need to evict test case

[Gail Badner (JIRA)]

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

Gail Badner commented on HHH-3637:
----------------------------------

Hi Juca,

I came across http://infocenter.sybase.com/help/topic/com.sybase.infocenter.dc32300.1502/html/sqlug/sqlug426.htm?resultof=%22%75%6e%69%6f%6e%22%20%22%64%65%72%69%76%65%64%22%20%22%64%65%72%69%76%22%20%22%74%61%62%6c%65%22%20%22%74%61%62%6c%22%20

which says:

"A union clause is allowed in a subquery inside a derived table expression. The following example uses a union clause in a subquery within a SQL derived table to list the titles of books sold at stores listed in the sales and sales_east tables:

select title_id from salesdetail
   where stor_id in
      (select stor_id from
         (select stor_id from sales
          union
          select stor_id from sales_east)
       dt_stores) "

It should be possible to create a sybase-specific filter for this test. 

> Union in where doesn't works for Sybase, need to evict test case
> ----------------------------------------------------------------
>
>                 Key: HHH-3637
>                 URL: http://opensource.atlassian.com/projects/hibernate/browse/HHH-3637
>             Project: Hibernate Core
>          Issue Type: Improvement
>          Components: testsuite
>         Environment: Sybase ASE 15.0.2
>            Reporter: Juraci Paixao Krohling
>            Assignee: Juraci Paixao Krohling
>         Attachments: DynamicFilterTest.java.diff, SupportsUnionInSubquery.diff
>
>
> It seems that Sybase doesn't supports UNION inside WHERE clauses, causing the test DynamicFilterTest#testSqlSyntaxOfFiltersWithUnions to fail. The scenario for this test case is not described in the documentation, but I would expect it to work:
> http://infocenter.sybase.com/help/index.jsp?topic=/com.sybase.infocenter.dc32300.1502/html/sqlug/X86286.htm
> The query is:
>     select
>         category0_.CAT_ID as CAT1_6_,
>         category0_.name as name6_,
>         category0_.eff_start_dt as eff3_6_,
>         category0_.eff_end_dt as eff4_6_ 
>     from
>         CATEGORY category0_ 
>     where
>         'abc' in (
>             select
>                 d.reg 
>             from
>                 department d 
>             where
>                 (
>                     d.dept_id=123
>                 ) 
>             union
>             select
>                 p.name 
>             from
>                 sales_person p 
> 		)
> 		
> If executed only the queries from inside the where clause (the two selects with union), it works as expected. Also, if removed the union and one of the queries inside the where, it works as expected. So, the problem happens only when using union inside a where clause.
> For now, I'd suggest to evict this test for Sybase. 

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