Extra Decimal Storage

[former_SQLA_member1694868]

SQLA 17.0.4 Build 2053 (also seen in 12 and 16 though) It appears that fields defined as decimal (30,6) can store more than 6 decimal places.

Take this example:

drop table if exists zMyRate;
create table zMyRate (
    ID integer default autoincrement, 
    Rate dec(30,6) NOT NULL,
    PRIMARY KEY (ID ASC))
    ;

insert into zMyRate (Rate) 
VALUES 
(1.234567), 
(8.901234), 
(5.678901),
(2.34567899)
;

SELECT ID, Rate, cast(Rate as dec(30,8)) as ExtraDecRate FROM zMyRate;
ID,Rate,ExtraDecRate
1,1.234567,1.23456700
2,8.901234,8.90123400
3,5.678901,5.67890100
4,2.345679,2.34567900

The last two digits on the fourth value are truncated during the insert as I would expect. But if you update these with a calculation that is not casted, the results are stored:

Update zMyRate
set Rate = Rate * 1.3456
;

SELECT ID, Rate, cast(Rate as dec(30,8)) as ExtraDecRate FROM zMyRate;
ID,Rate,ExtraDecRate
1,1.661233,1.66123336
2,11.977500,11.97750047
3,7.641529,7.64152919
4,3.156346,3.15634566

The extra decimal places are only returned if you cast the field as something defined with the extra decimals but not on the normal select as in column 2.

What is interesting is that if you alter the table and define the field as decimal (12,6), this behavior stops and the numbers are truncated in both situations - insert and update with calculation.

[Breck_Carter]

I'm guessing this has something to do with the fact the exact same storage is used for values that differ by one digit of actual precision and/or scale.

The formula for storage requirements is thus, where p and s are the actual precision and scale of the value, not the declared precision and scale:

2 + TRUNCNUM ( ( p - s + 1 ) / 2, 0 ) + TRUNCNUM ( ( s + 1 ) / 2, 0 )

You can use DATALENGTH() as a check on your work.

AFAIK this is the old-school "packed decimal" format where each digit is packed into 4 bits, but the field takes up a multiple of 8 bits, so half the time there's an extra nybble (nybble, get it? smaller than a byte? 🙂

BEGIN
DECLARE d5  DECIMAL ( 30, 5 );
DECLARE d6  DECIMAL ( 30, 6 );
SET d5  = 9.123;
SET d6  = 9.1234;
SELECT d5,
       DATALENGTH ( d5 ), 
       4 as p5, 
       3 as s5, 
       2 + TRUNCNUM ( ( p5 - s5 + 1 ) / 2, 0 ) + TRUNCNUM ( ( s5 + 1 ) / 2, 0 ) AS d5_length;
SELECT d6,
       DATALENGTH ( d6 ), 
       5 as p6, 
       4 as s6, 
       2 + TRUNCNUM ( ( p6 - s6 + 1 ) / 2, 0 ) + TRUNCNUM ( ( s6 + 1 ) / 2, 0 ) AS d6_length;
END;
                              d5 DATALENGTH(d5)     p5     s5 d5_length 
-------------------------------- -------------- ------ ------ --------- 
                         9.12300              5      4      3         5

                              d6 DATALENGTH(d6)     p6     s6 d6_length 
-------------------------------- -------------- ------ ------ --------- 
                        9.123400              5      5      4         5 

I know this doesn't answer your question, but maybe it gets you further along the way 🙂