[hibernate-issues] [Hibernate-JIRA] Commented: (HHH-1400) Using formula-based property causes invalid SQL code for children "subselect" query
kishorekpotta (JIRA)
noreply at atlassian.com
Fri Dec 5 10:54:15 EST 2008
[ http://opensource.atlassian.com/projects/hibernate/browse/HHH-1400?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=31894#action_31894 ]
kishorekpotta commented on HHH-1400:
------------------------------------
select territoryp0_.TERR_PRICE_CAT_ID as TERR12_1_, territoryp0_.TERRITORY_ID as TERRITORY1_1_, from ETRANS.DMD_TERR_PRICE_CATEGORY_DIM territoryp0_ where territoryp0_.TERR_PRICE_CAT_ID in (select this_.TERR_PRICE_CAT_ID from dmd.dmd_territory_cluster dtc where dtc.PARENT_TERRITORY_ID = territory2_.TERRITORY_ID and dtc.CHILD_TERRITORY_ID = 1) as formula11_0_ from ETRANS.DMD_TERRITORY_PRICE_CATEGORY this_ left outer join DMD.DMD_Territory territory2_ on this_.TERRITORY_ID=territory2_.TERRITORY_ID where this_.PRICE_CATEGORY_ID=? and this_.TERRITORY_ID<>? )
23:39:01,807 WARN [JDBCExceptionReporter] SQL Error: 933, SQLState: 42000
23:39:01,807 ERROR [JDBCExceptionReporter] ORA-00933: SQL command not properly ended
Below is my forumla query.
formula=(select distinct 'Y' from dmd.dmd_territory_cluster dtc
where dtc.PARENT_TERRITORY_ID = TERRITORY_ID
and dtc.CHILD_TERRITORY_ID = 1)"
> Using formula-based property causes invalid SQL code for children "subselect" query
> -----------------------------------------------------------------------------------
>
> Key: HHH-1400
> URL: http://opensource.atlassian.com/projects/hibernate/browse/HHH-1400
> Project: Hibernate Core
> Issue Type: Bug
> Components: core
> Affects Versions: 3.1 rc3
> Environment: Hibernate 3.0.5, Hibernate 3.1rc3
> Oracle 8i
> Reporter: Chris Rogers
> Attachments: SubselectFetch.java, subselectformula.zip, SubselectFormulaBug.zip
>
>
> I have simple one-to-many relationship, mapped as set:
> <class name="Parent">
> <set name="children" lazy="false" fetch="subselect">
> <key column="PARENT_OID"/>
> <one-to-many class="Child"/>
> </set>
> </class>
> this works fine, constructing subselect SQL which looks like (e.g. for HQL query "from Parent"):
> select <child fields> from <child table> where child.PARENT_OID in (select this_.OID from PARENT this_)
> (simplified)
> However, when adding a formula-based property into Parent:
> <property name="myFormulaField" formula="(complex_select )"/>
> Now SQL becomes:
> select <child fields> from <child table> where PARENT_OID in (complex_select) as formula0_1_, <some parent fields> from PARENT this_)
> This SQL fails because of incorrect grammar (it also seems that backet is missing).
> This is something weird, because subselect fetching only needs Parent's identity column, not any other properties. And I don't think it should be affected by Parent's formula-based properties.
> I can provide more details if necessary, I stripped out all extra mapping/SQL stuff because it seems to be irrelevant here.
> This bug appeared in 3.0.5 later I've downloaded 3.1rc3 and it also fails.
--
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
More information about the hibernate-issues
mailing list