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

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!