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

• Type: Bug
• Status: Resolved
• Priority: Major
• Resolution: Fixed
• Affects Version/s: 2.4
• Fix Version/s: 2.4
• Component/s: sql-cmp
• Assignee: Dave Birdsall
• Reporter: Dave Birdsall
• Created: 27/Mar/19 21:13
• Updated: 04/Apr/19 21:02
• Resolved: 04/Apr/19 21:02

Description

The following script reproduces the problem:

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;

When run, attempts to create a sample table fail with a syntax error. Instead we should fail with error 9246, as shown below:

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

Issue Links

• links to: GitHub Pull Request #1822