Scale/Precision error with decimal literal between -1 and 1 (0 left of decimal point)

[mikebin]

Describe the bug
A scale/precision error occurs when using a decimal literal between -1 and 1 in a ksqlDB query - in other words, when the value left of the decimal point is 0. For example, comparing a field to the literal 0.005 in a query will result in the following error:

DECIMAL precision must be >= scale: DECIMAL(1,3)

Steps to reproduce the behavior:
Version: 0.22.0 and CP 7.0.0

create stream s (val double) with (kafka_topic='s', value_format='json', partitions=1);
insert into s (val) values (123.456);

select * from s where val > 0.005 emit changes;

Expected behavior
No errors, query result returned.

Actual behaviour
Error message:

DECIMAL precision must be >= scale: DECIMAL(1,3)

Additional context
Decimals < -1 or > 1 have no issues. For example, the following works:

select * from s where val > 1.005 emit changes;

This also works:

select * from s where val > (1.005-1) emit changes;

There was a fix a while back for a similar issue: #5531

[agavra]

Natea was able to confirm, we'll pick it up in a fix-it-week

[maheshba]

I am taking a look at this with some help from @nae701. The root cause seems to be that BigDecimal's precision is not defined exactly the same way as SQL Decimal's precision.

TL;DR: can we treat "0.12" and ".12" the same way and return precision=3 for both instead of precision=2 for the latter? (this requires changing an existing unit test)

For SQL Decimal, precision is just the total number of digits; and scale is the number of digits to the right of the period. So scale has to be smaller or equal to precision; and the scale cannot be negative. So for "0.005", we expect to get (precision=4, scale=3). For ".005", we expect to get (precision=3, scale=3).

BigDecimal has a broader definition of precision and scale. It has the same definition for scale if the scale is zero or positive and for precision if the decimal is <= -1 or >= 1. But if the decimal is in (-1, 1), precision can be smaller than scale. For example, for both "0.005" and ".005", it returns (precision=1, scale=3).

The fix seems straightforward to implement if we know the desired behavior: when we do the conversion, we can check if the decimal is within (-1, 1) and treat it differently. The code is here:

ksql/ksqldb-common/src/main/java/io/confluent/ksql/util/DecimalUtil.java

Line 283 in 1abdb0d

return SqlTypes.decimal(decimal.precision(), Math.max(decimal.scale(), 0));

There is already a fix in there for the 0 case.

But I am not super-sure what the expected behavior is:

Specifically: If we convert to BigDecimal first as we do now, we lose the ability to distinguish between "0.005" and ".005"; is it okay in this case to always return the higher precision (4 rather than 3)? (This would break an existing unit test which expects precision = 2 for ".12" here:

ksql/ksqldb-common/src/test/java/io/confluent/ksql/util/DecimalUtilTest.java

Line 354 in 1abdb0d

public void shouldGetSchemaFromDecimal2_2() {

)

Another question that's not in the critical path: In the zero case right now, we retain the BigDecimal's setScale value rather than always return (precision=1, scale=0) -- is there a rationale for doing this? The test is here:

ksql/ksqldb-common/src/test/java/io/confluent/ksql/util/DecimalUtilTest.java

Line 101 in 1abdb0d

public void shouldExtractPrecisionFromZeroValue() {

[nateab]

@maheshba thanks for digging into this! I think we should stick with the SQL decimal definition, and precision=3 for both 0.12 and .12. I think it is fine to lose the ability to distinguish between 0.005 and .005. And we can update the unit tests appropriately. And for the zero case, the rationale is to stick with the SQL standard, which does differentiate between 0.0 and 0.00 for example. Once you put up the PR we can also get feedback from others if they have differing thoughts though.