strange group by error occuring SOMETIMES

Question

I "SOMETIMES" get the below exception on my server logs when executing the query. Query has a group by statement. If i remove the GROUP BY i never get the exception. Also not all queries with group by get the exception. Only some of them are victims.

I have two server in cluster and a client node.

Is there any problem with the query or am i hitting to a bug because the exception occurs randomly. The strange thing is that not all the queries got this exception. I think the query structure is OK.

Caused by: org.h2.jdbc.JdbcSQLException: Column "PR__Z0._VAL" must be in the GROUP BY list; SQL statement: 
SELECT 
PR__Z0._VAL __C0_0, 
PR__Z0."dailyRock" __C0_1, 
PR__Z0."productId" __C0_2 
FROM TABLE("category" CHAR=?1) T__Z2 
 INNER JOIN ACC_6185BAEEDA626000."ProductCategory" PC__Z1 
 ON TRUE 
 INNER JOIN ACC_6185BAEEDA626000."ProductRecord" PR__Z0 
 ON TRUE 
WHERE (PC__Z1."language" = ?2) AND ((PC__Z1."category" = T__Z2."category") AND (PR__Z0."productId" = PC__Z1."productId")) 
GROUP BY PR__Z0."productId" 
ORDER BY 2 DESC LIMIT ?3 + ?4 [90016-197] 
        at org.h2.message.DbException.getJdbcSQLException(DbException.java:357) 
        at org.h2.message.DbException.get(DbException.java:179) 
        at org.h2.message.DbException.get(DbException.java:155) 
        at org.h2.expression.ExpressionColumn.updateAggregate(ExpressionColumn.java:172) 
        at org.h2.expression.Alias.updateAggregate(Alias.java:87) 
        at org.h2.command.dml.Select.queryGroup(Select.java:350) 
        at org.h2.command.dml.Select.queryWithoutCache(Select.java:628) 
        at org.h2.command.dml.Query.queryWithoutCacheLazyCheck(Query.java:114) 
        at org.h2.command.dml.Query.query(Query.java:352) 
        at org.h2.command.dml.Query.query(Query.java:333) 
        at org.h2.command.CommandContainer.query(CommandContainer.java:114) 
        at org.h2.command.Command.executeQuery(Command.java:202) 
        at org.h2.jdbc.JdbcPreparedStatement.executeQuery(JdbcPreparedStatement.java:114) 
        at org.apache.ignite.internal.processors.query.h2.PreparedStatementExImpl.executeQuery(PreparedStatementExImpl.java:67) 
        at org.apache.ignite.internal.processors.query.h2.IgniteH2Indexing.executeSqlQuery(IgniteH2Indexing.java:1421) 
        ... 7 more

score 0 · Answer 1 · answered Dec 28 '18 at 10:08

See H2 DB - Column must be in Group By list.

Apache Ignite is strict in this sence where you are only allowed to put naked values in SELECT list that you also put in GROUP BY lisT:

SELECT V1, V2, V3 FROM table GROUP BY V1, V2, V3 <-- if you remove either V1, V2 or V3 from GROUP BY == error

All other values need to be wrapped in an aggregate function.

As in SELECT MAX(_VAL), AVG("dailyRock"), "productId" ... GROUP BY "productId";

strange group by error occuring SOMETIMES

1 Answers1