Databricks Spark SQL Query Error from Sigma

@Keith Wong

Databricks: execution error: unexpected operation state ERROR_STATE: [INTERNAL_ERROR] The Spark SQL phase optimization failed with an internal error. SQLSTATE: XX000

This is not caused by a syntax error or user mistake. Instead, it happens deep inside Spark’s SQL optimizer, specifically during the Catalyst Optimization phase.

Catalyst Optimizer Bug Spark’s Catalyst engine tries to transform your SQL query into an optimized execution plan. If it hits a bug while generating or optimizing this plan, it throws this INTERNAL_ERROR. This occurs due to the following scenarios:

Very Large / Complex Queries If the query has:

• Too many nested WITH clauses (CTEs)
• Lots of JOINs and CASE expressions
• Long chains of expressions and aggregations Spark might generate a plan that is too big or too complex for the optimizer to handle.

Exceeding internal limits There are hidden Spark thresholds like:

• Max query plan size
• Max number of logical operators Once these are exceeded, Spark may not throw a clear error — instead, it crashes with an internal error.

Bugs in Databricks Runtime or Plugins Sometimes it's caused by bugs in:

• The specific Databricks runtime version you’re using
• Delta Lake or Spark SQL extensions

Try the following:

Run each CTE (WITH clause) separately to see which one triggers the crash.

Create temporary views instead of chaining everything in one big query.

Upgrade your Databricks Runtime newer runtimes fix many optimizer bugs.

Note: This is not a user error. You’ve just hit an edge case in the optimizer where it cannot handle the size or structure of your query. The best action is to simplify the query.

For more details refer: Error conditions in Azure Databricks

I hope this information helps.

Kindly consider upvoting the comment if the information provided is helpful. This can assist other community members in resolving similar issues. 

@Keith Wong

We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. In case if you have any resolution please do share that same with the community as it can be helpful to others. Otherwise, will respond with more details and we will try to help.

@Keith Wong

Following up to see if the above suggestion was helpful. And, if you have any further query do let us know.

Thanks for the follow up Ganesh!

We are currently checking with app.sigmacomputing.com to see if they can simplify the script.

we're waiting on the result. Thanks!

@Keith Wong

Thank you for the update! That sounds like a good plan. Please let us know once you hear back from app.sigmacomputing.com regarding the script. We’re here to help if you need any further assistance after that.