[jira] [Closed] (CALCITE-4968) Calcite generates invalid MS SQL queries for the query with the LIMIT statement

["Liya Fan (Jira)" <ji...@apache.org>]

     [ https://issues.apache.org/jira/browse/CALCITE-4968?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]

Liya Fan closed CALCITE-4968.
-----------------------------

Resolved in release 1.30.0 (2022-03-20)

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