[TRAFODION-938] LP Bug: 1413767 - Drop schema cascade does not drop a table with IDENTITY column

• Type: Bug
• Status: Closed
• Priority: Critical
• Resolution: Fixed
• Fix Version/s: 1.1 (pre-incubation)
• Component/s: sql-cmu
• External issue URL: https://bugs.launchpad.net/bugs/1413767
• Assignee: Cliff Gray
• Reporter: Weishiun Tsai
• Created: 22/Jul/15 18:18
• Updated: 31/Aug/15 04:19
• Resolved: 31/Aug/15 04:04

Description

As shown here in the first part of the execution output, the table mytab contains an IDENTITY column a with auto-generated SEQUENCE numbers. Drop schema my_sch1 cascade at the end of the execution returns operation complete, but a ‘get tables’ statement afterwards shows that the table mytab still exists.

The second part of the execution out shows that it requires a ‘drop table mytab cascade’ explicitly before ‘drop schema my_sch2 cascade’ to properly drop the table mytab.

This is seen on the v0121 build installed on a workstation.

--------------------------------------------------------------

Here is the entire script to reproduce this problem:

create schema my_sch1;
set schema my_sch1;

create table mytab (
a LARGEINT GENERATED ALWAYS AS IDENTITY NOT NULL NOT DROPPABLE,
b int);

insert into mytab values (DEFAULT, 1);
select * from mytab;

drop schema my_sch1 cascade;
get tables;

create schema my_sch2;
set schema my_sch2;

create table mytab (
a LARGEINT GENERATED ALWAYS AS IDENTITY NOT NULL NOT DROPPABLE,
b int);

insert into mytab values (DEFAULT, 1);
select * from mytab;

drop table mytab cascade;
get tables;
drop schema my_sch2 cascade;
get tables;

--------------------------------------------------------------

Here is the 1st and the 2nd parts of the execution output:

>>create schema my_sch1;

— SQL operation complete.
>>set schema my_sch1;

— SQL operation complete.
>>
>>create table mytab (
+>a LARGEINT GENERATED ALWAYS AS IDENTITY NOT NULL NOT DROPPABLE,
+>b int);

— SQL operation complete.
>>
>>insert into mytab values (DEFAULT, 1);

— 1 row(s) inserted.
>>select * from mytab;

A B
-------------------- -----------

1 1

— 1 row(s) selected.
>>
>>drop schema my_sch1 cascade;

— SQL operation complete.
>>get tables;

Tables in Schema TRAFODION.MY_SCH1
==================================

MYTAB

— SQL operation complete.
>>
>>create schema my_sch2;

— SQL operation complete.
>>set schema my_sch2;

— SQL operation complete.
>>
>>create table mytab (
+>a LARGEINT GENERATED ALWAYS AS IDENTITY NOT NULL NOT DROPPABLE,
+>b int);

— SQL operation complete.
>>
>>insert into mytab values (DEFAULT, 1);

— 1 row(s) inserted.
>>select * from mytab;

A B
-------------------- -----------

1 1

— 1 row(s) selected.
>>
>>drop table mytab cascade;

— SQL operation complete.
>>get tables;

— SQL operation complete.
>>drop schema my_sch2 cascade;

— SQL operation complete.
>>get tables;

— SQL operation complete.