[
https://issues.apache.org/jira/browse/TRAFODION-165?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=14696220#comment-14696220
]
Atanu Mishra commented on TRAFODION-165:
----------------------------------------
Hans Zeller (hans-zeller) wrote on 2014-02-19: #1
The value causing the error is the "std deviation of frequency", which was
defined as a numeric(12,3) in the table and as a double in the code. When
converting from static to dynamic SQL, the logic to convert the value was no
longer working. I'm going to change the code to store the value as a
numeric(12,3) in the update stats code to avoid conversion in the SQL insert
statements in the SB_HISTOGRAM_INTERVALS table.
Changed in trafodion:
status: New → In Progress
Hans Zeller (hans-zeller) wrote on 2014-02-19: #2
Fix delivered into project/datalake_64_1 branch, rev. 37671.
Changed in trafodion:
status: In Progress → Fix Committed
Weishiun Tsai (wei-shiun-tsai) wrote on 2014-03-04: #3
Verified on the trafodion-ci-project-datalake_64_1-20140303-v38027_release.tar
build. This problem has been fixed:
-bash-4.1$ sqlci
Trafodion Conversational Interface 0.7.0
(c) Copyright 2014 Hewlett-Packard Development Company, LP.
>>create table t (pic_x_a PIC X(3) not null not droppable, pic_x_b PIC X(1) not
>>null not droppable, pic_x_c PIC X(2) not null not droppable, PRIMARY
>>KEY(pic_x_c, pic_x_b, pic_x_a) not droppable);
--- SQL operation complete.
>>create index ta ON t ( pic_x_a );
--- SQL operation complete.
>>insert into t values
>>('jo','Z','jo'),('al','Q','al'),('P','P','P'),('B','A','ed'),('jo','C','ek'),('JO','D','em'),('al','E','bo'),('
>> al','F','di'),('al ','F','al'),(' al','F','al');
--- 10 row(s) inserted.
>>update statistics for table t on every column;
--- SQL operation complete.
>>drop table t cascade;
--- SQL operation complete.
>>set schema trafodion.arkcase_arkt0025;
--- SQL operation complete.
>>create table t (pic_x_a PIC X(3) not null not droppable, pic_x_b PIC X(1) not
>>null not droppable, pic_x_c PIC X(2) not null not droppable, PRIMARY
>>KEY(pic_x_c, pic_x_b, pic_x_a) not droppable);
--- SQL operation complete.
>>create index ta ON t ( pic_x_a );
--- SQL operation complete.
>>insert into t values
>>('jo','Z','jo'),('al','Q','al'),('P','P','P'),('B','A','ed'),('jo','C','ek'),('JO','D','em'),('al','E','bo'),('
>> al','F','di'),('al ','F','al'),(' al','F','al');
--- 10 row(s) inserted.
>>update statistics for table t on every column;
--- SQL operation complete.
Changed in trafodion:
status: Fix Committed → Fix Released
> LP Bug: 1270031 - Update statistics sees errors with certain schema name
> ------------------------------------------------------------------------
>
> Key: TRAFODION-165
> URL: https://issues.apache.org/jira/browse/TRAFODION-165
> Project: Apache Trafodion
> Issue Type: Bug
> Components: sql-general
> Reporter: Weishiun Tsai
> Assignee: Hans Zeller
> Priority: Critical
> Fix For: 1.0 (pre-incubation)
>
>
> Update statistics sees errors on a 10-row table with index. The strange
> thing is that it only happens when the table is created using certain schema
> name. In the following example, the first part shows that the commands ran
> fine when the schema name was trafodion.mytest. The second part shows that
> the same sequence of commands encountered error 9200/8411/8839/8609 at the
> update statistics statement when the schema name was
> trafodion.arkcase_arkt0025.
> The build is the beta build
> trafodion-ci-release-trafodion_beta-20140115-v36803_release.tar running on a
> workstation.
> -bash-4.1$ sqlci
> Hewlett-Packard NonStop(TM) SQL/MX Conversational Interface 2.5
> (c) Copyright 2003-2010 Hewlett-Packard Development Company, LP.
> >>set schema trafodion.mytest;
> --- SQL operation complete.
> >>
> >>create table t (pic_x_a PIC X(3) not null not droppable, pic_x_b PIC X(1)
> >>not null not droppable, pic_x_c PIC X(2) not null not droppable, PRIMARY
> >>KEY(pic_x_c, pic_x_b, pic_x_a) not droppable);
> --- SQL operation complete.
> >>create index ta ON t ( pic_x_a );
> --- SQL operation complete.
> >>insert into t values
> >>('jo','Z','jo'),('al','Q','al'),('P','P','P'),('B','A','ed'),('jo','C','ek'),('JO','D','em'),('al','E','bo'),('
> >> al','F','di'),('al ','F','al'),(' al','F','al');
> --- 10 row(s) inserted.
> >>update statistics for table t on every column;
> --- SQL operation complete.
> >>
> >>drop table t cascade;
> --- SQL operation complete.
> >>
> >>set schema trafodion.arkcase_arkt0025;
> --- SQL operation complete.
> >>
> >>create table t (pic_x_a PIC X(3) not null not droppable, pic_x_b PIC X(1)
> >>not null not droppable, pic_x_c PIC X(2) not null not droppable, PRIMARY
> >>KEY(pic_x_c, pic_x_b, pic_x_a) not droppable);
> --- SQL operation complete.
> >>create index ta ON t ( pic_x_a );
> --- SQL operation complete.
> >>insert into t values
> >>('jo','Z','jo'),('al','Q','al'),('P','P','P'),('B','A','ed'),('jo','C','ek'),('JO','D','em'),('al','E','bo'),('
> >> al','F','di'),('al ','F','al'),(' al','F','al');
> --- 10 row(s) inserted.
> >>update statistics for table t on every column;
> *** ERROR[9200] UPDATE STATISTICS for table TRAFODION.ARKCASE_ARKT0025.T
> encountered an error (8411) from statement HSinsertHistint::flush().
> *** ERROR[8411] A numeric overflow occurred during an arithmetic computation
> or data conversion. Conversion of Source Type:LARGEINT(REC_BIN64_SIGNED)
> Source Value:4599332999182373.267 to Target Type:LARGEINT(REC_BIN64_SIGNED).
> *** ERROR[8839] Transaction was aborted.
> *** ERROR[9200] UPDATE STATISTICS for table TRAFODION.ARKCASE_ARKT0025.T
> encountered an error (8411) from statement FLUSH_STATISTICS.
> *** ERROR[8411] A numeric overflow occurred during an arithmetic computation
> or data conversion. Conversion of Source Type:LARGEINT(REC_BIN64_SIGNED)
> Source Value:4599332999182373.267 to Target Type:LARGEINT(REC_BIN64_SIGNED).
> *** ERROR[8839] Transaction was aborted.
> *** ERROR[9200] UPDATE STATISTICS for table TRAFODION.ARKCASE_ARKT0025.T
> encountered an error (8609) from statement Process_Query.
> *** ERROR[8609] Waited rollback performed without starting a transaction.
> --- SQL operation failed with errors.
> >>
> >>drop table t cascade;
> --- SQL operation complete.
--
This message was sent by Atlassian JIRA
(v6.3.4#6332)
