[
https://issues.apache.org/jira/browse/TRAFODION-2331?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15634549#comment-15634549
]
ASF GitHub Bot commented on TRAFODION-2331:
-------------------------------------------
Github user asfgit closed the pull request at:
https://github.com/apache/incubator-trafodion/pull/814
> Update stats sometimes fails silently after some other failure
> --------------------------------------------------------------
>
> Key: TRAFODION-2331
> URL: https://issues.apache.org/jira/browse/TRAFODION-2331
> Project: Apache Trafodion
> Issue Type: Bug
> Components: sql-cmp
> Affects Versions: 2.1-incubating
> Environment: All
> Reporter: David Wayne Birdsall
> Assignee: David Wayne Birdsall
>
> In the script below, we have an UPDATE STATISTICS CREATE SAMPLE fail due to
> the bug described in JIRA TRAFODION-2322. Then we do another UPDATE
> STATISTICS CREATE SAMPLE which appears to succeed, yet doesn't. That is, it
> reports no error, but no sample table is created.
> drop schema if exists trafodion.updatestats_incremental cascade;
> create schema trafodion.updatestats_incremental;
> set schema trafodion.updatestats_incremental;
> create table myFullTable (
> c_char char(50),
> c_char_upshift char(50) upshift,
> c_char_not_casespecific char(50) not casespecific,
> c_char_varying char varying(50),
> c_char_varying_upshift char varying(50) upshift,
> c_char_varying_not_casespecific char varying(50) not casespecific,
> c_varchar varchar(50),
> c_varchar_upshift varchar(50) upshift,
> c_varchar_not_casespecific varchar(50) not casespecific,
> c_nchar nchar(50),
> c_nchar_upshift nchar(50) upshift,
> c_nchar_not_casespecific nchar(50) not casespecific,
> c_nchar_varying nchar varying(50),
> c_nchar_varying_upshift nchar varying(50) upshift,
> c_nchar_varying_not_casespecific nchar varying(50) not casespecific,
> c_numeric numeric(9,2),
> c_numeric_unsigned numeric(9,2) unsigned,
> c_decimal decimal(9,2),
> c_decimal_unsigned decimal(9,2) unsigned,
> c_integer integer,
> c_integer_unsigned integer unsigned,
> c_largeint largeint,
> c_smallint smallint,
> c_smallint_unsigned smallint unsigned,
> c_float float(10),
> c_real real,
> c_double_precision double precision,
> c_date date,
> c_time time, -- default is (0)
> c_time5 time(5),
> c_timestamp timestamp, -- default is (6)
> c_timestamp5 timestamp(5),
> c_interval interval year to month,
> c_clob clob,
> c_blob blob
> );
> insert into myFullTable values
> ('CHAR_1', 'char_1', 'char_1', 'CHARVAR_1', 'charvar_1', 'charvar_1',
> 'VARCHAR_1', 'varchar_1', 'varchar_1', 'NCHAR_1', 'nchar_1', 'nchar_1',
> 'NCHARVAR_1', 'ncharvar_1', 'ncharvar_1', -1, 1, -1.11, 1.11, -1, 1, -1, -1,
> 1, -1.11, -1.11, -1.11, date '2001-01-01', time '01:01:01', time
> '01:01:01.12345', timestamp '2001-01-01 01:01:01.123456', timestamp
> '2001-01-01 01:01:01.12345', interval '01-01' year to month, 'clob_1',
> 'blob_1'
> ),
> ('CHAR_2', 'char_2', 'char_2', 'CHARVAR_2', 'charvar_2', 'charvar_2',
> 'VARCHAR_2', 'varchar_2', 'varchar_2', 'NCHAR_2', 'nchar_2', 'nchar_2',
> 'NCHARVAR_2', 'ncharvar_2', 'ncharvar_2', -2, 2, -2.22, 2.22, -2, 2, -2, -2,
> 2, -2.22, -2.22, -2.22, date '2002-02-02', time '02:02:02', time
> '02:02:02.12345', timestamp '2002-02-02 02:02:02.123456', timestamp
> '2002-02-02 02:02:02.12345', interval '02-02' year to month, 'clob_2',
> 'blob_2'
> ),
> ('CHAR_3', 'char_3', 'char_3', 'CHARVAR_3', 'charvar_3', 'charvar_3',
> 'VARCHAR_3', 'varchar_3', 'varchar_3', 'NCHAR_3', 'nchar_3', 'nchar_3',
> 'NCHARVAR_3', 'ncharvar_3', 'ncharvar_3', -3, 3, -3.33, 3.33, -3, 3, -3, -3,
> 3, -3.33, -3.33, -3.33, date '2003-03-03', time '03:03:03', time
> '03:03:03.12345', timestamp '2003-03-03 03:03:03.123456', timestamp
> '2003-03-03 03:03:03.12345', interval '03-03' year to month, 'clob_3',
> 'blob_3'
> ),
> ('CHAR_4', 'char_4', 'char_4', 'CHARVAR_4', 'charvar_4', 'charvar_4',
> 'VARCHAR_4', 'varchar_4', 'varchar_4', 'NCHAR_4', 'nchar_4', 'nchar_4',
> 'NCHARVAR_4', 'ncharvar_4', 'ncharvar_4', -4, 4, -4.44, 4.44, -4, 4, -4, -4,
> 4, -4.44, -4.44, -4.44, date '2004-04-04', time '04:04:04', time
> '04:04:04.12345', timestamp '2004-04-04 04:04:04.123456', timestamp
> '2004-04-04 04:04:04.12345', interval '04-04' year to month, 'clob_4',
> 'blob_4'
> ),
> (null, null, null, null, null, null, null, null, null, null, null, null,
> null, null, null, null, null, null, null, null, null, null, null, null, null,
> null, null, null, null, null, null, null, null, null, null
> )
> ;
> update statistics for table myFullTable on necessary column, (c_char) to
> c_clob sample random 100 percent persistent;
> drop schema if exists trafodion.updatestats_incremental_new cascade;
> create schema trafodion.updatestats_incremental_new;
> set schema trafodion.updatestats_incremental_new;
> create table mytable(a int not null not droppable primary key, b int);
> upsert using load into mytable(a, b)
> select c0+c1*10+c2*100+c3*1000,
> c0+c1*10+c2*100+c3*1000
> from (values(1)) t
> transpose 0,1,2,3,4,5,6,7,8,9 as c0
> transpose 0,1,2,3,4,5,6,7,8,9 as c1
> transpose 0,1,2,3,4,5,6,7,8,9 as c2
> transpose 0,1,2,3,4,5,6,7,8,9 as c3;
> update mytable set b=mod(b,10);
> update statistics for table mytable create sample random 100 percent;
> get tables;
> select object_uid from "_MD_".objects where
> schema_name='UPDATESTATS_INCREMENTAL_NEW' and object_name='MYTABLE';
> -- the next select should return 1 row, but it returns 0 rows; no sample
> table was created!
> select * from updatestats_incremental_new.sb_persistent_samples;
--
This message was sent by Atlassian JIRA
(v6.3.4#6332)
