[
https://issues.apache.org/jira/browse/TRAFODION-804?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Suresh Subbiah resolved TRAFODION-804.
--------------------------------------
Resolution: Fixed
> LP Bug: 1398932 - Update statistics creates internal table ‘WITH DIVISION’
> and sees syntax error
> ------------------------------------------------------------------------------------------------
>
> Key: TRAFODION-804
> URL: https://issues.apache.org/jira/browse/TRAFODION-804
> Project: Apache Trafodion
> Issue Type: Bug
> Components: sql-cmp
> Reporter: Weishiun Tsai
> Assignee: Qifan Chen
> Priority: Blocker
>
> Update statistics tries to create an internal table with the syntax ‘WITH
> DIVISION’, which then returns a 15001 syntax error. This is seen on the
> v1202_0830 build installed on a cluster. It is a regression introduced
> between the v1125_0830 build and the v1202_0830 build.
> ----------------------------------------------------
> Here is the entire script to reproduce it. This requires the QA g_tpch2x
> global tables:
> set schema mytest;
> showddl trafodion.g_tpch2x.lineitem;
> select count(*) from trafodion.g_tpch2x.lineitem;
> create table lineitem like trafodion.g_tpch2x.lineitem with constraints with
> partitions;
> upsert using load into lineitem (select * from trafodion.g_tpch2x.lineitem);
> showddl lineitem;
> select count(*) from lineitem;
> create index lineix1 on lineitem (l_suppkey);
> update statistics for table lineitem on every key sample;
> drop schema mytest cascade;
> ----------------------------------------------------
> Here the execution output:
> >>set schema mytest;
> --- SQL operation complete.
> >>
> >>showddl trafodion.g_tpch2x.lineitem;
> CREATE TABLE TRAFODION.G_TPCH2X.LINEITEM
> (
> L_ORDERKEY INT NO DEFAULT NOT NULL NOT DROPPABLE
> , L_PARTKEY INT NO DEFAULT NOT NULL NOT DROPPABLE
> , L_SUPPKEY INT NO DEFAULT NOT NULL NOT DROPPABLE
> , L_LINENUMBER INT NO DEFAULT NOT NULL NOT DROPPABLE
> , L_QUANTITY NUMERIC(12, 2) NO DEFAULT NOT NULL NOT
> DROPPABLE
> , L_EXTENDEDPRICE NUMERIC(12, 2) NO DEFAULT NOT NULL NOT
> DROPPABLE
> , L_DISCOUNT NUMERIC(12, 2) NO DEFAULT NOT NULL NOT
> DROPPABLE
> , L_TAX NUMERIC(12, 2) NO DEFAULT NOT NULL NOT
> DROPPABLE
> , L_RETURNFLAG CHAR(1) CHARACTER SET ISO88591 COLLATE
> DEFAULT NO DEFAULT NOT NULL NOT DROPPABLE
> , L_LINESTATUS CHAR(1) CHARACTER SET ISO88591 COLLATE
> DEFAULT NO DEFAULT NOT NULL NOT DROPPABLE
> , L_SHIPDATE DATE NO DEFAULT NOT NULL NOT DROPPABLE
> , L_COMMITDATE DATE NO DEFAULT NOT NULL NOT DROPPABLE
> , L_RECEIPTDATE DATE NO DEFAULT NOT NULL NOT DROPPABLE
> , L_SHIPINSTRUCT CHAR(25) CHARACTER SET ISO88591 COLLATE
> DEFAULT NO DEFAULT NOT NULL NOT DROPPABLE
> , L_SHIPMODE CHAR(10) CHARACTER SET ISO88591 COLLATE
> DEFAULT NO DEFAULT NOT NULL NOT DROPPABLE
> , L_COMMENT VARCHAR(44) CHARACTER SET ISO88591
> COLLATE
> DEFAULT NO DEFAULT NOT NULL NOT DROPPABLE
> , PRIMARY KEY (L_SHIPDATE ASC, L_ORDERKEY ASC, L_LINENUMBER ASC)
> )
> SALT USING 8 PARTITIONS
> ;
> --- SQL operation complete.
> >>select count(*) from trafodion.g_tpch2x.lineitem;
> (EXPR)
> --------------------
> 11997996
> --- 1 row(s) selected.
> >>
> >>create table lineitem like trafodion.g_tpch2x.lineitem with constraints
> >>with partitions;
> --- SQL operation complete.
> >>
> >>upsert using load into lineitem (select * from trafodion.g_tpch2x.lineitem);
> --- 11997996 row(s) inserted.
> >>
> >>showddl lineitem;
> CREATE TABLE TRAFODION.MYTEST.LINEITEM
> (
> L_ORDERKEY INT NO DEFAULT NOT NULL NOT DROPPABLE
> , L_PARTKEY INT NO DEFAULT NOT NULL NOT DROPPABLE
> , L_SUPPKEY INT NO DEFAULT NOT NULL NOT DROPPABLE
> , L_LINENUMBER INT NO DEFAULT NOT NULL NOT DROPPABLE
> , L_QUANTITY NUMERIC(12, 2) NO DEFAULT NOT NULL NOT
> DROPPABLE
> , L_EXTENDEDPRICE NUMERIC(12, 2) NO DEFAULT NOT NULL NOT
> DROPPABLE
> , L_DISCOUNT NUMERIC(12, 2) NO DEFAULT NOT NULL NOT
> DROPPABLE
> , L_TAX NUMERIC(12, 2) NO DEFAULT NOT NULL NOT
> DROPPABLE
> , L_RETURNFLAG CHAR(1) CHARACTER SET ISO88591 COLLATE
> DEFAULT NO DEFAULT NOT NULL NOT DROPPABLE
> , L_LINESTATUS CHAR(1) CHARACTER SET ISO88591 COLLATE
> DEFAULT NO DEFAULT NOT NULL NOT DROPPABLE
> , L_SHIPDATE DATE NO DEFAULT NOT NULL NOT DROPPABLE
> , L_COMMITDATE DATE NO DEFAULT NOT NULL NOT DROPPABLE
> , L_RECEIPTDATE DATE NO DEFAULT NOT NULL NOT DROPPABLE
> , L_SHIPINSTRUCT CHAR(25) CHARACTER SET ISO88591 COLLATE
> DEFAULT NO DEFAULT NOT NULL NOT DROPPABLE
> , L_SHIPMODE CHAR(10) CHARACTER SET ISO88591 COLLATE
> DEFAULT NO DEFAULT NOT NULL NOT DROPPABLE
> , L_COMMENT VARCHAR(44) CHARACTER SET ISO88591
> COLLATE
> DEFAULT NO DEFAULT NOT NULL NOT DROPPABLE
> , PRIMARY KEY (L_SHIPDATE ASC, L_ORDERKEY ASC, L_LINENUMBER ASC)
> )
> ;
> --- SQL operation complete.
> >>select count(*) from lineitem;
> (EXPR)
> --------------------
> 11997996
> --- 1 row(s) selected.
> >>
> >>create index lineix1 on lineitem (l_suppkey);
> --- SQL operation complete.
> >>
> >>update statistics for table lineitem on every key sample;
> *** ERROR[9214] Object
> TRAFODION.PUBLIC_ACCESS_SCHEMA.SQLMX_72672800840904042_1417630419_376553
> could not be created.
> *** ERROR[15001] A syntax error occurred at or before:
> CREATE TABLE
> TRAFODION.PUBLIC_ACCESS_SCHEMA.SQLMX_72672800840904042_1417630419_
> 376553 LIKE TRAFODION.MYTEST.LINEITEM WITH DIVISION WITH PARTITIONS ATTRIBUTE
> N
> ^ (130 characters from
> start of SQL statement)
> *** ERROR[8822] The statement was not prepared.
> *** ERROR[9201] Unable to DROP object
> TRAFODION.PUBLIC_ACCESS_SCHEMA.SQLMX_72672800840904042_1417630419_376553.
> *** ERROR[1389] Object
> TRAFODION.PUBLIC_ACCESS_SCHEMA.SQLMX_72672800840904042_1417630419_376553 does
> not exist in Trafodion.
> --- SQL operation failed with errors.
> >>
> >>drop schema mytest cascade;
> --- SQL operation complete.
--
This message was sent by Atlassian JIRA
(v6.3.4#6332)
