[
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 <[email protected]>
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)
