SQL generated by Hibernate invalid for MS SQL

Question

Hibernate (HQL) generated the following SQL for which I inserted the parameters:

select 
sum(activity0_.calculated_work) as col_0_0_
, employee2_.id as col_1_0_
, projectele1_.id as col_2_0_ 

from 
activity activity0_ 
inner join generic_object activity0_1_ on activity0_.id=activity0_1_.id 
left outer join project_element projectele1_ on     activity0_.project_element_id=projectele1_.id 
left outer join employee employee2_ on activity0_.owner_id=employee2_.id 
left outer join org_unit orgunit3_ on employee2_.org_unit_id=orgunit3_.id 

where 
activity0_1_.deleted=0 and 
activity0_.client_id=22 

 group by 
 employee2_.id order by SUM(activity0_.calculated_work) DESC

Error message: Column 'project_element.id' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

I executed this SQL directly in the SQL Server Studio with the same result. I commented this line:

, projectele1_.id as col_2_0_

The SQL was then accepted by the SQL Server

The table project_element definitely has a column with the name id it is also referenced in the LEFT OUTER JOIN and there this column is not causing an error.

Removing the alias projectele1_ had no effect.

To me this looks like a really simple SQL statement. I cannot imagine what is wrong with it and the error message is not helping at all. Any ideas what could be wrong with the SQL?

Afsun Khammadli · Accepted Answer · 2015-05-01 07:31:32Z

3

Your SQL syntax is wrong.If you add projectele1_.id to group by clause it will work.Only aggregate functions work in select statement with group by clause.Or if you remove projectele1_.id from select it will work fine.

answered May 1, 2015 at 7:31

Afsun Khammadli

2,0684 gold badges19 silver badges28 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

gunnar247 Over a year ago

Do you happen to know whether that is SQL standard? MySQL does support the statement and Hibernate does not generate a SQL which is supported by MS SQL which was unexpected to me.

Koray Tugay · Accepted Answer · 2022-09-09 18:25:59Z

0

My mistake. I should have read the error message several times. projectele1_id is not in the group by clause. MS SQL does not allow to include such a column into the select list. This seems to be a consistency check. Too bad though that the usage of HQL leads to such an exception in SQL Server but not in MySQL Server.

edited Sep 9, 2022 at 18:25

Koray Tugay

24k49 gold badges206 silver badges335 bronze badges

answered May 1, 2015 at 7:31

gunnar247

3142 silver badges13 bronze badges

Collectives™ on Stack Overflow

SQL generated by Hibernate invalid for MS SQL

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related