[
https://issues.apache.org/jira/browse/TRAFODION-22?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Qifan Chen resolved TRAFODION-22.
---------------------------------
Resolution: Implemented
The fix is as follows. The idea is that when the result's precision is larger
than what the hardware can handle, we will automatically increase the precision
by 10.
diff -r old new
diff -r old/core/sql/optimizer/SynthType.cpp
new/core/sql/optimizer/SynthType.cpp
735c735,740
< }
---
> } else {
>
> if ( precision >= MAX_NUMERIC_PRECISION + 1 )
> precision = MINOF(precision + 10,
>
> (Lng32)CmpCommon::getDefaultNumeric(MAX_NUMERIC_PRECISION_ALLOWED));
> }
> 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)
