Odd DISTINCT behavior from lakehouse SQL Analytics Endpoint

gmangiante — Tue, 05 Dec 2023 18:58:17 GMT

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

Re: Odd DISTINCT behavior from lakehouse SQL Analytics Endpoint

Anonymous — Thu, 07 Dec 2023 05:36:34 GMT

Hi @gmangiante

Thanks for using Microsoft Fabric Community.

Apologies for the issue that you are facing.

This might require a deeper investigation from our engineering team about your workspace and the logic behind it to properly understand what might be happening.

Please go ahead and raise a support ticket to reach our support team:

https://support.fabric.microsoft.com/support
Please provide the ticket number here as we can keep an eye on it.

Thanks.

Re: Odd DISTINCT behavior from lakehouse SQL Analytics Endpoint

Anonymous — Fri, 08 Dec 2023 18:43:06 GMT

Hi @gmangiante
We haven’t heard from you on the last response and was just checking back to see if you got a chance to create a support ticket. If yes please share the details here.
Otherwise, will respond back with the more details and we will try to help.
Thanks

Re: Odd DISTINCT behavior from lakehouse SQL Analytics Endpoint

HimanshuS-msft — Fri, 08 Dec 2023 19:29:49 GMT

Hello @gmangiante ,

Thanks for the adding the ask in so much details , this aloways helps .

I am sorry as i do not have a solution but when PowerBi is converting the SQL into DAX query I am sure , somthing is happening there . I suggest you to please see if you can simplify the query and even better if you add that in a view and call the view from PowerBI ,

I really want to help but without the data and the schema i cannot do much from myside for now .

Thanks
Himanshu

topic Re: Odd DISTINCT behavior from lakehouse SQL Analytics Endpoint in Data Engineering

Odd DISTINCT behavior from lakehouse SQL Analytics Endpoint

Re: Odd DISTINCT behavior from lakehouse SQL Analytics Endpoint

Re: Odd DISTINCT behavior from lakehouse SQL Analytics Endpoint

Re: Odd DISTINCT behavior from lakehouse SQL Analytics Endpoint