You are viewing a plain text version of this content. The canonical link for it is here.
Posted to issues@trafodion.apache.org by "ASF GitHub Bot (JIRA)" <ji...@apache.org> on 2017/09/08 20:03:00 UTC
[jira] [Commented] (TRAFODION-2732) UPDATE STATS fails sometimes
when a column name is a SQL keyword
[ https://issues.apache.org/jira/browse/TRAFODION-2732?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16159199#comment-16159199 ]
ASF GitHub Bot commented on TRAFODION-2732:
-------------------------------------------
GitHub user DaveBirdsall opened a pull request:
https://github.com/apache/incubator-trafodion/pull/1227
[TRAFODION-2732] Add delimited identifier handling to sample table code path
UPDATE STATISTICS generates several SQL statements internally. When referencing column names, the safest thing to do is to double-quote them, because sometimes we have SQL keywords serving as column names. Many code paths in UPDATE STATISTICS already do this; this particular change is for one that didn't.
You can merge this pull request into a Git repository by running:
$ git pull https://github.com/DaveBirdsall/incubator-trafodion Trafodion2732
Alternatively you can review and apply these changes as the patch at:
https://github.com/apache/incubator-trafodion/pull/1227.patch
To close this pull request, make a commit to your master/trunk branch
with (at least) the following in the commit message:
This closes #1227
----
commit ffe35c883e0861a88e601968ba1cf249e8eaae72
Author: Dave Birdsall <db...@apache.org>
Date: 2017-09-08T19:57:32Z
[TRAFODION-2732] Add delimited identifier handling to sample table code path
----
> 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)