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

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

     [ https://issues.apache.org/jira/browse/TRAFODION-2732?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]

Work on TRAFODION-2732 started by David Wayne Birdsall.
-------------------------------------------------------
> 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)