[hibernate-issues] [Hibernate-JIRA] Created: (HHH-3743) MININDEX function applied on entity of many-to-many mapped collection yields incorrect SQL

[Stefan Wachter (JIRA)]

MININDEX function applied on entity of many-to-many mapped collection yields incorrect SQL
------------------------------------------------------------------------------------------

                 Key: HHH-3743
                 URL: http://opensource.atlassian.com/projects/hibernate/browse/HHH-3743
             Project: Hibernate Core
          Issue Type: Bug
          Components: core
    Affects Versions: 3.3.1
         Environment: Hibernate 3.3.1, org.hibernate.dialect.DerbyDialect and org.hibernate.dialect.MySQLInnoDBDialect
            Reporter: Stefan Wachter


I have a "t_Person" entity with a "many-to-many" list to a "t_Address" entity:

   <class entity-name="t_Person" table="F5_T_PERSON" lazy="true">
      <id name="_id" column="ID" type="long">
         <generator class="native"/>
      </id>
      <list lazy="true" cascade="all" name="elemAddress" table="F5_T_PERSON_ELM_ADDRESS">
         <key column="OWNER_KEY"/>
         <list-index column="IDX"/>
         <many-to-many column="FOREIGN_KEY" entity-name="t_Address"/>
      </list>
   </class>

   <class entity-name="t_Address" table="F5_T_ADDRESS" lazy="true">
      <id name="_id" column="ID" type="long">
         <generator class="native"/>
      </id>
      <property name="attrStreet" type="string">
         <column name="A_STREET"/>
      </property>
   </class>

When I try to retrieve the minimum index of address entities using the following HQL

select MININDEX(a) from t_Person as p join p.elemAddress as a

incorrect SQL results:

select min(t_address2_.IDX) as col_0_0_ from F5_T_PERSON t_person0_ inner join F5_T_PERSON_ELM_ADDRESS elemaddres1_ on t_person0_.ID=elemaddres1_.OWNER_KEY inner join F5_T_ADDRESS t_address2_ on elemaddres1_.FOREIGN_KEY=t_address2_.ID

In particular the addressed column "t_address2_.IDX" does not exists. Instead "elemaddres1_.IDX" should be used because the index column is contained in the join table "F5_T_PERSON_ELM_ADDRESS".

I assume that the MININDEX function does not consider the case when it is applied to an element of a many-to-many collection.

I tested this with the DerbyDialect and the MySQLInnoDBDialect.

-- 
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