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.

Answer 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