[hibernate-issues] [Hibernate-JIRA] Created: (HHH-6429) GROUP BY clause doesn't work with composite objects

[Fernando Santos (JIRA)]

GROUP BY clause doesn't work with composite objects
---------------------------------------------------

                 Key: HHH-6429
                 URL: http://opensource.atlassian.com/projects/hibernate/browse/HHH-6429
             Project: Hibernate Core
          Issue Type: Bug
          Components: query-hql
    Affects Versions: 3.6.5
         Environment: Firebird 2.1 Server, Hibernate 3.6.5, java 6, eclipse IDE
            Reporter: Fernando Santos
            Priority: Critical


I'm trying to run the HQL below in the latest hibernate version

{quote}
SELECT
  m.account,
  (SELECT sum(amount) from Movement where date > '20-02-2011') as primo
FROM
  Movement m
GROUP BY
  m.account
{quote}

The problem is when the HQL is parsed, the account fields are added to my SELECT query but not to my GROUP BY query like below. Has someone been faced the same problem?

Generated SQL:

{quote}
select movement0_.MOV_ACCOUNT_NUMBER as col_0_0_, 
       movement0_.MOV_ACCOUNT_AFDELING_ID as col_0_1_, 
       (select sum(movement2_.MOV_AMOUNT) from MOVEMENT movement2_) as col_1_0_, 
       account1_.KONTONR as KONTONR12_, 
       account1_.AFDELING_ID as AFDELING14_12_, 
       account1_.KON_FUNCTION as KON2_12_, 
       account1_.KON_BALANCE as KON3_12_, 
       account1_.KON_BLOCKED as KON4_12_, 
       account1_.KONTO_NAVN as KONTO5_12_, 
       account1_.KON_KEY_FIGURE_CODE as KON6_12_, 
       account1_.KON_MONITORED as KON7_12_, 
       account1_.KON_SUGGEST_CONTRA_ACC as KON8_12_, 
       account1_.KON_TOTALFROM as KON9_12_, 
       account1_.KON_TYPE as KON10_12_, 
       account1_.UAFD_GRP_NAVN as UAFD11_12_, 
       account1_.UAFD_NAVN as UAFD12_12_, 
       account1_.KON_TAX as KON15_12_, 
       account1_.OPTLOCK as OPTLOCK12_ 
from MOVEMENT movement0_ 
inner join KONTOPLAN account1_ 
       on movement0_.MOV_ACCOUNT_NUMBER=account1_.KONTONR 
       and movement0_.MOV_ACCOUNT_AFDELING_ID=account1_.AFDELING_ID 
group by 
  movement0_.MOV_ACCOUNT_NUMBER, 
  movement0_.MOV_ACCOUNT_AFDELING_ID
{quote}

Error

{quote}
Caused by: org.firebirdsql.jdbc.FBSQLException: GDS Exception. 335544569. Dynamic SQL Error
SQL error code = -104
Invalid expression in the select list (not contained in either an aggregate function or the GROUP BY clause)
{quote}

--
This message is automatically generated by JIRA.
For more information on JIRA, see: http://www.atlassian.com/software/jira