[
https://issues.apache.org/jira/browse/TRAFODION-330?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Suresh Subbiah closed TRAFODION-330.
------------------------------------
> 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)
