Hibernate version: 3.0.2

Code between sessionFactory.openSession() and session.close():
Built up the following criteria:

DetachedCriteria detachedCriteria = DetachedCriteria.forClass(Location.class);

DetachedCriteria subQueryCriteria = DetachedCriteria.forClass(AgmtContractLine.class);
subQueryCriteria.createAlias("versions", "versions");
subQueryCriteria.add(Restrictions.eq("versions.endUse", "Sales"));
subQueryCriteria = subQueryCriteria.setProjection(Property.forName("versions.sourceLocation"));

detachedCriteria.add(Subqueries.propertyIn("addrId", subQueryCriteria));
detachedCriteria.getExecutableCriteria(session).list();


Full stack trace of any exception that occurs:
org.hibernate.exception.SQLGrammarException: could not execute query
at org.hibernate.exception.ErrorCodeConverter.convert(ErrorCodeConverter.java:70)
at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:43)
at org.hibernate.loader.Loader.doList(Loader.java:1560)
at org.hibernate.loader.Loader.list(Loader.java:1540)
at org.hibernate.loader.criteria.CriteriaLoader.list(CriteriaLoader.java:113)
at org.hibernate.impl.SessionImpl.list(SessionImpl.java:1254)
at org.hibernate.impl.CriteriaImpl.list(CriteriaImpl.java:299)

<our stack snipped>

Caused by: java.sql.SQLException: ORA-00904: "VERSIONSJO1_"."END_USE_CODE": invalid identifier

at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:134)
at oracle.jdbc.ttc7.TTIoer.processError(TTIoer.java:289)
at oracle.jdbc.ttc7.Oall7.receive(Oall7.java:579)
at oracle.jdbc.ttc7.TTC7Protocol.doOall7(TTC7Protocol.java:1894)
at oracle.jdbc.ttc7.TTC7Protocol.parseExecuteDescribe(TTC7Protocol.java:831)
at oracle.jdbc.driver.OracleStatement.doExecuteQuery(OracleStatement.java:2496)
at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:2840)
at oracle.jdbc.driver.OraclePreparedStatement.executeUpdate(OraclePreparedStatement.java:608)
at oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePreparedStatement.java:536)
at org.hibernate.jdbc.AbstractBatcher.getResultSet(AbstractBatcher.java:118)
at org.hibernate.loader.Loader.getResultSet(Loader.java:1233)
at org.hibernate.loader.Loader.doQuery(Loader.java:370)
at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:210)
at org.hibernate.loader.Loader.doList(Loader.java:1557)
... 44 more
Name and version of the database you are using:
Oracle 9i

The generated SQL (show_sql=true):
select this_.ADDR_ID as ADDR1_0_, this_.ADDR_LINE1 as ADDR3_85_0_, this_.ADDR_LINE2 as ADDR4_85_0_, this_.ADDR_LINE3 as ADDR5_85_0_, this_.CITY as CITY85_0_, this_.POST_ZIP_CODE as POST7_85_0_, this_.X_COORD as X8_85_0_, this_.Y_COORD as Y9_85_0_, this_.Z_COORD as Z10_85_0_, this_.DESCRIPTION as DESCRIP11_85_0_, this_.EDI_ACCOUNT as EDI12_85_0_, this_.TIME_ZONE as TIME13_85_0_, this_.CUTOFF_TIME as CUTOFF14_85_0_, this_.FACILITY_BALANCE_DEFAULT as FACILITY15_85_0_, this_.DFLT_UOM_CODE as DFLT16_85_0_, this_.DFLT_TEMP_UOM_CODE as DFLT17_85_0_, this_.RECEIPT_VOLUME_TYPE as RECEIPT18_85_0_, this_.OPERATOR_REF_REQ_IND as OPERATOR19_85_0_, this_.OPERATOR_ID as OPERATOR20_85_0_, this_.DIRECT_SHIP_CITY as DIRECT21_85_0_, this_.BANK_IDENTIFIER_NUMBER as BANK22_85_0_, this_.BANK_ACCOUNT_NUMBER as BANK23_85_0_, this_.SEQ_TICKET_REF_FLG as SEQ24_85_0_, this_.SEQ_TICKET_REF_PREFIX as SEQ25_85_0_, this_.ADDR_LINE4 as ADDR26_85_0_, this_.SUBTYPE as SUBTYPE85_0_, this_.DISPLAY_ON_MAP_FLG as DISPLAY28_85_0_, this_.TERTIARY_JURISD_CODE as TERTIARY29_85_0_, this_.PROV_ST_JURISD_CODE as PROV30_85_0_, this_.COUNTRY_JURISD_CODE as COUNTRY31_85_0_, this_.TYPE as TYPE85_0_, this_.GOVERNMENT_CODE as GOVERNMENT32_85_0_, this_.NOTES_ID as NOTES33_85_0_, this_.UPDATE_DATE as UPDATE34_85_0_, this_.CREATE_DATE as CREATE35_85_0_, this_.UPDATE_USER as UPDATE36_85_0_, this_.CREATE_USER as CREATE37_85_0_, this_.TYPE as TYPE0_ from ADDRESS this_ where this_.ADDR_ID IN (SELECT version.ADDR_ID FROM ADDRESS_V version WHERE this_.ADDR_ID = version.ADDR_ID AND version.CREATE_DATE = (SELECT MAX(b.CREATE_DATE) from ADDRESS_V b WHERE version.ADDR_ID = b.ADDR_ID AND ? < b.INACTIVE_DATE AND ? > b.EFFECTIVE_DATE) AND version.ACTIVE_FLG=?) and this_.addr_id IN (SELECT ar.addr_id FROM address_relation ar INNER JOIN address_relation_v arv ON ar.address_relation_id = arv.address_relation_id WHERE arv.rel_addr_id = ?) and this_.ADDR_ID in (select versionsjo1_.SRC_ADDR_ID as y0_ from AGMT_CNTRCT_LINE this0__ where versionsjo1_.END_USE_CODE=?)


Upon executing this, the SQL at the very end doesn't show the join that should be created from the createAlias.

Executing the subquery as a main query yields the following SQL:
select versions1_.SRC_ADDR_ID as y0_ from AGMT_CNTRCT_LINE this_ inner join AGMT_CNTRCT_LINE_V versions1_ on this_.AGMT_CNTRCT_LINE_ID=versions1_.AGMT_CNTRCT_LINE_ID and versions1_.ACTIVE_FLG = ? and ? < versions1_.INACTIVE_DATE AND ? > versions1_.EFFECTIVE_DATE and versions1_.CREATE_DATE = (SELECT MAX(b.CREATE_DATE) from AGMT_CNTRCT_LINE_V b WHERE versions1_.AGMT_CNTRCT_LINE_ID = b.AGMT_CNTRCT_LINE_ID AND ? < b.INACTIVE_DATE AND ? > b.EFFECTIVE_DATE) AND versions1_.ACTIVE_FLG=? where this_.AGMT_CNTRCT_LINE_ID IN (SELECT version.AGMT_CNTRCT_LINE_ID FROM AGMT_CNTRCT_LINE_V version WHERE this_.AGMT_CNTRCT_LINE_ID = version.AGMT_CNTRCT_LINE_ID AND version.CREATE_DATE = (SELECT MAX(b.CREATE_DATE) from AGMT_CNTRCT_LINE_V b WHERE version.AGMT_CNTRCT_LINE_ID = b.AGMT_CNTRCT_LINE_ID AND ? < b.INACTIVE_DATE AND ? > b.EFFECTIVE_DATE) AND version.ACTIVE_FLG=?) and versions1_.END_USE_CODE=?

which includes the expected inner join.

In addition, the filtering that should have appeared in the first sql snippet now appears which leads me to believe that there is also a gap in filters being applied to subqueries.

Ok, even better I've asked this before, I knew this all sounded familiar.

http://forum.hibernate.org/viewtopic.php?t=938858

However, that was almost 2 months ago, any updates?

I think there is still a potential issue with the filters not getting applied though, anyone else come to the same conclusion?