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

[Andrew (JIRA) <no...@at...>]

    [ http://opensource.atlassian.com/projects/hibernate/browse/HHH-842?page=comments#action_23548 ] 

Andrew commented on HHH-842:
----------------------------

   Sorry.    There is no bag now  with  ORDER BY.

There is that exception only when I use SELECT count(*) ... and ORDER BY expression together.
And this is trouble of MS SQL,  because  ORACLE executed this query correctly



> Projection with fetch="join" and order-by="" in mappings causes failure
> -----------------------------------------------------------------------
>
>          Key: HHH-842
>          URL: http://opensource.atlassian.com/projects/hibernate/browse/HHH-842
>      Project: Hibernate3
>         Type: Bug

>   Components: metamodel, core
>     Versions: 3.0.5
>  Environment: MS SQL 2000 SP3, Hibernate 3.0.5
>     Reporter: Vasyl Zhabko
>  Attachments: src.zip
>
>
> 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. 

-- 
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