David Wayne Birdsall created TRAFODION-3290:
-----------------------------------------------
Summary: 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
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)
