[
https://issues.apache.org/jira/browse/CALCITE-7592?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Kirill Tkalenko updated CALCITE-7592:
-------------------------------------
Description:
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 "FETCH" in this ticket.
h3. Brief description:
It is proposed to add the ability to use arithmetic expressions and scalar
function calls for "FETCH" only when enclosed in parentheses, by analogy with
other DBMSs.
Currently, it's
{noformat}
[ FETCH { FIRST | NEXT } [ count ] { ROW | ROWS } ONLY ]{noformat}
, but the proposal is to make it
{noformat}
[ FETCH { FIRST | NEXT } [ count | (expression) ] { ROW | ROWS } ONLY
]{noformat}
.
h3. Query examples:
# select * from person fetch next (? + 1) rows only.
# select * from person fetch next (udf(?)) rows only.
# select * from person fetch next (1 + nvl(?, 10000)) rows only.
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 integer and range from 0 to
Integer.MAX_VALUE.
# Expressions should't use table columns.
was:
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 "FETCH" in this ticket.
h2. Brief description:
It is proposed to add the ability to use arithmetic expressions and scalar
function calls for "FETCH" only when enclosed in parentheses, by analogy with
other DBMSs.
Currently, it's
{noformat}
[ FETCH { FIRST | NEXT } [ count ] { ROW | ROWS } ONLY ]{noformat}
, but the proposal is to make it
{noformat}
[ FETCH { FIRST | NEXT } [ count | (expression) ] { ROW | ROWS } ONLY
]{noformat}
.
h2. Query examples:
# select * from person fetch next (? + 1) rows only.
# select * from person fetch next (udf(?)) rows only.
# select * from person fetch next (1 + nvl(?, 10000)) rows only.
h2. 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]
> Add expression support for FETCH
> --------------------------------
>
> Key: CALCITE-7592
> URL: https://issues.apache.org/jira/browse/CALCITE-7592
> Project: Calcite
> Issue Type: Improvement
> Reporter: Kirill Tkalenko
> Priority: Major
> Labels: pull-request-available
>
> 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 "FETCH" in this ticket.
> h3. Brief description:
> It is proposed to add the ability to use arithmetic expressions and scalar
> function calls for "FETCH" only when enclosed in parentheses, by analogy with
> other DBMSs.
> Currently, it's
> {noformat}
> [ FETCH { FIRST | NEXT } [ count ] { ROW | ROWS } ONLY ]{noformat}
> , but the proposal is to make it
> {noformat}
> [ FETCH { FIRST | NEXT } [ count | (expression) ] { ROW | ROWS } ONLY
> ]{noformat}
> .
> h3. Query examples:
> # select * from person fetch next (? + 1) rows only.
> # select * from person fetch next (udf(?)) rows only.
> # select * from person fetch next (1 + nvl(?, 10000)) rows only.
> 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 integer and range from 0 to
> Integer.MAX_VALUE.
> # Expressions should't use table columns.
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
