[SPARK-49311] Large INTERVAL SECOND values cannot be cast to decimal

• Type: Bug
• Status: Resolved
• Priority: Major
• Resolution: Fixed
• Affects Version/s: 4.0.0
• Fix Version/s: 4.0.0
• Component/s: SQL
• Labels: pull-request-available
• Target Version/s: 4.0.0
• Assignee: Harsh Motwani
• Reporter: Harsh Motwani
• Created: 19/Aug/24 22:39
• Updated: 26/Aug/24 12:52
• Resolved: 26/Aug/24 12:52

Description

Spark fails when trying to cast an `interval second` value to decimal where the number of microseconds requires 19 digits.

scala> sql("select 1000000000000.000000::interval second").show(false)

+---------------------------------------------+

|CAST(1000000000000.000000 AS INTERVAL SECOND)|

+---------------------------------------------+

|INTERVAL '1000000000000' SECOND              |

+---------------------------------------------+

 

 

scala> sql("select 1000000000000.000000::interval second::decimal(38, 10)").show(false)

org.apache.spark.SparkArithmeticException: [NUMERIC_VALUE_OUT_OF_RANGE.WITH_SUGGESTION]  0 cannot be represented as Decimal(18, 6). If necessary set "spark.sql.ansi.enabled" to "false" to bypass this error, and return NULL instead. SQLSTATE: 22003

Issue Links

• links to: GitHub Pull Request #47808