[DRILL-7959] Minimum scale for decimal when precision is truncated

• Type: Improvement
• Status: Open
• Priority: Major
• Resolution: Unresolved
• Component/s: Query Planning & Optimization
• Assignee: Unassigned
• Reporter: Nick Stenroos-Dam
• Created: 30/Jun/21 13:20
• Updated: 01/Jul/21 07:42

Description

Currently when doing math on decimals with a large precision Apache Drill will set the scale to 0 in most cases, this is causing us quite a few issues.

If we look at RDBMS they behave quite differently, and I would like to emulate this behaviour a little bit more.

• Microsoft SQL Server, will always keep a minimum scale of 6
• MySQL, will always keep the largest possible scale, but limited to half their total precision (30).

I would suggest that a user setting is implemented: "drill.exec.fallback_decimal_scale_on_overflow"

Then when the precision is overflowing and set to 38, the scale would be set to the "drill.exec.fallback_decimal_scale_on_overflow", or the requested scale if its less then the fallback value.

The default value of "drill.exec.fallback_decimal_scale_on_overflow" should either by 6 or 0. My personal preference would be 6 as its more user-friendly for new users.

Examples:

1. CAST(x as DECIMAL(29,6)) * CAST(x as DECIMAL(29,6))
   Resulting data type in Drill 1.19: DECIMAL(38, 0)
   New data type with this change: DECIMAL(38, 6)
2. CAST(x AS DECIMAL(29,2)) * CAST(x as DECIMAL(29, 2))
   Resulting data type in Drill 1.19: DECIMAL(38, 0)
   New data type with this change: DECIMAL(38, 4)