Hi all,
Trying to run a native query using a subquery in the from clause to find table entries that have the same name but different versions when joined with another table.
When I run the query below in a console window, results are as expected, I get both lab_archive objects returned that have the same name but different version numbers.
The problem is that when run by hibernate, the result set loaded always loads in the first object found twice.
Queries, pojos’, console, and log output below.
It seems like this is a bug.
Can anyone confirm this is the right process for running this query?

This is using Hibernate version 3.2.6GA.

Thanks,
Chris

The query looks like this:
select prev.*, curr.* from (select la.* from lab_archive la inner join lab_rf lb on la.lab_id = lb.lab_id where lb.name = :currLab ) curr inner join (select la.* from lab_archive la inner join lab_rf lb on la.lab_id = lb.lab_id where lb.name = :prevLab ) prev on (prev.name = curr.name and prev.version != curr.version)

When run in a console window results look like this, I bolded the version diffs below.
80jarnull2008-05-14 06:55:28.01.4.2_06-b03 (Sun Microsystems Inc.) Fix problem with Remote Pricing/claims/clmsys04/lib/Claim.jarClaim.jarnull2010-02-09 23:44:31.755 integration.1.004515511jarnull2008-05-29 07:02:08.01.4.2_06-b03 (Sun Microsystems Inc.) Issues: 002719,002725, 002726, 002732,002733, 002734/claims/clmsys10/lib/Claim.jarClaim.jarnull2010-01-25 00:06:27.989 etrak_27803.1.00071154

LabArchive table
@MappedSuperclass
public abstract class AbstractLabArchive implements java.io.Serializable, IArchive {

// Fields

private Long pk;
private String version;
private LabRf labRf;
private ApplicationRf applicationRf;
private String name;
private String archivetype;
private String location;
private String builtby;
private String comments;
private String userId;
private Date updDt;
private Date buildDt;
private String autodeploy;
private String relMgrExclList;

LabRf Table:


private Long labId;
private ApplicationRf applicationRf;
private String name;
private String userId;
private Date updDt;
private String host;
private String labOwner;
private String labPath;
private Set<DrRequest> drRequestsForFromLabId = new HashSet<DrRequest>(0);
private Set<DrRequest> drRequestsForScmDeployLabId = new HashSet<DrRequest>(
0);
private Set<DrRequest> drRequestsForToLabId = new HashSet<DrRequest>(0);
private Set<LabArchive> labArchives = new HashSet<LabArchive>(0);
private Set<DrArchive> drArchives = new HashSet<DrArchive>(0);

DAO Code:
try {
StringBuffer sb = new StringBuffer();
sb.append("select prev.*, curr.* ");
sb.append("from (select la.* ");
sb.append("from lab_archive la inner join lab_rf lb on la.lab_id = lb.lab_id ");
sb.append("where lb.name = :currLab ");
sb.append(") curr ");
sb.append("inner join (select la.* ");
sb.append("from lab_archive la inner join lab_rf lb on la.lab_id = lb.lab_id ");
sb.append("where lb.name = :prevLab ) prev ");
sb.append("on (prev.name = curr.name and prev.version != curr.version)");

final String queryString = sb .toString();
System.out.println(queryString);
return getJpaTemplate().executeFind(new JpaCallback() {
public Object doInJpa(EntityManager em)
throws PersistenceException {

Session session = (Session) em.getDelegate();

List resList = session.createSQLQuery(queryString)
//.addEntity("prev",LabArchive.class)
//.addScalar("version")
.setParameter("currLab", currLab)
.setParameter("prevLab", prevLab)
.list();



Log output :
010-03-06 16:20:49,043 DEBUG SQL:401 - select prev.*, curr.* from (select la.* from lab_archive la inner join lab_rf lb on la.lab_id = lb.lab_id where lb.name = ? ) curr inner join (select la.* from lab_archive la inner join lab_rf lb on la.lab_id = lb.lab_id where lb.name = ? ) prev on (prev.name = curr.name and prev.version != curr.version)
2010-03-06 16:20:49,043 DEBUG AbstractBatcher:484 - preparing statement
2010-03-06 16:20:49,054 DEBUG Loader:1747 - bindNamedParameters() clmsys04s -> currLab [1]
2010-03-06 16:20:49,055 DEBUG StringType:133 - binding 'clmsys04s' to parameter: 1
2010-03-06 16:20:49,055 DEBUG Loader:1747 - bindNamedParameters() clmsys10s -> prevLab [2]
2010-03-06 16:20:49,055 DEBUG StringType:133 - binding 'clmsys10s' to parameter: 2
2010-03-06 16:20:49,084 DEBUG AbstractBatcher:382 - about to open ResultSet (open ResultSets: 0, globally: 0)
2010-03-06 16:20:49,092 DEBUG Loader:694 - processing result set
2010-03-06 16:20:49,093 DEBUG Loader:699 - result set row: 0
2010-03-06 16:20:49,093 DEBUG Loader:1173 - result row:
2010-03-06 16:20:49,094 DEBUG BigIntegerType:172 - returning '80' as column: PK
2010-03-06 16:20:49,095 DEBUG StringType:172 - returning 'jar' as column: ARCHIVETYPE
2010-03-06 16:20:49,095 DEBUG StringType:166 - returning null as column: AUTODEPLOY
2010-03-06 16:20:49,096 DEBUG TimestampType:172 - returning '2008-05-14 06:55:28' as column: BUILD_DT
2010-03-06 16:20:49,096 DEBUG StringType:172 - returning '1.4.2_06-b03 (Sun Microsystems Inc.)' as column: BUILTBY
2010-03-06 16:20:49,096 DEBUG StringType:172 - returning ' Fix problem with Remote Pricing' as column: COMMENTS
2010-03-06 16:20:49,096 DEBUG StringType:172 - returning '/claims/clmsys04/lib/Claim.jar' as column: LOCATION
2010-03-06 16:20:49,097 DEBUG StringType:172 - returning 'Claim.jar' as column: NAME
2010-03-06 16:20:49,097 DEBUG StringType:166 - returning null as column: REL_MGR_EXCL_LIST
2010-03-06 16:20:49,097 DEBUG TimestampType:172 - returning '2010-02-09 23:44:31' as column: UPD_DT
2010-03-06 16:20:49,097 DEBUG StringType:172 - returning ' ' as column: USER_ID
2010-03-06 16:20:49,098 DEBUG StringType:172 - returning 'integration.1.0045' as column: VERSION
2010-03-06 16:20:49,098 DEBUG BigIntegerType:172 - returning '1' as column: APP_ID
2010-03-06 16:20:49,098 DEBUG BigIntegerType:172 - returning '155' as column: LAB_ID
2010-03-06 16:20:49,098 DEBUG BigIntegerType:172 - returning '80' as column: PK
2010-03-06 16:20:49,099 DEBUG StringType:172 - returning 'jar' as column: ARCHIVETYPE
2010-03-06 16:20:49,100 DEBUG StringType:166 - returning null as column: AUTODEPLOY
2010-03-06 16:20:49,100 DEBUG TimestampType:172 - returning '2008-05-14 06:55:28' as column: BUILD_DT
2010-03-06 16:20:49,101 DEBUG StringType:172 - returning '1.4.2_06-b03 (Sun Microsystems Inc.)' as column: BUILTBY
2010-03-06 16:20:49,101 DEBUG StringType:172 - returning ' Fix problem with Remote Pricing' as column: COMMENTS
2010-03-06 16:20:49,101 DEBUG StringType:172 - returning '/ /claims/clmsys04/lib/Claim.jar' as column: LOCATION
2010-03-06 16:20:49,101 DEBUG StringType:172 - returning 'Claim.jar' as column: NAME
2010-03-06 16:20:49,102 DEBUG StringType:166 - returning null as column: REL_MGR_EXCL_LIST
2010-03-06 16:20:49,102 DEBUG TimestampType:172 - returning '2010-02-09 23:44:31' as column: UPD_DT
2010-03-06 16:20:49,102 DEBUG StringType:172 - returning ' ' as column: USER_ID
2010-03-06 16:20:49,102 DEBUG StringType:172 - returning 'integration.1.0045' as column: VERSION
2010-03-06 16:20:49,103 DEBUG BigIntegerType:172 - returning '1' as column: APP_ID
2010-03-06 16:20:49,103 DEBUG BigIntegerType:172 - returning '155' as column: LAB_ID