[
https://issues.apache.org/jira/browse/TRAFODION-22?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Qifan Chen closed TRAFODION-22.
-------------------------------
The fix has been merged into the main on 7/13/2015.
> 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)
