ClickHouse / ClickHouse/clickhouse-odbc
Power BI Direct Query CAST to DECIMAL in DIVIDE function causing precision errors
- Dominant language
- C
- Stars
- 285
- Forks
- 105
- Avg merge
- 3h 56m
- Merged PRs (30d)
- 3
Description
### Describe the bug
I am running Power BI with Clickhouse connector and have a simple DAX measure:
`SUMX(TABLE1, DIVIDE(TABLE1.COL1, RELATED(TABLE2.COL1)))`
`TABLE1.COL1 and TABLE2.COL1 are decimal(38,15)` types.
`TABLE2.COL1 (denominator)` is losing precision and causing the multiplication to be wrong (multiplication by an integer rounded value)
### Steps to reproduce
1. As mentioned above
### Expected behaviour
### Code example
### Error log
### Query log
Got from system.query_log:
```sql
SELECT OTBL.xxx,
OTBL.xxx,
OTBL.xxx,
OTBL.xxx,
OTBL.xxx,
ITBL.xxx,
multiIf(
OTBL.T1COL1 IS NULL,
NULL,
multiIf(
(
CAST(OTBL.T2COL1 , 'DOUBLE') IS NULL
)
OR (
CAST(OTBL.T2COL1, 'DOUBLE') = _CAST(0., 'Nullable(Float64)')
),
NULL,
OTBL.T1COL1 / CAST(
CAST(OTBL.T2COL2, 'DOUBLE'),
'DECIMAL'
)
)
) AS C1,
FROM .... (normal select with joins)
```
The problem comes from:
```sql
CAST(
CAST(OTBL.T2COL2, 'DOUBLE'),
'DECIMAL'
)
```
`CAST to DECIMAL` added here is causing the column to be changed to an whole number (removing scale)
### Configuration
#### Environment
* Driver version: [1.3.3.20250317](https://github.com/ClickHouse/clickhouse-odbc/releases/tag/1.3.3.20250317)
* OS: Windows 11, Clickhouse 25.4.2 in docker
* ODBC Driver manager: latest
#### ClickHouse server
* ClickHouse Server version: 25.4.2
* ClickHouse Server non-default settings, if any:
* `CREATE TABLE` statements for tables involved:
* Sample data for all these tables, use [clickhouse-obfuscator](https://github.com/ClickHouse/ClickHouse/blob/master/programs/obfuscator/Obfuscator.cpp#L42-L80) if necessary
Contributor guide
Assessment
This issue has not been assessed yet.