Column "XYZ" must be in the GROUP BY list; SQL Error: 90016

Description

(Originally posted here: https://github.com/h2database/h2database/issues/2997

I'm seeing the following issue when running a custom query using the CriteriaBuilder in Spring Boot (Data/JPA). It appears to fail because of the ORDER BY statement I added. (H2 1.4.200)

The query that gets printed in the logs works just fine in DataGrip with H2:

I can also run the query as a native query:

However, the CriteriaBuilder will complain:

I do apply the instr function to the contributors:

Exception I'm seeing:

> Column "DOCUMENT0_.NAME" must be in the GROUP BY list; SQL statement:
select INSTR(document0_.name, '/', 1) as col_0_0_ from ds_document document0_ group by INSTR(document0_.name, '/', 1) order by INSTR(document0_.name, ?, 1) asc [90016-200]

….
at org.h2.message.DbException.getJdbcSQLException(DbException.java:576) ~[h2-1.4.200.jar:1.4.200]
at org.h2.message.DbException.getJdbcSQLException(DbException.java:429) ~[h2-1.4.200.jar:1.4.200]
at org.h2.message.DbException.get(DbException.java:205) ~[h2-1.4.200.jar:1.4.200]
at org.h2.message.DbException.get(DbException.java:181) ~[h2-1.4.200.jar:1.4.200]
at org.h2.expression.ExpressionColumn.updateAggregate(ExpressionColumn.java:182) ~[h2-1.4.200.jar:1.4.200]
at org.h2.expression.function.Function.updateAggregate(Function.java:3049) ~[h2-1.4.200.jar:1.4.200]
at org.h2.command.dml.Select.updateAgg(Select.java:545) ~[h2-1.4.200.jar:1.4.200]
at org.h2.command.dml.Select.gatherGroup(Select.java:525) ~[h2-1.4.200.jar:1.4.200]
at org.h2.command.dml.Select.queryGroup(Select.java:487) ~[h2-1.4.200.jar:1.4.200]
at org.h2.command.dml.Select.queryWithoutCache(Select.java:839) ~[h2-1.4.200.jar:1.4.200]
at org.h2.command.dml.Query.queryWithoutCacheLazyCheck(Query.java:201) ~[h2-1.4.200.jar:1.4.200]
at org.h2.command.dml.Query.query(Query.java:489) ~[h2-1.4.200.jar:1.4.200]
at org.h2.command.dml.Query.query(Query.java:451) ~[h2-1.4.200.jar:1.4.200]
at org.h2.command.CommandContainer.query(CommandContainer.java:285) ~[h2-1.4.200.jar:1.4.200]
at org.h2.command.Command.executeQuery(Command.java:195) ~[h2-1.4.200.jar:1.4.200]

at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.extract(ResultSetReturnImpl.java:57) ~[hibernate-core-5.4.21.Final.jar:5.4.21.Final] at org.hibernate.loader.Loader.getResultSet(Loader.java:2341) ~[hibernate-core-5.4.21.Final.jar:5.4.21.Final] at org.hibernate.loader.Loader.executeQueryStatement(Loader.java:2094) ~[hibernate-core-5.4.21.Final.jar:5.4.21.Final] at org.hibernate.loader.Loader.executeQueryStatement(Loader.java:2056) ~[hibernate-core-5.4.21.Final.jar:5.4.21.Final] at org.hibernate.loader.Loader.doQuery(Loader.java:953) ~[hibernate-core-5.4.21.Final.jar:5.4.21.Final] at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:350) ~[hibernate-core-5.4.21.Final.jar:5.4.21.Fi

Comment from a contributor of the github project:

It looks like in expression passed to OrderImpl literals are replaced with parameters for a some weird reason. This issue is not related with H2 in any way. Some DBMS may support such queries because they compile them only when parameters are already set and recompile when they are changed, but H2 compiles queries and other commands immediately for performance reasons and they must be valid as is (with not yet assigned parameters).

Environment

None

Activity

Show:
Christian Beikov
January 8, 2021, 8:14 AM

This is fixed in 6 already.

Daniel Langerenken
January 8, 2021, 5:01 PM

Is there any workaround for now?

Christian Beikov
January 8, 2021, 6:37 PM

Use HQL. There you are in control of whether a literal or parameter ends up in the group by expression.

Daniel Langerenken
January 8, 2021, 8:37 PM

Thanks Christian. I found another workaround. Given that I always use '/' for my instr(), I can just hardcode it into the function such as:

Fixed

Assignee

Christian Beikov

Reporter

Daniel Langerenken

Fix versions

Labels

None

backPortable

None

Suitable for new contributors

None

Requires Release Note

None

Pull Request

None

backportDecision

None

Components

Affects versions

Priority

Major
Configure