We're updating the issue view to help you get more done. 

SelectCase does not work when simultaneously exists in select and group by sections

Description

When selectCase is passed to 'select' and 'groupBy' method simultaneously there is an error during criteria query compilation.

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 javax.persistence.PersistenceException: org.hibernate.exception.SQLGrammarException: could not extract ResultSet at org.hibernate.internal.ExceptionConverterImpl.convert(ExceptionConverterImpl.java:149) at org.hibernate.internal.ExceptionConverterImpl.convert(ExceptionConverterImpl.java:157) at org.hibernate.query.internal.AbstractProducedQuery.list(AbstractProducedQuery.java:1511) at org.hibernate.query.Query.getResultList(Query.java:146) at org.hibernate.query.criteria.internal.compile.CriteriaQueryTypeQueryAdapter.getResultList(CriteriaQueryTypeQueryAdapter.java:73) at org.hibernate.jpa.test.criteria.selectcase.GroupBySelectCaseTest.selectCaseInGroupByAndSelectSection(GroupBySelectCaseTest.java:45) 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.hibernate.testing.junit4.ExtendedFrameworkMethod.invokeExplosively(ExtendedFrameworkMethod.java:45) at org.junit.internal.runners.statements.InvokeMethod.evaluate(InvokeMethod.java:17) at org.junit.internal.runners.statements.RunBefores.evaluate(RunBefores.java:26) at org.junit.internal.runners.statements.RunAfters.evaluate(RunAfters.java:27) at org.junit.internal.runners.statements.FailOnTimeout$CallableStatement.call(FailOnTimeout.java:298) at org.junit.internal.runners.statements.FailOnTimeout$CallableStatement.call(FailOnTimeout.java:292) at java.util.concurrent.FutureTask.run(FutureTask.java:266) at java.lang.Thread.run(Thread.java:745) Caused by: org.hibernate.exception.SQLGrammarException: could not extract ResultSet at org.hibernate.exception.internal.SQLStateConversionDelegate.convert(SQLStateConversionDelegate.java:106) at org.hibernate.exception.internal.StandardSQLExceptionConverter.convert(StandardSQLExceptionConverter.java:42) at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:111) at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:97) at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.extract(ResultSetReturnImpl.java:69) at org.hibernate.loader.Loader.getResultSet(Loader.java:2168) at org.hibernate.loader.Loader.executeQueryStatement(Loader.java:1931) at org.hibernate.loader.Loader.executeQueryStatement(Loader.java:1893) at org.hibernate.loader.Loader.doQuery(Loader.java:938) at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:341) at org.hibernate.loader.Loader.doList(Loader.java:2692) at org.hibernate.loader.Loader.doList(Loader.java:2675) at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2507) at org.hibernate.loader.Loader.list(Loader.java:2502) at org.hibernate.loader.hql.QueryLoader.list(QueryLoader.java:504) at org.hibernate.hql.internal.ast.QueryTranslatorImpl.list(QueryTranslatorImpl.java:397) at org.hibernate.engine.query.spi.HQLQueryPlan.performList(HQLQueryPlan.java:221) at org.hibernate.internal.SessionImpl.list(SessionImpl.java:1494) at org.hibernate.query.internal.AbstractProducedQuery.doList(AbstractProducedQuery.java:1534) at org.hibernate.query.internal.AbstractProducedQuery.list(AbstractProducedQuery.java:1502) ... 18 more Caused by: org.postgresql.util.PSQLException: ERROR: column "person0_.age" must appear in the GROUP BY clause or be used in an aggregate function Pozycja: 18 at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2476) at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2189) at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:300) at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:428) at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:354) at org.postgresql.jdbc.PgPreparedStatement.executeWithFlags(PgPreparedStatement.java:169) at org.postgresql.jdbc.PgPreparedStatement.executeQuery(PgPreparedStatement.java:117) at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.extract(ResultSetReturnImpl.java:60) ... 33 more

It was tested with PostgreSQL95Dialect, MySQL57Dialect and H2Dialect.
Only PostgreSQL95Dialect is affected.

SQL generated for prepare statements mechanism cannot be validated by postgres engine.

1 2 3 4 5 6 7 8 9 10 11 12 13 14 select case when person0_.age between 0 and 10 then 'child' when person0_.age between 11 and 20 then 'teenager' else 'adult' end as col_0_0_ from Person person0_ group by case when person0_.age between 0 and 10 then ? when person0_.age between 11 and 20 then ? else ? end

Directly from postgres console:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 PREPARE select_case_in_group_by_test(TEXT, TEXT, TEXT) AS select case when person0_.age between 0 and 10 then 'child' when person0_.age between 11 and 20 then 'teenager' else 'adult' end as col_0_0_ from Person person0_ group by case when person0_.age between 0 and 10 then $1 when person0_.age between 11 and 20 then $2 else $3 end;

Error response:

1 2 ERROR: column "person0_.age" must appear in the GROUP BY clause or be used in an aggregate function LINE 4: when person0_.age between 0 and 10 then 'child'

Error occurs due different methods of literal handling in 'select' and 'group by' query section.
Literals from 'select' case section are handled by:

1 org.hibernate.query.criteria.internal.expression.LiteralExpression#renderProjection

but literals from 'group by' case section are handled by:

1 org.hibernate.query.criteria.internal.expression.LiteralExpression#render

Postgres intreprets that if in the same case section in 'select' and 'group by' are different values (e.g. 'child' and $1) it is not valid query (it needs two same values or literals there e.g. $1 and $1 or 'child' and 'child').

There is a workaround if org.hibernate.query.criteria.LiteralHandlingMode#INLINE is set then generated query is valid.

1 2 3 4 5 6 7 8 9 10 11 12 13 14 select case when person0_.age between 0 and 10 then 'child' when person0_.age between 11 and 20 then 'teenager' else 'adult' end as col_0_0_ from Person person0_ group by case when person0_.age between 0 and 10 then 'child' when person0_.age between 11 and 20 then 'teenager' else 'adult' end

but

from AvailableSettings#CRITERIA_LITERAL_HANDLING_MODE

The org.hibernate.query.criteria.LiteralHandlingMode.INLINE mode will inline literal values as-is. To prevent SQL injection, never use org.hibernate.query.criteria.LiteralHandlingMode.INLINE with String variables.

I also tried to use named parameters in selectCase but there I get other error.

Environment

hibernate-orm 5.2.12
Postgres 9.5
Windows 10 Pro

Status

Assignee

Unassigned

Reporter

Adrian Łukawski

Fix versions

Labels

backPortable

None

Suitable for new contributors

None

Requires Release Note

Affirmative

Pull Request

None

backportDecision

None

Components

Affects versions

5.2.12
5.3.0.Beta1

Priority

Major