Uploaded image for project: 'Apache Trafodion'
  1. Apache Trafodion
  2. TRAFODION-3290

Creating a sample table on an all-LOB table fails with syntax error

    XMLWordPrintableJSON

    Details

    • Type: Bug
    • Status: Resolved
    • Priority: Major
    • Resolution: Fixed
    • Affects Version/s: 2.4
    • Fix Version/s: 2.4
    • Component/s: sql-cmp
    • Labels:
      None

      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

        Attachments

          Issue Links

            Activity

              People

              • Assignee:
                dbirdsall Dave Birdsall
                Reporter:
                dbirdsall Dave Birdsall
              • Votes:
                0 Vote for this issue
                Watchers:
                1 Start watching this issue

                Dates

                • Created:
                  Updated:
                  Resolved:

                  Time Tracking

                  Estimated:
                  Original Estimate - Not Specified
                  Not Specified
                  Remaining:
                  Remaining Estimate - 0h
                  0h
                  Logged:
                  Time Spent - 40m
                  40m