A failed create view causes a volatile table to disappear

[Weishiun Tsai]

Create view on a volatile table is not supported, but it should not mess with the volatile table itself. As shown here in the execution output, the create view failed as expected, but an attempt afterwards to insert values into the volatile table also returned error 4082.

This is seen on the v1.0.0 rc2 build.

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

Here is the entire script to reproduce it:

create schema mytest;
set schema mytest;
create volatile table myvtbl (c1 int, c2 int);
create view myview as select c1, c2 from myvtbl where c1 > 10;
insert into myvtbl values (1, 1);
drop schema mytest cascade;

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

Here is the execution output:

>>create schema mytest;

--- SQL operation complete.
>>set schema mytest;

--- SQL operation complete.
>>create volatile table myvtbl (c1 int, c2 int);

--- SQL operation complete.
>>create view myview as select c1, c2 from myvtbl where c1 > 10;

*** ERROR[4082] Object TRAFODION.MYTEST.MYVTBL does not exist or is inaccessible.

--- SQL operation failed with errors.
>>insert into myvtbl values (1, 1);

*** ERROR[4082] Object TRAFODION.MYTEST.MYVTBL does not exist or is inaccessible.

*** ERROR[8822] The statement was not prepared.

>>drop schema mytest cascade;

--- SQL operation complete.

[Roberta Marton (roberta-marton)]

During binding of create view, the volatile schema in use flag was turned off but not turned back on. Therefore, subsequent requests would not look for objects in volatile schema's. Hence the insert later failed. Fixed code to restore the volatile schema in use flag.

[Weishiun Tsai (wei-shiun-tsai)]

Verified on the v0330 build installed on a workstation. This problem is now fixed:

>>create schema mytest;

--- SQL operation complete.
>>set schema mytest;

--- SQL operation complete.
>>create volatile table myvtbl (c1 int, c2 int);

--- SQL operation complete.
>>create view myview as select c1, c2 from myvtbl where c1 > 10;

*** ERROR[4082] Object TRAFODION.MYTEST.MYVTBL does not exist or is inaccessible.

--- SQL operation failed with errors.
>>insert into myvtbl values (1, 1);

--- 1 row(s) inserted.
>>select * from myvtbl;

C1 C2
----------- -----------

          1 1

--- 1 row(s) selected.
>>drop schema mytest cascade;

--- SQL operation complete.