Add support for BigDecimal as a Pinot data type

[nizarhejazi]

Pinot currently support storing BigDecimal as byte[], and a couple inbuilt scalar functions for converting a string to BigDecimal bytes and converting BigDecimal bytes to strings:

byte[] bigDecimalToBytes(String number)
String bytesToBigDecimal(byte[] bytes)

There is also support for sum aggregation with precision and scale: SUMPRECISION(expression, precision, scale).

Pinot supports BigDecimal with an arbitrary precision integer unscaled value, and a 16-bit integer scale (instead of 32-bit integer scale supported by java.math.BigDecimal class). Data types are defined inside an enum (enum ObjectType) which makes BigDecimal not configurable.

There is a need to support BigDecimal data type in Pinot so clients (e.g. Presto, Trino, etc.) can map Pinot's BigDecimal to the equivalent supported data type.

Please note that Presto/Trino supports a fixed precision decimal number (up to 38 digits). One possible way to map Pinot's representation of BigDecimal to Trino is similar to how Trino handles Postgres decimal type:

• The scale of the resulting type is controlled via the decimal-default-scale configuration property or the decimal-rounding-mode session property.
• The precision is always 38.

The ask is to support BigDecimal in:

• SELECT statement.
• DISTINCT statement.
• ORDER BY statement.
• GROUP BY statement.
• HAVING statement.
• CASE statement.
• CAST transform.
• Groovy transform.
• Identifier transform.
• Literal transform.
• Lookup transform.
• JsonPathEvaluator (w/ custom ObjectMapper and JsonNodeFactory).
• JsonExtractScalar transform.
• Addition, Subtraction, Multiplication, and Division.
• abs(), ceil(), and floor() math functions.
• Greatest, and Least transforms.
• EQUAL, NOT_EQUAL, IN, NOT_IN, RANGE predicates.
• NoDictionary based EQUAL, NOT_EQUAL and IN predicates.
• Binary operators (=, !=, >=, >, <=, <).

[KKcorps]

This is really useful (and almost necessary) for storing fixed precision numbers such as amount of money paid.

[siddharthteotia]

+1

Any reason why we don't want to go for arbitrary precision or why can't the precision be less than 38 ?

Also, do we think we can make the user level construct more generic / aligned with may be how other OLAP systems expose ?

For example, I have seen DECIMAL (precision, scale) notation being used in a couple of systems.

[nizarhejazi]

@siddharthteotia Pinot BigDecimal can store arbitrary precision integer unscaled value, and a 16-bit integer scale.

Precision and scale configuration are related Presto/Trino-Pinot-connector. The goal is to have parity w/ how decimals are exposed in Presto-PostgreSQL-connector. Supporting smaller precision should be doable. Actually, Presto recommends a precision of 18 or less for ideal performance.

[KKcorps]

Yes, we should focus on maintaining as much parity as possible with Presto DECIMAL datatype. Also, for precision less <= 18, we can store the unscaled value as long which should be easier to support.

[KKcorps]

Will this help meanwhile or is it not worth it @siddharthteotia - #8440

[siddharthteotia]

Hi @KKcorps - I will take a look at the PR today

[Jackie-Jiang]

Link the PR: #8503

[Jackie-Jiang]

Currently we store BigDecimal as bytes, but because the ordering of BigDecimal is different from bytes, we might have to introduce the BigDecimal into the data representation layer.
Checked the decimal value support in other systems, and most of them support up to 38 precision digits. Wondering the reason behind that, and if we can store the value in 136-bit format (1 sign bit, 6 scale bit, 129 precision bits) if we also limit the 38 precision digits. If we can keep the ordering the same for BigDecimal and underlying bytes, we can reuse the bytes comparing logic and not introducing BigDecimal special ordering handling.

[Jackie-Jiang]

I think the reason for the 38 precision digits is that we can use 128 bits to represent the value. If we know the digits after the decimal point (e.g. DECIMAL(38, 10)), we don't need to store the scale, and we can preserve the bytes order

[nizarhejazi]

@Jackie-Jiang Supporting fixed-precision BigDecimal w/ configurable precision and scale (DECIMAL(p, s)) is a big intake and requires:

• Optimizing storage based on different precision values (1-9, 10-18, 19-38).
• Updating PinotDataType to not be an enum.
• Arithmetic operations that are precision-aware and scale-aware (example impl.).
• Aggregate transforms that are scale-aware (e.g. the result of AVG aggregate function is precision: 38, and scale = max(scale of input col, 6).
• etc.

I wonder if it makes sense to add support now for BIG_DECIMAL data type (arbitrary precision integer unscaled value, and a 16-bit integer scale). Someone else can take on adding fixed-precision DECIMAL(p,s) data type later.

My first PR 8468 introduced BIG_DECIMAL data type, and conversion between byte[] and BigDecimal close to the data storage layer for two reasons:

• Avoid updating comparing logic everywhere (since BigDecimal comparison is not equivalent to comparing corresponding byte[]).
• Avoid the cost of converting byte[] to BigDecimal multiple times.

Let me know if I should proceed w/ supporting arbitrary precision BIG_DECIMAL data type.

[Jackie-Jiang]

@nizarhejazi Good point on the challenges of supporting DECIMAL(p, s). I think it is okay for us to add BIG_DECIMAL data type for arbitrary precision and scale (-32768 to 32767) first, then figure out how to add DECIMAL support in the future.
Go ahead adding the dictionary/raw forward index/transform function support etc. for BIG_DECIMAL. Sorry I didn't think through the problem initially and made the bad suggestion.