Implement DECIMAL type

[alamb]

Note: migrated from original JIRA: https://issues.apache.org/jira/browse/ARROW-10818

The TPC-H benchmarks correctly specify that all MONEY columns are DECIMAL type (precision and scale are not specified). We currently use DataType::Float64 which is much lighter than a true Decimal type.

To be a valid benchmark we need to ensure we support the same precision as the reference implementation.

[j-a-m-l]

@alamb any idea about how to start this?

[xudong963]

Hi @j-a-m-l, I see arrow-rs has supported Decimal, maybe we can directly change here https://github.com/apache/arrow-datafusion/blob/22a7d74e9dece44adb790b5ea3fca27d26a2fe51/datafusion/src/sql/planner.rs#L312 cc @alamb

[alamb]

@j-a-m-l I think a @xudong963 has a good point that Arrow already includes some support for DecimalArrays.

A good place to start then be to write some tests in sql.rs that create a TableProvider with a column of DecimalArray type and try to run some basic sql

The typical order I would try is something like

// projection
SELECT col + 4 ... FROM table_with_decimal_col

// filter
SELECT col  FROM table_with_decimal_col where col < 5

// sort
SELECT col  FROM table_with_decimal_col order by col

// grouping / aggregatie
SELECT sum(col)  FROM table_with_decimal_col;
SELECT count(other_col)  FROM table_with_decimal_col group by col

Writing those tests is probably a good way to figure out how to plumb through the support in DataFusion -- I would also recommend doing it in that order (projection, filter, sort, grouping / aggregation)

[j-a-m-l]

Thanks @xudong963 and @alamb

[liukun4515]

Is there any plan for implementation Decimal Type for datafusion?
@alamb

[xudong963]

Hi @j-a-m-l, do you have time to work on this?

[liukun4515]

Is there any plan for implementation Decimal Type for datafusion? @alamb

I want to take some tasks to support the decimal data type in the datafusion.

[alamb]

Is there any plan for implementation Decimal Type for datafusion?

I don't know of any plans other than what is on this ticket. As I mentioned in #122 (comment) I think figuring out what subtasks are needed (by writing some tests and trying out what works / doesn't work) is a good first step

[j-a-m-l]

No, @xudong963. Currently I don't have enough time to do that, and I'll probably focus first on fixing other issues that I've found, such as #1173.

[alamb]

Here are several other issues that seem to be related to DECIMAL:

• Support DataType::Decimal(15, 2) in TPC-H benchmark #174
• Add support for group by Decimal numbers #210
• Bug for CAST from Decimal(12, 2) to Utf8 in ballista_scheduler #1233

[liukun4515]

Thanks for the feedback.
I will begin to support DECIMAL datatype.
Please assign this issue to me @alamb

[liukun4515]

• create external csv table with decimal data type in SQL parse and logical plan. support decimal scalar value  #1394
• decimal scalar support the decimal scalar value #1393
• create table and select column with decimal datatype support decimal data type in create table #1431
• support aggregate function(example min,max,count,sum)
  • min/max Support decimal for min and max aggregate #1407
  • sum/avg support sum/avg agg for decimal, change sum(float32) --> float64 #1408
  • sum/avg overflow checker check out of range / overflow for decimal agg function #1460
  • count/ApproxDistinct/ArrayAgg
  • coercion rule for aggregate function Add coercion rules for AggregateFunctions #1387
• support try_cast，cast : (1) support in arrow-rs support cast for decimal data type arrow-rs#1043 (2) support in datafusion support cast to decimal data type in datafusion #1443
• support comparison binary operation for decimal
  • comparison operation which implements in the datafusion. support comparison for decimal data type and refactor the binary coercion rule #1483
  • use arrow-rs kernel to replace datafusion for =,!=,>,<...，lt. This should be implemented in the arrow-rs. we can refer Support =, <, <=, >, >=, !=, is distinct from, is not distinct from for BooleanArray #1163
  • binary comparison coercion rules support comparison for decimal data type and refactor the binary coercion rule #1483
• support mathematics binary operation for decimal
  • array with scalar value operation: support mathematics operation for array with scalar value #1513
  • support decimal type for mathematics operation(+,-,*,/,%) support mathematics operation for decimal data type #1554
• consolidate coercion rule for binary operation from this comments support mathematics operation for decimal data type #1554 (comment)
• add sql level test: from support comparison for decimal data type and refactor the binary coercion rule #1483 (review) comments.
• support isdistinct/isnotdistinct for decimal data type IsDistinctFrom/IsNotDistinctFrom for decimal type #1590
• SIMD in arrow-rs SIMD for decimal data type arrow-rs#1010
• support expr evaluation (using in select, filter and other clause)
• support sort by decimal column support sorting decimal data type #1522
• support hasher/group by decimal type support hasher for decimal type #1535
• add document for promotion precision and scale Add document for explaining the promotion precision and scale for decimal data type #1461

utils:

• add from function to create a decimal array: Add creator from Iterator of i128 to get the decimalarray arrow-rs#1009
• discussion about the result data type: Talk about the result type for coerced type #1418

[liukun4515]

In order to implement decimal in datafusion, we should ref some data type rules, like spark data type and type coercion.
Now, the numerical data type just support some native type and the type coercion rules are simple. if we add the decimal data type, the type checking, type cast, type promotion and precision checking are all need to be considered.