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

(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).