Internal Error During Spark SQL Phase Optimization – Possible Bug in Spark/Databricks Runtime

[boitumelodikoko]

We are experiencing the following issues.

Description:

I encountered an issue while executing a Spark SQL query in Databricks, and it seems to be related to the query optimization phase. The error message suggests an internal bug within Spark or the Spark plugins used in our environment. I am reaching out to report this issue and seek assistance in troubleshooting and resolving it.

Error Message:

[INTERNAL_ERROR] The Spark SQL phase optimization failed with an internal error. You hit a bug in Spark or the Spark plugins you use. Please, report this bug to the corresponding communities or vendors, and provide the full stack trace. SQLSTATE: XX000

Environment Details:

• Cloud: Azure
• Compute: SQL Warehouse, Serverless
• Channel: (v 2024.35)
• Size: X-Small

Query:

Here is the SQL query that triggered the internal error:

WITH isos AS ( SELECT DISTINCT Name AS full_name, CAST(RecordingTime AS DATE) AS test_date, LOWER(TestType) AS test_type, TestTypeName, CASE WHEN LOWER(TestTypeName) = 'slppf' OR LOWER(TestType) = 'rsaip' THEN 'ankle iso push' WHEN LOWER(TestTypeName) = 'slsquat' OR LOWER(TestType) = 'rskip' THEN 'knee iso push' WHEN LOWER(TestTypeName) = 'slhipext' OR LOWER(TestType) = 'rship' THEN 'hip iso push' ELSE LOWER(TestTypeName) END AS test_type_name, Limb, `Peak Vertical Force / BW` AS peak_vertical_force FROM <catalog>.<schema>.<table> ), isos_next AS ( SELECT * FROM isos

WHERE test_type_name = 'hip iso push'   ) SELECT * FROM isos_next;

Steps to Reproduce:

1. Executed the above SQL query within a Databricks notebook.
2. The error occurs during query execution, specifically during the Spark SQL optimization phase.

Troubleshooting Attempts:

• I simplified the query to isolate the issue, and it appears to be related to the internal Spark SQL optimizer.
• I've checked for any unsupported functions or potential syntax issues, but the query seems valid based on Spark SQL syntax.

Request:

I believe this may be a bug in Spark or the Databricks runtime, and I would appreciate guidance on resolving it. If further investigation is required, I am happy to provide any additional details or logs.

[cgrant]

This is not expected. Can you please verify:

• Are there any admin settings set on the cluster?
• Are you selecting from any views? If so, what do their definitions look like?

[boitumelodikoko · Accepted solution]

Update:
Response from the Databricks Team.

Symptoms
Internal Error During Spark SQL Phase Optimization.

Cause
DataBricks PG Engineering team confirmed that this is indeed a bug in CASE WHEN optimization & they are working on the fix for this issue.

Resolution
The fix has been merged and it will soon be available in runtime. Once merged, we await the deployment of version 16.1. The ETA for the fix for this issue with the new DBR 16.1 is mid-January.