ClickHouse / ClickHouse/clickhouse-odbc

Power BI Direct Query CAST to DECIMAL in DIVIDE function causing precision errors

Open
#505 7 comments 0 reactions 1 assignee Claimed by @slabko View on GitHub
bug
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

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.