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.

[alamb]

https://github.com/apache/arrow-datafusion/blob/master/datafusion/src/physical_plan/expressions/coercion.rs and https://github.com/apache/arrow-datafusion/blob/4687899957463ce81c4795a6d35d31320db0252b/datafusion/src/physical_plan/type_coercion.rs are the two places I know of in DataFusion today that do this type coercion

…

On Mon, Nov 22, 2021 at 9:33 PM Kun Liu ***@***.***> wrote: In order to implement decimal in datafusion, we should ref some data type rules, like spark data type <https://spark.apache.org/docs/3.2.0/sql-ref-datatypes.html> and type coercion <https://spark.apache.org/docs/3.2.0/sql-ref-ansi-compliance.html#type-promotion-and-precedence> . 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. — You are receiving this because you were mentioned. Reply to this email directly, view it on GitHub <#122 (comment)>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/AADXZMNH25DW2NQZKCQ75BDUNL4OBANCNFSM43S4FY3Q> . Triage notifications on the go with GitHub Mobile for iOS <https://apps.apple.com/app/apple-store/id1477376905?ct=notification-email&mt=8&pt=524675> or Android <https://play.google.com/store/apps/details?id=com.github.android&referrer=utm_campaign%3Dnotification-email%26utm_medium%3Demail%26utm_source%3Dgithub>.

[liukun4515]

I know of in DataFusion today that do this type coercion

yep, Now I just try to support some simple demo code for decimal. For example function(min,max) and just Projection the column with decimal data type (without any operation and functions)

The rule of conversion in the above two files should be refactored, If we want to align the behavior with PG database exactly.

In the PG database, decimal and other numerical data type can be convert to each other, and in some case the varchar also can be convert to a decimal data type. In my opinion, we can support some simple and right rules first, and support other complex features later. For example, In the add mathematics operation for the decimal datatype, if left or right is the decimal datatype, we must require that two size are all decimal type. It is means that we don't support the cast or try cast to or from decimal data type implicitly.

What do you think about? @alamb

[alamb]

In my opinion, we can support some simple and right rules first, and support other complex features later

I think this is a great idea -- taking stock / consolidating the existing coercion logic might be a good place to start (before you try to extend it to support Decimal)