[jira] [Created] (TRAFODION-2298) INC UPD STATS: Incremental update stats on every column should ignore LOB columns

["David Wayne Birdsall (JIRA)" <ji...@apache.org>]

David Wayne Birdsall created TRAFODION-2298:
-----------------------------------------------

             Summary: INC UPD STATS: Incremental update stats on every column should ignore LOB columns
                 Key: TRAFODION-2298
                 URL: https://issues.apache.org/jira/browse/TRAFODION-2298
             Project: Apache Trafodion
          Issue Type: Bug
          Components: sql-cmp
    Affects Versions: 2.1-incubating
         Environment: All
            Reporter: David Wayne Birdsall
            Assignee: David Wayne Birdsall


This problem is similar to JIRA TRAFODION-1978, which has been fixed. With that fix, when update stats is run on LOB columns, it returns the 9246 error. When it is run on every column, it quietly ignores the LOB columns. But as shown here, 'update statistics ... sample random n percent persistent' returns perplexing 9200 and 4035 errors when run on every column, even though it returns the proper 9246 error when run on only one LOB column (c1).

>>control query default TRAF_BLOB_AS_VARCHAR 'OFF';

--- SQL operation complete.
>>control query default TRAF_CLOB_AS_VARCHAR 'OFF';

--- SQL operation complete.
>>
>>create table mytable (row_id int, c1 blob, c2 clob);

--- SQL operation complete.
>>
>>insert into table mytable values (1, stringtolob('string 1'), stringtolob('string 2'));

--- 1 row(s) inserted.
>>insert into table mytable values (2, stringtolob('string 3'), stringtolob('string 4'));

--- 1 row(s) inserted.
>>insert into table mytable values (3, stringtolob('string 5'), stringtolob('string 6'));

--- 1 row(s) inserted.
>>
>>update statistics for table mytable on c1 random 100 percent persistent;

*** ERROR[9246] UPDATE STATISTICS is not supported on LOB columns. Column C1 is a LOB column.

--- SQL operation failed with errors.
>>update statistics for table mytable remove sample;

*** WARNING[9228] There were no sample tables to drop.

--- SQL operation completed with warnings.
>>update statistics for table mytable on every column sample random 100 percent persistent;

*** ERROR[9200] UPDATE STATISTICS for table TRAFODION.MYSCHEMA1.MYTABLE encountered an error (4035) from statement Process_Query.

*** ERROR[4035] Type LOB cannot be cast to type VARCHAR(100000).

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

--- SQL operation failed with errors.
>>update statistics for table mytable remove sample;

*** WARNING[9228] There were no sample tables to drop.

Steps To Reproduce	

create schema myschema1;
set schema myschema1;

control query default TRAF_BLOB_AS_VARCHAR 'OFF';
control query default TRAF_CLOB_AS_VARCHAR 'OFF';

create table mytable (row_id int, c1 blob, c2 clob);

insert into table mytable values (1, stringtolob('string 1'), stringtolob('string 2'));
insert into table mytable values (2, stringtolob('string 3'), stringtolob('string 4'));
insert into table mytable values (3, stringtolob('string 5'), stringtolob('string 6'));

update statistics for table mytable on c1 random 100 percent persistent;
update statistics for table mytable remove sample;
update statistics for table mytable on every column sample random 100 percent persistent;
update statistics for table mytable remove sample;
drop schema myschema1 cascade;



--
This message was sent by Atlassian JIRA
(v6.3.4#6332)