[jira] [Resolved] (TRAFODION-3290) Creating a sample table on an all-LOB table fails with syntax error

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

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

David Wayne Birdsall resolved TRAFODION-3290.
---------------------------------------------
       Resolution: Fixed
    Fix Version/s: 2.4

> Creating a sample table on an all-LOB table fails with syntax error
> -------------------------------------------------------------------
>
>                 Key: TRAFODION-3290
>                 URL: https://issues.apache.org/jira/browse/TRAFODION-3290
>             Project: Apache Trafodion
>          Issue Type: Bug
>          Components: sql-cmp
>    Affects Versions: 2.4
>            Reporter: David Wayne Birdsall
>            Assignee: David Wayne Birdsall
>            Priority: Major
>             Fix For: 2.4
>
>          Time Spent: 40m
>  Remaining Estimate: 0h
>
> The following script reproduces the problem:
> {quote}control query default TRAF_BLOB_AS_VARCHAR 'OFF';
> control query default TRAF_CLOB_AS_VARCHAR 'OFF';
> drop table if exists t1;
> create table t1 (c_clob clob, c_blob blob);
> insert into t1 values (stringtolob('clob_1'), stringtolob('blob_1'));
> update statistics for table t1 on (c_clob, c_blob) sample random 50 percent persistent;
> update statistics for table t1 create sample random 100 percent;
> update statistics for table t1 on every column sample random 100 percent persistent;
> update statistics for table t1 on every key sample random 100 percent persistent;
> update statistics for table t1 on existing column sample random 100 percent persistent;
> update statistics for table t1 on necessary column sample random 100 percent persistent;
> {quote}
> When run, attempts to create a sample table fail with a syntax error. Instead we should fail with error 9246, as shown below:
> {quote}>>obey repro.sql;
> >>control query default TRAF_BLOB_AS_VARCHAR 'OFF';
> --- SQL operation complete.
> >>control query default TRAF_CLOB_AS_VARCHAR 'OFF';
> --- SQL operation complete.
> >>
> >>drop table if exists t1;
> --- SQL operation complete.
> >>
> >>create table t1 (c_clob clob, c_blob blob);
> --- SQL operation complete.
> >>insert into t1 values (stringtolob('clob_1'), stringtolob('blob_1'));
> --- 1 row(s) inserted.
> >>
> >>update statistics for table t1 on (c_clob, c_blob) sample random 50 percent persistent;
> *** ERROR[9246] UPDATE STATISTICS is not supported on LOB columns. Column C_BLOB is a LOB column.
> --- SQL operation failed with errors.
> >>
> >>update statistics for table t1 create sample random 100 percent;
> *** ERROR[9214] Object TRAFODION.SCH.TRAF_SAMPLE_00469125659506823465_1553720877_834150 could not be created.
> *** ERROR[15001] A syntax error occurred at or before: 
> create table TRAFODION.SCH.TRAF_SAMPLE_00469125659506823465_1553720877_834150 
>  ( ) ATTRIBUTES ALIGNED FORMAT;
>  ^ (86 characters from start of SQL statement)
> *** ERROR[8822] The statement was not prepared.
> *** ERROR[8839] Transaction was aborted.
> --- SQL operation failed with errors.
> >>update statistics for table t1 on every column sample random 100 percent persistent;
> *** ERROR[9214] Object TRAFODION.SCH.TRAF_SAMPLE_00469125659506823465_1553720916_953637 could not be created.
> *** ERROR[15001] A syntax error occurred at or before: 
> create table TRAFODION.SCH.TRAF_SAMPLE_00469125659506823465_1553720916_953637 
>  ( ) ATTRIBUTES ALIGNED FORMAT;
>  ^ (86 characters from start of SQL statement)
> *** ERROR[8822] The statement was not prepared.
> *** ERROR[8839] Transaction was aborted.
> --- SQL operation failed with errors.
> >>update statistics for table t1 on every key sample random 100 percent persistent;
> *** ERROR[9214] Object TRAFODION.SCH.TRAF_SAMPLE_00469125659506823465_1553720950_701762 could not be created.
> *** ERROR[15001] A syntax error occurred at or before: 
> create table TRAFODION.SCH.TRAF_SAMPLE_00469125659506823465_1553720950_701762 
>  ( ) ATTRIBUTES ALIGNED FORMAT;
>  ^ (86 characters from start of SQL statement)
> *** ERROR[8822] The statement was not prepared.
> *** ERROR[8839] Transaction was aborted.
> --- SQL operation failed with errors.
> >>update statistics for table t1 on existing column sample random 100 percent persistent;
> *** WARNING[9220] The table is empty, so no persistent sample table was created.
> *** WARNING[9217] The statement will have no effect because no histograms are currently maintained for the table.
> --- SQL operation completed with warnings.
> >>update statistics for table t1 on necessary column sample random 100 percent persistent;
> *** WARNING[9220] The table is empty, so no persistent sample table was created.
> *** WARNING[9218] The statement will have no effect because no histograms need to be updated.
> --- SQL operation completed with warnings.
> >>exit;
> End of MXCI Session
> {quote}



--
This message was sent by Atlassian JIRA
(v7.6.3#76005)