Power BI Desktop Dremio Connector SQL Query Translation

[HaythemJ]

Hi,

We are getting an out of memory error when we try to do a distinct count on an int column stored in Dremio. When we remove the “Cast” part and run the query from the dremio sql runner it runs successfully. Obviously it’s the “Cast” part that causes the error and we don’t see why our filter is translated with it in this case when we dont need it. Can anyone help please ? We are just trying to get a TOP 10 rows with a Distinctcount 

 It should’nt be a big deal.

I’ve added a screenshot from our PowerBI Visual and Filter.

The error we are getting and the query that the connector is translating in PowerBI :
Release:
April 2023

Product Version:
2.116.966.0 (23.04) (x64)

Error Message:
Erreur OLE DB ou ODBC: [DataSource.Error] ODBC: ERROR [HY000] [Microsoft][Drill] (1040) Drill failed to execute the query: select “C1”,
cast(count(distinct(“siret”)) as DOUBLE) + cast(max(“C2”) as DOUBLE) as “C2”
from
(
select “OTBL”.“siret”,
“ITBL”.“siren” as “C1”,
case
when “OTBL”.“siret” is null
then 1
else 0
end as “C2”
from “Sirene”.“Etablissement” as “OTBL”
left outer join “Sirene”.“UniteLegale” as “ITBL” on (“OTBL”.“siren” = “ITBL”.“siren”)
) as “ITBL”
group by “C1”
OFFSET 0 ROWS FETCH FIRST 1000001 ROWS ONLY
[30039]Query execution error. Details:[
OUT_OF_MEMORY ERROR: Query was cancelled because it exceeded the memory limits set by the administrator.Feedback Type:
Frown (Error)