[Hibernate-issues] [Hibernate-JIRA] Created: (HHH-842) Projection with fetch="join" and order-by="" in mappings causes failure

[<no...@at...>]

Message:

  A new issue has been created in JIRA.

---------------------------------------------------------------------
View the issue:
  http://opensource.atlassian.com/projects/hibernate/browse/HHH-842

Here is an overview of the issue:
---------------------------------------------------------------------
        Key: HHH-842
    Summary: Projection with fetch="join" and order-by="" in mappings causes failure
       Type: Bug

     Status: Unassigned
   Priority: Major

 Original Estimate: Unknown
 Time Spent: Unknown
  Remaining: Unknown

    Project: Hibernate3
 Components: 
             core
             metamodel
   Versions:
             3.0.5

   Assignee: 
   Reporter: Vasyl Zhabko

    Created: Tue, 9 Aug 2005 4:32 PM
    Updated: Tue, 9 Aug 2005 4:32 PM
Environment: MS SQL 2000 SP3, Hibernate 3.0.5

Description:
If you have in your mappings set fetch="join" and order-by="" for child collection like in this mapping:

<?xml version="1.0"?>
<!DOCTYPE hibernate-mapping PUBLIC
                            "-//Hibernate/Hibernate Mapping DTD 3.0//EN"
                            "http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd" >

<hibernate-mapping package="test.model">

    <class name="Cat" table="CAT">
        <id name="catId" column="CAT_ID" type="java.lang.Integer">
            <generator class="native"/>
        </id>
 
        <property name="name" column="NAME" type="java.lang.String" />
        <property name="color" column="COLOR" type="java.lang.String" />
        <property name="gender" column="GENDER" type="java.lang.String" />
        
		<set 
			name="kittens" fetch="join"
			cascade="all-delete-orphan" inverse="true"
			order-by="name" >
			 <key column="MOTHER_ID"/>
			 <one-to-many class="Cat"/>
		</set>  
		
    </class>
    
</hibernate-mapping>

adding Projection to criteria will have in a result incorrect query  generated and execution will fail, like that:

Hibernate: select count(*) as y0_ from CAT this_ left outer join CAT kittens2_ on this_.CAT_ID=kittens2_.MOTHER_ID where this_.COLOR=? order by kittens2_.name
14:12:45,163 ERROR JDBCExceptionReporter:72 - [Microsoft][SQLServer 2000 Driver for JDBC][SQLServer]Column name 'kittens2_.NAME' is invalid in the ORDER BY clause because it is not contained in an aggregate function and there is no GROUP BY clause.
org.hibernate.exception.GenericJDBCException: could not execute query
	at org.hibernate.exception.SQLStateConverter.handledNonSpecificException(SQLStateConverter.java:82)
	at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:70)
	at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:43)
	at org.hibernate.loader.Loader.doList(Loader.java:1596)
	at org.hibernate.loader.Loader.list(Loader.java:1577)
	at org.hibernate.loader.criteria.CriteriaLoader.list(CriteriaLoader.java:111)
	at org.hibernate.impl.SessionImpl.list(SessionImpl.java:1322)
	at org.hibernate.impl.CriteriaImpl.list(CriteriaImpl.java:300)
	at test.test.Test.test(Test.java:62)
	at test.test.Test.main(Test.java:79)
Caused by: java.sql.SQLException: [Microsoft][SQLServer 2000 Driver for JDBC][SQLServer]Column name 'kittens2_.NAME' is invalid in the ORDER BY clause because it is not contained in an aggregate function and there is no GROUP BY clause.
	at com.microsoft.jdbc.base.BaseExceptions.createException(Unknown Source)
	at com.microsoft.jdbc.base.BaseExceptions.getException(Unknown Source)
	at com.microsoft.jdbc.sqlserver.tds.TDSRequest.processErrorToken(Unknown Source)
	at com.microsoft.jdbc.sqlserver.tds.TDSRequest.processReplyToken(Unknown Source)
	at com.microsoft.jdbc.sqlserver.tds.TDSRPCRequest.processReplyToken(Unknown Source)
	at com.microsoft.jdbc.sqlserver.tds.TDSRequest.processReply(Unknown Source)
	at com.microsoft.jdbc.sqlserver.SQLServerImplStatement.getNextResultType(Unknown Source)
	at com.microsoft.jdbc.base.BaseStatement.commonTransitionToState(Unknown Source)
	at com.microsoft.jdbc.base.BaseStatement.postImplExecute(Unknown Source)
	at com.microsoft.jdbc.base.BasePreparedStatement.postImplExecute(Unknown Source)
	at com.microsoft.jdbc.base.BaseStatement.commonExecute(Unknown Source)
	at com.microsoft.jdbc.base.BaseStatement.executeQueryInternal(Unknown Source)
	at com.microsoft.jdbc.base.BasePreparedStatement.executeQuery(Unknown Source)
	at org.hibernate.jdbc.AbstractBatcher.getResultSet(AbstractBatcher.java:120)
	at org.hibernate.loader.Loader.getResultSet(Loader.java:1272)
	at org.hibernate.loader.Loader.doQuery(Loader.java:391)
	at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:218)
	at org.hibernate.loader.Loader.doList(Loader.java:1593)
	... 6 more

Source code for criteria is:

            List results = session.createCriteria(Cat.class)
                .setProjection( Projections.rowCount() )
                .add( Restrictions.eq("color", "BLACK") )
                .list();


If you remove fetch="join" everything works just fine. Even if you will use subcriteria with child attributes. Changes in any other attributes have no effect. 


---------------------------------------------------------------------
JIRA INFORMATION:
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

If you want more information on JIRA, or have a bug to report see:
   http://www.atlassian.com/software/jira