Problem with generated SQL from Hibernate (MS SQLServer)

Question

I have a problem with Hibernate generating an SQL that do not work on SQLServer (works on PostgreSQL without any problems). I have tried to set the hibernate dialect for SQLServer but the same SQL is still generated and still do not work. The HQL query looks like this:

 select count(t) from ValidationLog t

The generated SQL looks like this:

 select count((vl.dataKey, vl.dataType)) from ValidationLog vl;

So my question is if there is anyway around it? Would really like to have the same code for both databases.

Pascal Thivent · Accepted Answer · 2010-10-18 20:13:46Z

1

According to the JPA specification, your JPQL query is perfectly valid:

4.8 SELECT Clause

...

The SELECT clause has the following syntax:

select_clause ::= SELECT [DISTINCT] select_expression {, select_expression}*
select_expression ::=
     single_valued_path_expression |
     aggregate_expression |
     identification_variable |
     OBJECT(identification_variable) |
     constructor_expression
constructor_expression ::=
     NEW constructor_name ( constructor_item {, constructor_item}*)
constructor_item ::= single_valued_path_expression | aggregate_expression
aggregate_expression ::=
     { AVG | MAX | MIN | SUM } ([DISTINCT] state_field_path_expression) |
     COUNT ([DISTINCT] identification_variable | state_field_path_expression |
          single_valued_association_path_expression)

However, you might be a victim of a bug reported in issues like HHH-4044, HHH-3096, HHH-2266 (or even HHH-5419).

Possible workaround: use a state_field_path_expression.

select count(t.someField) from ValidationLog t

edited Oct 18, 2010 at 20:13

answered Oct 18, 2010 at 13:41

Pascal Thivent

572k140 gold badges1.1k silver badges1.1k bronze badges

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

2 Comments

HLGEM Over a year ago

But it is not SQL that SQL Server would ever recognize as t-sql doesn't have that capability.

Pascal Thivent Over a year ago

@HLGEM What SQL? The currently generated? If yes, then I agree it is incorrect, which is exactly the problem (according to the spec, the JPQL query from the OP is correct, it's up to the JPA provider to generate the appropriate SQL).

Mark PM · Accepted Answer · 2010-10-18 11:15:42Z

0

The HQL looks wrong to me, should be:

select count(t.dataKey) from ValidationLog t

answered Oct 18, 2010 at 11:15

Mark PM

2,90416 silver badges21 bronze badges

4 Comments

Peter Eriksson Over a year ago

No, it is a composite key in the table so there has to be at least two columns.

Mark PM Over a year ago

What are you trying to count? The number of rows or distinct composite keys?

Peter Eriksson Over a year ago

Number of rows in the tables is what I would like to count. Which should be the same as the number of distinct composite keys.

Mark PM Over a year ago

In that case my answer still stands.

Collectives™ on Stack Overflow

Problem with generated SQL from Hibernate (MS SQLServer)

2 Answers 2

4.8 SELECT Clause

2 Comments

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4.8 SELECT Clause

2 Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related