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

["ASF GitHub Bot (JIRA)" <ji...@apache.org>]

    [ https://issues.apache.org/jira/browse/TRAFODION-2298?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15590117#comment-15590117 ] 

ASF GitHub Bot commented on TRAFODION-2298:
-------------------------------------------

GitHub user DaveBirdsall opened a pull request:

    https://github.com/apache/incubator-trafodion/pull/770

    [TRAFODION-2298] Fix issue with incremental stats and LOB columns

    Strange error messages would occur when creating a sample table (persistent or not) against a table having LOB columns. This has been fixed.
    
    While I was at it, I noticed that attempts to do update stats on an empty table with the PERSISTENT keyword would apparently succeed, but fail to create a persistent sample table. This is reasonable behavior, as there is no data to store. However it would be good to warn the user that no persistent sample table was created. A warning 9220 is now issued in this case.

You can merge this pull request into a Git repository by running:

    $ git pull https://github.com/DaveBirdsall/incubator-trafodion Trafodion2298

Alternatively you can review and apply these changes as the patch at:

    https://github.com/apache/incubator-trafodion/pull/770.patch

To close this pull request, make a commit to your master/trunk branch
with (at least) the following in the commit message:

    This closes #770
    
----
commit 98561ca51012b154422da024e45f115e2e957da0
Author: Dave Birdsall <db...@apache.org>
Date:   2016-10-19T22:51:04Z

    [TRAFODION-2298] Fix issue with incremental stats and LOB columns

----


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