[TRAFODION-3300] ROUND function on extreme data types fails with numeric overflows

• Type: Bug
• Status: Resolved
• Priority: Major
• Resolution: Fixed
• Affects Version/s: 2.4
• Fix Version/s: 2.4
• Component/s: sql-cmp, sql-exe
• Assignee: Dave Birdsall
• Reporter: Dave Birdsall
• Created: 18/Apr/19 17:09
• Updated: 24/Apr/19 20:01
• Resolved: 24/Apr/19 20:01

Description

The following script reproduces the failures.

drop table if exists t;
create table t (
c1 numeric(128),
c2 numeric(128,1),
c3 numeric(128,2));

insert into t values (
12345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678,
1234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567.8,
123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456.78
);
select * from t;

select ROUND(c1, 1) from t;
select ROUND(c2, 1) from t;
select ROUND(c3, 1) from t;

select ROUND(c1, 2) from t;
select ROUND(c2, 2) from t;
select ROUND(c3, 2) from t;

select ROUND(c1, 3) from t;
select ROUND(c2, 3) from t;
select ROUND(c3, 3) from t;

When run, some of the ROUND functions fail with numeric overflows. They all should execute successfully.

>>drop table if exists t;

— SQL operation complete.
>>create table t (
+>c1 numeric(128),
+>c2 numeric(128,1),
+>c3 numeric(128,2));

— SQL operation complete.
>>
>>insert into t values (
+>12345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678,
+>1234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567.8,
+>123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456.78
+>);

— 1 row(s) inserted.
>>select * from t;

C1 C2 C3
--------------------------------------------------------------------------------------------------------------------------------- ---------------------------------------------------------------------------------------------------------------------------------- ----------------------------------------------------------------------------------------------------------------------------------

12345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678 1234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567.8 123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456.78

— 1 row(s) selected.
>>
>>select ROUND(c1, 1) from t;

(EXPR)
---------------------------------------------------------------------------------------------------------------------------------

12345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678

— 1 row(s) selected.
>>select ROUND(c2, 1) from t;

• • • ERROR[8411] A numeric overflow occurred during an arithmetic computation or data conversion. Intermediate conversion of Source Type:CHAR(REC_BYTE_F_ASCII,129 BYTES,ISO88591) Source Value:123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456780 to Target Type:NUMERIC(REC_NUM_BIG_SIGNED).

— 0 row(s) selected.
>>select ROUND(c3, 1) from t;

(EXPR)
----------------------------------------------------------------------------------------------------------------------------------

123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456.80

— 1 row(s) selected.
>>
>>select ROUND(c1, 2) from t;

(EXPR)
---------------------------------------------------------------------------------------------------------------------------------

12345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678

— 1 row(s) selected.
>>select ROUND(c2, 2) from t;

• • • ERROR[8411] A numeric overflow occurred during an arithmetic computation or data conversion. Intermediate conversion of Source Type:CHAR(REC_BYTE_F_ASCII,130 BYTES,ISO88591) Source Value:1234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567800 to Target Type:NUMERIC(REC_NUM_BIG_SIGNED).

— 0 row(s) selected.
>>select ROUND(c3, 2) from t;

• • • ERROR[8411] A numeric overflow occurred during an arithmetic computation or data conversion. Intermediate conversion of Source Type:CHAR(REC_BYTE_F_ASCII,129 BYTES,ISO88591) Source Value:123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456780 to Target Type:NUMERIC(REC_NUM_BIG_SIGNED).

— 0 row(s) selected.
>>
>>select ROUND(c1, 3) from t;

(EXPR)
---------------------------------------------------------------------------------------------------------------------------------

12345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678

— 1 row(s) selected.
>>select ROUND(c2, 3) from t;

• • • ERROR[8411] A numeric overflow occurred during an arithmetic computation or data conversion. Intermediate conversion of Source Type:CHAR(REC_BYTE_F_ASCII,131 BYTES,ISO88591) Source Value:12345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678000 to Target Type:NUMERIC(REC_NUM_BIG_SIGNED).

— 0 row(s) selected.
>>select ROUND(c3, 3) from t;

• • • ERROR[8411] A numeric overflow occurred during an arithmetic computation or data conversion. Intermediate conversion of Source Type:CHAR(REC_BYTE_F_ASCII,130 BYTES,ISO88591) Source Value:1234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567800 to Target Type:NUMERIC(REC_NUM_BIG_SIGNED).

— 0 row(s) selected.
>>exit;

End of MXCI Session

Issue Links

• links to: GitHub Pull Request #1831