spring jpa @Query error, expecting CLOSE, found '(' near

Question

I have following JPQL:

@Query("SELECT su.id, su.nameCn, count(b.id), avg(s.rate), count(concat(b.id, '@', s.user.id)) "
            + "FROM S su, B b, S s where b.st.id = su.id and s.bd.id = b.id and su.mt.id = ?1 group by su.id")

When I add concat(b.id, '@', s.user.id), it shows me:

org.hibernate.hql.internal.ast.QuerySyntaxException: expecting CLOSE, found '(' near line 1, at org.hibernate.hql.internal.ast.QuerySyntaxException.convert(QuerySyntaxException.java:74) ~[hibernate-core-5.1.0.Final.jar:5.1.0.Final] at org.hibernate.hql.internal.ast.ErrorCounter.throwQueryException(ErrorCounter.java:91) ~[hibernate-core-5.1.0.Final.jar:5.1.0.Final] at org.hibernate.hql.internal.ast.QueryTranslatorImpl.parse(QueryTranslatorImpl.java:288) ~[hibernate-core-5.1.0.Final.jar:5.1.0.Final] at org.hibernate.hql.internal.ast.QueryTranslatorImpl.doCompile(QueryTranslatorImpl.java:187) ~[hibernate-core-5.1.0.Final.jar:5.1.0.Final] at org.hibernate.hql.internal.ast.QueryTranslatorImpl.compile(QueryTranslatorImpl.java:142) ~[hibernate-core-5.1.0.Final.jar:5.1.0.Final] at org.hibernate.engine.query.spi.HQLQueryPlan.(HQLQueryPlan.java:115) ~[hibernate-core-5.1.0.Final.jar:5.1.0.Final] at org.hibernate.engine.query.spi.HQLQueryPlan.(HQLQueryPlan.java:76) ~[hibernate-core-5.1.0.Final.jar:5.1.0.Final] at org.hibernate.engine.query.spi.QueryPlanCache.getHQLQueryPlan(QueryPlanCache.java:150) ~[hibernate-core-5.1.0.Final.jar:5.1.0.Final] at org.hibernate.internal.AbstractSessionImpl.getHQLQueryPlan(AbstractSessionImpl.java:302) ~[hibernate-core-5.1.0.Final.jar:5.1.0.Final] at org.hibernate.internal.AbstractSessionImpl.createQuery(AbstractSessionImpl.java:240) ~[hibernate-core-5.1.0.Final.jar:5.1.0.Final] at org.hibernate.internal.SessionImpl.createQuery(SessionImpl.java:1907) ~[hibernate-core-5.1.0.Final.jar:5.1.0.Final] at org.hibernate.jpa.spi.AbstractEntityManagerImpl.createQuery(AbstractEntityManagerImpl.java:291) ~[hibernate-entitymanager-5.1.0.Final.jar:5.1.0.Final]

Answer 1

expecting CLOSE, found (

The term CLOSE here refers to a closing bracket, ).

It would appear that the query parser does not support calling another function inside the count(...).

In other words, the syntax error is here:

SELECT su.id, su.nameCn, count(b.id), avg(s.rate), count(concat(b.id, '@', s.user.id))
                                                               ^

Perhaps you mean to be doing some kind of join, and counting the rows there?

Answer 2

Problem is using both count and concatenate function together.

If you write your query without count() like below, should work. This is just for testing to know who is causing the issue exactly.

Try writing query different way using count and concatenate seperatly.

...concat(b.id, '@', s.user.id)....