[Hibernate-issues] [Hibernate-JIRA] Commented: (HB-7) Oracle "Paging Select" w/association problem

[<leg...@at...>]

The following comment has been added to this issue:

     Author: Dave White
    Created: Fri, 11 Jul 2003 2:27 PM
       Body:
Gavin,

Sorry, but I was unable to verify that this was fixed for quite some
time.  I just recently got around to checking this and found that
this wasn't actually fixed.  I looked into this a bit deeper and found that the problem seems to stem from the fact that when generating the the SQL select statement for an entity, the "identity" and the "rest of the properties" are generated in two seperate code paths.  Here's a snippet from QueryTranslator which shows where the problem lies:

if ( !shallowQuery ) {
    renderIdentifierSelect(sql);
    renderPropertiesSelect(sql);
}

I saw that there was a fix put into SelectFragment in the sql package, which filters out duplicate columns when generating the columns to select.  The problem is that this only worked when there were duplicates in only in the identity or only in the "other" properties.  When the same colum is defined in the identity portion and in a composite many-to-one association, it is still broken.

I have applied a fix to this in my local source tree that involves the files:

.\src\net\sf\hibernate\hql\QueryTranslator.java
.\src\net\sf\hibernate\loader\OuterJoinLoader.java
.\src\net\sf\hibernate\persister\AbstractEntityPersister.java
.\src\net\sf\hibernate\persister\EntityPersister.java
.\src\net\sf\hibernate\persister\Loadable.java
.\src\net\sf\hibernate\persister\NormalizedEntityPersister.java
.\src\net\sf\hibernate\sql\SelectFragment.java

What I did was basically add an append(SelectFragment fragment)
method to the SelectFragment class.  I then changed all places that were building the identity & "other" properties select fragments seperately, to build them together by appending one to the other.  By doing this, I was able to take advantage of the duplicate filtering that the SelectFragment is already doing in its toFragmentString()
method.

Let me know if you'd like me to submit these changes as a patch.

---------------------------------------------------------------------
View the issue:

  http://opensource.atlassian.com/projects/hibernate/secure/ViewIssue.jspa?key=HB-7


Here is an overview of the issue:
---------------------------------------------------------------------
        Key: HB-7
    Summary: Oracle "Paging Select" w/association problem   
       Type: Bug

     Status: Closed
   Priority: Major
 Resolution: FIXED

    Project: Hibernate2
 Components: 
             core
   Fix Fors:
             2.0 final
   Versions:
             2.0rc2

   Assignee: Gavin King
   Reporter: Dave White

    Created: Wed, 30 Apr 2003 9:06 PM
    Updated: Wed, 11 Jun 2003 8:03 AM

Description:
I have a mapping that looks like this: 

<class name="Foo" proxy="Foo" table="Tbl_Foo" dynamic-update="true"> 
  <composite-id name="fooKey" class="FooKey"> 
    <key-property name="barId" column="barID" type="int"/> 
    <key-property name="id2"   column="ID2"   type="int"/> 
  </composite-id> 
  <property name="location" column="Location" type="string"/> 
  <many-to-one name="barRef" class="Bar" cascade="none" 
               insert="false" update="false"> 
    <column name="barID"/> 
  </many-to-one> 
</class> 

As you can see, the the id of Foo is the same as the foreign key used in the many-to-one association to Bar. I've used the insert/update attributes to say that the barID column should not be include (twice) in the insert/update statements. However, when I do a query against this class the generated SQL contains the barID column twice in the select statement. Once for the composite-id, and once for the many-to-one association. This became a problem when I applied my "paging select" patch for Oracle which looks like this: 

select * 
from (select a.*, rownum rnum 
from ( <hibernate-generated-sql> ) a 
where rownum <= ?) 
where rnum >= ? 

The reason this is a problem is that when the <hibernate-generated-sql> statement contains the same column twice in the select clause, Oracle complains about ambiguously declared columns. I'm wondering if different aliases could be generated in the SQL for columns defined in composite-id vs. property vs. many-to-one? 

I did look into the <key-many-to-one> tag in the composite-id, but that didn't fit well with my (already-in-place/legacy) object model. 



---------------------------------------------------------------------
JIRA INFORMATION:
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

If you want more information on JIRA, or have a bug to report see:
   http://www.atlassian.com/software/jira