Criteria - Select vs Group By with case

Hibernate ORM
1

Hi,

Hi having an issue with one of my criteria queries after migrating to hibernate 6.2.6 Final.

Hi have a query like this with a case in select and group by:

cq.select(cb.construct(MyCountDTO.class, cb.count(root.get("id")), cb.<Boolean>selectCase().when(cb.isNotNull(root.get("photo")), true).otherwise(false)))
				.groupBy(cb.<Boolean>selectCase().when(cb.isNotNull(root.get("photo")), true).otherwise(false));

The generated sql case is different in select and in groupBy:

select
        count(m1_0.id),
        case 
            when m1_0.Photo is not null then cast(? as boolean) 
            else cast(? as boolean) 
        end 
    from
        MyEntity m1_0 
    group by
        case 
            when m1_0.Photo is not null then ? 
            else ? 
        end

Using sql server this throws a sql exception:

SQLServerException: Column ‘Photo’ is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

I’m i doing something something wrong ou stumbled in a bug?

2

You should be using literals e.g. cb.literal(true)/cb.literal(false) instead of the when(Expression, Object) method, or even simpler:

cq.select(
  cb.construct(
    MyCountDTO.class, 
    cb.count(root.get("id")), 
    cb.isNotNull(root.get("photo"))
  )
)
.groupBy(root.get("photo"));
3

Hi Beikov,

Thank you for your answer. I followed your suggestion but i get another error:

SQLServerException: The text, ntext, and image data types cannot be compared or sorted, except when using IS NULL or LIKE operator.

photo is an image (sql server).

4

Alright, then I guess you’ll have to use the literals for the group by expression after all:

cq.select(
  cb.construct(
    MyCountDTO.class, 
    cb.count(root.get("id")), 
    cb.isNotNull(root.get("photo"))
  )
)
.groupBy(
  cb.<Boolean>selectCase().when(root.get("photo"), cb.literal(true)).otherwise(cb.literal(false))
);
5

Hi Beikov,

Issue resolved, many thanks for your help