[hibernate-issues] [Hibernate-JIRA] Commented: (HHH-3003) Native SQL Query module not able to find the alias for collections when property name is not "*"

[Max Rydahl Andersen (JIRA)]

    [ http://opensource.atlassian.com/projects/hibernate/browse/HHH-3003?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel#action_29100 ] 

Max Rydahl Andersen commented on HHH-3003:
------------------------------------------

this depends greatly on what mapping you are using. did all the sqlqueries tests still pass?

have you read the docs on how the {} syntax works for collections ?

> 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.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira