Drop schema cascade does not drop a table with IDENTITY column

Bug #1413767 reported by Weishiun Tsai
6
This bug affects 1 person
Affects Status Importance Assigned to Milestone
Trafodion
Fix Released
High
Cliff Gray

Bug 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.

Tags: sql-cmu
Cliff Gray (cliff-gray)
Changed in trafodion:
assignee: nobody → Cliff Gray (cliff-gray)
status: New → In Progress
Revision history for this message
Cliff Gray (cliff-gray) wrote :

Problem has been corrected. Fix will be delivered after r1.0.

Revision history for this message
Cliff Gray (cliff-gray) wrote :

Fix delivered in 1082.

Changed in trafodion:
status: In Progress → Fix Committed
Revision history for this message
Weishiun Tsai (wei-shiun-tsai) wrote :

Verified on the v0210 build installed on a workstation. This problem has been fixed:

>>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;

--- 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.
>>

Changed in trafodion:
status: Fix Committed → Fix Released
To post a comment you must log in.
This report contains Public information  
Everyone can see this information.

Other bug subscribers

Remote bug watches

Bug watches keep track of this bug in other bug trackers.