Kirill Tkalenko created CALCITE-7662:
----------------------------------------
Summary: Add expression support for OFFSET
Key: CALCITE-7662
URL: https://issues.apache.org/jira/browse/CALCITE-7662
Project: Calcite
Issue Type: Improvement
Reporter: Kirill Tkalenko
Assignee: Kirill Tkalenko
I have not found any objections to the previously submitted proposal [Add
expression for OFFSET ROWS and FETCH FIRST ROWS
ONLY|https://lists.apache.org/thread/xgdgc8mgxh7rjclzblzy3lk5fcjyn1q4]; I
propose implementing it for "OFFSET" in this ticket by analogy with "FETCH" in
CALCITE-7592.
h3. Brief description:
It is proposed to add the ability to use arithmetic expressions and scalar
function calls for "OFFSET", by analogy with other DBMSs.
Currently, it's
{noformat}
[ OFFSET start { ROW | ROWS } ]{noformat}
, but the proposal is to make it
{noformat}
[ OFFSET { start | expression } { ROW | ROWS } ]{noformat}
h3. Query examples:
# select * from person offset (? + 1) rows.
# select * from person offset ? + 1 rows.
# select * from person offset udf(?) rows.
# select * from person offset (1 + nvl(?, 10000)) rows.
h3. DBMSs supporting similar functionality:
# [postgresql|https://www.postgresql.org/docs/18/sql-select.html#SQL-LIMIT]
#
[oracle|https://docs.oracle.com/en/database/oracle/oracle-database/12.2/sqlrf/SELECT.html]
# [h2|https://h2database.com/html/commands.html#select]
#
[msql|https://learn.microsoft.com/ru-ru/sql/t-sql/queries/select-order-by-clause-transact-sql?view=sql-server-ver17#fetch--first--next---integer_constant--fetch_row_count_expression---row--rows--only]
h3. Validation rules:
# Expression should be either arithmetic or scalar.
# Result of the expression should be an BigDecimal and greater than zero.
# Expressions should't use table columns.
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
