[
https://issues.apache.org/jira/browse/CALCITE-7662?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=18097610#comment-18097610
]
Julian Hyde commented on CALCITE-7662:
--------------------------------------
Expressions in OFFSET/FETCH/LIMIT should be allowed if (and only if) they are
constant for that query (or subquery). Obviously constants are constant, and
query parameters are constant, and pure functions on constants are constant.
But columns are constant if they come from an enclosing query (i.e. are
correlations).
Do this the right way, and validate using some notion of "constant within the
current query".
> 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
> Priority: Major
>
> 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)
