Incremental refreshkey with updateWindow for pre-aggregation generates broken SQL for MSSQL

[florianfischerx]

Describe the bug Incremental refreshkey with updateWindow for pre-aggregation generates broken SQL for MSSQL

To Reproduce

Setting up an incremental update for a pre-aggregation like this (ignore bogus values):

 somePreaggregation: {
            type: `rollup`,
            measureReferences: [somecount],
            timeDimensionReference: timestamp,
            granularity: `minute`,
            partitionGranularity: `day`,
            scheduledRefresh: true,
            refreshKey: {
                every: `2 day`,
                incremental: true,
                updateWindow: `7 day`,
           }

Results in the following output:

Error while querying: 88b92be5-076a-4140-8e65-9b5f6bfa6e1a-span-1 (63ms)
{
  "processingId": 1,
  "queueSize": 1,
  "queryKey": [
    "SELECT CASE\n    WHEN CURRENT_TIMESTAMP < CAST(@_1 AS DATETIME) + interval '7 day' THEN FLOOR((DATEDIFF(SECOND,'1970-01-01', GETUTCDATE())) / 86400) END",
    [
      "2021-02-28T23:59:59Z"
    ]
  ],
  "queuePrefix": "SQL_QUERY_STANDALONE_default",
  "timeInQueue": 3
} 
RequestError: Incorrect syntax near '7 day'.
    at handleError (/home/fisf/Code/swarm-cube-api/node_modules/mssql/lib/tedious/request.js:374:15)
    at Connection.emit (events.js:311:20)

The problem is that a query as following is generated:

SELECT 
  CASE 
    WHEN CURRENT_TIMESTAMP < Cast('2021-02-28T23:59:59Z' AS DATETIME) + interval '7 day' THEN floor((datediff(second,'1970-01-01', getutcdate())) / 86400) 
  END

But "interval" is not valid inMssqlMSSQL (I think?). This happens because addInterval, subtractInterval are not overridden for Mssql.

I believe something like https://github.com/hal9000-swarm/cube.js/blob/15a25990ebb172c4cb4066f591bbbf543bc082c5/packages/cubejs-schema-compiler/src/adapter/MssqlQuery.js#L30 is needed here.

Expected behavior Generate a query like:

SELECT
  CASE 
    WHEN CURRENT_TIMESTAMP < Cast('2021-02-28T23:59:59Z' AS DATETIME) + Dateadd(day, 7, '2021-02-28T23:59:59Z')THEN Floor((Datediff(second, '1970-01-01', Getutcdate()) ) / 86400)
  END

by implementing addInterval, subtractInterval

[github-actions[bot]]

If you are interested in working on this issue, please leave a comment below and we will be happy to assign the issue to you. If this is the first time you are contributing a Pull Request to Cube.js, please check our contribution guidelines. You can also post any questions while contributing in the #contributors channel in the Cube.js Slack.

[florianfischerx]

Above should fix this minor problem.