[jira] [Created] (TRAFODION-307) LP Bug: 1323873 - Drop schema cascade does not drop internal histogram objects from _MD_

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

Alice Chen created TRAFODION-307:
------------------------------------

             Summary: LP Bug: 1323873 - Drop schema cascade does not drop internal histogram objects from _MD_
                 Key: TRAFODION-307
                 URL: https://issues.apache.org/jira/browse/TRAFODION-307
             Project: Apache Trafodion
          Issue Type: Bug
          Components: sql-cmp
            Reporter: Weishiun Tsai
            Assignee: Anoop Sharma
            Priority: Blocker


Drop schema cascade does not drop internal histogram objects SB_HISTOGRAMS_xxxx and SB_HISTOGRAM_INTERVA_xxxx in _MD_ that were created by the update statistics statement.  These objects have a ‘PK’ OBJECT_TYPE, so presumably they are primary keys for SB_HISTOGRAMS and SB_HISTOGRAM_INTERVALS tables.  As shown in the following execution output, after dropping the schema, these objects still exist in _MD_.OBJECTS.   Therefore, ‘get schemas’ still shows the schema, but get tables no longer shows any table in that schema.  Within hbase shell, there is no object matching the schema name either.
	
This problem not only causes ‘get schemas’ and ‘get tables’ to return inconsistent information.  It also leaves a trail of schemas that can’t be dropped after each QA regression test run.  The case is marked as critical from the test execution perspective.

This is seen on the datalake v40963 build installed on a workstation.

Here is the script to reproduce this problem:

set schema mytest;
get schemas;
get tables;
<list 'TRAFODION.MYTEST.*' from hbase shell>
select cast(object_name as char(40)) from "_MD_".objects where schema_name='MYTEST';
create table t1 (ch_1 char(10) not null primary key, int_1 int);
update statistics for table t1 on (int_1);
get schemas;
get tables;
select cast(object_name as char(40)) from "_MD_".objects where schema_name='MYTEST';
<list 'TRAFODION.MYTEST.*' from hbase shell>
drop schema mytest cascade;
get schemas;
get tables;
select cast(object_name as char(40)) from "_MD_".objects where schema_name='MYTEST';
<list 'TRAFODION.MYTEST.*' from hbase shell>

Here is the execution output of the script:

++ SQLCI +++++++++++++++++++++++++++++++++++++++++

>>set schema mytest;

--- SQL operation complete.
>>get schemas;

Schemas in Catalog TRAFODION
============================

SEABASE
_MD_

--- SQL operation complete.
>>get tables;

--- SQL operation complete.
>>select cast(object_name as char(40)) from "_MD_".objects where schema_name='MYTEST';

--- 0 row(s) selected.

++ HBASE +++++++++++++++++++++++++++++++++++++++++

hbase(main):004:0> list 'TRAFODION.MYTEST.*'
TABLE
0 row(s) in 0.0140 seconds

++ SQLCI +++++++++++++++++++++++++++++++++++++++++

>>create table t1 (ch_1 char(10) not null primary key, int_1 int);

--- SQL operation complete.
>>update statistics for table t1 on (int_1);

--- SQL operation complete.
>>get schemas;

Schemas in Catalog TRAFODION
============================

MYTEST
SEABASE
_MD_

--- SQL operation complete.
>>get tables;

Tables in Schema TRAFODION.MYTEST
=================================

SB_HISTOGRAMS
SB_HISTOGRAM_INTERVALS
T1

--- SQL operation complete.

>>select cast(object_name as char(40)) from "_MD_".objects where schema_name='MYTEST';

(EXPR)
----------------------------------------------------------------------------------------------------------------------------------------------------------------

SB_HISTOGRAMS                                                                   
SB_HISTOGRAMS_828367615_2811                                                    
SB_HISTOGRAM_INTERVALS                                                          
SB_HISTOGRAM_INTERVA_378467615_2811                                             
T1                                                                              
T1_291698566_7536                                                               

--- 6 row(s) selected.


++ HBASE +++++++++++++++++++++++++++++++++++++++++

hbase(main):006:0> list 'TRAFODION.MYTEST.*'
TABLE
TRAFODION.MYTEST.SB_HISTOGRAMS
TRAFODION.MYTEST.SB_HISTOGRAM_INTERVALS
TRAFODION.MYTEST.T1
3 row(s) in 0.0160 seconds

++ SQLCI +++++++++++++++++++++++++++++++++++++++++

>>drop schema mytest cascade;

--- SQL operation complete.

>>get schemas;

Schemas in Catalog TRAFODION
============================

MYTEST
SEABASE
_MD_

--- SQL operation complete.
>>get tables;

--- SQL operation complete.

>>select cast(object_name as char(40)) from "_MD_".objects where schema_name='MYTEST';

(EXPR)
----------------------------------------------------------------------------------------------------------------------------------------------------------------

SB_HISTOGRAMS_828367615_2811                                                    
SB_HISTOGRAM_INTERVA_378467615_2811                                             

--- 2 row(s) selected.

++ HBASE +++++++++++++++++++++++++++++++++++++++++

hbase(main):007:0> list 'TRAFODION.MYTEST.*'
TABLE
0 row(s) in 0.0130 seconds



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