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

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

     [ https://issues.apache.org/jira/browse/TRAFODION-2298?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]

David Wayne Birdsall resolved TRAFODION-2298.
---------------------------------------------
    Resolution: Fixed

> 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)