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

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

Message:

  A new issue has been created in JIRA.

---------------------------------------------------------------------
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: Unassigned
   Priority: Major

    Project: Hibernate2
   Versions:
             2.1

   Assignee: 
   Reporter: Alessandro Vernet

    Created: Mon, 11 Aug 2003 7:58 PM
    Updated: Mon, 11 Aug 2003 7:58 PM

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