I have the following problem setting my Report.locations property according to the following original SQL query:

select l.*, r.* from reports r left outer join locations l on (r.location = l.location or substring(r.location,1,6) = l.location or substring(r.location,1,4) = l.location or substring(r.location,1,2) = l.location) where r.id = ?

So I have no proper foreign key relation ship but one based on an expression. I think I could solve this with a <load-collection> SQL query. This query would possibly return multiple rows, in my example there are three.

In my mapping I've defined a <load-collection> SQL query to fill the locations collection in my Report class. The query is correctly executed, I see that the Location objects are created, that the locations collection has the correct number of items, but when I call report.getLocations() I get a List object with 0 items.

Hibernate version:
3.0.5

Mapping documents:
<?xml version="1.0" encoding="utf-8" ?>
<!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD 3.0//EN" "http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">
<hibernate-mapping>
<class name="Location" table="locations">
<id name="location" type="string">
<generator class="assigned"/>
</id>
<property name="name"/>
</class>
</hibernate-mapping>

<?xml version="1.0" encoding="utf-8" ?>
<!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD 3.0//EN" "http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">
<hibernate-mapping>
<class name="Report" table="reports">
<id name="id">
<generator class="native">
<param name="sequence">seq_reports</param>
</generator>
</id>
<bag name="locations" inverse="true">
<key column="location" not-null="true"/>
<one-to-many class="Location"/>
<loader query-ref="query-locations"/>
</bag>
</class>

<sql-query name="query-locations">
<load-collection alias="l" role="Report.locations"/>
select {l.*} from reports r left outer join locations l on (r.location = l.location or substring(r.location,1,6) = l.location or substring(r.location,1,4) = l.location or substring(r.location,1,2) = l.location) where r.id = ?
</sql-query>
</hibernate-mapping>


Code between sessionFactory.openSession() and session.close():
Report report = (Report) session.get(Report.class, Integer.parseInt(args[0]));
System.out.println("*********REPORT*********" + report.getLocations());

Full stack trace of any exception that occurs:
None

Name and version of the database you are using:
PostgreSQL 8.0

The generated SQL (show_sql=true):
select l.location as location0__, l.location as location0_, l.name as name1_0_ from reports r left outer join locations l on (r.location = l.location or substring(r.location,1,6) = l.location or substring(r.location,1,4) = l.location or substring(r.location,1,2) = l.location) where r.id = ?

Debug level Hibernate log excerpt:
Rather large, but here's the relevant part:

[java] 2005-06-02 11:42:09,289 DEBUG SessionImpl SQL query: select l.location as location0__, l.location as location0_, l.name as name1_0_ from reports r left outer join locations l on (r.location = l.location or substring(r.location,1,6) = l.location or substring(r.location,1,4) = l.location or substring(r.location,1,2) = l.location) where r.id = ?
[java] 7407 [main] DEBUG org.hibernate.impl.SessionImpl - SQL query: select l.location as location0__, l.location as location0_, l.name as name1_0_ from reports r left outer join locations l on (r.location = l.location or substring(r.location,1,6) = l.location or substring(r.location,1,4) = l.location or substring(r.location,1,2) = l.location) where r.id = ?
[java] 2005-06-02 11:42:09,301 DEBUG AbstractBatcher about to open PreparedStatement (open PreparedStatements: 0, globally: 0)
[java] 7419 [main] DEBUG org.hibernate.jdbc.AbstractBatcher - about to open PreparedStatement (open PreparedStatements: 0, globally: 0)
[java] 2005-06-02 11:42:09,306 DEBUG SQL select l.location as location0__, l.location as location0_, l.name as name1_0_ from reports r left outer join locations l on (r.location = l.location or substring(r.location,1,6) = l.location or substring(r.location,1,4) = l.location or substring(r.location,1,2) = l.location) where r.id = ?
[java] 7424 [main] DEBUG org.hibernate.SQL - select l.location as location0__, l.location as location0_, l.name as name1_0_ from reports r left outer join locations l on (r.location = l.location or substring(r.location,1,6) = l.location or substring(r.location,1,4) = l.location or substring(r.location,1,2) = l.location) where r.id = ?
[java] Hibernate: select l.location as location0__, l.location as location0_, l.name as name1_0_ from reports r left outer join locations l on (r.location = l.location or substring(r.location,1,6) = l.location or substring(r.location,1,4) = l.location or substring(r.location,1,2) = l.location) where r.id = ?
[java] 2005-06-02 11:42:09,311 DEBUG AbstractBatcher preparing statement
[java] 7429 [main] DEBUG org.hibernate.jdbc.AbstractBatcher - preparing statement
[java] 2005-06-02 11:42:09,313 DEBUG IntegerType binding '1' to parameter: 1
[java] 7431 [main] DEBUG org.hibernate.type.IntegerType - binding '1' to parameter: 1
[java] 2005-06-02 11:42:09,339 DEBUG AbstractBatcher about to open ResultSet (open ResultSets: 0, globally: 0)
[java] 7457 [main] DEBUG org.hibernate.jdbc.AbstractBatcher - about to open ResultSet (open ResultSets: 0, globally: 0)
[java] 2005-06-02 11:42:09,342 DEBUG Loader result set contains (possibly empty) collection: [Report.locations#1]
[java] 7460 [main] DEBUG org.hibernate.loader.Loader - result set contains (possibly empty) collection: [Report.locations#1]
[java] 2005-06-02 11:42:09,352 DEBUG CollectionLoadContext uninitialized collection: initializing
[java] 7470 [main] DEBUG org.hibernate.engine.CollectionLoadContext - uninitialized collection: initializing
[java] 2005-06-02 11:42:09,357 DEBUG Loader processing result set
[java] 7475 [main] DEBUG org.hibernate.loader.Loader - processing result set
[java] 2005-06-02 11:42:09,359 DEBUG Loader result set row: 0
[java] 7477 [main] DEBUG org.hibernate.loader.Loader - result set row: 0
[java] 2005-06-02 11:42:09,362 DEBUG StringType returning '12' as column: location0_
[java] 7480 [main] DEBUG org.hibernate.type.StringType - returning '12' as column: location0_
[java] 2005-06-02 11:42:09,364 DEBUG Loader result row: EntityKey[Location#12]
[java] 7482 [main] DEBUG org.hibernate.loader.Loader - result row: EntityKey[Location#12]
[java] 2005-06-02 11:42:09,366 DEBUG Loader Initializing object from ResultSet: [Location#12]
[java] 7484 [main] DEBUG org.hibernate.loader.Loader - Initializing object from ResultSet: [Location#12]
[java] 2005-06-02 11:42:09,367 DEBUG BasicEntityPersister Hydrating entity: [Location#12]
[java] 7485 [main] DEBUG org.hibernate.persister.entity.BasicEntityPersister - Hydrating entity: [Location#12]
[java] 2005-06-02 11:42:09,373 DEBUG StringType returning 'State' as column: name1_0_
[java] 7491 [main] DEBUG org.hibernate.type.StringType - returning 'State' as column: name1_0_
[java] 2005-06-02 11:42:09,376 DEBUG IntegerType returning '12' as column: location0__
[java] 7494 [main] DEBUG org.hibernate.type.IntegerType - returning '12' as column: location0__
[java] 2005-06-02 11:42:09,378 DEBUG Loader found row of collection: [Report.locations#12]
[java] 7496 [main] DEBUG org.hibernate.loader.Loader - found row of collection: [Report.locations#12]
[java] 2005-06-02 11:42:09,379 DEBUG CollectionLoadContext new collection: instantiating
[java] 7497 [main] DEBUG org.hibernate.engine.CollectionLoadContext - new collection: instantiating
[java] 2005-06-02 11:42:09,390 DEBUG StringType returning '12' as column: location0__
[java] 7508 [main] DEBUG org.hibernate.type.StringType - returning '12' as column: location0__
[java] 2005-06-02 11:42:09,392 DEBUG DefaultLoadEventListener loading entity: [Location#12]
[java] 7510 [main] DEBUG org.hibernate.event.def.DefaultLoadEventListener - loading entity: [Location#12]
[java] 2005-06-02 11:42:09,396 DEBUG DefaultLoadEventListener attempting to resolve: [Location#12]
[java] 7514 [main] DEBUG org.hibernate.event.def.DefaultLoadEventListener - attempting to resolve: [Location#12]
[java] 2005-06-02 11:42:09,398 DEBUG DefaultLoadEventListener resolved object in session cache: [Location#12]
[java] 7516 [main] DEBUG org.hibernate.event.def.DefaultLoadEventListener - resolved object in session cache: [Location#12]
[java] 2005-06-02 11:42:09,406 DEBUG Loader result set row: 1
[java] 7524 [main] DEBUG org.hibernate.loader.Loader - result set row: 1
[java] 2005-06-02 11:42:09,408 DEBUG StringType returning '1234' as column: location0_
[java] 7526 [main] DEBUG org.hibernate.type.StringType - returning '1234' as column: location0_
[java] 2005-06-02 11:42:09,410 DEBUG Loader result row: EntityKey[Location#1234]
[java] 7528 [main] DEBUG org.hibernate.loader.Loader - result row: EntityKey[Location#1234]
[java] 2005-06-02 11:42:09,411 DEBUG Loader Initializing object from ResultSet: [Location#1234]
[java] 7529 [main] DEBUG org.hibernate.loader.Loader - Initializing object from ResultSet: [Location#1234]
[java] 2005-06-02 11:42:09,413 DEBUG BasicEntityPersister Hydrating entity: [Location#1234]
[java] 7531 [main] DEBUG org.hibernate.persister.entity.BasicEntityPersister - Hydrating entity: [Location#1234]
[java] 2005-06-02 11:42:09,415 DEBUG StringType returning 'City' as column: name1_0_
[java] 7533 [main] DEBUG org.hibernate.type.StringType - returning 'City' as column: name1_0_
[java] 2005-06-02 11:42:09,417 DEBUG IntegerType returning '1234' as column: location0__
[java] 7535 [main] DEBUG org.hibernate.type.IntegerType - returning '1234' as column: location0__
[java] 2005-06-02 11:42:09,419 DEBUG Loader found row of collection: [Report.locations#1234]
[java] 7537 [main] DEBUG org.hibernate.loader.Loader - found row of collection: [Report.locations#1234]
[java] 2005-06-02 11:42:09,422 DEBUG CollectionLoadContext new collection: instantiating
[java] 7540 [main] DEBUG org.hibernate.engine.CollectionLoadContext - new collection: instantiating
[java] 2005-06-02 11:42:09,429 DEBUG StringType returning '1234' as column: location0__
[java] 7547 [main] DEBUG org.hibernate.type.StringType - returning '1234' as column: location0__
[java] 2005-06-02 11:42:09,431 DEBUG DefaultLoadEventListener loading entity: [Location#1234]
[java] 7549 [main] DEBUG org.hibernate.event.def.DefaultLoadEventListener - loading entity: [Location#1234]
[java] 2005-06-02 11:42:09,433 DEBUG DefaultLoadEventListener attempting to resolve: [Location#1234]
[java] 7551 [main] DEBUG org.hibernate.event.def.DefaultLoadEventListener - attempting to resolve: [Location#1234]
[java] 2005-06-02 11:42:09,434 DEBUG DefaultLoadEventListener resolved object in session cache: [Location#1234]
[java] 7552 [main] DEBUG org.hibernate.event.def.DefaultLoadEventListener - resolved object in session cache: [Location#1234]
[java] 2005-06-02 11:42:09,438 DEBUG Loader result set row: 2
[java] 7556 [main] DEBUG org.hibernate.loader.Loader - result set row: 2
[java] 2005-06-02 11:42:09,440 DEBUG StringType returning '123456' as column: location0_
[java] 7558 [main] DEBUG org.hibernate.type.StringType - returning '123456' as column: location0_
[java] 2005-06-02 11:42:09,442 DEBUG Loader result row: EntityKey[Location#123456]
[java] 7560 [main] DEBUG org.hibernate.loader.Loader - result row: EntityKey[Location#123456]
[java] 2005-06-02 11:42:09,446 DEBUG Loader Initializing object from ResultSet: [Location#123456]
[java] 7564 [main] DEBUG org.hibernate.loader.Loader - Initializing object from ResultSet: [Location#123456]
[java] 2005-06-02 11:42:09,451 DEBUG BasicEntityPersister Hydrating entity: [Location#123456]
[java] 7569 [main] DEBUG org.hibernate.persister.entity.BasicEntityPersister - Hydrating entity: [Location#123456]
[java] 2005-06-02 11:42:09,459 DEBUG StringType returning 'Neightbourhood' as column: name1_0_
[java] 7577 [main] DEBUG org.hibernate.type.StringType - returning 'Neightbourhood' as column: name1_0_
[java] 2005-06-02 11:42:09,473 DEBUG IntegerType returning '123456' as column: location0__
[java] 7591 [main] DEBUG org.hibernate.type.IntegerType - returning '123456' as column: location0__
[java] 2005-06-02 11:42:09,475 DEBUG Loader found row of collection: [Report.locations#123456]
[java] 7593 [main] DEBUG org.hibernate.loader.Loader - found row of collection: [Report.locations#123456]
[java] 2005-06-02 11:42:09,476 DEBUG CollectionLoadContext new collection: instantiating
[java] 7594 [main] DEBUG org.hibernate.engine.CollectionLoadContext - new collection: instantiating
[java] 2005-06-02 11:42:09,478 DEBUG StringType returning '123456' as column: location0__
[java] 7596 [main] DEBUG org.hibernate.type.StringType - returning '123456' as column: location0__
[java] 2005-06-02 11:42:09,485 DEBUG DefaultLoadEventListener loading entity: [Location#123456]
[java] 7603 [main] DEBUG org.hibernate.event.def.DefaultLoadEventListener - loading entity: [Location#123456]
[java] 2005-06-02 11:42:09,487 DEBUG DefaultLoadEventListener attempting to resolve: [Location#123456]
[java] 7605 [main] DEBUG org.hibernate.event.def.DefaultLoadEventListener - attempting to resolve: [Location#123456]
[java] 2005-06-02 11:42:09,489 DEBUG DefaultLoadEventListener resolved object in session cache: [Location#123456]
[java] 7607 [main] DEBUG org.hibernate.event.def.DefaultLoadEventListener - resolved object in session cache: [Location#123456]
[java] 2005-06-02 11:42:09,490 DEBUG Loader done processing result set (3 rows)
[java] 7608 [main] DEBUG org.hibernate.loader.Loader - done processing result set (3 rows)
[java] 2005-06-02 11:42:09,492 DEBUG AbstractBatcher about to close ResultSet (open ResultSets: 1, globally: 1)
[java] 7610 [main] DEBUG org.hibernate.jdbc.AbstractBatcher - about to close ResultSet (open ResultSets: 1, globally: 1)
[java] 2005-06-02 11:42:09,494 DEBUG AbstractBatcher about to close PreparedStatement (open PreparedStatements: 1, globally: 1)
[java] 7612 [main] DEBUG org.hibernate.jdbc.AbstractBatcher - about to close PreparedStatement (open PreparedStatements: 1, globally: 1)
[java] 2005-06-02 11:42:09,495 DEBUG AbstractBatcher closing statement
[java] 7613 [main] DEBUG org.hibernate.jdbc.AbstractBatcher - closing statement
[java] 2005-06-02 11:42:09,497 DEBUG Loader total objects hydrated: 3
[java] 7615 [main] DEBUG org.hibernate.loader.Loader - total objects hydrated: 3
[java] 2005-06-02 11:42:09,498 DEBUG TwoPhaseLoad resolving associations for [Location#12]
[java] 7616 [main] DEBUG org.hibernate.engine.TwoPhaseLoad - resolving associations for [Location#12]
[java] 2005-06-02 11:42:09,501 DEBUG TwoPhaseLoad done materializing entity [Location#12]
[java] 7619 [main] DEBUG org.hibernate.engine.TwoPhaseLoad - done materializing entity [Location#12]
[java] 2005-06-02 11:42:09,504 DEBUG TwoPhaseLoad resolving associations for [Location#1234]
[java] 7622 [main] DEBUG org.hibernate.engine.TwoPhaseLoad - resolving associations for [Location#1234]
[java] 2005-06-02 11:42:09,505 DEBUG TwoPhaseLoad done materializing entity [Location#1234]
[java] 7623 [main] DEBUG org.hibernate.engine.TwoPhaseLoad - done materializing entity [Location#1234]
[java] 2005-06-02 11:42:09,507 DEBUG TwoPhaseLoad resolving associations for [Location#123456]
[java] 7625 [main] DEBUG org.hibernate.engine.TwoPhaseLoad - resolving associations for [Location#123456]
[java] 2005-06-02 11:42:09,514 DEBUG TwoPhaseLoad done materializing entity [Location#123456]
[java] 7632 [main] DEBUG org.hibernate.engine.TwoPhaseLoad - done materializing entity [Location#123456]
[java] 2005-06-02 11:42:09,517 DEBUG CollectionLoadContext 4 collections were found in result set for role: Report.locations
[java] 7635 [main] DEBUG org.hibernate.engine.CollectionLoadContext - 4 collections were found in result set for role: Report.locations
[java] 2005-06-02 11:42:09,519 DEBUG CollectionLoadContext collection fully initialized: [Report.locations#1234]
[java] 7637 [main] DEBUG org.hibernate.engine.CollectionLoadContext - collection fully initialized: [Report.locations#1234]
[java] 2005-06-02 11:42:09,521 DEBUG CollectionLoadContext collection fully initialized: [Report.locations#123456]
[java] 7639 [main] DEBUG org.hibernate.engine.CollectionLoadContext - collection fully initialized: [Report.locations#123456]
[java] 2005-06-02 11:42:09,523 DEBUG CollectionLoadContext collection fully initialized: [Report.locations#1]
[java] 7641 [main] DEBUG org.hibernate.engine.CollectionLoadContext - collection fully initialized: [Report.locations#1]
[java] 2005-06-02 11:42:09,525 DEBUG CollectionLoadContext collection fully initialized: [Report.locations#12]
[java] 7643 [main] DEBUG org.hibernate.engine.CollectionLoadContext - collection fully initialized: [Report.locations#12]
[java] 2005-06-02 11:42:09,526 DEBUG CollectionLoadContext 4 collections initialized for role: Report.locations
[java] 7644 [main] DEBUG org.hibernate.engine.CollectionLoadContext - 4 collections initialized for role: Report.locations
[java] 2005-06-02 11:42:09,528 DEBUG PersistenceContext initializing non-lazy collections
[java] 7646 [main] DEBUG org.hibernate.engine.PersistenceContext - initializing non-lazy collections
[java] 2005-06-02 11:42:09,530 DEBUG JDBCContext after autocommit
[java] 7648 [main] DEBUG org.hibernate.jdbc.JDBCContext - after autocommit
[java] 2005-06-02 11:42:09,531 DEBUG SessionImpl setting flush mode to: AUTO
[java] 7649 [main] DEBUG org.hibernate.impl.SessionImpl - setting flush mode to: AUTO
[java] 2005-06-02 11:42:09,533 DEBUG tializeCollectionEventListener collection initialized
[java] 7651 [main] DEBUG org.hibernate.event.def.DefaultInitializeCollectionEventListener - collection initialized
[java] *********REPORT********* []

The last line shows the empty List...

Please help as I'm really stumped as to why this doesn't work.

Regards,


Jeroen