cube-js / cube-js/cube

PreAggregation with segment behaves differently then non preaggregation: 128 bit decimal support

Open
#5,467 3 comments 2 reactions 0 assignees View on GitHub
cube store
Dominant language
Rust
Stars
20.8k
Forks
2.1k
Avg merge
1d 2h
Merged PRs (30d)
181

Description

**Describe the bug**
I implemented cube for my needs and it is returning correct values. I added preAggregation via Playground suggestion.
When I run my query with preaggregation, either return nonsense data for certain granularities or calculation fails completely on this error
`sql cannot represent '121383130843856.02' with scale 5 without loosing precision `

**To Reproduce**
Cube looks like this
```
cube(`CANValueData`, {
sql: `SELECT T1.[Id] as Id
,T1.[TagId] as TagId
,T1.[TimeStamp] as [From]
,T2.[TimeStamp] as [To]
,T1.[Value] as [ValueFrom]
,T2.[Value] as [ValueTo]
,(CAST(T2.[Value] as int) - CAST(T1.[Value] as int)) as ValueDiff
,DATEDIFF(SECOND, T1.[TimeStamp], T2.[TimeStamp]) as TimeDiff
FROM [Sidas3Uns].[UnsStorage].[CANValueData] T1
JOIN [Sidas3Uns].[UnsStorage].[CANValueData] T2
ON T1.FollowingRecordId = T2.Id
`,
preAggregations: {

canValuePre: {
measures: [CANValueData.valueOverTimeSum, CANValueData.timeSum],
dimensions: [CANValueTag.tag],
//segments: [CANValueData.speedSegment],
timeDimension: CANValueData.from,
granularity: `hour`
}
},
joins: {
CANValueTag: {
relationship: `hasOne`,
sql: `${CUBE}.TagId = ${CANValueTag}.Id`
}
},
segments: {
speedSegment: {
sql: `${CUBE.tagId} >= 1 and ${CUBE.tagId} < 5`
},
counterSegment: {
sql: `${CUBE.tagId} = 7`
}
},
measures: {
count: {
type: `count`
},
//TimeSumation
valueOverTimeSum: {
sql: `${CUBE}."ValueFrom" * ${CUBE}."TimeDiff"`,
type: `sum`
},
timeSum: {
type: `sum`,
sql: `${CUBE}."TimeDiff"`
},
calcAvgSum: {
sql: `${CUBE.valueOverTimeSum} / ${CUBE.timeSum}`,
type: `number`
},
},
dimensions: {
id: {
sql: `${CUBE}."Id"`,
type: `number`,
primaryKey: true
},
tagId: {
sql: `${CUBE}."TagId"`,
type: `number`
},
from: {
sql: `${CUBE}."From"`,
type: `time`
},
to: {
sql: `${CUBE}."To"`,
type: `time`
}
},
dataSource: `data`
});

```
I test it against following query
`query={"dimensions":["CANValueTag.tag"],"order":{"CANValueData.from":"asc"},"timeDimensions":[{"dimension":"CANValueData.from","granularity":"hour","dateRange":["2021-10-21","2021-10-22"]}],"segments":["CANValueData.speedSegment"],"measures":["CANValueData.calcAvgSum"]}`

**Expected behavior**
When uncommenting the segment part in rollup, it should return the same data as without it.

I am using MSSQL as data source.

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.