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

["Vova Vysotskyi (Jira)" <ji...@apache.org>]

Vova Vysotskyi created CALCITE-4968:
---------------------------------------

             Summary: 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


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)