[hibernate-issues] [Hibernate-JIRA] Commented: (HHH-3227) Oracle's connect by syntax is mishandled when used with Filter

[Robert Raksi (JIRA)]

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

Robert Raksi commented on HHH-3227:
-----------------------------------

I just ran into this bug, any progress with it? It's two years old now and marked as major.

> Oracle's connect by syntax is mishandled when used with Filter
> --------------------------------------------------------------
>
>                 Key: HHH-3227
>                 URL: http://opensource.atlassian.com/projects/hibernate/browse/HHH-3227
>             Project: Hibernate Core
>          Issue Type: Bug
>          Components: query-sql
>    Affects Versions: 3.2.5
>         Environment: Hibernaet 3.2.5.GA, Oracle 10g.
>            Reporter: Kyrill Alyoshin
>
> Basically, I have a filter definition on a class (via annotation), here it is:
> @Filter(name = "limitInvoicesByAccountHolderHierarchy",
>         condition = "CHRG_ACCT_ID in (" +
>                 "select ca1.chrg_acct_id from cv_chrg_acct ca1 " +
>                 "inner join cv_org_unit og1 on ca1.acct_hldr_id = og1.org_unit_id " +
>                 "where og1.org_unit_id in (" +
>                 "select og2.org_unit_id from cv_org_unit og2 " +
>                 "start with og2.org_unit_id = :root " +
>                 " connect by prior og2.org_unit_id = og2.hier_par_org_unit_id" +
>                 ')' +
>                 ')'
> It contains Oracle's "connect by" clause. Here is the SQL statement that Hibernate generates when trying to query invoices with the filter enabled:
> Hibernate: 
>     /* 
> from
>     Invoice */ select
>         invoice0_.inv_id as inv1_31_,
>         ...... 
>     from
>         CV_INV invoice0_ 
>     where
>         invoice0_.CHRG_ACCT_ID in (
>             select
>                 ca1.chrg_acct_id 
>             from
>                 cv_chrg_acct ca1 
>             inner join
>                 cv_org_unit og1 
>                     on ca1.acct_hldr_id = og1.org_unit_id 
>             where
>                 og1.org_unit_id in (
>                     select
>                         og2.org_unit_id 
>                     from
>                         cv_org_unit og2 invoice0_.start invoice0_.with og2.org_unit_id = ?  invoice0_.connect 
>                     by
>                         invoice0_.prior og2.org_unit_id = og2.hier_par_org_unit_id
>                 )
>             )
> Even though the documentation states that the condition of the filter is pure SQL (which is great!), it still does not pass SQL verbatim but is actually trying to pre-process the following words: start, with, connect, prior.
> Is there an easy work around to make Oracle10gDialect recognize these words?
> Thanks a lot for looking into this!

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