Criteria API count records with COALESCE throws QuerySyntaxException

Question

is it possible to count records with coalesce? In Oracle it is possible with this select:

    SELECT
    COUNT(DISTINCT coalesce(foo.parent, foo.ident)) AS c    FROM
    FOO foo;

I tried to do with this code:

CriteriaBuilder criteriaBuilder = entityManager.getCriteriaBuilder();
CriteriaQuery<Long> criteriaQuery = criteriaBuilder.createQuery(Long.class);
Root root = criteriaQuery.from(Foo.class);
CriteriaBuilder.Coalesce<Long> coalesce = criteriaBuilder.coalesce();
coalesce.value(root.<Long> get("parent"));
coalesce.value(root.<Long> get("id"));
CriteriaQuery<Long> select = criteriaQuery.select(criteriaBuilder.countDistinct(coalesce));
TypedQuery<Long> typedQuery = entityManager.createQuery(select);
return typedQuery.getSingleResult();

But this throws an exception

Caused by: org.hibernate.hql.internal.ast.QuerySyntaxException: expecting CLOSE, found '(' near line 1, column 31

Generated query

[select count(distinct coalesce(generatedAlias0.parent, generatedAlias0.id)) from foo.Foo as generatedAlias0]

Have you tried to write a JPA named query instead of using the Criteria API? This discussion might help you: https://stackoverflow.com/questions/27528976/coalesce-in-jpa-namedquery — rieckpil, Jul 11 '19 at 11:07
I need to use Criteria Api, because on our project we have huge dynamic filter there is applied according to attribues, so Namend query as a String is not so good in this case for me — user1604064, Jul 11 '19 at 12:07
Perhaps `CriteriaBuilder.Coalesce` is immutable? Try `criteriaBuilder.countDistinct(criteriaBuilder.coalesce().value(root. get("parent")).value(root. get("id")))` instead — crizzis, Jul 11 '19 at 13:17
It doesn't help, I'm using the same syntax in other query and it works, the problem is tahat for me coalesce doesn't work with count — user1604064, Jul 11 '19 at 13:34
Seems like bug https://hibernate.atlassian.net/browse/HHH-9182 — Tijkijiki, Jul 11 '19 at 15:17

snieguu · Answer 1 · 2019-09-16T15:10:19.827

As a workaround for bug HHH-9182 (pointed by @Tijkijiki) in comments to your question, you can use CASE Expression instead of COALESCE

So You Criteria should be:

CriteriaBuilder criteriaBuilder = entityManager.getCriteriaBuilder();
CriteriaQuery<Long> criteriaQuery = criteriaBuilder.createQuery(Long.class);
Root root = criteriaQuery.from(Foo.class);
Expression<Long> selectCase = builder<Long>selectCase()
    .when(builder.isNotNull(root.get("parent")), root.get("parent"))
    .otherwise(root.get("id"));
CriteriaQuery<Long> select = 
criteriaQuery.select(criteriaBuilder.countDistinct(selectCase));
TypedQuery<Long> typedQuery = entityManager.createQuery(select);
return typedQuery.getSingleResult();

Criteria API count records with COALESCE throws QuerySyntaxException

1 Answers1