[ https://issues.apache.org/jira/browse/TRAFODION-2732?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
David Wayne Birdsall resolved TRAFODION-2732. --------------------------------------------- Resolution: Fixed Fix Version/s: 2.3-incubating > 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 > Fix For: 2.3-incubating > > > 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)