Lambda Preaggregations inconsitently fail with mssql
- Dominant language
- Rust
- Stars
- 20.8k
- Forks
- 2.1k
- Avg merge
- 1d 2h
- Merged PRs (30d)
- 181
Description
**Describe the bug**
When using lambda preaggregations with mssql for non-trivial queries (happens at least for queries with few 100ms execution time somewhat consistently) the query returns an error with message "Canceled"
**To Reproduce**
Very inconsistently reproducable.
Used cube definition and table data below and loaded counts so that lambda-preaggregation gets triggered. Sometimes produced described error.
**Expected behavior**
Data is consistently returned
**Screenshots**

**Minimally reproducible Cube Schema**
In case your bug report is data modelling related please put your minimally reproducible Cube Schema here.
You can use selects without tables in order to achieve that as follows.
```javascript
cube(`CrossingEvents`, {
title: `Counting Line`,
description: `Counting Line related information`,
sql: `SELECT timestamp,
class,
subclass
FROM eventdata.timestamp_events_test (NOLOCK)
`,
rewriteQueries: true,
refreshKey: {
every: `1 minute`,
},
measures: {
count: {
type: `count`,
title: `Count`,
description: `Count`
}
},
dimensions: {
timestamp: {
title: `Timestamp`,
description: `Time of event generation`,
sql: `timestamp`,
type: `time`
},
classification: {
title: `Class`,
description: `Classification of an object`,
sql: `class`,
type: `string`
},
subclass: {
title: `Subclass`,
description: `Fine grained classification of an object`,
sql: `subclass`,
type: `string`
}
},
preAggregations: {
lambda: {
type: `rollup_lambda`,
union_with_source_data: true,
rollups: [CUBE.attributesByHour]
},
attributesByHour: {
type: `rollup`,
measures: [CrossingEvents.count],
timeDimension: CrossingEvents.timestamp,
dimensions: [
'CrossingEvents.classification',
'CrossingEvents.subclass'
],
granularity: `hour`,
partitionGranularity: `day`,
buildRangeStart: {
sql: `SELECT CONVERT(datetime2, '2023-09-01 00:00:00.0000000')`
},
buildRangeEnd: {
// select 2023-10-03 in mssql datetime2 format
sql: `SELECT CONVERT(datetime2, '2023-10-03 00:00:00.0000000')`
},
refreshKey: {
every: `2 hours`,
incremental: true,
updateWindow: `1 day`,
}
}
}
});
```
DDL for table:
```sql
CREATE TABLE eventdata.timestamp_events_test
(
timestamp DATETIME2,
class NVARCHAR,
subclass NVARCHAR
)
```
Query to create entries in db (Issue is likely dependant on query-runtimes and therfore computational power of the server but - for me i had to run multiple times to get around 2 million rows to reproduce issue with this minimal example):
```sql
WITH DateCTE AS (
SELECT @StartDate AS CurrentDate
UNION ALL
SELECT DATEADD(SECOND, 1, CurrentDate)
FROM DateCTE
WHERE CurrentDate < @EndDate
)
INSERT INTO eventdata.timestamp_events_test (timestamp)
SELECT CurrentDate
FROM DateCTE
OPTION (MAXRECURSION 0);
```
Query to also set class/subclass values (in order to slow query down even mor by ordering by):
```sql
WITH NumberedEvents AS (
SELECT *,
ROW_NUMBER() OVER (ORDER BY timestamp) AS RowNum
FROM eventdata.timestamp_events_test
)
UPDATE NumberedEvents
SET class = CAST(((RowNum - 1) % 3 + 1) AS NVARCHAR(1)),
subclass = CAST(((RowNum - 1) % 5 + 1) AS NVARCHAR(1));
```
**Version:**
[e.g. 0.34.0]
**Additional context**
Stacktraces from cube-api:
```
{
2023-10-04T10:34:08.117044207Z "queueId": 1357,
2023-10-04T10:34:08.117052239Z "processingId": "63",
2023-10-04T10:34:08.117060311Z "queueSize": 0,
2023-10-04T10:34:08.117067246Z "queryKey": [
2023-10-04T10:34:08.117077807Z "SELECT TOP 10000 ...",
2023-10-04T10:34:08.117139673Z [
2023-10-04T10:34:08.117147246Z "2023-10-03T00:00:00.000Z"
2023-10-04T10:34:08.117155276Z ]
2023-10-04T10:34:08.117161791Z ],
2023-10-04T10:34:08.117169175Z "queuePrefix": "SQL_QUERY_CUBEJS_APP_abcde_default",
2023-10-04T10:34:08.117177508Z "timeInQueue": 9,
2023-10-04T10:34:08.117185465Z "addedToQueueTime": 1696415647255
2023-10-04T10:34:08.117193386Z }
2023-10-04T10:34:08.117200606Z RequestError: Canceled.
2023-10-04T10:34:08.117208777Z at Request.userCallback (/cube/node_modules/mssql/lib/tedious/request.js:437:19)
2023-10-04T10:34:08.117216664Z at Request.callback (/cube/node_modules/tedious/lib/request.js:56:14)
2023-10-04T10:34:08.117223560Z at Connection.message (/cube/node_modules/tedious/lib/connection.js:2451:24)
2023-10-04T10:34:08.117231066Z at Connection.dispatchEvent (/cube/node_modules/tedious/lib/connection.js:1279:15)
2023-10-04T10:34:08.117238806Z at MessageIO. (/cube/node_modules/tedious/lib/connection.js:1139:14)
2023-10-04T10:34:08.117246544Z at MessageIO.emit (node:events:513:28)
2023-10-04T10:34:08.117253791Z at Message. (/cube/node_modules/tedious/lib/message-io.js:46:14)
2023-10-04T10:34:08.117261270Z at Message.emit (node:events:525:35)
2023-10-04T10:34:08.117268655Z at endReadableNT (/cube/node_modules/readable-stream/lib/_stream_readable.js:1094:12)
2023-10-04T10:34:08.117276904Z at processTicksAndRejections (node:internal/process/task_queues:83:21)
2023-10-04T10:34:08.121410910Z Dropping Cache: 1d327e57-6b7f-4853-9d66-c47536b6bb02-span-1
2023-10-04T10:34:08.121461144Z {
2023-10-04T10:34:08.121468189Z "cacheKey": [
2023-10-04T10:34:08.121475738Z "SELECT TOP 10000...",
2023-10-04T10:34:08.121512564Z [
2023-10-04T10:34:08.121520612Z "2023-10-03T00:00:00.000Z"
2023-10-04T10:34:08.121525636Z ]
2023-10-04T10:34:08.121530997Z ],
2023-10-04T10:34:08.121536131Z "spanId": "9f6146ca528935d66e15b1a1032b21cb",
2023-10-04T10:34:08.121541760Z "primaryQuery": true
2023-10-04T10:34:08.121552600Z }
2023-10-04T10:34:08.121560546Z Error: Canceled.
2023-10-04T10:34:08.121568552Z at QueryQueue.parseResult (/cube/node_modules/@cubejs-backend/query-orchestrator/src/orchestrator/QueryQueue.js:368:13)
2023-10-04T10:34:08.121576468Z at QueryQueue.executeInQueue (/cube/node_modules/@cubejs-backend/query-orchestrator/src/orchestrator/QueryQueue.js:340:19)
2023-10-04T10:34:08.121585125Z at processTicksAndRejections (node:internal/process/task_queues:96:5)
2023-10-04T10:34:08.121593496Z at /cube/node_modules/@cubejs-backend/query-orchestrator/src/orchestrator/QueryCache.ts:745:17
2023-10-04T10:34:08.121755605Z at PreAggregationPartitionRangeLoader.downloadLambdaTable (/cube/node_modules/@cubejs-backend/query-orchestrator/src/orchestrator/PreAggregations.ts:1763:22)
2023-10-04T10:34:08.121876830Z at PreAggregationPartitionRangeLoader.loadPreAggregations (/cube/node_modules/@cubejs-backend/query-orchestrator/src/orchestrator/PreAggregations.ts:1699:25)
2023-10-04T10:34:08.121959218Z at preAggregationPromise (/cube/node_modules/@cubejs-backend/query-orchestrator/src/orchestrator/PreAggregations.ts:2168:30)
2023-10-04T10:34:08.122074952Z at QueryOrchestrator.fetchQuery (/cube/node_modules/@cubejs-backend/query-orchestrator/src/orchestrator/QueryOrchestrator.ts:241:9)
2023-10-04T10:34:08.122144389Z at OrchestratorApi.executeQuery (/cube/node_modules/@cubejs-backend/server-core/src/core/OrchestratorApi.ts:98:20)
2023-10-04T10:34:08.122230010Z at /cube/node_modules/@cubejs-backend/api-gateway/src/gateway.ts:1434:21
2023-10-04T10:34:08.122257192Z at async Promise.all (index 0)
2023-10-04T10:34:08.122271898Z at ApiGateway.getSqlResponseInternal (/cube/node_modules/@cubejs-backend/api-gateway/src/gateway.ts:1432:31)
2023-10-04T10:34:08.122284354Z at /cube/node_modules/@cubejs-backend/api-gateway/src/gateway.ts:1610:28
2023-10-04T10:34:08.122327300Z at async Promise.all (index 0)
2023-10-04T10:34:08.122399717Z at ApiGateway.load (/cube/node_modules/@cubejs-backend/api-gateway/src/gateway.ts:1601:23)
2023-10-04T10:34:08.122482140Z at /cube/node_modules/@cubejs-backend/api-gateway/src/gateway.ts:248:7
2023-10-04T10:34:08.122523419Z Error querying db: 1d327e57-6b7f-4853-9d66-c47536b6bb02-span-1
2023-10-04T10:34:08.122536139Z --
2023-10-04T10:34:08.122544135Z "SELECT ..."
2023-10-04T10:34:08.122552475Z --
2023-10-04T10:34:08.122556733Z {
2023-10-04T10:34:08.122561071Z "params": [
2023-10-04T10:34:08.122565683Z "2023-10-01T00:00:00.000",
2023-10-04T10:34:08.122570673Z "2023-10-31T23:59:59.999"
2023-10-04T10:34:08.122575790Z ]
2023-10-04T10:34:08.122594335Z }
2023-10-04T10:34:08.122598853Z Error: Canceled.
2023-10-04T10:34:08.122606014Z at QueryQueue.parseResult (/cube/node_modules/@cubejs-backend/query-orchestrator/src/orchestrator/QueryQueue.js:368:13)
2023-10-04T10:34:08.122613366Z at QueryQueue.executeInQueue (/cube/node_modules/@cubejs-backend/query-orchestrator/src/orchestrator/QueryQueue.js:340:19)
2023-10-04T10:34:08.122621245Z at processTicksAndRejections (node:internal/process/task_queues:96:5)
2023-10-04T10:34:08.122626434Z at /cube/node_modules/@cubejs-backend/query-orchestrator/src/orchestrator/QueryCache.ts:745:17
2023-10-04T10:34:08.122631295Z at PreAggregationPartitionRangeLoader.downloadLambdaTable (/cube/node_modules/@cubejs-backend/query-orchestrator/src/orchestrator/PreAggregations.ts:1763:22)
2023-10-04T10:34:08.122636583Z at PreAggregationPartitionRangeLoader.loadPreAggregations (/cube/node_modules/@cubejs-backend/query-orchestrator/src/orchestrator/PreAggregations.ts:1699:25)
2023-10-04T10:34:08.122641483Z at preAggregationPromise (/cube/node_modules/@cubejs-backend/query-orchestrator/src/orchestrator/PreAggregations.ts:2168:30)
2023-10-04T10:34:08.122646466Z at QueryOrchestrator.fetchQuery (/cube/node_modules/@cubejs-backend/query-orchestrator/src/orchestrator/QueryOrchestrator.ts:241:9)
2023-10-04T10:34:08.122655231Z at OrchestratorApi.executeQuery (/cube/node_modules/@cubejs-backend/server-core/src/core/OrchestratorApi.ts:98:20)
2023-10-04T10:34:08.122662028Z at /cube/node_modules/@cubejs-backend/api-gateway/src/gateway.ts:1434:21
2023-10-04T10:34:08.122667408Z at async Promise.all (index 0)
2023-10-04T10:34:08.122671885Z at ApiGateway.getSqlResponseInternal (/cube/node_modules/@cubejs-backend/api-gateway/src/gateway.ts:1432:31)
2023-10-04T10:34:08.122676547Z at /cube/node_modules/@cubejs-backend/api-gateway/src/gateway.ts:1610:28
2023-10-04T10:34:08.122682258Z at async Promise.all (index 0)
2023-10-04T10:34:08.122689762Z at ApiGateway.load (/cube/node_modules/@cubejs-backend/api-gateway/src/gateway.ts:1601:23)
2023-10-04T10:34:08.122698089Z at /cube/node_modules/@cubejs-backend/api-gateway/src/gateway.ts:248:7
2023-10-04T10:34:08.122705747Z Orchestrator error: 1d327e57-6b7f-4853-9d66-c47536b6bb02-span-1 (904ms)
2023-10-04T10:34:08.122710930Z --
2023-10-04T10:34:08.122715330Z {
2023-10-04T10:34:08.122720061Z "limit": 5000,
2023-10-04T10:34:08.122724701Z "measures": [
2023-10-04T10:34:08.122729562Z "CrossingEvents.count"
2023-10-04T10:34:08.122736356Z ],
2023-10-04T10:34:08.122742706Z "timeDimensions": [
2023-10-04T10:34:08.122748827Z {
2023-10-04T10:34:08.122755298Z "dimension": "CrossingEvents.timestamp",
2023-10-04T10:34:08.122762447Z "granularity": "hour",
2023-10-04T10:34:08.122769367Z "dateRange": "This month"
2023-10-04T10:34:08.122784199Z }
2023-10-04T10:34:08.122791103Z ],
2023-10-04T10:34:08.122797811Z "order": {
2023-10-04T10:34:08.122805470Z "CrossingEvents.timestamp": "asc"
2023-10-04T10:34:08.122813060Z }
2023-10-04T10:34:08.122820334Z }
2023-10-04T10:34:08.122827702Z --
2023-10-04T10:34:08.122832602Z {
2023-10-04T10:34:08.122836690Z "securityContext": {
2023-10-04T10:34:08.122841221Z ...
2023-10-04T10:34:08.122850722Z }
2023-10-04T10:34:08.122854894Z }
2023-10-04T10:34:08.122859127Z Error: Canceled.
```
Contributor guide
Assessment
This issue has not been assessed yet.