Semantically wrong query is executed by H2 when LIMIT 0 is used

[lukaseder]

With this schema:

CREATE TABLE t (a int);
INSERT INTO t VALUES (1),(2);

The following query is semantically wrong, but executed nonetheless:

SELECT a
FROM t
HAVING TRUE 
LIMIT 0

It produces an empty result set with column A. It should produce the same error as this query:

SELECT a
FROM t
HAVING TRUE 
LIMIT 1

I.e.:

SQL Error [90016] [90016]: Column "A" must be in the GROUP BY list; SQL statement:
SELECT a
FROM t
HAVING TRUE 
LIMIT 1 [90016-220]

[katzyn]

This is a side effect of partially implemented optional feature T301, “Functional dependencies” in combination with optimizations for OFFSET / FETCH clauses.

Some applications execute additional queries with FETCH FIRST 0 ROWS ONLY / LIMIT 0 / TOP 0 before actual execution of a query, so presence of this optimization is performance-critical. Because the SQL Standard explicitly forbids values less than 1 in <fetch first row count>, the whole such query is not compliant with the Standard, DBMS should throw data exception — invalid row count in fetch first clause exception. In our case we historically allow 0 here and this value means that an application or a user wants to perform basic syntax checks and get actual columns with data types returned by a query. Perhaps it should be documented somewhere.

[manticore-projects]

Thanks @katzyn, again I have learned something!