[TRAFODION-2201] drop schema cascade fails with error 1069 when update stats sample table present

• Type: Bug
• Status: Open
• Priority: Major
• Resolution: Unresolved
• Component/s: sql-cmu
• Assignee: Unassigned
• Reporter: Roberta Marton
• Created: 31/Aug/16 15:34
• Updated: 31/Aug/16 15:34

Description

Attempting a "drop schema cascade" after performing an update statistics in a separate session causes error 1069 to be returned. Test case succeeds if the table is dropped prior to dropping the schema.

test case:

Copy the following commands to a file called drop_schema_fails and execute through sqlci:

create schema mysch1;
set schema mysch1;
CREATE TABLE t1
( c1 char(12) not null, c2 char(12) not null, c3 char(12) not null,
PRIMARY KEY (C1 ASC))
SALT USING 4 PARTITIONS
ON (C1);
get tables;
upsert using load into t1
select
x1 || x2 || x3 || x4 || x5,
x2 || x4 || x1,
x5 || x3
– the from clause below creates 100,000 rows, the cross product of
– 5 copies of

{ 'a', 'b', 'c', 'd', 'e', 'f', 'g', 'h', 'i', 'j' }

from (values('a'),('b'),('c'),('d'),('e'),('f'),('g'),('h'),('i'),('j')) T(x1)
transpose 'a', 'b', 'c', 'd', 'e', 'f', 'g', 'h', 'i', 'j' as x2
transpose 'a', 'b', 'c', 'd', 'e', 'f', 'g', 'h', 'i', 'j' as x3
transpose 'a', 'b', 'c', 'd', 'e', 'f', 'g', 'h', 'i', 'j' as x4
transpose 'a', 'b', 'c', 'd', 'e', 'f', 'g', 'h', 'i', 'j' as x5;
;
select count from t1;

sh sqlci -i "drop_schema_fails(dostats)";

get tables;
– if the drop table is uncommented the test case works
--drop table t1;
get tables;
drop schema mysch1 cascade; --> this test fails with error 1069
get tables;
cleanup schema mysch1;
exit;

?section dostats
set schema mysch1;
update statistics for table t1 create sample random 10 percent;
get tables;

regression test privs1/TEST132 is showing this issue, testcase has been modified to add a drop table before the drop schema