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)
