Add support for LIMIT and OFFSET on AQL syntax

Description

Currently AQL only supports TOP, which is based on the keyword supported by SQLServer to limit the number of results, but current AQL doesn't support an offset to be able to paginate results.

Checking keywords supported by different DB vendors, and also by different ANSI SQL specs, ANSI only added support for this on the latest versions, but DB vendors have their own pagination keywords.

ANSI: uses OFFSET offset FETCH FIRST max

Full spec from shared by Pieter on Slack from ANSI SQL 2011:

<result offset clause> ::=
OFFSET <offset row count> { ROW | ROWS }
<fetch first clause> ::=
FETCH { FIRST | NEXT } [ <fetch first quantity> ] { ROW | ROWS } { ONLY | WITH TIES }
<fetch first quantity> ::=
<fetch first row count>

<fetch first percentage>

DB2 – select * from table fetch first 10 rows only
Informix – select first 10 * from table
Microsoft SQL Server and Access – select top 10 * from table
MySQL and PostgreSQL – select * from table limit 10
Oracle 8i – select * from (select * from table) where rownum <= 10
Later versions of SQLServer support OFFSET 234 ROWS FETCH NEXT 10 ROWS ONLY

IMO the most readable is LIMIT/OFFSET:

SELECT ...
FROM ...
WHERE ...
LIMIT 10 OFFSET 0

I guess we need to vote.

Environment

None

Reporter

Pablo Pazos

Labels

None

Components

Priority

Major
Configure