Question Re: [314]: numeric overflow ? in Technology Q&A

[314]: numeric overflow ?

Former Member — Wed, 26 Sep 2012 14:18:03 GMT

Hi,

I try to understand ...

I want to aggregate some values, but i've got this error message :

[314]: numeric overflow: search table error: [6944] AttributeEngine: overflow in numeric calculation;JEAggregate pop1(setIndex('TEST:F_EXPORT','en'),setAttribute('MT_VALEUR'),setAggregationTypes(1))

SQL is :

SQL
select cd_pays,sum(mt_valeur) from f_export group by cd_pays

SQL

select

cd_pays,sum(mt_valeur)

from

f_export

group

by cd_pays

I work with AWS

max() returns values..

Re: [314]: numeric overflow ?

Former Member — Thu, 27 Sep 2012 00:51:04 GMT

It looks like a real overflow issue. The numeric data types on HANA do have limits - apparently those limits also apply to an aggregation function such as sum(). You find the maximum values for the supported data types on http://help.sap.com/hana/html/_csql_data_types.html

Just choose a data type that can hold bigger values. I'll contact development to check if that limitation is intentional, because it looks like a bug to me...

--juergen

Re: [314]: numeric overflow ?

Former Member — Thu, 27 Sep 2012 03:52:28 GMT

would you mind posting your table definition? I still stick to my initial assumption, but after some testing I can say that it works fine (on Revision 36) at least for the data types I tested (tinyint with max_value = 255 and decimal(3) with max_value = 999) - this works fine and returns [A, 300]:

create column table foobar(

foo varchar(1),

bar tinyint

);

insert into foobar values('A',100);

insert into foobar values('A',200);

select foo,sum(bar) from foobar group by foo;

Re: [314]: numeric overflow ?

Former Member — Thu, 27 Sep 2012 17:16:13 GMT

Hi Stéphane,

You might have hit the upper limit of the datatype on column "mt_valeur" and the AttributeEngine gives you the numeric overflow error message.

You may need to alter the table column datatype to higher (for example from REAL to DOUBLE), or you can try another workaround with datatype conversion function (for example TO_DOUBLE) before the aggregation function:

select

cd_pays,sum(TO_DOUBLE(mt_valeur))

from

f_export

group

by cd_pays

Regards,

Ferry

Re: [314]: numeric overflow ?

Former Member — Fri, 28 Sep 2012 08:23:14 GMT

Hi Juergen, Ferry

You're right Ferry, it's ok with to_double()

The max value is 69 943 708 731 and column is integer.

But it looks strange, Juergen's example is ok too on my side

I share Juergen's point of view, it looks like a bug ...

My AWS's version is 1.00.28.361821

Thanks

Stéphane

Re: [314]: numeric overflow ?

Former Member — Fri, 28 Sep 2012 08:35:38 GMT

Hi Stéphane,

Obviously I used a stupid example - I'm quite sure that internally HANA will use a larger data type for tinyint, so the overflow doesn't happen. With integer, I get the numeric overflow. To keep data conversion as cheap as possible, I would cast to the next-bigger integer type which is bigint:

drop table foobar cascade;

create column table foobar(

foo varchar(1),

bar integer

);

insert into foobar values('A',2147483647);

insert into foobar values('A',200);

; does not work:

select foo,sum(bar) from foobar group by foo;

; works:

select foo,sum(to_bigint(bar)) from foobar group by foo;

--juergen

Re: [314]: numeric overflow ?

Former Member — Fri, 16 Sep 2016 16:14:04 GMT

This message was moderated.