Re: DECIMAL(2, 3) meaning

[stanilovsky evgeny]

Stamatis, as i can see this discussion is about values\literals validation  
instead of NUMERIC types, not only types at all.

I would say that type checks for precision/scale etc would fit better
inside the RelDataTypeFactory and its respective type system.
Best,
Stamatis

On Mon, Aug 7, 2023 at 12:39 AM <mbu...@gmail.com> wrote:

I found this documentation for Oracle DECIMAL data type:  
https://docs.oracle.com/en/database/oracle/oracle-database/23/sqlrf/Data-Types.html#GUID-75209AF6-476D-4C44-A5DC-5FA70D701B78,  
which explains what a SCALE > PRECISION should mean.

> Scale can be greater than precision, most commonly when e notation is  
used. When scale is greater than precision, the precision specifies the  
maximum number of significant digits to the right of the decimal point.  
For example, a column defined as NUMBER(4,5) requires a zero for the  
first digit after the decimal point and rounds all values past the  
fifth digit after the decimal point.

Let me ask a related question: in my backend I want to reject such  
numbers. What is the right way to do it?
Should this be done in a SqlShuttle? Or should some Validator class be  
extended?

Thank you,
Mihai

-----Original Message-----
From: Julian Hyde
Sent: Sunday, August 06, 2023 2:19 PM
To: dev@calcite.apache.org
Subject: Re: DECIMAL(2, 3) meaning

As I commented in https://issues.apache.org/jira/browse/CALCITE-5901, I  
don’t think it’s a bug to support behavior beyond what the standard  
requires. Which Calcite does, intentionally.

Julian

> On Aug 6, 2023, at 08:35, stanilovsky evgeny  
<estanilovs...@gridgain.com> wrote:
>
> Ok, seems like a bug.
> Feel free to fill the issue.
>
>> I have added this test to SqlOperatorTest:
>>
>>    f.checkScalar("cast(0.012 as DECIMAL(2, 5))", new  
BigDecimal("0.012"),
>>        "DECIMAL(2, 5) NOT NULL");
>>
>> and it has passed. That's why I am asking. It should fail, but it  
doesn't.
>>
>> Mihai
>>
>> -----Original Message-----
>> From: stanilovsky evgeny
>> Sent: Friday, August 04, 2023 7:00 AM
>> To: dev@calcite.apache.org
>> Subject: Re: DECIMAL(2, 3) meaning
>>
>> Hello Mihai.
>> A bit older standard describes Precision as : Precision of decimal  
floating-point values is a positive value that specifies the number of  
significant decimal digits in the mantissa.
>>
>> Thus:
>> cast(0.012 as DECIMAL(3, 3)) - ok
>> cast(0.012 as DECIMAL(2, 3)) - fail
>> cast(0.012 as DECIMAL(1, 3)) - fail
>> cast(0.012 as DECIMAL(2, 5)) - fail
>>
>>
>>> Hello,
>>>
>>>
>>> I notice that Calcite happily accepts decimal type specifications
>>> where the scale is greater than the precision.
>>>
>>> There are quite a few tests with such types.
>>>
>>>
>>> What is the meaning of such types?
>>>
>>>
>>> The SQL 92 standard has this statement on page 109:
>>>
>>>
>>> 15)The <scale> of an <exact numeric type> shall not be greater than
>>>
>>>            the <precision> of the <exact numeric type>.
>>>
>>>
>>> Thank you,
>>>
>>> Mihai