[jira] [Created] (TRAFODION-710) LP Bug: 1384380 - Unpopulated index causes drop schema cascade to fail with error 1069

["Alice Chen (JIRA)" <ji...@apache.org>]

Alice Chen created TRAFODION-710:
------------------------------------

             Summary: LP Bug: 1384380 - Unpopulated index causes drop schema cascade to fail with error 1069
                 Key: TRAFODION-710
                 URL: https://issues.apache.org/jira/browse/TRAFODION-710
             Project: Apache Trafodion
          Issue Type: Bug
          Components: sql-exe
            Reporter: Weishiun Tsai
            Assignee: Anoop Sharma
            Priority: Critical


If an index was created with ‘no populate’ and if ‘populate index’ was never executed, ‘drop schema cascade’ failed with error 1069.  This is seen on the v1021_0830 daily build installed on a workstation.

The following execution output shows that mysch1 was dropped fine with the index idx1 being populated.  Dropping mysch2 returned error 1069 when idx2 was not populated.

--------------------------------------------------------------------
Here is the entire script to reproduce this problem:

set parserflags 1;

set schema mysch1;
create table t1 (a int);
create index idx1 on t1 (a) no populate;
insert into table t1 values (1),(2),(3);
select * from table(INDEX_TABLE idx1);
populate index idx1 on t1;
select * from table(INDEX_TABLE idx1);
drop schema mysch1 cascade;

set schema mysch2;
create table t2 (a int);
create index idx2 on t2 (a) no populate;
insert into table t2 values (1),(2),(3);
select * from table(INDEX_TABLE idx2);
drop schema mysch2 cascade;

--------------------------------------------------------------------
Here is the execution output:

>>set parserflags 1;

--- SQL operation complete.
>>
>>set schema mysch1;

--- SQL operation complete.
>>create table t1 (a int);

--- SQL operation complete.
>>create index idx1 on t1 (a) no populate;

--- SQL operation complete.
>>insert into table t1 values (1),(2),(3);

--- 3 row(s) inserted.
>>select * from table(INDEX_TABLE idx1);

--- 0 row(s) selected.
>>populate index idx1 on t1;

--- SQL operation complete.
>>select * from table(INDEX_TABLE idx1);

A@           SYSKEY
-----------  --------------------

          1     94061272967999114
          2     94061272968007854
          3     94061272968011695

--- 3 row(s) selected.
>>drop schema mysch1 cascade;

--- SQL operation complete.
>>
>>set schema mysch2;

--- SQL operation complete.
>>create table t2 (a int);

--- SQL operation complete.
>>create index idx2 on t2 (a) no populate;

--- SQL operation complete.
>>insert into table t2 values (1),(2),(3);

--- 3 row(s) inserted.
>>select * from table(INDEX_TABLE idx2);

--- 0 row(s) selected.
>>drop schema mysch2 cascade;

*** ERROR[1069] Schema TRAFODION.MYSCH2 could not be dropped.

--- SQL operation failed with errors.



--
This message was sent by Atlassian JIRA
(v6.3.4#6332)