5:29 a.m.
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....
-
For more information on JIRA, see: http://www.atlassian.com/software/jira
5:18 p.m.
New subject: [Hibernate-JIRA] Commented: (HHH-3227) Oracle's connect by syntax is mishandled when used with Filter
[
http://opensource.atlassian.com/projects/hibernate/browse/HHH-3227?page=c...
]
bugmenot commented on HHH-3227:
-------------------------------
I also have this issue. Hibernate should recognise 'connect by' as a reserved word
and not prefix them with the table alias.
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....
-
For more information on JIRA, see: http://www.atlassian.com/software/jira
10:01 p.m.
New subject: [Hibernate-JIRA] Commented: (HHH-3227) Oracle's connect by syntax is mishandled when used with Filter
[
http://opensource.atlassian.com/projects/hibernate/browse/HHH-3227?page=c...
]
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....
-
For more information on JIRA, see: http://www.atlassian.com/software/jira
7:06 a.m.
New subject: [Hibernate-JIRA] Commented: (HHH-3227) Oracle's connect by syntax is mishandled when used with Filter
[
http://opensource.atlassian.com/projects/hibernate/browse/HHH-3227?page=c...
]
Sean Molloy commented on HHH-3227:
----------------------------------
I have been able to solve this by creating a custom Dialect that extends the Oracle10g
dialect, and registering the CONNECT BY PRIOR START WITH words as keywords. Here is my
class:
public class Oracle10gDialectFixed extends Oracle10gDialect {
public Oracle10gDialectFixed() {
super();
// workaround for
http://opensource.atlassian.com/projects/hibernate/browse/HHH-2304
registerHibernateType(Types.CHAR,1, Hibernate.CHARACTER.getName());
registerHibernateType(Types.CHAR,255, Hibernate.STRING.getName());
// workaround for
http://opensource.atlassian.com/projects/hibernate/browse/HHH-3227
registerKeyword("CONNECT");
registerKeyword("PRIOR");
registerKeyword("START");
registerKeyword("WITH");
registerKeyword("connect");
registerKeyword("prior");
registerKeyword("start");
registerKeyword("with");
}
}
And then I use this dialect in my persistence.xml and it works!
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....
-
For more information on JIRA, see: http://www.atlassian.com/software/jira
4976
days inactive
5874
days old
hibernate-issues@lists.jboss.org
3 comments
4 participants
participants (4)
-
bugmenot (JIRA) -
Kyrill Alyoshin (JIRA)
-
Robert Raksi (JIRA)
-
Sean Molloy (JIRA)