MS SQL offset parsing error

[zzehli]

Describe the bug When I query with an non 0 offset, I get the following error:

{
    "error": "Error: Parse error at line: 4, column: 184: Incorrect syntax near 'OFFSET'.",
    "requestId": "0a7bbbea-f722-4fe2-b587-ef30eb619559-span-1"
}

To Reproduce Steps to reproduce the behavior: Cube server is connected with a MS SQL db. A sample query is below:

curl --location 'http://xxxxxxxx:4000/cubejs-api/v1/load' \
--header 'Content-Type: application/json' \
--data '{
    "query": {
        "measures": [
            "transactions.count"
        ],
        "offset": 1
    }
}'

This query will generate the following sql statement when hit /sql endpoint

 "sql": [
            "SELECT\n      count(\"transactions\".transaction_id) \"transactions__count\"\n    FROM\n      analytics_learn.transactions AS \"transactions\"  OFFSET 1 ROWS FETCH NEXT 10000 ROWS ONLY",
            []
        ],

If I set offset to 0, the sql statement becomes

 "sql": [
            "SELECT TOP 10000\n      count(\"transactions\".transaction_id) \"transactions__count\"\n    FROM\n      analytics_learn.transactions AS \"transactions\" ",
            []
        ],

Expected behavior Be able to parse non 0 offset

Version: latest docker image: cubejs/cube:latest

[paveltiunov]

@zzehli Which MSSQL database version do you use?

[zzehli]

@zzehli Which MSSQL database version do you use?

Microsoft Azure SQL Data Warehouse - 10.0.25730.0

[zzehli]

In fact, a similar error happens for total field. If I set total to false, then the query runs fine, but if I set total to true, then I get the following error

{
    "error": "Error: Parse error at line: 2, column: 1: The ORDER BY clause is not valid in views, inline functions, derived tables, sub-queries, and common table expressions, unless TOP or FOR XML is also specified.",
    "requestId": "2162fa8d-3453-4cf0-a8c9-a18853262fac-span-1"
}