Hello - this is a bit of an involved issue (apologies for length!) that started in PowerBI but seems to be really a SQL Analytics Endpoint oddity. I was creating a very simple bar chart report with a very simple DirectQuery data model - 3 tables coming from our lakehouse SQL Analytics Endpoint - and found that, for the Y-axis, if I asked for a COUNT, it worked fine; but if I asked for a COUNT DISTINCT, I got a very nondescript "SQL command could not be executed" error - but only for certain selections in my "Keyword" slicer. I grabbed the diagnostic output, found the query that PowerBI had constructed, and popped it into a live SQL query against the endpoint, and got exactly the same result - "Msg 100000 SQL command could not be executed." I've put the query below, whittled down so it only has relevant fields, and noted a couple of things I found that got around this mystery error, but none of them help me with my COUNT DISTINCT reporting requirement. Hoping someone has some insight!
SELECT
basetable0.c55,
(COUNT_BIG(DISTINCT a0) + MAX(CASE WHEN a0 IS NULL THEN 1 ELSE 0 END)) AS a0
FROM
(
(
SELECT
t4.PerformanceNo AS c43,
t4.Facility AS c55,
t3.SeatNo AS a0
FROM
(
(
select
MyTable.PerformanceNo as PerformanceNo,
MyTable.SeatNo as SeatNo
from
dbo.us_performance_seats as MyTable
) AS t3
INNER JOIN
(
select
MyTable.PerformanceNo as PerformanceNo,
MyTable.Facility as Facility
from
dbo.us_performances as MyTable
) AS t4
on t3.PerformanceNo = t4.PerformanceNo
)
) AS basetable0
INNER JOIN
(
SELECT
t4.PerformanceNo AS c43,
t4.Facility AS c55
FROM
(
(
select
MyTable.PerformanceNo as PerformanceNo,
MyTable.Keyword as Keyword
from
dbo.us_performance_keywords as MyTable
) AS t0
INNER JOIN
(
select
MyTable.PerformanceNo as PerformanceNo,
MyTable.Facility as Facility
from
dbo.us_performances as MyTable
) AS t4
on t0.PerformanceNo = t4.PerformanceNo
)
WHERE t0.Keyword = 'Keyword1'
GROUP BY t4.PerformanceNo,t4.Facility
) AS semijoin1
on
(semijoin1.c43 = basetable0.c43 OR semijoin1.c43 IS NULL AND basetable0.c43 IS NULL)
AND
(semijoin1.c55 = basetable0.c55 OR semijoin1.c55 IS NULL AND basetable0.c55 IS NULL)
)
GROUP BY basetable0.c55
Things that made the error go away:
- removing the DISTINCT from the outer SELECT (same behavior as in PowerBI)
- changing the "Keyword1" filter to "Keyword2" (same behavior as in PowerBI) - I checked out the raw results prior to the GROUP BY, and Keyword1 doesn't have a huge number of rows more than Keyword2 - like 26K instead of 13K - so it doesn't feel like a data volume issue to me
- taking out the MAX(CASE) statement for [a0] in the outer SELECT - instantly returned expected results
- adding a TOP N clause to the SELECT for [basetable0] - tried numbers as big as 10M, didn't matter how large or small - as long as there was a TOP N, it was happy and returned the expected results immediately (adding TOP N to the SELECT for [semijoin1] had no effect)
- removing the very bottom null-join-check (OR semijoin1.c55 IS NULL and basetable0.c55 IS NULL) also produced instant correct results - no other parts of the join had any effect when removed - and note that c55, the Facility column, does not contain any nulls
