Commented by: @mrotteveel

By the looks of it, this would be "as simple" (ignoring potential parser problems, FIRST, NEXT valueless and ROW/ROWS, ONLY/WITH TIES) as adding to parse.y for rows_clause:

\| OFFSET value
	\{
		$$ = newNode<RowsClause\>\(\);
		$$\-\>skip = $2;
	\}
\| FETCH value
	\{
		$$ = newNode<RowsClause\>\(\);
		$$\-\>length = $2;
	\}
\| OFFSET value FETCH value
	\{
		$$ = newNode<RowsClause\>\(\);
                    $$\-\>skip = $2;
		$$\-\>length = $4;
	\}

Commented by: @mrotteveel

Note that this quick fix also ignores the fact that according to SQL:2011 the <result offset clause> and <fetch first clause> are also allowed in subqueries, while the rows_clause is only defined for a statement level select (select_expr), instead it should be defined on the level of query_spec.

Commented by: Lukas Eder (lukas.eder)

As far as OFFSET and FETCH are concerned, yes, they're allowed (and very useful) in subqueries. A good use-case is, for instance:

SELECT \*
FROM table1
WHERE \(a, b\) = \(SELECT x, y FROM table2 ORDER BY x, y FETCH FIRST 1 ROW ONLY\)

When row value expressions are involved, finding the "MIN" or "MAX" row is hard to implement in any other way.

Commented by: @mrotteveel

Implemented, will land in Firebird 3 beta 2.

Note that my comment of the 22nd of August was wrong, it also works in subqueries.

Commented by: @sim1984

why the new syntax does not work with subqueries?

select *
from color
order by name
fetch first (select 4 from rdb$database) rows only

Invalid token.
Dynamic SQL Error.
SQL error code = -104.
Token unknown - line 4, column 13.
(.

While the following query works correctly

select *
from color
order by name
rows (select 4 from rdb$database)

Commented by: @dyemanov

The standard does not allow complex expressions there, only constants and variables/parameters are allowed. We decided to follow the SQL specification and see how soon people will notice that limitation and ask us to support complex expressions there. Can you think about a real need for subqueries there or just noticed the difference with ROWS while testing?

Commented by: @mrotteveel

That is documented in README.offset_fetch.txt ("5. Expressions, column references, etc are not allowed within either clause."). The primary reason is that the SQL standard doesn't define this for OFFSET and FETCH (it only allows a simple value specification, meaning a literal, parameter (DSQL) or variable (PSQL).

Technically it could of course be allowed by changing the syntax, but in general OFFSET and FETCH is for clientside paging or fixed limit (eg FETCH FIRST ROW ONLY), and there is in my mind (and the standards committee seems to agree) no good reason to allow expressions etc here.

Commented by: @sim1984

I read 5 points. I understand that the standard provides only simple expressions, but we could extend these clause.
In fact, as said Dmitry, when testing, I found that the new syntax does not take as arguments subqueries. Personally I do not use subqueries in expressions limit rows, but those who use it may deter from the transition to the new syntax. Of course the old syntax is still supported and backward compatibility is present.