[jira] [Commented] (TRAFODION-1978) Skip LOB columns for UPDATE STATS

[ASF GitHub Bot (JIRA)]

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

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

Github user asfgit closed the pull request at:

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


> Skip LOB columns for UPDATE STATS
> -
>
> Key: TRAFODION-1978
> URL: https://issues.apache.org/jira/browse/TRAFODION-1978
> Project: Apache Trafodion
>  Issue Type: Bug
>  Components: sql-cmp
>Affects Versions: 2.0-incubating
> Environment: All
>Reporter: David Wayne Birdsall
>Assignee: David Wayne Birdsall
>
> As shown below, update stats currently does not work for LOB columns. This 
> case is filed to track this issue. In the long run, we perhaps should 
> consider supporting update stats on LOB data types. In the short run, we 
> should make it return better error messages. The current error messages are 
> all over the map with lots of internal references.
> This was seen on the AdvEnt2.1 v0419 build installed on a 4-node cluster.
> >>control query default TRAF_BLOB_AS_VARCHAR 'OFF';
> --- SQL operation complete.
> >>control query default TRAF_CLOB_AS_VARCHAR 'OFF';
> --- SQL operation complete.
> >>
> >>create schema mytest;
> --- SQL operation complete.
> >>set schema mytest;
> --- 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);
> *** ERROR[9200] UPDATE STATISTICS for table TRAFODION.MYTEST.MYTABLE 
> encountered an error (4322) from statement HSCursor::prepareRowsetInternal().
> *** ERROR[4322] A column with BLOB datatype cannot be used in this clause or 
> function.
> *** ERROR[8822] The statement was not prepared.
> *** ERROR[9200] UPDATE STATISTICS for table TRAFODION.MYTEST.MYTABLE 
> encountered an error (4322) from statement FETCH_BOUNDARY_ROWSET.
> *** ERROR[4322] A column with BLOB datatype cannot be used in this clause or 
> function.
> *** ERROR[8822] The statement was not prepared.
> --- SQL operation failed with errors.
> >>update statistics for table mytable on (c2);
> *** ERROR[9200] UPDATE STATISTICS for table TRAFODION.MYTEST.MYTABLE 
> encountered an error (4322) from statement HSCursor::prepareRowsetInternal().
> *** ERROR[4322] A column with BLOB datatype cannot be used in this clause or 
> function.
> *** ERROR[8822] The statement was not prepared.
> *** ERROR[9200] UPDATE STATISTICS for table TRAFODION.MYTEST.MYTABLE 
> encountered an error (4322) from statement FETCH_BOUNDARY_ROWSET.
> *** ERROR[4322] A column with BLOB datatype cannot be used in this clause or 
> function.
> *** ERROR[8822] The statement was not prepared.
> --- SQL operation failed with errors.
> >>update statistics for table mytable on every column;
> *** ERROR[9200] UPDATE STATISTICS for table TRAFODION.MYTEST.MYTABLE 
> encountered an error (4322) from statement HSCursor::prepareRowsetInternal().
> *** ERROR[4322] A column with BLOB datatype cannot be used in this clause or 
> function.
> *** ERROR[8822] The statement was not prepared.
> *** ERROR[9200] UPDATE STATISTICS for table TRAFODION.MYTEST.MYTABLE 
> encountered an error (4322) from statement FETCH_BOUNDARY_ROWSET.
> *** ERROR[4322] A column with BLOB datatype cannot be used in this clause or 
> function.
> *** ERROR[8822] The statement was not prepared.
> --- SQL operation failed with errors.
> Here is the entire script to reproduce this problem:
> control query default TRAF_BLOB_AS_VARCHAR 'OFF';
> control query default TRAF_CLOB_AS_VARCHAR 'OFF';
> create schema mytest;
> set schema mytest;
> 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);
> update statistics for table mytable on (c2);
> update statistics for table mytable on every column;
> drop schema mytest cascade;



--
This message was sent by Atlassian JIRA
(v6.3.4#6332)

[jira] [Commented] (TRAFODION-1978) Skip LOB columns for UPDATE STATS

[ASF GitHub Bot (JIRA)]

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

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

GitHub user DaveBirdsall opened a pull request:

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

[TRAFODION-1978] Report a nice error when attempting UPD STATS on LOBs

Histogram statistics on LOB columns do not make sense at the moment; 
Trafodion does not support them. However, the UPDATE STATISTICS command would 
fail in odd and diverse ways when asked to create stats on LOB columns. This 
has been fixed. Now when LOB columns are explicitly referenced in an UPDATE 
STATISTICS command, an error 9246 will result. When LOB columns are implicitly 
referenced (e.g., in an ON EVERY COLUMN clause), they will be silently skipped.

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

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

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

https://github.com/apache/incubator-trafodion/pull/469.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 #469


commit 18e0a35804c1d9798155d3a364eb57b06f400a27
Author: Dave Birdsall 
Date:   2016-05-06T22:06:15Z

[TRAFODION-1978] Report a nice error when attempting UPD STATS on LOBs




> Skip LOB columns for UPDATE STATS
> -
>
> Key: TRAFODION-1978
> URL: https://issues.apache.org/jira/browse/TRAFODION-1978
> Project: Apache Trafodion
>  Issue Type: Bug
>  Components: sql-cmp
>Affects Versions: 2.0-incubating
> Environment: All
>Reporter: David Wayne Birdsall
>Assignee: David Wayne Birdsall
>
> As shown below, update stats currently does not work for LOB columns. This 
> case is filed to track this issue. In the long run, we perhaps should 
> consider supporting update stats on LOB data types. In the short run, we 
> should make it return better error messages. The current error messages are 
> all over the map with lots of internal references.
> This was seen on the AdvEnt2.1 v0419 build installed on a 4-node cluster.
> >>control query default TRAF_BLOB_AS_VARCHAR 'OFF';
> --- SQL operation complete.
> >>control query default TRAF_CLOB_AS_VARCHAR 'OFF';
> --- SQL operation complete.
> >>
> >>create schema mytest;
> --- SQL operation complete.
> >>set schema mytest;
> --- 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);
> *** ERROR[9200] UPDATE STATISTICS for table TRAFODION.MYTEST.MYTABLE 
> encountered an error (4322) from statement HSCursor::prepareRowsetInternal().
> *** ERROR[4322] A column with BLOB datatype cannot be used in this clause or 
> function.
> *** ERROR[8822] The statement was not prepared.
> *** ERROR[9200] UPDATE STATISTICS for table TRAFODION.MYTEST.MYTABLE 
> encountered an error (4322) from statement FETCH_BOUNDARY_ROWSET.
> *** ERROR[4322] A column with BLOB datatype cannot be used in this clause or 
> function.
> *** ERROR[8822] The statement was not prepared.
> --- SQL operation failed with errors.
> >>update statistics for table mytable on (c2);
> *** ERROR[9200] UPDATE STATISTICS for table TRAFODION.MYTEST.MYTABLE 
> encountered an error (4322) from statement HSCursor::prepareRowsetInternal().
> *** ERROR[4322] A column with BLOB datatype cannot be used in this clause or 
> function.
> *** ERROR[8822] The statement was not prepared.
> *** ERROR[9200] UPDATE STATISTICS for table TRAFODION.MYTEST.MYTABLE 
> encountered an error (4322) from statement FETCH_BOUNDARY_ROWSET.
> *** ERROR[4322] A column with BLOB datatype cannot be used in this clause or 
> function.
> *** ERROR[8822] The statement was not prepared.
> --- SQL operation failed with errors.
> >>update statistics for table mytable on every column;
> *** ERROR[9200] UPDATE STATISTICS for table TRAFODION.MYTEST.MYTABLE 
> encountered an error (4322) from statement HSCursor::prepareRowsetInternal().
> *** ERROR[4322] A column with BLOB datatype cannot be used in this clause or 
> function.
> *** ERROR[8822] The statement was not prepared.
> *** ERROR[9200] UPDATE STATISTICS for table TRAFODION.MYTEST.MYTABLE 
> encountered an error (4322) from statement