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

[Kyrill Alyoshin (JIRA)]

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