[
https://issues.apache.org/jira/browse/TRAFODION-804?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=14721176#comment-14721176
]
Suresh Subbiah commented on TRAFODION-804:
------------------------------------------
QF Chen (qifan-chen) wrote on 2014-12-04: #1
removed the WITH DIVISION clause from the ustat code.
Changed in trafodion:
status: New → Fix Committed
assignee: nobody → QF Chen (qifan-chen)
Weishiun Tsai (wei-shiun-tsai) wrote on 2014-12-11: #2
Download full text (4.2 KiB)
Verified on the v1209_0830 build installed on a cluster. This problem has been
fixed:
>>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 N...
Read more...
Changed in trafodion:
status: Fix Committed → Fix Released
See full activity log
> 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)
