[Hibernate-issues] [Hibernate-JIRA] Closed: (HB-247) Aggregate and expression

[<leg...@at...>]

Message:

   The following issue has been closed.

   Resolver: Gavin King
       Date: Tue, 12 Aug 2003 2:55 AM

As per the documentation, HQL does not yet allow algebraic expressions in the SELECT clause. There is an existing feature request for this ... which you are welcome to tackle, if you like ;)
---------------------------------------------------------------------
View the issue:

  http://opensource.atlassian.com/projects/hibernate/secure/ViewIssue.jspa?key=HB-247


Here is an overview of the issue:
---------------------------------------------------------------------
        Key: HB-247
    Summary: Aggregate and expression
       Type: Bug

     Status: Closed
   Priority: Major
 Resolution: REJECTED

    Project: Hibernate2
   Versions:
             2.1

   Assignee: 
   Reporter: Alessandro Vernet

    Created: Mon, 11 Aug 2003 7:58 PM
    Updated: Tue, 12 Aug 2003 2:55 AM

Description:
The code:

session.iterate("select p.year, sum(f.units), sum(a.units), sum(f.units*pp.listPrice)" +
                        " from Period p" +
                        " left outer join p.forecasts f" +
                        " left outer join p.actuals a" +
                        " left outer join p.productPrices pp" +
                        " where p.period >= ? and p.period <= ?" +
                        "   and f.customer = ? and f.product = ? and f.employee = ?" +
                        "   and a.customer = ? and a.product = ? and a.employee = ?" +
                        "   and pp.customer = ? and pp.product = ?" +
                        " group by p.year order by p.year", ...

The important part is the "sum(f.units*pp.listPrice)". Hibernate generates this query:

select period0_.year as x0_0_, sum(forecast1_.units) as x1_0_, sum(actuals2_.units) as x2_0_, sum(forecast1_.units*productp3_.list) as x3_0_ from owf_period period0_ left outer join owf_forecast forecast1_ on period0_.period=forecast1_.period left outer join owf_actual actuals2_ on period0_.period=actuals2_.period left outer join owf_product_price productp3_ on period0_.period=productp3_.period where (period0_.period>=? )and(period0_.period<=? )and(forecast1_.customer_id=? )and(forecast1_.product_id=? )and(forecast1_.employee_id=? )and(actuals2_.customer_id=? )and(actuals2_.product_id=? )and(actuals2_.employee_id=? )and(productp3_.customer_id=? )and(productp3_.product_id=? ) group by  period0_.year order by  period0_.year

Running this throws the exception:

java.sql.SQLException: [Microsoft][SQLServer 2000 Driver for JDBC]Invalid column name: x4_0_
        at com.microsoft.jdbc.base.BaseExceptions.createException(Unknown Source)
        at com.microsoft.jdbc.base.BaseExceptions.getException(Unknown Source)
        at com.microsoft.jdbc.base.BaseResultSet.getColumnOrdinal(Unknown Source)
        at com.microsoft.jdbc.base.BaseResultSet.getInt(Unknown Source)
        at org.apache.commons.dbcp.DelegatingResultSet.getInt(DelegatingResultSet.java:159)
        at net.sf.hibernate.type.IntegerType.get(IntegerType.java:14)
        at net.sf.hibernate.type.NullableType.nullSafeGet(NullableType.java:60)
        at net.sf.hibernate.type.NullableType.nullSafeGet(NullableType.java:51)
        at net.sf.hibernate.impl.IteratorImpl.postNext(IteratorImpl.java:62)
        at net.sf.hibernate.impl.IteratorImpl.<init>(IteratorImpl.java:44)
        at net.sf.hibernate.hql.QueryTranslator.iterate(QueryTranslator.java:835)
        at net.sf.hibernate.impl.SessionImpl.iterate(SessionImpl.java:1439)
        at net.sf.hibernate.impl.SessionImpl.iterate(SessionImpl.java:1416)


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