[jira] [Created] (TRAFODION-2732) UPDATE STATS fails sometimes when a column name is a SQL keyword

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

David Wayne Birdsall created TRAFODION-2732:
-----------------------------------------------

             Summary: UPDATE STATS fails sometimes when a column name is a SQL keyword
                 Key: TRAFODION-2732
                 URL: https://issues.apache.org/jira/browse/TRAFODION-2732
             Project: Apache Trafodion
          Issue Type: Bug
          Components: sql-cmp
    Affects Versions: any
         Environment: All
            Reporter: David Wayne Birdsall
            Assignee: David Wayne Birdsall


On tables having the following characteristics:

1. Large table having a column named using a reserved SQL keyword (e.g. "YEAR")
2. The sample data is too large to fit in memory (or CQD USTAT_INTERNAL_SORT is set to 'OFF')
3. The table also has a VARCHAR column of longer than 256 characters

Then UPDATE STATISTICS will fail when it tries to populate a sample table, reporting a syntax error on an internally-generated UPSERT statement.

The following sqlci session demonstrates how to reproduce the problem:

>>obey repro1j.sql;
>>?section setup
>>
>>drop table UstatsBug;

*** ERROR[1389] Object TRAFODION.SCH.USTATSBUG does not exist in Trafodion.

--- SQL operation failed with errors.
>>
>>CREATE TABLE UstatsBug
+>  (
+>    ID                               VARCHAR(32 CHARS) CHARACTER SET UTF8
+>      COLLATE DEFAULT NO DEFAULT NOT NULL NOT DROPPABLE NOT SERIALIZED
+>  , "YEAR"                             NUMERIC(12, 0) DEFAULT NULL NOT SERIALIZED
+>  , JUNK                             VARCHAR(1000 CHARS) CHARACTER SET UTF8
+>  , PRIMARY KEY (ID ASC)
+>  )
+> ATTRIBUTES ALIGNED FORMAT
+>  HBASE_OPTIONS
+>  (
+>    DATA_BLOCK_ENCODING = 'FAST_DIFF',
+>    MEMSTORE_FLUSH_SIZE = '1073741824'
+>  )
+>;

--- SQL operation complete.
>>
>>insert into UstatsBug values ('a',1992,'junk'),('b',1993,'junk'),('c',1994,'junk'),('d',1995,'junk');

--- 4 row(s) inserted.
>>
>>insert into UstatsBug select id || 'a', "YEAR", junk from UstatsBug;

--- 4 row(s) inserted.
>>
>>insert into UstatsBug select id || 'b', "YEAR", junk from UstatsBug;

--- 8 row(s) inserted.
>>
>>insert into UstatsBug select id || 'c', "YEAR", junk from UstatsBug;

--- 16 row(s) inserted.
>>
>>insert into UstatsBug select id || 'd', "YEAR", junk from UstatsBug;

--- 32 row(s) inserted.
>>
>>insert into UstatsBug select id || 'e', "YEAR", junk from UstatsBug;

--- 64 row(s) inserted.
>>
>>insert into UstatsBug select id || 'f', "YEAR", junk from UstatsBug;

--- 128 row(s) inserted.
>>
>>insert into UstatsBug select id || 'g', "YEAR", junk from UstatsBug;

--- 256 row(s) inserted.
>>
>>?section doit
>>
>>cqd ustat_internal_sort 'OFF';

--- SQL operation complete.
>>
>>UPDATE STATISTICS FOR TABLE UstatsBug create SAMPLE random 50 percent;

*** ERROR[9200] UPDATE STATISTICS for table TRAFODION.SCH.USTATSBUG encountered an error (15001) from statement Process_Query.

*** ERROR[15001] A syntax error occurred at or before: 
UPSERT USING LOAD INTO TRAFODION.SCH.TRAF_SAMPLE_02393802717774510155_150488830
9_629934 SELECT ID, YEAR, SUBSTRING(JUNK FOR 64) AS JUNK FROM TRAFODION.SCH.UST
                        ^ (104 characters from start of SQL statement)

*** ERROR[8822] The statement was not prepared.

--- SQL operation failed with errors.
>>
>>exit;

End of MXCI Session




--
This message was sent by Atlassian JIRA
(v6.4.14#64029)