[
https://issues.apache.org/jira/browse/DRILL-7959?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Nick Stenroos-Dam updated DRILL-7959:
-------------------------------------
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:
"planner.fallback_decimal_scale_on_overflow"
Then when the precision is overflowing and set to 38, the scale would be set to
the "planner.fallback_decimal_scale_on_overflow", or the requested scale if its
less then the fallback value.
The default value of "planner.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:
# 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)
# 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)
was:
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:
"planner.fallback_decimal_scale_on_overflow"
Then when the precision is overflowing and set to 38, the scale would be set to
the fallback_decimal_scale_on_overflow, or the requested scale if its less then
the fallback value.
The default value of "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:
# 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)
# 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)
> Minimum scale for decimal when precision overflows
> --------------------------------------------------
>
> Key: DRILL-7959
> URL: https://issues.apache.org/jira/browse/DRILL-7959
> Project: Apache Drill
> Issue Type: Improvement
> Components: Query Planning & Optimization
> Reporter: Nick Stenroos-Dam
> Priority: Major
>
> 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:
> "planner.fallback_decimal_scale_on_overflow"
> Then when the precision is overflowing and set to 38, the scale would be set
> to the "planner.fallback_decimal_scale_on_overflow", or the requested scale
> if its less then the fallback value.
> The default value of "planner.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:
> # 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)
> # 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)
--
This message was sent by Atlassian Jira
(v8.3.4#803005)
