You are viewing a plain text version of this content. The canonical link for it is here.
Posted to issues@trafodion.apache.org by "ASF GitHub Bot (JIRA)" <ji...@apache.org> on 2019/04/18 17:19:00 UTC
[jira] [Work logged] (TRAFODION-3300) ROUND function on extreme
data types fails with numeric overflows
[ https://issues.apache.org/jira/browse/TRAFODION-3300?focusedWorklogId=229826&page=com.atlassian.jira.plugin.system.issuetabpanels:worklog-tabpanel#worklog-229826 ]
ASF GitHub Bot logged work on TRAFODION-3300:
---------------------------------------------
Author: ASF GitHub Bot
Created on: 18/Apr/19 17:18
Start Date: 18/Apr/19 17:18
Worklog Time Spent: 10m
Work Description: DaveBirdsall commented on pull request #1831: [TRAFODION-3300] Fix overflow issues with extreme big nums + ROUND
URL: https://github.com/apache/trafodion/pull/1831
This set of changes does the following:
1. Implements the ROUND function for BIG NUM data types (e.g. NUMERIC(n,m) where n > 18) in the expression evaluator. Formerly, the binder would rewrite ROUND as a rather complicated series of other functions, which unfortunately cause overflows in extreme cases.
2. Adds extensive white box tests to the regression library for the new ROUND implementation.
3. Adds error message 4054 to cover the case where a user specifies ROUND(A,m) where m is larger than the maximum precision allowed for the data type. Formerly, we would give an incorrect error in this case.
4. Add method NAType::display to make it easier for developers to view NAType information when debugging the compiler.
5. As a test aid, I left the old code in. If CQD COMP_BOOL_15 is set to 'OFF', we use the old code. If it is 'ON' we use the new code. The default is 'ON'. It is intended that a future JIRA will remove this CQD and remove the old code, once the new code has sufficient exposure.
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
Issue Time Tracking
-------------------
Worklog Id: (was: 229826)
Time Spent: 10m
Remaining Estimate: 0h
> ROUND function on extreme data types fails with numeric overflows
> -----------------------------------------------------------------
>
> Key: TRAFODION-3300
> URL: https://issues.apache.org/jira/browse/TRAFODION-3300
> Project: Apache Trafodion
> Issue Type: Bug
> Components: sql-cmp, sql-exe
> Affects Versions: 2.4
> Reporter: David Wayne Birdsall
> Assignee: David Wayne Birdsall
> Priority: Major
> Time Spent: 10m
> Remaining Estimate: 0h
>
> The following script reproduces the failures.
> {quote}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;
> {quote}
> When run, some of the ROUND functions fail with numeric overflows. They all should execute successfully.
> {quote}>>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
> {quote}
--
This message was sent by Atlassian JIRA
(v7.6.3#76005)