When I query a numeric field in the SQL Editor it returns a value of 0.02875 and the data type is decimal but when I run the same query in a SQL notebook cell it returns 0.0287500 and decimal(7,7). I'm assuming this is expected behavior but is there any documentation on this? I found after looking at the table schema that 0.0287500 and decimal(7,7) are correct but it would be nice to have confirmation because I can't find info on this difference anywhere on the web. Is the notebook returning the raw table value and data type?
SQL Editor
SQL notebook cell
1 ACCEPTED SOLUTION
Accepted Solutions
Hello @help_needed_445
Good day!
its very indeed interesting case study!
I found below from LLM models.
Yes, this difference in decimal display between the Databricks SQL Editor (which uses the Photon engine in Databricks SQL) and notebooks (which use Spark SQL) is expected behavior related to how each interface handles formatting and schema inference for decimal types, particularly when dealing with underlying storage formats like Parquet in Delta tables.
After this, I did create a table on MYSQL to check what is RDBMS is behaving, interstingly MySQL gave the solution as expected.
What I understand is from LLM models, SQL Editor uses photon engine, if its true then it is having a different design, and the output is different.
However, even LLM models failed to share any documentation link for reference on this.
can we have anyone check this on interactive cluster with photon acc. enabled. ( just to check may be I am not sure on this)
Thank you.
( I am open to other solutions, it should be interesting to know this thing. :))
Hello @help_needed_445
Good day!
its very indeed interesting case study!
I found below from LLM models.
Yes, this difference in decimal display between the Databricks SQL Editor (which uses the Photon engine in Databricks SQL) and notebooks (which use Spark SQL) is expected behavior related to how each interface handles formatting and schema inference for decimal types, particularly when dealing with underlying storage formats like Parquet in Delta tables.
After this, I did create a table on MYSQL to check what is RDBMS is behaving, interstingly MySQL gave the solution as expected.
What I understand is from LLM models, SQL Editor uses photon engine, if its true then it is having a different design, and the output is different.
However, even LLM models failed to share any documentation link for reference on this.
can we have anyone check this on interactive cluster with photon acc. enabled. ( just to check may be I am not sure on this)
Thank you.
( I am open to other solutions, it should be interesting to know this thing. :))
