By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement . We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Try the following in h2 vs SQLite. All will pass in SQLite, but some will the last one will fail in h2:

create table if not exists test(id integer, value integer);
select id, value from test;
select id, max(value) from test group by id;
select id, max(value) from test;

Failure:
Column "ID" must be in the GROUP BY list; SQL statement:
select id, max(value) from test [90016-147] 90016/90016

My example above is bad because it is based on a buggy SQL statement, however, it still highlights a difference in the accepted syntax for h2 vs SQLite. See below for an explanation of how aggregate functions are handled in SQLite with no GROUP BY clause is specified:

From: http://www.sqlite.org/lang_select.html#resultset

"If the SELECT statement is an aggregate query without a GROUP BY clause, then each aggregate expression in the result-set is evaluated once across the entire dataset. Each non-aggregate expression in the result-set is evaluated once for an arbitrarily selected row of the dataset. The same arbitrarily selected row is used for each non-aggregate expression. Or, if the dataset contains zero rows, then each non-aggregate expression is evaluated against a row consisting entirely of NULL values.

The single row of result-set data created by evaluating the aggregate and non-aggregate expressions in the result-set forms the result of an aggregate query without a GROUP BY clause. An aggregate query without a GROUP BY clause always returns exactly one row of data, even if there are zero rows of input data."