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

• Type: Bug
• Status: Closed
• Priority: Major
• Resolution: Fixed
• Affects Version/s: any
• Fix Version/s: 2.3
• Component/s: sql-cmp
• Environment: All
• Assignee: Dave Birdsall
• Reporter: Dave Birdsall
• Created: 08/Sep/17 16:38
• Updated: 13/Aug/18 21:06
• Resolved: 12/Sep/17 16:37

Description

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

Issue Links

• links to: GitHub Pull Request #1227