Update stats does not work for a volatile table

[Weishiun Tsai]

Update statistics currently does not work for a volatile table, as shown in the following example. It has trouble handling SB_HISTOGRAMS in a volatile schema and returns error 4082 right now.

This is seen on the v1.0.0 rc2 build:

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

Here is the entire script to reproduce this problem:

create schema mytest;
set schema mytest;
create volatile table myvtbl (c1 int, c2 int);
insert into myvtbl values (1, 1);
update statistics for table myvtbl on every column;
drop schema mytest cascade;

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

Here is the execution output:

>>set schema mytest;

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

--- SQL operation complete.
>>insert into myvtbl values (1, 1);

--- 1 row(s) inserted.
>>update statistics for table myvtbl on every column;

*** ERROR[9200] UPDATE STATISTICS for table TRAFODION.VOLATILE_SCHEMA_MXID110000100932122891606570214610000000002.MYVTBL encountered an error (4082) from statement .

*** ERROR[4082] Object TRAFODION.VOLATILE_SCHEMA_MXID110000100932122891606570214610000000002.SB_HISTOGRAMS does not exist or is inaccessible.

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

--- SQL operation failed with errors.
>>drop schema mytest cascade;

[Weishiun Tsai (wei-shiun-tsai)]

After digging out some old logs, this feature apparently used to work at some point. So it is a regression:

SQL>CREATE VOLATILE TABLE vsaltnum(
            colint int not null,
            colnum numeric(11,3) not null,
            colvchrucs2 varchar(115) character set ucs2 not null,
            primary key (colint, colnum))
            salt using 7 partitions on (colnum)

--- SQL statement completed.

SQL>UPSERT USING LOAD INTO vsaltnum SELECT
            c1+c2*10+c3*100+c4*1000,
            cast(c1+c2*10+c3*100+c4*1000 as numeric(11,3)),
            cast(c1+c2*10+c3*100+c4*1000 as varchar(115) character set ucs2)
            from (values(1)) t
            transpose 0,1,2,3,4,5,6,7,8,9 as c1
            transpose 0,1,2,3,4,5,6,7,8,9 as c2
            transpose 0,1,2,3,4,5,6,7,8,9 as c3
            transpose 0,1,2,3,4,5,6,7,8,9 as c4

--- 10000 rows inserted.

SQL>UPDATE STATISTICS FOR TABLE vsaltnum ON EVERY COLUMN

[Weishiun Tsai (wei-shiun-tsai)]

The full output....

SQL>CREATE VOLATILE TABLE vsaltnum(
            colint int not null,
            colnum numeric(11,3) not null,
            colvchrucs2 varchar(115) character set ucs2 not null,
            primary key (colint, colnum))
            salt using 7 partitions on (colnum)

--- SQL statement completed.

SQL>UPSERT USING LOAD INTO vsaltnum SELECT
            c1+c2*10+c3*100+c4*1000,
            cast(c1+c2*10+c3*100+c4*1000 as numeric(11,3)),
            cast(c1+c2*10+c3*100+c4*1000 as varchar(115) character set ucs2)
            from (values(1)) t
            transpose 0,1,2,3,4,5,6,7,8,9 as c1
            transpose 0,1,2,3,4,5,6,7,8,9 as c2
            transpose 0,1,2,3,4,5,6,7,8,9 as c3
            transpose 0,1,2,3,4,5,6,7,8,9 as c4

--- 10000 rows inserted.

SQL>UPDATE STATISTICS FOR TABLE vsaltnum ON EVERY COLUMN

--- SQL statement completed.