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

[Viral B (JIRA)]

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