[SPARK-21332] Incorrect result type inferred for some decimal expressions

• Type: Bug
• Status: Resolved
• Priority: Major
• Resolution: Fixed
• Affects Version/s: 2.0.2
• Fix Version/s: 2.0.3, 2.1.2, 2.2.1, 2.3.0
• Component/s: SQL
• Labels: correctness
• Assignee: Anton Okolnychyi
• Reporter: Alexander Shkapsky
• Created: 06/Jul/17 23:42
• Updated: 12/Dec/17 18:56
• Resolved: 18/Jul/17 04:10

Description

Decimal expressions do not always follow the type inference rules explained in DecimalPrecision.scala. An incorrect result type is produced when the expressions contains more than 2 decimals.

For example:
spark-sql> CREATE TABLE Decimals(decimal_26_6 DECIMAL(26,6));
...
spark-sql> describe decimals;
...
decimal_26_6 decimal(26,6) NULL
spark-sql> explain select decimal_26_6 * decimal_26_6 from decimals;
...
== Physical Plan ==
*Project CheckOverflow((decimal_26_6#99 * decimal_26_6#99), DecimalType(38,12)) AS (decimal_26_6 * decimal_26_6)#100
+- HiveTableScan decimal_26_6#99, MetastoreRelation default, decimals

However:
spark-sql> explain select decimal_26_6 * decimal_26_6 * decimal_26_6 from decimals;
...
== Physical Plan ==
*Project CheckOverflow((cast(CheckOverflow((decimal_26_6#104 * decimal_26_6#104), DecimalType(38,12)) as decimal(26,6)) * decimal_26_6#104), DecimalType(38,12)) AS ((decimal_26_6 * decimal_26_6) * decimal_26_6)#105
+- HiveTableScan decimal_26_6#104, MetastoreRelation default, decimals

The expected result type is DecimalType(38,18).

In Hive 1.1.0:
hive> explain select decimal_26_6 * decimal_26_6 from decimals;
OK
STAGE DEPENDENCIES:
Stage-0 is a root stage

STAGE PLANS:
Stage: Stage-0
Fetch Operator
limit: -1
Processor Tree:
TableScan
alias: decimals
Statistics: Num rows: 0 Data size: 0 Basic stats: NONE Column stats: NONE
Select Operator
expressions: (decimal_26_6 * decimal_26_6) (type: decimal(38,12))
outputColumnNames: _col0
Statistics: Num rows: 0 Data size: 0 Basic stats: NONE Column stats: NONE
ListSink

Time taken: 0.772 seconds, Fetched: 17 row(s)
hive> explain select decimal_26_6 * decimal_26_6 * decimal_26_6 from decimals;
OK
STAGE DEPENDENCIES:
Stage-0 is a root stage

STAGE PLANS:
Stage: Stage-0
Fetch Operator
limit: -1
Processor Tree:
TableScan
alias: decimals
Statistics: Num rows: 0 Data size: 0 Basic stats: NONE Column stats: NONE
Select Operator
expressions: ((decimal_26_6 * decimal_26_6) * decimal_26_6) (type: decimal(38,18))
outputColumnNames: _col0
Statistics: Num rows: 0 Data size: 0 Basic stats: NONE Column stats: NONE
ListSink

Time taken: 0.064 seconds, Fetched: 17 row(s)

Issue Links

• relates to: SPARK-22755 Expression (946-885)*1.0/946 < 0.1 and (946-885)*1.000/946 < 0.1 return different results
• links to: [Github] Pull Request #18583 (aokolnychyi)