Native SQL Query module not able to find the alias for collections when property name is not "*" ------------------------------------------------------------------------------------------------- Key: HHH-3003 URL: http://opensource.atlassian.com/projects/hibernate/browse/HHH-3003 Project: Hibernate3 Issue Type: Patch Components: query-sql Affects Versions: 3.2.5 Environment: Hibernate 3.2.5.ga, Oracle 9i Reporter: Viral B Attachments: Native SQL Collection ALias Patch.zip SQLQueryParser replaces query aliases and property names with corresponding auto generated aliases. The probelm is if we use {aliasName.*} then everything works fine , but along with {aliasName.*} , if you use {aliasName.propertyName} then it doesnot work if the alias is a collection but works if aliasName is an Entity. To testify this I created two hibernate files Employee and Department , Department has many employees I wrote the following testcase, public void testNativeSqlQueryCollectionAliasDefect() { String query = "select * from ( select {d.*},{e.*} " + " from department d, employee e where d.department_id = e.department_id " + " ) where {e.id} = 232 "; getSession().createSQLQuery(query).addEntity("d",Department.class) .addJoin("e","d.employees" ) .list(); } The testcase throws following exception, org.hibernate.QueryException: No column name found for property [id] for alias [e] [select * from ( select {d.*},{e.*} from department d, employee e where d.department_id = e.department_id ) where {e.id} = 232 ] at org.hibernate.loader.custom.sql.SQLQueryParser.resolveCollectionProperties(SQLQueryParser.java:152) at org.hibernate.loader.custom.sql.SQLQueryParser.substituteBrackets(SQLQueryParser.java:98) at org.hibernate.loader.custom.sql.SQLQueryParser.process(SQLQueryParser.java:51) at org.hibernate.loader.custom.sql.SQLCustomQuery.<init>(SQLCustomQuery.java:110) at org.hibernate.engine.query.NativeSQLQueryPlan.<init>(NativeSQLQueryPlan.java:47) at org.hibernate.engine.query.QueryPlanCache.getNativeSQLQueryPlan(QueryPlanCache.java:114) at org.hibernate.impl.AbstractSessionImpl.getNativeSQLQueryPlan(AbstractSessionImpl.java:137) at org.hibernate.impl.AbstractSessionImpl.list(AbstractSessionImpl.java:142) at org.hibernate.impl.SQLQueryImpl.list(SQLQueryImpl.java:152) at com.sony.spe.test.nativeSql.TestPageDAO.testNativeSqlQuery(TestPageDAO.java:117) at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39) at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25) at java.lang.reflect.Method.invoke(Method.java:585) at junit.framework.TestCase.runTest(TestCase.java:154) at junit.framework.TestCase.runBare(TestCase.java:127) at junit.framework.TestResult$1.protect(TestResult.java:106) at junit.framework.TestResult.runProtected(TestResult.java:124) at junit.framework.TestResult.run(TestResult.java:109) at junit.framework.TestCase.run(TestCase.java:118) at org.eclipse.jdt.internal.junit.runner.junit3.JUnit3TestReference.run(JUnit3TestReference.java:128) at org.eclipse.jdt.internal.junit.runner.TestExecution.run(TestExecution.java:38) at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:460) at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:673) at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.run(RemoteTestRunner.java:386) at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.main(RemoteTestRunner.java:196) The following testcase runs fine if instead of {e.id} , we use {d.id} sine alias d is of type entity where as e here is a collection SQLQueryParser.java Line No: 148 columnAliases = collectionPersister.getCollectionPropertyColumnAliases( propertyName, collectionSuffix ); AbstractCollectionPersister.getCollectionPropertyColumnAliases() uses a wrong Map (collectionPropertyColumnAliases) for looking up the property alias, This method is the culprit. Fix: Instead of calling getCollectionPropertyColumnAliases bypass and call resolveProperties which works fine //Existing Code //columnAliases = collectionPersister.getCollectionPropertyColumnAliases( propertyName, collectionSuffix ); //Added: Viral columnAliases = new String[]{resolveProperties(aliasName, propertyName)}; Now the test case wors fine and generates the alias : EMPLOYEE1_0_1_ in the below query select * from ( select d.DEPARTMENT_ID as DEPARTMENT1_1_0_, d.DEPARTMENT_NAME as DEPARTMENT2_1_0_, e.DEPARTMENT_ID as DEPARTMENT3_0__, e.EMPLOYEE_ID as EMPLOYEE1_0__, e.EMPLOYEE_ID as EMPLOYEE1_0_1_, e.EMPLOYEE_NAME as EMPLOYEE2_0_1_ from department d, employee e where d.department_id = e.department_id ) where EMPLOYEE1_0_1_ = 232 I have attached SQLQueryParser fixed and the test domain files I created for testing purpose -- 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.... - For more information on JIRA, see: http://www.atlassian.com/software/jira