search

ClickHouse / power-bi-clickhouse

This connector allows you to retrieve data from ClickHouse directly into Power BI for analysis and visualization
Apache License 2.0
41 stars 1 forks source link

PowerBI Desktop - Direct Query - Table - Generates invalid nested SQL with overlapping aliases #20

Open GithubUser5462 opened 2 months ago

GithubUser5462 commented 2 months ago

Describe the bug Table visualization generates broken SQL.

To Reproduce In clickhouse create a table with multiple numeric columns (int64, float64) and import it as direct query in powerbi. In powerbi add a table visualization and add multiple columns to it. Initially it works fine, but eventually it starts nesting the query and assigning aliases like C1, C2, C3,... which overlap. This causes errors "aggregate function found inside another aggregate function" and "cyclic alias".

Example of generated query:

SELECT sum(CAST(C2, 'DOUBLE')) AS C1, sum(CAST(C1, 'DOUBLE')) AS C2
FROM (SELECT CAST(round(CAST(impressions, 'DOUBLE'), _CAST(0, 'Nullable(Int32)')), 'BIGINT') AS C1, CAST(round(CAST(inviews, 'DOUBLE'), _CAST(0, 'Nullable(Int32)')), 'BIGINT') AS C2 FROM ...

Configuration

BentsiLeviav commented 1 month ago

Thank you for your feedback!

Could you be more precise? Did you do something actively before getting this error? If possible, could you add precise steps to reproduce the issue? I couldn't produce it with your current explanation.