Presto: Add mysql-style LIMIT support to Presto

[q977734161]

[kokosing]

Can you please give an example of query? Is this ANSI compliant syntax?

[findepi]

Whenever want a query like

SELECT * FROM some_table
LIMIT 5 OFFSET 5 -- this doesn't work, obviously

you can do the following (which is standard SQL syntax and thus should work in most databases, not only in Presto):

SELECT * FROM (
    SELECT row_number() over() AS rn, * FROM some_table)
WHERE rn BETWEEN 5 AND 10;

However, you should use this construct judiciously. Let me quote @electrum from https://stackoverflow.com/a/45114359/65458

Using OFFSET for pagination is very inefficient, especially for an analytic database like Presto that often has to perform a full table or partition scan. Additionally, the results will not necessarily be consistent between queries, so you can have duplicate or missing results when navigating between pages.

In an OLTP database like MySQL or PostgreSQL, it's better to use a range query over an index, where you keep track of the last value seen on the previous page.

In an OLAP database like Presto, it's better to cache the result set and perform pagination using the cached data. You don't want to run an expensive query over billions or trillions of rows each time the user clicks to go to a different page.

See these articles for a longer explanation of the problem and the index approach:

http://use-the-index-luke.com/no-offset
http://use-the-index-luke.com/sql/partial-results/fetch-next-page

[martint]

OFFSET + LIMIT should not be used for pagination, as explained above, but it has legitimate uses (e.g., getting the middle of the pack from a histogram).

In terms of syntax, this is what the spec says:

A <query expression> can also optionally contain a <result offset clause>, 
which may limit the cardinality of the derived table by removing a specified 
number of rows from the beginning of the derived table. If a <query expression> 
contains both an <order by clause> and a <result offset clause>, then the rows 
in the derived table are first sorted according to the <order by clause> and then
 limited by dropping the number of rows specified in the <result offset clause> 
from the beginning of the result produced by the <query expression>. If the 
cardinality of the result of an evaluation of a <query expression> is less than
 the offset value specified by a <result offset clause>, then the derived table is empty.

And

<query expression> ::=
  [ <with clause> ] <query expression body>
      [ <order by clause> ] [ <result offset clause> ] [ <fetch first clause> ]

<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>

<offset row count> ::=
  <simple value specification>

<fetch first row count> ::=
  <simple value specification>

<fetch first percentage> ::=
  <simple value specification> PERCENT

[electrum]

PostgreSQL supports the standard syntax: https://www.postgresql.org/docs/11/sql-select.html#SQL-LIMIT

[q977734161]

Ok,I got it.Thanks for your suggestion!

[mbasmanova]

@q977734161 Based on your last comment I assume this issue is resolved, hence, closing. Feel free to reopen if there is anything else left to discuss.