Skip to Content
author's profile photo SEBASTIEN FLAESCH
0
SEBASTIEN FLAESCH

Is DECIMAL(p,s) precision limit really 38 or rather 34 ?

Posted on Sep 15, 2017 at 03:16 PM 533 Views

The documentation states that DECIMAL(p,s) can have p<=38.
In reality, the exact max precision is 34 significant digits.
Otherwise, how do you explain:

create local temporary table #t1 ( pk int, d1 decimal(38,0) );
insert into #t1 values ( 101, 12345678901234567890123456789012345 );
select * from #t1 where d1 =  12345678901234567890123456789012345 or d1 =  1234567890123456789012345678901235 ;

Returns:

0 rows affected (overall time 1754 usec; server time 353 usec)
1 row affected (overall time 2084 usec; server time 329 usec)
PK,D1
101,12345678901234567890123456789012350
1 row selected (overall time 3331 usec; server time 130 usec)

Can someone explain / confirm?
Several questions posted here an not much feedback... someone there?

Add a comment
10|10000 characters needed characters exceeded

Assigned Tags

Related questions

2 Answers

  • Best Answer
    author's profile photo Lars Breddemann
    Lars Breddemann
    Posted on Sep 18, 2017 at 05:19 AM
    1

    Hmm... this one looks tricky and could be a bug.

    Tracing the actual output of the query on different levels (JDBC, server-side SQL trace) shows that the result decimal is always rounded to the 34 precision (i.e. ending in ...12350 instead of ...12345). Internally, though, processing is not affected by this "rounding". So filtering/matching values works with the full precision and feeding the data into functions also works with the full precision.

    Example 1:

    select pk, to_bigint(d1) 
from t1 where d1 =  12345678901234567890123456789012345;

Started: 2017-09-18 14:34:43
Could not execute 'select pk, to_bigint(d1) from t1 where d1 = 12345678901234567890123456789012345' in 5 ms 283 µs . 
[314]: numeric overflow:cannot convert to BigInt type: 12345678901234567890123456789012345 at function to_bigint() (at pos 23) 

>>> FULL PRECISION IN ERROR MESSAGE

    Example 2:

    select pk, to_char(d1) from t1;
PK  TO_CHAR(D1)                       
101 12345678901234567890123456789012345

>>> FULL PRECISION AFTER CONVERSION TO CHAR

    I just wanted to finish this comment off by writing that this should be checked by HANA support when I found SAP Knowledgebase article https://launchpad.support.sap.com/#/notes/2410312 (2410312 - Tail bits are set to zero for big decimal in SAP HANA). This KBA basically describes the same phenomenon and declares it as "not a bug".

    So, while HANA (both HANA 1 and HANA 2) handle decimal internally with the full precision of 38 bits, the direct output is limited to 34 bit.

    cheers,

    Lars

    Add a comment
    10|10000 characters needed characters exceeded

    Comment on This Answer
  • author's profile photo Tae Suk Son
    Tae Suk Son
    Posted on Dec 13, 2017 at 09:10 AM
    0

    As Lars mentioned, internally all data is treated as Decimal(38,0). Issue is when streaming the data to the client where it was rounded to 34 precision. This planned to be resolved for the next HANA release which is HANA 2.0 SPS03.

    Add a comment
    10|10000 characters needed characters exceeded

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.