You are viewing a plain text version of this content. The canonical link for it is here.
Posted to issues@trafodion.apache.org by "Alice Chen (JIRA)" <ji...@apache.org> on 2015/07/22 20:15:40 UTC
[jira] [Created] (TRAFODION-330) LP Bug: 1324682 - Drop table fails to drop ‘no populate’ index from _MD_
Alice Chen created TRAFODION-330:
------------------------------------
Summary: LP Bug: 1324682 - Drop table fails to drop ‘no populate’ index from _MD_
Key: TRAFODION-330
URL: https://issues.apache.org/jira/browse/TRAFODION-330
Project: Apache Trafodion
Issue Type: Bug
Components: sql-exe
Reporter: Weishiun Tsai
Priority: Blocker
When an index is created with ‘no populate’, drop <table> cascade fails to drop this index from _MD_. Since a user can’t delete entries from _MD_, this leaves a schema unusable next time for the same test. An index with the same name can’t be created in the same schema anymore.
This is seen on the datalake v40963 build installed on a workstation. The following 2 execution outputs demonstrate this problem. The first part of the output shows that if the index is created with ‘no populate’. It remains in _MD_ even after the drop table statement. The 2nd part of the output shows that the problem does not exist if the index is not created with ‘no populate’.
Here is the entire script to reproduce this problem:
set schema MYSCH10;
select * from "_MD_".objects where schema_name='MYSCH10';
create table t (a int);
create index i on t (a) no populate;
select * from "_MD_".objects where schema_name='MYSCH10';
drop table t cascade;
select * from "_MD_".objects where schema_name='MYSCH10';
set schema MYSCH20;
select * from "_MD_".objects where schema_name='MYSCH20';
create table t (a int);
create index i on t (a);
select * from "_MD_".objects where schema_name='MYSCH20';
drop table t cascade;
select * from "_MD_".objects where schema_name='MYSCH20';
Here is the execution output to show this bug when the index is created with ‘no populate’:
>>set schema MYSCH10;
--- SQL operation complete.
>>select * from "_MD_".objects where schema_name='MYSCH10';
--- 0 row(s) selected.
>>create table t (a int);
--- SQL operation complete.
>>create index i on t (a) no populate;
--- SQL operation complete.
>>select * from "_MD_".objects where schema_name='MYSCH10';
CATALOG_NAME SCHEMA_NAME OBJECT_NAME OBJECT_TYPE OBJECT_UID CREATE_TIME REDEF_TIME VALID_DEF OBJECT_OWNER
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ----------- -------------------- -------------------- -------------------- --------- ------------
TRAFODION MYSCH10 I IX 36627768424950448 212268153897749845 212268153897749845 N 0
TRAFODION MYSCH10 T BT 36627768424949943 212268153892692153 212268153892692153 Y 33333
--- 2 row(s) selected.
>>drop table t cascade;
--- SQL operation complete.
>>select * from "_MD_".objects where schema_name='MYSCH10';
CATALOG_NAME SCHEMA_NAME OBJECT_NAME OBJECT_TYPE OBJECT_UID CREATE_TIME REDEF_TIME VALID_DEF OBJECT_OWNER
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ----------- -------------------- -------------------- -------------------- --------- ------------
TRAFODION MYSCH10 I IX 36627768424950448 212268153897749845 212268153897749845 N 0
--- 1 row(s) selected.
Here is the execution output to show that this problem does not exist when the index is not created with ‘no populate’:
>>set schema MYSCH20;
--- SQL operation complete.
>>select * from "_MD_".objects where schema_name='MYSCH20';
--- 0 row(s) selected.
>>create table t (a int);
--- SQL operation complete.
>>create index i on t (a);
--- SQL operation complete.
>>select * from "_MD_".objects where schema_name='MYSCH20';
CATALOG_NAME SCHEMA_NAME OBJECT_NAME OBJECT_TYPE OBJECT_UID CREATE_TIME REDEF_TIME VALID_DEF OBJECT_OWNER
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ----------- -------------------- -------------------- -------------------- --------- ------------
TRAFODION MYSCH20 I IX 36627768424951740 212268153910668215 212268153910668215 Y 0
TRAFODION MYSCH20 T BT 36627768424951421 212268153907477514 212268153907477514 Y 33333
--- 2 row(s) selected.
>>drop table t cascade;
--- SQL operation complete.
>>select * from "_MD_".objects where schema_name='MYSCH20';
--- 0 row(s) selected.
>>
--
This message was sent by Atlassian JIRA
(v6.3.4#6332)