cube-js / cube-js/cube

Default Refresh Key SQL Seems to Generate Incorrectly for MSSQL

Open
#3,629 1 comment 0 reactions 0 assignees View on GitHub
driver:mssql help wanted
Dominant language
Rust
Stars
20.8k
Forks
2.1k
Avg merge
1d 2h
Merged PRs (30d)
181

Description

**Describe the bug**
When I don't specify a sql parameter in my preaggregation refreshKey, the the default refresh key that seems to be generated is invalid for MSSQL.

**To Reproduce**
Steps to reproduce the behavior:
1. In the .env file, specify `CUBEJS_DB_TYPE=mssql`
2. Setup a preaggregation with the following configuration:
```js
myAggregation: {
refreshKey: {
every: `1 minute`,
updateWindow: `12 week`
},
type: `rollup`,
external: true,
scheduledRefresh: true,
measureReferences: [...],
dimensionReferences: [...],
granularity: `month`,
partitionGranularity: `quarter`,
buildRangeStart: {
sql: `SELECT CAST('2020-10-01' AS DATETIME2)`
},
buildRangeEnd: {
sql: `Select GETDATE()`
}
}
```
3. After spinning up a cube instance, the log output reads as follows:
```
Executing SQL: scheduler-85bb3bf2-32a9-4e30-bc0f-50a68ba092a3
--
SELECT FLOOR((UNIX_TIMESTAMP()) / 60) as refresh_key
--
Executing SQL: scheduler-85bb3bf2-32a9-4e30-bc0f-50a68ba092a3
--
SELECT FLOOR((UNIX_TIMESTAMP()) / 60) as refresh_key
--
Performing query: scheduler-85bb3bf2-32a9-4e30-bc0f-50a68ba092a3
Executing SQL: scheduler-85bb3bf2-32a9-4e30-bc0f-50a68ba092a3
--
SELECT FLOOR((UNIX_TIMESTAMP()) / 60) as refresh_key
```

And, when I instead specify my own sql refreshKey such as `SELECT DATEDIFF(SECOND, '19700101', sysutcdatetime()) / 60`, the logs show this valid MSSQL being executed. I would like to be able to leverage the "incremental" and "updateWindow" capabilities, but as explained in this question: https://github.com/cube-js/cube.js/issues/3585, it's not currently possible to both specify custom sql and leverage these parameters. So in order to be able to use parameters like updateWindow and incremental, the default refreshKey that gets generated needs to be a valid MSSQL statement.

**Expected behavior**
A valid MSSQL query is generated as the default refreshKey. Looking at the MssqlQuery adapater, it seems like the components are all there for the query to be generated correctly, so I'm unsure why the logs are indicating otherwise. The adapter correctly implements valid MSSQL for the unix timestamp functions:
https://github.com/cube-js/cube.js/blob/master/packages/cubejs-schema-compiler/src/adapter/MssqlQuery.js#L111-L118

**Version:**
"@cubejs-backend/cubestore-driver": "0.28.53",
"@cubejs-backend/mssql-driver": "0.28.52",
"@cubejs-backend/server": "0.28.53"

Contributor guide

Open the contributing guide

Assessment

This issue has not been assessed yet.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.