[
https://issues.apache.org/jira/browse/CALCITE-4968?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17468146#comment-17468146
]
Julian Hyde commented on CALCITE-4968:
--------------------------------------
Since this case doesn't spell out the new/desired behavior, let me do it. After
the fix Calcite, for a query with fetch but no offset, generates a query using
{{TOP \(n)}}:
{code}
SELECT TOP (1)
FROM [foodmart].[employee]
{code}
If there is both fetch and offset, on MSSQL, Calcite now generates "OFFSET ...
FETCH":
{code}
SELECT *
FROM [foodmart].[employee]
OFFSET 1 ROWS
FETCH NEXT 1 ROWS ONLY
{code}
On Sybase, Calcite now generates "TOP ... START AT":
{code}
SELECT TOP (1) START AT 1
FROM foodmart.employee
{code}
> Calcite generates invalid MS SQL queries for the query with the LIMIT
> statement
> -------------------------------------------------------------------------------
>
> Key: CALCITE-4968
> URL: https://issues.apache.org/jira/browse/CALCITE-4968
> Project: Calcite
> Issue Type: Bug
> Reporter: Vova Vysotskyi
> Assignee: Vova Vysotskyi
> Priority: Major
> Labels: pull-request-available
> Fix For: 1.30.0
>
> Time Spent: 40m
> Remaining Estimate: 0h
>
> Calcite converts the following query
> {code:sql}
> SELECT * FROM "employee" LIMIT 1
> {code}
> to the following one for {{MssqlSqlDialect}}:
> {code:sql}
> SELECT *
> FROM [foodmart].[employee]
> FETCH NEXT 1 ROWS ONLY
> {code}
> The issue is that MS SQL Server allows using {{FETCH}} (and {{OFFSET}})
> statements only when {{ORDER BY}} is specified, so the query above fails.
> One of the workarounds for this limitation is to insert {{ORDER BY (SELECT
> NULL)}} for the case when no actual {{ORDER BY}} statement is specified.
--
This message was sent by Atlassian Jira
(v8.20.1#820001)
