Hibernate unable to parse count() with upper()

Question

I have a table of Location entities with a city property of type String, and an active property of type Boolean.

For example, we have two entities with city "Derby" and "DERBY". I need to return just one string entry for these two.

The following hql is parsed ok by Hibernate:

select distinct upper(l.city) from Location l where l.active = true

Hibernate is apparently unable to parse the following hql:

select count(distinct upper(l.city)) from Location l where l.active = true

Hibernate seems to throw an exception at the "upper" where it looks like it expects an alias or identifier.

The exact same select statement works fine as standard sql against our postgres database.

How else can I extract distinct rows of the upper-cased city property from Location?

Edit: Here's the stack trace...

    [ERROR][20-08-2018 16:11:21,904][org.hibernate.hql.internal.ast.ErrorCounter] :: line 1:28: expecting CLOSE, found '('
[ERROR][20-08-2018 16:11:21,905][org.hibernate.hql.internal.ast.ErrorCounter] :: line 1:28: expecting CLOSE, found '('
line 1:28: expecting CLOSE, found '('
    at antlr.Parser.match(Parser.java:211)
    at org.hibernate.hql.internal.antlr.HqlBaseParser.aggregate(HqlBaseParser.java:4969)
    at org.hibernate.hql.internal.antlr.HqlBaseParser.identPrimary(HqlBaseParser.java:4405)
    at org.hibernate.hql.internal.antlr.HqlBaseParser.primaryExpression(HqlBaseParser.java:975)
    at org.hibernate.hql.internal.antlr.HqlBaseParser.atom(HqlBaseParser.java:3530)
    at org.hibernate.hql.internal.antlr.HqlBaseParser.unaryExpression(HqlBaseParser.java:3383)
    at org.hibernate.hql.internal.antlr.HqlBaseParser.multiplyExpression(HqlBaseParser.java:3255)
    at org.hibernate.hql.internal.antlr.HqlBaseParser.additiveExpression(HqlBaseParser.java:2912)
    at org.hibernate.hql.internal.antlr.HqlBaseParser.concatenation(HqlBaseParser.java:597)
    at org.hibernate.hql.internal.antlr.HqlBaseParser.relationalExpression(HqlBaseParser.java:2679)
    at org.hibernate.hql.internal.antlr.HqlBaseParser.equalityExpression(HqlBaseParser.java:2540)
    at org.hibernate.hql.internal.antlr.HqlBaseParser.negatedExpression(HqlBaseParser.java:2504)
    at org.hibernate.hql.internal.antlr.HqlBaseParser.logicalAndExpression(HqlBaseParser.java:2420)
    at org.hibernate.hql.internal.antlr.HqlBaseParser.logicalOrExpression(HqlBaseParser.java:2385)
    at org.hibernate.hql.internal.antlr.HqlBaseParser.expression(HqlBaseParser.java:2098)
    at org.hibernate.hql.internal.antlr.HqlBaseParser.aliasedExpression(HqlBaseParser.java:2339)
    at org.hibernate.hql.internal.antlr.HqlBaseParser.selectedPropertiesList(HqlBaseParser.java:1372)
    at org.hibernate.hql.internal.antlr.HqlBaseParser.selectClause(HqlBaseParser.java:1282)
    at org.hibernate.hql.internal.antlr.HqlBaseParser.selectFrom(HqlBaseParser.java:1022)
    at org.hibernate.hql.internal.antlr.HqlBaseParser.queryRule(HqlBaseParser.java:730)
    at org.hibernate.hql.internal.antlr.HqlBaseParser.selectStatement(HqlBaseParser.java:323)
    at org.hibernate.hql.internal.antlr.HqlBaseParser.statement(HqlBaseParser.java:186)
    at org.hibernate.hql.internal.ast.QueryTranslatorImpl.parse(QueryTranslatorImpl.java:279)
    at org.hibernate.hql.internal.ast.QueryTranslatorImpl.doCompile(QueryTranslatorImpl.java:187)
    at org.hibernate.hql.internal.ast.QueryTranslatorImpl.compile(QueryTranslatorImpl.java:142)
    at org.hibernate.engine.query.spi.HQLQueryPlan.<init>(HQLQueryPlan.java:115)
    at org.hibernate.engine.query.spi.HQLQueryPlan.<init>(HQLQueryPlan.java:76)
    at org.hibernate.engine.query.spi.QueryPlanCache.getHQLQueryPlan(QueryPlanCache.java:150)
    at org.hibernate.internal.AbstractSessionImpl.getHQLQueryPlan(AbstractSessionImpl.java:302)
    at org.hibernate.internal.AbstractSessionImpl.createQuery(AbstractSessionImpl.java:240)
    at org.hibernate.internal.SessionImpl.createQuery(SessionImpl.java:1907)
    at org.hibernate.jpa.spi.AbstractEntityManagerImpl.createQuery(AbstractEntityManagerImpl.java:291)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at java.lang.reflect.Method.invoke(Method.java:497)
    at org.springframework.orm.jpa.SharedEntityManagerCreator$SharedEntityManagerInvocationHandler.invoke(SharedEntityManagerCreator.java:294)
    at com.sun.proxy.$Proxy41.createQuery(Unknown Source)
    at com.kn.mik.aetherserver.server.service.AetherServiceImpl.getUniqueCities(AetherServiceImpl.java:4237)
    at com.kn.mik.aetherserver.server.test.AetherServiceTest.testGetUniqueCities(AetherServiceTest.java:381)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at java.lang.reflect.Method.invoke(Method.java:497)
    at org.junit.runners.model.FrameworkMethod$1.runReflectiveCall(FrameworkMethod.java:50)
    at org.junit.internal.runners.model.ReflectiveCallable.run(ReflectiveCallable.java:12)
    at org.junit.runners.model.FrameworkMethod.invokeExplosively(FrameworkMethod.java:47)
    at org.junit.internal.runners.statements.InvokeMethod.evaluate(InvokeMethod.java:17)
    at org.junit.internal.runners.statements.RunBefores.evaluate(RunBefores.java:26)
    at org.springframework.test.context.junit4.statements.RunBeforeTestMethodCallbacks.evaluate(RunBeforeTestMethodCallbacks.java:75)
    at org.springframework.test.context.junit4.statements.RunAfterTestMethodCallbacks.evaluate(RunAfterTestMethodCallbacks.java:86)
    at org.junit.rules.ExpectedException$ExpectedExceptionStatement.evaluate(ExpectedException.java:239)
    at org.junit.rules.RunRules.evaluate(RunRules.java:20)
    at org.springframework.test.context.junit4.statements.SpringRepeat.evaluate(SpringRepeat.java:84)
    at org.junit.runners.ParentRunner.runLeaf(ParentRunner.java:325)
    at org.springframework.test.context.junit4.SpringJUnit4ClassRunner.runChild(SpringJUnit4ClassRunner.java:254)
    at org.springframework.test.context.junit4.SpringJUnit4ClassRunner.runChild(SpringJUnit4ClassRunner.java:89)
    at org.junit.runners.ParentRunner$3.run(ParentRunner.java:290)
    at org.junit.runners.ParentRunner$1.schedule(ParentRunner.java:71)
    at org.junit.runners.ParentRunner.runChildren(ParentRunner.java:288)
    at org.junit.runners.ParentRunner.access$000(ParentRunner.java:58)
    at org.junit.runners.ParentRunner$2.evaluate(ParentRunner.java:268)
    at org.springframework.test.context.junit4.statements.RunBeforeTestClassCallbacks.evaluate(RunBeforeTestClassCallbacks.java:61)
    at org.springframework.test.context.junit4.statements.RunAfterTestClassCallbacks.evaluate(RunAfterTestClassCallbacks.java:70)
    at org.junit.runners.ParentRunner.run(ParentRunner.java:363)
    at org.springframework.test.context.junit4.SpringJUnit4ClassRunner.run(SpringJUnit4ClassRunner.java:193)
    at org.eclipse.jdt.internal.junit4.runner.JUnit4TestReference.run(JUnit4TestReference.java:86)
    at org.eclipse.jdt.internal.junit.runner.TestExecution.run(TestExecution.java:38)
    at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:459)
    at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:678)
    at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.run(RemoteTestRunner.java:382)
    at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.main(RemoteTestRunner.java:192)
[ERROR][20-08-2018 16:11:21,912][org.hibernate.hql.internal.ast.ErrorCounter] :: line 1:29: unexpected token: l
[ERROR][20-08-2018 16:11:21,912][org.hibernate.hql.internal.ast.ErrorCounter] :: line 1:29: unexpected token: l
line 1:29: unexpected token: l
    at org.hibernate.hql.internal.antlr.HqlBaseParser.aliasedExpression(HqlBaseParser.java:2365)
    at org.hibernate.hql.internal.antlr.HqlBaseParser.selectedPropertiesList(HqlBaseParser.java:1372)
    at org.hibernate.hql.internal.antlr.HqlBaseParser.selectClause(HqlBaseParser.java:1282)
    at org.hibernate.hql.internal.antlr.HqlBaseParser.selectFrom(HqlBaseParser.java:1022)
    at org.hibernate.hql.internal.antlr.HqlBaseParser.queryRule(HqlBaseParser.java:730)
    at org.hibernate.hql.internal.antlr.HqlBaseParser.selectStatement(HqlBaseParser.java:323)
    at org.hibernate.hql.internal.antlr.HqlBaseParser.statement(HqlBaseParser.java:186)
    at org.hibernate.hql.internal.ast.QueryTranslatorImpl.parse(QueryTranslatorImpl.java:279)
    at org.hibernate.hql.internal.ast.QueryTranslatorImpl.doCompile(QueryTranslatorImpl.java:187)
    at org.hibernate.hql.internal.ast.QueryTranslatorImpl.compile(QueryTranslatorImpl.java:142)
    at org.hibernate.engine.query.spi.HQLQueryPlan.<init>(HQLQueryPlan.java:115)
    at org.hibernate.engine.query.spi.HQLQueryPlan.<init>(HQLQueryPlan.java:76)
    at org.hibernate.engine.query.spi.QueryPlanCache.getHQLQueryPlan(QueryPlanCache.java:150)
    at org.hibernate.internal.AbstractSessionImpl.getHQLQueryPlan(AbstractSessionImpl.java:302)
    at org.hibernate.internal.AbstractSessionImpl.createQuery(AbstractSessionImpl.java:240)
    at org.hibernate.internal.SessionImpl.createQuery(SessionImpl.java:1907)
    at org.hibernate.jpa.spi.AbstractEntityManagerImpl.createQuery(AbstractEntityManagerImpl.java:291)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at java.lang.reflect.Method.invoke(Method.java:497)
    at org.springframework.orm.jpa.SharedEntityManagerCreator$SharedEntityManagerInvocationHandler.invoke(SharedEntityManagerCreator.java:294)
    at com.sun.proxy.$Proxy41.createQuery(Unknown Source)
    at com.kn.mik.aetherserver.server.service.AetherServiceImpl.getUniqueCities(AetherServiceImpl.java:4237)
    at com.kn.mik.aetherserver.server.test.AetherServiceTest.testGetUniqueCities(AetherServiceTest.java:381)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at java.lang.reflect.Method.invoke(Method.java:497)
    at org.junit.runners.model.FrameworkMethod$1.runReflectiveCall(FrameworkMethod.java:50)
    at org.junit.internal.runners.model.ReflectiveCallable.run(ReflectiveCallable.java:12)
    at org.junit.runners.model.FrameworkMethod.invokeExplosively(FrameworkMethod.java:47)
    at org.junit.internal.runners.statements.InvokeMethod.evaluate(InvokeMethod.java:17)
    at org.junit.internal.runners.statements.RunBefores.evaluate(RunBefores.java:26)
    at org.springframework.test.context.junit4.statements.RunBeforeTestMethodCallbacks.evaluate(RunBeforeTestMethodCallbacks.java:75)
    at org.springframework.test.context.junit4.statements.RunAfterTestMethodCallbacks.evaluate(RunAfterTestMethodCallbacks.java:86)
    at org.junit.rules.ExpectedException$ExpectedExceptionStatement.evaluate(ExpectedException.java:239)
    at org.junit.rules.RunRules.evaluate(RunRules.java:20)
    at org.springframework.test.context.junit4.statements.SpringRepeat.evaluate(SpringRepeat.java:84)
    at org.junit.runners.ParentRunner.runLeaf(ParentRunner.java:325)
    at org.springframework.test.context.junit4.SpringJUnit4ClassRunner.runChild(SpringJUnit4ClassRunner.java:254)
    at org.springframework.test.context.junit4.SpringJUnit4ClassRunner.runChild(SpringJUnit4ClassRunner.java:89)
    at org.junit.runners.ParentRunner$3.run(ParentRunner.java:290)
    at org.junit.runners.ParentRunner$1.schedule(ParentRunner.java:71)
    at org.junit.runners.ParentRunner.runChildren(ParentRunner.java:288)
    at org.junit.runners.ParentRunner.access$000(ParentRunner.java:58)
    at org.junit.runners.ParentRunner$2.evaluate(ParentRunner.java:268)
    at org.springframework.test.context.junit4.statements.RunBeforeTestClassCallbacks.evaluate(RunBeforeTestClassCallbacks.java:61)
    at org.springframework.test.context.junit4.statements.RunAfterTestClassCallbacks.evaluate(RunAfterTestClassCallbacks.java:70)
    at org.junit.runners.ParentRunner.run(ParentRunner.java:363)
    at org.springframework.test.context.junit4.SpringJUnit4ClassRunner.run(SpringJUnit4ClassRunner.java:193)
    at org.eclipse.jdt.internal.junit4.runner.JUnit4TestReference.run(JUnit4TestReference.java:86)
    at org.eclipse.jdt.internal.junit.runner.TestExecution.run(TestExecution.java:38)
    at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:459)
    at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:678)
    at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.run(RemoteTestRunner.java:382)
    at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.main(RemoteTestRunner.java:192)

what if you rewrite query to `select count(distinct upper(l.city)) as cnt from Location l where l.active = true` — bilak, Aug 20 '18 at 14:55
No difference - the hql doesn't make use of the new _cnt_ alias anyway...???? — Ed Syrett, Aug 20 '18 at 15:16

score 0 · Answer 1 · answered Aug 20 '18 at 15:16

0

You might use the following query:

select l.city from Location l where l.active = true group by upper(l.city)

And then get the result size:

int numberOfCities = yourSession.createQuery(hqlSelect).getResultList().size();

answered Aug 20 '18 at 15:16

Christian Hoffmann

170
8

Hi Christian - unfortunately the query you posted isn't legal. The nearest to this is `select count(l.city) from Location l where l.active = true group by upper(l.city)` which will just return multiple rows containing the number of time each city appears in the table. – Ed Syrett Aug 20 '18 at 15:24
If the query isn't legal you might want to check the naming of your fields. `select u.name from User u where u.tutorialState = 1000 group by upper(u.name)` works fine on my setup. And yes, the query doesn't return the number you want. That's why you retrieve it afterwards by the size of your resultset. Another possible approach is highlighted here: https://stackoverflow.com/a/50081864/7200161 – Christian Hoffmann Aug 20 '18 at 15:33

Minar Mahmud · Accepted Answer · 2018-08-20T19:58:52.653

From JPA 2.1 Specification Final Release:

BNF Grammar of the Java Persistence Query Language:

aggregate_expression ::=
    {AVG | MAX | MIN | SUM} ([DISTINCT] state_field_path_expression) |
    COUNT ([DISTINCT] identification_variable | state_field_path_expression |
          single_valued_association_path_expression) |
    function_invocation

So, you cannot use UPPER() like this in this case with JPQL.

You can use your working sql query with hibernate native query, for example:

public long getCount() {
    String sql = "SELECT COUNT(DISTINCT UPPER(l.city))"
            + " FROM location_table_name l WHERE l.active = true";

    return ((BigInteger) em.createNativeQuery(sql)
            .getSingleResult())
            .longValue();
}

NOTE:

If you have case insensitive collation, following JPQL should simply work:

SELECT COUNT(DISTINCT l.city) FROM Location l WHERE l.active = true

Hibernate unable to parse count() with upper()

2 Answers2