[
https://issues.apache.org/jira/browse/TRAFODION-2331?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15633431#comment-15633431
]
ASF GitHub Bot commented on TRAFODION-2331:
-------------------------------------------
GitHub user DaveBirdsall opened a pull request:
https://github.com/apache/incubator-trafodion/pull/814
[TRAFODION-2331] UPD STATS sometimes fails silently after some other …
…failure
There are three changes in this check-in:
1. UPDATE STATISTICS does a ROLLBACK after detecting a SQL error. Sometimes
the ROLLBACK fails, because the Executor has already rolled back the
transaction. This caused UPDATE STATISTICS to get confused about transaction
state. This has been fixed; now after an error on ROLLBACK, UPDATE STATISTICS
simply checks to see if a transaction is still active, and if not, just ignores
the error.
2. When executing DDL statements, UPDATE STATISTICS tries to start a
transaction. If it is confused about transaction state (as in item 1 above), it
thinks this attempt to start a transaction has failed. In that particular case,
no error was being reported. This has been fixed.
3. Some obsolete code that used static SQL for BEGIN WORK, COMMIT and
ROLLBACK has been removed.
You can merge this pull request into a Git repository by running:
$ git pull https://github.com/DaveBirdsall/incubator-trafodion
SampleTableBug
Alternatively you can review and apply these changes as the patch at:
https://github.com/apache/incubator-trafodion/pull/814.patch
To close this pull request, make a commit to your master/trunk branch
with (at least) the following in the commit message:
This closes #814
----
commit 9e37d9c22c74daee32399dac42f84c43f7141c5e
Author: Dave Birdsall <[email protected]>
Date: 2016-11-03T16:46:05Z
[TRAFODION-2331] UPD STATS sometimes fails silently after some other failure
----
> 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)
