Skip to main content

Overflow in Numeric Types in version 7.x

  • September 13, 2017
  • 1 reply
  • 5 views
victtim
Forum|alt.badge.img

This blog mentions that

"Prior to version 8.0, using a column that has a numeric data type with the functions SUM, SUM_FLOAT, or AVG could result in numeric overflow. When using SUM, SUM_FLOAT, or AVG functions with a numeric data type, Vertica would silently overflow if the result exceeded the precision you set when defining the numeric column."

I don't quite understand or rather don't see it in action. For example, if I have this example table

create table test_numeric(n NUMERIC(5,3));
insert into test_numeric values (12.123);

Now, if I run the below select statement, the result is 221240 which exceeds the precision 5 that I defined on the numeric column. However,

select sum(n*10000) from test_numeric
    sum
------------
 221240.000
(1 row)

I suppose this is because the result is implicitly cast to FLOAT? Or did it in fact "silently overflow", but my example is too naive so can't really observe the incorrect result?

    1 reply

    victtim
    Forum|alt.badge.img
    • victtim
    • Author
    • Participating Frequently
    • September 14, 2017

    A better example -

    create table test_numeric(n NUMERIC(1024,1023));
insert into test_numeric values (1.123);

select sum(n*10) from trading.test_numeric;
ERROR 4408:  Precision must be less than 1024; result would be numeric(1042,1023)

    This tells me that what is actually happening is that Vertica tries to extends the precision without compromising the scale. If it cannot do this successfully, then it errors as above. Is this what you mean by "silently overflow" in the blog?

    Still scrolling?
    Ask a question
    Terms & ConditionsAccessibility statement