[nhibernate-issues] [JIRA] Updated: (NH-224) Reference Type OR Property conditions produce bad JOIN syntax for HQL Queries against parent Objects

[NHibernate J. <mik...@us...>]

     [ http://jira.nhibernate.org/browse/NH-224?page=all ]

Sergey Koshcheyev updated NH-224:
---------------------------------

    Fix Version: LATER
        Version: 1.2.0.Beta2

> Reference Type OR Property conditions produce bad JOIN syntax for HQL Queries against parent Objects
> ----------------------------------------------------------------------------------------------------
>
>          Key: NH-224
>          URL: http://jira.nhibernate.org/browse/NH-224
>      Project: NHibernate
>         Type: Improvement
>   Components: Core
>     Versions: 1.2.0.Beta2, alpha
>  Environment: Oracle + ODP.NET 10.1.0.3.0
> C# - Client/Server
>     Reporter: Jeff Mayeur
>     Assignee: Mike Doerfler
>     Priority: Minor
>      Fix For: LATER

>
> REF FORUM: http://sourceforge.net/forum/forum.php?thread_id=1232722&forum_id=252014
> Summary:  When using properies of Reference Type as part of an HQL query where the Child Ref Type Property is within the scope of an OR, the JOIN ID = ID is placed within the bounds of the SQL OR condition, producing fautly results.
> Additionally, it seems that an ORDER BY using the Child Ref Type properties produces invalid SQL, as the ORDER BY column is not included in the select statement. 
> From the forumn post:
> --- Let me preface this by saying, while I think of this aa a bug, I fully understand the point of view that "if you used it like it was meant to be used you wouldn't have this issue." 
>  
>  
> Problem:  
> For many-to-one joins, if an HQL contains an evaluation criteria for this Obj in an OR Expression the join is not properly restricted. Below is sample HQL, classes, hbm files, and the output SQL. [ note these are abstractions from real code, so forgive and typeos... trust me there really is an issue here. ] 
>  
> Of course the HQL could be written differently to avoid this issue, but we are implementing a "Query Tool" to allow users to define their own queries via a click and drag UI. Additionally the problem can be addressed by "pre-parsing" the query for issues like this, but at that point, why not go all the way to SQL. 
>  
> HQL:  
> SELECT item  
> FROM ParentObj AS item  
> WHERE ( 
> LOWER( item.SomeText ) = 'fooText'  
> and ( 
> item.SomeNumber = 55  
> or ( 
> LOWER( item.FirstLookup.Description ) = 'my firstDesc'  
> and ( 
> item.SomeNumber = 56  
> or item.ModelYear = 57) 
> ) 
> ) 
> ) order by item.Id asc 
>  
>  
> /*********** NHIBERNATE 6 *************************/ 
> select  
> parentob0_.MUID as MUID 
> , parentob0_.SOME_TEXT as SOME2_ 
> , parentob0_.SOME_NUMBER as SOME3_ 
> , parentob0_.OTHER_VALUE as OTHER4_ 
> , parentob0_.FIRST_LOOKUP_ID as FIRST5_ 
> , parentob0_.SECOND_LOOKUP_ID as SECOND6_ 
> , parentob0_.THIRD_LOOKUP_ID as THIRD7_ 
> FROM PARENT_OBJ parentob0_ 
> , LOOKUP_OBJ lookupobj2_  
> where ( 
> ( LOWER( parentob0_.SOME_TEXT ) = 'fooText' ) 
> and ( 
> ( parentob0_.SOME_NUMBER = 55 ) 
> or (  
> (  
> LOWER( lookupob2_.LOOKUP_DESC ) = 'my firstDesc'  
> and parentob0_.FIRST_LOOKUP_ID = lookupob2_.LOOKUP_ID 
> ) 
> and ( 
> ( parentob0_.SOME_NUMBER = 56 ) 
> or( parentob0_.SOME_NUMBER = 57 ) 
> ) 
> ) 
> ) 
> ) 
> order by parentob0_.MUID asc; 
> /*********** BETTER [ HAND BUILT ]*************************/ 
> select  
> parentob0_.MUID as MUID 
> , parentob0_.SOME_TEXT as SOME2_ 
> , parentob0_.SOME_NUMBER as SOME3_ 
> , parentob0_.OTHER_VALUE as OTHER4_ 
> , parentob0_.FIRST_LOOKUP_ID as FIRST5_ 
> , parentob0_.SECOND_LOOKUP_ID as SECOND6_ 
> , parentob0_.THIRD_LOOKUP_ID as THIRD7_ 
> FROM PARENT_OBJ parentob0_ 
> , LOOKUP_OBJ lookupobj2_  
> where ( 
> ( LOWER( parentob0_.SOME_TEXT ) = 'fooText' ) 
> and ( 
> ( parentob0_.SOME_NUMBER = 55 ) 
> or (  
> LOWER( lookupob2_.LOOKUP_DESC ) = 'my firstDesc'  
> and ( 
> ( parentob0_.SOME_NUMBER = 56 ) 
> or( parentob0_.SOME_NUMBER = 57 ) 
> ) 
> ) 
> ) 
> ) 
> AND parentob0_.FIRST_LOOKUP_ID = lookupob2_.LOOKUP_ID /* JOIN**/ 
> order by parentob0_.MUID asc; 
>  
>  
> CLASSES/HBM 
>  
> using System; 
>  
> namespace NHibernateIssue { 
> /// <summary> 
> /// Summary description for ParentObj. 
> /// </summary> 
> public class ParentObj { 
> private int id; 
> private string someText; 
> private int someNumber; 
> private string otherValue; 
>  
> private LookupObj firstLookup; 
> private LookupObj secondLookup; 
> private LookupObj thirdLookup; 
>  
> /// <summary> 
> /// Unique ID [ORA_DB SEQUENCE] 
> /// </summary> 
> public int Id { 
> get { return id; } 
> set { id = value; } 
> } 
>  
> /// <summary> 
> /// A text Value [ORA_DB VARCHAR2] 
> /// </summary> 
> public string SomeText { 
> get { return someText; } 
> set { someText = value; } 
> } 
>  
> /// <summary> 
> /// A number Value [ORA_DB NUMBER(38,0)] 
> /// </summary> 
> public int SomeNumber { 
> get { return someNumber; } 
> set { someNumber = value; } 
> } 
>  
> /// <summary> 
> /// Another text Value [ORA_DB VARCHAR2] 
> /// </summary> 
> public string OtherValue { 
> get { return otherValue; } 
> set { otherValue = value; } 
> } 
>  
> /// <summary> 
> /// A Lookup Item, ( DB table PARENT_OBJ is related to LOOKUP_OBJ by FIRST_LOOKUP_ID == LOOKUP_ID ) 
> /// </summary> 
> public LookupObj FirstLookup { 
> get { return firstLookup; } 
> set { firstLookup = value; } 
> } 
>  
> /// <summary> 
> /// A Lookup Item, ( DB table PARENT_OBJ is related to LOOKUP_OBJ by FIRST_LOOKUP_ID == LOOKUP_ID ) 
> /// </summary> 
> public LookupObj SecondLookup { 
> get { return secondLookup; } 
> set { secondLookup = value; } 
> } 
>  
> /// <summary> 
> /// A Lookup Item, ( DB table PARENT_OBJ is related to LOOKUP_OBJ by FIRST_LOOKUP_ID == LOOKUP_ID ) 
> /// </summary> 
> public LookupObj Thirdookup { 
> get { return thirdLookup; } 
> set { thirdLookup = value; } 
> } 
>  
>  
> public ParentObj() { 
> } 
>  
>  
> } 
> } 
>  
>  
>  
>  
> using System; 
>  
> namespace NHibernateIssue { 
> /// <summary> 
> /// Summary description for ChildObj. 
> /// </summary> 
> public class LookupObj { 
> private int lookupId; 
> private string description; 
>  
> /// <summary> 
> /// Unique "LOOKUP" ID 
> /// </summary> 
> public int LookupId { 
> get { return this.lookupId; } 
> set { this.lookupId = value; } 
> } 
>  
> /// <summary> 
> /// "LOOKUP" Description 
> /// </summary> 
> public string Description { 
> get { return this.description; } 
> set { this.description = value; } 
> } 
>  
> public LookupObj() { 
> } 
>  
> } 
> } 
>  
>  
>  
> <?xml version="1.0" encoding="utf-8" ?> 
> <hibernate-mapping xmlns="urn:nhibernate-mapping-2.0"> 
> <class name="NHibernateIssue.ParentObj, NHibernateIssue" table="PARENT_OBJ"> 
> <id name="Id" column="MUID" unsaved-value="0"> 
> <generator class="sequence"> 
> <param name="sequence">UID_SEQ</param> 
> </generator> 
> </id> 
> <property name="SomeText" column="SOME_TEXT" not-null="false" /> 
> <property name="SomeNumber" column="SOME_NUMBER" type="NHibernate.DomainModel.NHSpecific.NullInt32UserType, NHibernate.DomainModel" not-null="false" /> 
> <property name="OtherValue" column="OTHER_VALUE" not-null="false" /> 
> <many-to-one name="FirstLookup" class="NHibernateIssue.LookupObj, NHibernateIssue"  
> column="FIRST_LOOKUP_ID" cascade="none"/> 
> <many-to-one name="SecondLookup" class="NHibernateIssue.LookupObj, NHibernateIssue"  
> column="SECOND_LOOKUP_ID" cascade="none"/> 
> <many-to-one name="ThirdLookup" class="NHibernateIssue.LookupObj, NHibernateIssue"  
> column="THIRD_LOOKUP_ID" cascade="none"/> 
> </class> 
> </hibernate-mapping> 
>  
>  
>  
>  
> <?xml version="1.0" encoding="utf-8" ?>  
> <hibernate-mapping xmlns="urn:nhibernate-mapping-2.0"> 
> <class name="NHibernateIssue.LookupObj, NHibernateIssue" table="LOOKUP_OBJ"> 
> <id name="LookupId" column="LOOKUP_ID" unsaved-value="0"> 
> <generator class="sequence"> 
> <param name="sequence">LID</param> 
> </generator> 
> </id> 
> <property name="Description" column="LOOKUP_DESC" /> 
> </class> 
> </hibernate-mapping> 

-- 
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
   http://jira.nhibernate.org/secure/Administrators.jspa
-
For more information on JIRA, see:
   http://www.atlassian.com/software/jira