Search code examples
sqlgroup-byh2criteria-api

Criteria API with H2 and GROUP BY clause

I use H2 database in a Spring Boot test. It tests a function that uses the following criteria query:

        query
                .multiselect(
                        tenant,
                        cb.count(root)
                )
                .where(cb.and(
                    statusIsEqual,
                    lifeSpanNotNull,
                    lifespanGtZero,
                    notExpired
                ))
                .groupBy(tenant);

When I run the test I get the following error:

Column "P1_0.TENANT_APP_ID" must be in the GROUP BY list; SQL statement:

select p1_0.tenant_app_id, count(p1_0.id)
from push_notification p1_0
where p1_0.status = ?
  and p1_0.lifespan is not null and p1_0.lifespan > ?
  and current_date <= p1_0.expiry_date
group by 1

Does anyone have an idea what's wrong?

Solution

  • I solved the problem through a SQ statement without Criteria API.