Drop schema cascade does not drop a table with IDENTITY column

[Weishiun Tsai]

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.

[Cliff Gray (cliff-gray)]

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

[Cliff Gray (cliff-gray)]

Fix delivered in 1082.

[Weishiun Tsai (wei-shiun-tsai)]

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