Implement DECIMAL type

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.

@alamb

@alamb any idea about how to start this?

@j-a-m-l

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

@j-a-m-l

@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)

@xudong963

Thanks @xudong963 and @alamb

@alamb

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

@j-a-m-l

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

@alamb

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.

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

@xudong963

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.

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

@alamb

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

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

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.

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>.

@alamb

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

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)

Update roadmap with features completed

Update roadmap with features completed (#1464)

I think this is done and no longer tracking anything so closing the ticket!

test: Move MacOS (x86) pipelines to post-commit (apache#122)

Navigation Menu

Repository navigation

Implement DECIMAL type #122

Metadata

Assignees

Labels

Type

Projects

Milestone

Relationships

Development

Implement DECIMAL type #122

Description

Activity

j-a-m-l commented on Oct 30, 2021

xudong963 commented on Oct 30, 2021

alamb commented on Nov 1, 2021

j-a-m-l commented on Nov 1, 2021

liukun4515 commented on Nov 8, 2021

xudong963 commented on Nov 8, 2021

liukun4515 commented on Nov 8, 2021

alamb commented on Nov 8, 2021

j-a-m-l commented on Nov 9, 2021

alamb commented on Nov 9, 2021

liukun4515 commented on Nov 14, 2021

liukun4515 commented on Nov 22, 2021

liukun4515 commented on Nov 23, 2021

alamb commented on Nov 23, 2021

liukun4515 commented on Nov 24, 2021

alamb commented on Nov 24, 2021

alamb commented on Mar 13, 2025

Metadata

Metadata

Assignees

Labels

Type

Projects

Milestone

Relationships

Development

Issue actions