Sum of TINYINT values causes numeric overflow for not null column

[Former Member]

Does anyone know why a null column is behaving differently from a not null column when summing values in a calculation view?

In my transaction table I have two record counters type TINYINT with always value 1. One counter is a NULL column, the other a NOT NULL column.
When I preview the data from the table I see the correct sum of close to 4 million records for both counters. However when I include this in a simple calculation view then I get the below numeric overflow error for the NOT NULL column. The NULL column gives the correct sum without an error.

I understand that the conversion to tinyint causes an error, just don't understand why only in case of a NOT NULL column.

By the way, I do expect a sum to not be limited by the data type of the column.

Error: [314]: numeric overflow: 3746903 at function to_tinyint()

Thanks.

[muthuram_shanmugavel2]

Hi Jozsef,

Please change the Data Type of Not Null Column to BIGINT or Decimal.

These error would be avoided.

Best Regards,

Muthu

[nithinu]

Hi Muthu,

I just tried to produce the error. Could you please point where to change the datatype ? How would the change in NOT NULL column resolve the issue ? Can you please explain.

Regards,

Nithin

[muthuram_shanmugavel2]

Hi Nithin,

I follow the same steps mentioned in the file but I am not able to reproduce this error.

With TINYINT Data Type only, I can able to view the data in Data Preview in CV.

(I inserted one more extra in Col1 as 257)

(I mention the Data Type change is Creating one calculated column in CV and Make the Data Type to Decimal or BIGINT)

Regards,

Muthu

[Former Member]

Hello Muthu,

Thanks for your response. Indeed there are several ways to avoid the error. My main reason to post this issue is to create awareness so that other HANA view developers don't loose unnecessary time over this and also so that SAP can provide a solution if required. 

It seems that in SPS 11 rev.110 this issue does not occur anymore.

Regards,

József Zerczi.

[lucas_oliveira]

Hello Jozsef,

You're gonna have to provide more data in order for us to understand what you meant. At least I could not understand. How's this table defined? How's your counter defined ? And the 'simple calculation' you mentioned? Give us more details about the scenario and we may be able to help more.

BRs,

Lucas de Oliveira

[Former Member]

Hello Lucas,

Thanks for your reply. The test transaction table consists of a primary key column (ID) and two columns for the counters (REC_COUNT_NULL, REC_COUNT_NOT_NULL).  It is populated with 3.3 million records with unique ID and value '1' for both counters. A Data Preview of the table in HANA Studio shows the sum of both counters correctly in the Analysis tab.

A HANA information view of type calculation view sits on top of the table. It consists of an Aggregation of the table and the 3 columns in the Sematic level. A Data Preview over the calculation view shows the correct sum fro the NULL columns, but throws the numeric overflow error when trying to sum the NOT NULL column.

Using a select statement instead of the Data Preview produces the same results.

The used HANA version is SPS10 revision 102 patch 00.

Thanks.

[lucas_oliveira]

Hi,

I've made a few tests internally (HANA rev 102.03)  with no issues. I've created a table that I have to assume is similar to what you have and also a calculation view that looks similar as far as I could understand your description.

The table summed up 16 million records (after inserting more data into itself) and using data preview worked without any problems. Here's the create statement and how I fill the table:

create schema TDTTEST;
set schema TDTTEST;

create column table testtable (col1 varchar(300), val tinyint, val2 tinyint);

insert into testtable select object_name, val, map(val,0,1,1,0) from  (
select top 4000000 o1.object_name, to_int(mod(rand()*1000,2)) val from objects o1 cross join objects o2);

And the results on a CV with aggregation node:

No issues.

Additionally: at first I though you're using Counters but it seems you're not. You haven't mentioned anything about the 'simple calculations' you're using as well... So it's tough to help without proper information.

Therefore, if you need further help be as precise as you can: provide table creation statements and the model you're using (you can export it if you want to).

BRs,

Lucas de Oliveira

[Former Member]

Hello Lucas,

If both of your value columns allow NULL values then please change one into NOT NULL and then try again. The rest is the same as I have. The error only appears when summing the NOT NULL column in a calculation view.

Thanks again for your help,

Regards,

József Zérczi.

[lucas_oliveira]

I didnt think it would change  as there was no null values on the columns. Anyhow, I added the column constraints as you suggested and there was still no issue.

Again: be clear. If you want further help provide:

(1) The create statement of your table;

(2) Your Model;

(3) The SQL statement leaving HANA Studio under data preview.

BRs,

Lucas de Oliveira

[Former Member]

Hello Lucas,

My excuses for the delay in response. I was able to reproduce the issue in HANA rev 102.04. And I realized that very few records are needed in the test table. Attached are all the statements used to reproduce the issue (I am not able copy text into this message). The calculation view is the same as yours, a simple aggregation over the table.

Basic finding from the results is that summing the not null column causes an overflow error when the sum is greater than 255, which is the maximum value of a tinyint. However when summing the same values but for a column in which null are allowed, then the error does not appear.

Hopefully you are able to reproduce this error as well.

Thanks for your help.

József.

TEST_TINYINT.txt.zip

[lucas_oliveira]

Hello Jozsef,

Thanks for that. I could finally reproduce the issue on a revision 102.04 system.

However, on SP11 revision 110 that's not reproducible anymore.

As this deserves further verification from our side, could you please open an SAP support ticket ?

Please send me the ticket number *in private*.

Thanks and Regards,

Lucas de Oliveira