[
https://issues.apache.org/jira/browse/TRAFODION-2298?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15593383#comment-15593383
]
ASF GitHub Bot commented on TRAFODION-2298:
-------------------------------------------
Github user asfgit closed the pull request at:
https://github.com/apache/incubator-trafodion/pull/770
> 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)
