Hi,

I have a collection that is loaded with load-collection. The resultset contains the primary key of the "FROM" entity aliased as ID. The <key/> element of the mapping is blank, which defaults to ID. The problem is that I cannot use this association in an HQL "join fetch". Hibernate is 3.1

Here are the relevant parts of the mapping:

<hibernate-mapping>
<class name="com.bootdb.RelationDefinition" ...

<set name="implementedTypeDefinitionSet" inverse="true">
<key/>
<one-to-many class="com.bootdb.TypeDefinition"/>
<loader query-ref="implementedTypeDefinitionSetQry"/>
</set>
.......
<sql-query name="implementedTypeDefinitionSetQry">
<load-collection alias="to_type"
role="com.bootdb.RelationDefinition.implementedTypeDefinitionSet"/>
SELECT {to_type.*}
from (select tydf.*,foreign_key_table.id
from type_definition tydf
,(select ? as foreign_key from dual) foreign_key_table
where api_tydf_custom.get_impl(tydf.tydf_id) = foreign_key_table.foreign_key ) to_type
</sql-query>



The following HQL query works fine:

from RelationDefinition reldf where reldf.name in ('KNOWN_VALUE','RELATION_DEFINITION') order by reldf.name

I can then iterate over the RelationDefinition entities and call getImplementedTypeDefinitionSet, it returns the right stuff.

The following join fetch does not work.

from RelationDefinition reldf join fetch reldf.implementedTypeDefinitionSet where reldf.name in ('KNOWN_VALUE','RELATION_DEFINITION') order by reldf.name. What I want is to load the collection in the same query.

It generates the following SQL which is invalid:

select relationde0_.IMPO_ID as IMPO1_3_0_, implemente1_.TYDF_ID as TYDF1_9_1_, relationde0_.VERSION as VERSION3_0_, relationde0_.OWNER_NAME as OWNER3_3_0_, relationde0_.NAME as NAME3_0_, relationde0_.IMPLEMENTATION_NAME as IMPLEMEN5_3_0_, relationde0_.EFFECTIVE_DATE as EFFECTIVE6_3_0_, relationde0_.ALIAS as ALIAS3_0_, relationde0_.SOURCE_FILE_PATH as SOURCE8_3_0_, relationde0_.SOURCE_FILE_NAME as SOURCE9_3_0_, relationde0_.END_DATE as END10_3_0_, relationde0_.HINT_TEXT as HINT11_3_0_, relationde0_.SHORT_NOTE as SHORT12_3_0_, relationde0_.DISPLAY_NAME as DISPLAY13_3_0_, relationde0_.COLUMN_PREFIX as COLUMN14_3_0_, relationde0_.JOURNAL_LOCATION as JOURNAL15_3_0_, relationde0_.CREATE_DATE_TIME as CREATE16_3_0_, relationde0_.CREATE_USER as CREATE17_3_0_, relationde0_.UPDATE_DATE_TIME as UPDATE18_3_0_, relationde0_.UPDATE_USER as UPDATE19_3_0_, relationde0_.IMPLANG_ID as IMPLANG20_3_0_, relationde0_.CREATE_PTY_ID as CREATE21_3_0_, relationde0_.UPDATE_PTY_ID as UPDATE22_3_0_, relationde0_.WHERE_CLAUSE as WHERE23_3_0_, relationde0_.IS_IN_ALL_OBJECTS as IS24_3_0_, relationde0_.IS_SNAPSHOT_SUPPORT_REQUIRED as IS25_3_0_, relationde0_.IS_IN_ALL_RELATIONSHIPS as IS26_3_0_, relationde0_.IS_IN_DATAMSGCNT as IS27_3_0_, relationde0_.TABLESPACE_NAME as TABLESPACE28_3_0_, relationde0_.APPSYSCON_ID as APPSYSCON29_3_0_, relationde0_.IMPLEMENTATION_STATUS as IMPLEME30_3_0_, relationde0_.LIFE_CYCLE_STATUS as LIFE31_3_0_, relationde0_.MIGRATION_STATUS as MIGRATION32_3_0_,
(SELECT to_type.impo_id
from key_constraint primary_key
,key_constraint_column pk_concol
,column_definition to_type
WHERE primary_key.impo_id_reldf = relationde0_.impo_id
AND pk_concol.impo_id_kcon = primary_key.impo_id
AND pk_concol.impo_id_coldf = to_type.impo_id
AND primary_key.tydf_id = tydf_get_id.PK)
as formula0_0_, (select tydf.tydf_id from type_definition tydf
where api_tydf_custom.get_impl(tydf.tydf_id) = relationde0_.impo_id
and api_tydf_custom.isImplementationLevel(tydf.tydf_id)='Y') as formula1_0_, implemente1_.TYDF_ID_ROOTED_BY as TYDF2_9_1_, implemente1_.TYDF_ID_TYPED_BY as TYDF3_9_1_, implemente1_.CODE as CODE9_1_, implemente1_.NAME as NAME9_1_, implemente1_.PLURAL as PLURAL9_1_, implemente1_.FULL_NAME as FULL7_9_1_, implemente1_.DESCRIPTION as DESCRIPT8_9_1_, implemente1_.ICON as ICON9_1_, implemente1_.DISPLAY_NAME as DISPLAY10_9_1_, implemente1_.DISPLAY_PLURAL as DISPLAY11_9_1_, implemente1_.IS_INSTANTIABLE as IS12_9_1_, implemente1_.ORDER_SEQUENCE as ORDER13_9_1_, implemente1_.IS_SET as IS14_9_1_, implemente1_.IS_SYSTEM_IDENTIFIER_USED as IS15_9_1_, implemente1_.AGGREGATION_FUNCTION as AGGREGA16_9_1_, implemente1_.IS_OWNER_REQUIRED as IS17_9_1_, implemente1_.IS_A_HIERARCHICAL_RELATIONSHIP as IS18_9_1_, implemente1_.IS_VALUE_UNIQUE_OVER_ALL_TIME as IS19_9_1_, implemente1_.CREATE_DATE_TIME as CREATE20_9_1_, implemente1_.CREATE_USER as CREATE21_9_1_, implemente1_.CREATE_PTY_ID as CREATE22_9_1_, implemente1_.UPDATE_DATE_TIME as UPDATE23_9_1_, implemente1_.UPDATE_USER as UPDATE24_9_1_, implemente1_.UPDATE_PTY_ID as UPDATE25_9_1_, implemente1_.USER_HELP as USER26_9_1_, implemente1_.NOTES as NOTES9_1_, implemente1_.GENERATE_CONSTANTS as GENERATE28_9_1_, implemente1_.DESCRIPTOR_EXPRESSION as DESCRIPTOR29_9_1_, implemente1_.IS_API_GENERATED as IS30_9_1_, implemente1_.IS_CORE as IS31_9_1_, implemente1_.OBJ_ID as OBJ32_9_1_, implemente1_.APPSYSCON_ID as APPSYSCON33_9_1_, implemente1_.LIFE_CYCLE_STATUS as LIFE34_9_1_, implemente1_.MIGRATION_STATUS as MIGRATION35_9_1_, implemente1_.TYDF_ID_PARENT as TYDF36_9_1_, NVL(api_tydf_custom.isImplementationLevel(implemente1_.tydf_id),'N') as formula2_1_, api_tydf_custom.get_impl(implemente1_.tydf_id) as formula3_1_, implemente1_.id as id0__, implemente1_.TYDF_ID as TYDF1_0__ from GAME.RELATION_DEFINITION relationde0_ inner join GAME.TYPE_DEFINITION implemente1_ on relationde0_.IMPO_ID=implemente1_.id where relationde0_.NAME in ('KNOWN_VALUE' , 'RELATION_DEFINITION') order by relationde0_.NAME
6039 WARN JDBCExceptionReporter - SQL Error: 904, SQLState: 42000
6039 ERROR JDBCExceptionReporter - ORA-00904: "IMPLEMENTE1_"."ID": invalid identifier


type_Definition does not have an ID identifier, I was returning that in the load-collection query as a foreign key replacement. When I HQL "join fetch" or HQL "join" hibernate thinks that is really a foreign key, which it is not.

Any idea how I can work around this?

Thanks you,

Calin

it would be great of I could get this join fetch to work.. I am still not sure if I'm hitting a hibernate bug, or I'm doing something wrong.

Thanks,

Calin