Aggregate Functions Supported for Cube, Rollup and Grouping Set in serverless sql pool

Question

Hello,

The count distinct SQL Function does not work correctly when we use cube/rollup/grouping sets. I get below error when i try to join two open rowset datasets and perform count distinct.

I could not find any documentation on list of SQL functions not supported in serverless sql pool. Could you please share any documentation that explains this?

Request to perform an external distributed computation has failed with error "Assertion failed: PhysicalPlan::ExtractPlan should only be called after optimization is complete.".

Answer 1

Hi @Suraj Shashidhar
Welcome to Microsoft Q&A platform.
Thank you for reaching out with your query regarding the use of aggregate functions like COUNT(DISTINCT ...) in conjunction with CUBE, ROLLUP, and GROUPING SETS in Azure Synapse Analytics' serverless SQL pool.

Currently, serverless SQL pool does not support the GROUP BY options such as CUBE, ROLLUP, and GROUPING SETS. This limitation is documented in the official Microsoft Learn documentation.

The error message you're encountering:

"Request to perform an external distributed computation has failed with error 'Assertion failed: PhysicalPlan::ExtractPlan should only be called after optimization is complete.'"

is indicative of this unsupported functionality.

As of now, there isn't a comprehensive list detailing all unsupported SQL functions in serverless SQL pool. However, for advanced grouping operations, you might consider using alternative approaches or leveraging other components within Azure Synapse Analytics that offer the required capabilities.

If this issue is critical to your workflow, I recommend raising a support ticket with Microsoft Azure Support. They can provide deeper insights into the limitations and potential workarounds for your specific scenario.

Hope this helps. Do let us know if you any further queries.

---

If this answers your query, do click Accept Answer and Yes for was this answer helpful. And, if you have any further query do let us know.