David Wayne Birdsall created TRAFODION-2331:
-----------------------------------------------
Summary: 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)
