You are viewing a plain text version of this content. The canonical link for it is here.
Posted to issues@trafodion.apache.org by "Qifan Chen (JIRA)" <ji...@apache.org> on 2015/07/09 01:46:04 UTC
[jira] [Commented] (TRAFODION-22) SQL function AVG over Large Int
data type could overflow
[ https://issues.apache.org/jira/browse/TRAFODION-22?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=14619601#comment-14619601 ]
Qifan Chen commented on TRAFODION-22:
-------------------------------------
here are the steps to reproduce.
1. create table: create table tlgint (a largeint);
2. load the table: insert into table tlgint values(5009199676962937417),(5009199676962955591);
3. obey the following script dml to reproduce.
---- dml ----------
log dml.log clear;
prepare xx from
select avg(a) from tlgint;
explain xx;
execute xx;
> SQL function AVG over Large Int data type could overflow
> --------------------------------------------------------
>
> Key: TRAFODION-22
> URL: https://issues.apache.org/jira/browse/TRAFODION-22
> Project: Apache Trafodion
> Issue Type: Bug
> Reporter: Qifan Chen
> Labels: patch
>
> The execution of the following query in qat/qatdml04 raises an SQL error:
> select pic_comp_1
> from btsel04
> group by pic_comp_1
> having avg(SYSKEY) > 0;
> *** ERROR[8411] A numeric overflow occurred during an arithmetic computation or data conversion. Intermediate conversion of Source Type:CHAR(REC_BYTE_F_ASCII,39 BYTES,ISO88591) Source Value:100183993539258930080000000000000000000 to Target Type:NUMERIC(REC_NUM_BIG_SIGNED).
> The selection predicate computes the avg as follows. The multiplication of the sum() with a large number is the root cause.
> HASH_GROUPBY ============================== SEQ_NO 2 ONLY CHILD 1
> REQUESTS_IN .............. 1
> ROWS_OUT ................. 1
> EST_OPER_COST ............ 0.01
> EST_TOTAL_COST ........... 0.01
> DESCRIPTION
> memory_quota ........... 1200 MB
> max_card_est ........... 2
> fragment_id ............ 0
> parent_frag ............ (none)
> fragment_type .......... master
> grouping_columns ....... TRAFODION.SCH.BTSEL04.PIC_COMP_1
> aggregates ............. sum(TRAFODION.SCH.BTSEL04.SYSKEY), count(1 )
> selection_predicates ... ((cast((sum(TRAFODION.SCH.BTSEL04.SYSKEY) * 10000
> ...0)) / cast(count(1 ))) > cast((cast(0) * 10000
> How to reproduce.
> 1. cd sql/regression/qat
> 2. ./runregr -sb
> 3. Once the entire test suite in step finishes, execute the query in question individually.
> --------
> Two rows involved in the computation. Sum of 5009199676962937417 and 5009199676962955591 is 10018399353925893008. That value, when multiple by 10^19, causes overflow.
> 100 5009199676962937417
> 100 5009199676962955591
--
This message was sent by Atlassian JIRA
(v6.3.4#6332)