[jira] [Created] (TRAFODION-3093) Create index returns 15001 internal syntax error on a salted table with interval second column

["zhang.lei (JIRA)" <ji...@apache.org>]

zhang.lei created TRAFODION-3093:
------------------------------------

             Summary: Create index returns 15001 internal syntax error on a salted table with interval second column
                 Key: TRAFODION-3093
                 URL: https://issues.apache.org/jira/browse/TRAFODION-3093
             Project: Apache Trafodion
          Issue Type: Bug
          Components: sql-cmp
    Affects Versions: any
            Reporter: zhang.lei
            Assignee: zhang.lei


| |As shown below, create index idx1 on t1(c1) returns a 15001 internal syntax error. t1 is a salted table on column c1, which is a interval second(18,0) column. This may be related to the fact that t1 is a salted table. Also shown here is that create index idx2 on t2(c1) works fine when t2 is not salted.
 
 This is seen on the AdvEnt2.5 20180509 daily build.
 
 >>drop schema if exists mytest cascade;
 
 --- SQL operation complete.
 >>create schema mytest;
 
 --- SQL operation complete.
 >>set schema mytest;
 
 --- SQL operation complete.
 >>
 >>create table t1 (c1 interval second(18,0) not null) store by (c1) salt using 2 partitions;
 
 --- SQL operation complete.
 >>insert into t1 values (interval '333333333333333333' second(18,0));
 
 --- 1 row(s) inserted.
 >>select * from t1;
 
 C1
 -------------------
 
  333333333333333333
 
 --- 1 row(s) selected.
 >>create index idx1 on t1 (c1);
 
 *** ERROR[15001] A syntax error occurred at or before:
 CAST ( INTERVAL - ' 999999999999999999' SECOND(18, 0)(0) AS INTERVAL SECOND(18,
                                                      ^ (54 characters from start of SQL statement)
 
 *** WARNING[2053] Optimizer pass two assertion failure (itemExpr != NULL) in file ../optimizer/EncodedKeyValue.cpp at line 231. Attempting to recover and produce a plan.
 
 *** ERROR[15001] A syntax error occurred at or before:
 CAST ( INTERVAL - ' 999999999999999999' SECOND(18, 0)(0) AS INTERVAL SECOND(18,
                                                      ^ (54 characters from start of SQL statement)
 
 *** ERROR[8822] The statement was not prepared.
 
 *** ERROR[1081] Loading of index TRAFODION.MYTEST.IDX1 failed unexpectedly.
 
 --- SQL operation failed with errors.
 >>
 >>create table t2 (c1 interval second(18,0) not null) store by (c1);
 
 --- SQL operation complete.
 >>insert into t2 values (interval '333333333333333333' second(18,0));
 
 --- 1 row(s) inserted.
 >>select * from t2;
 
 C1
 -------------------
 
  333333333333333333
 
 --- 1 row(s) selected.
 >>create index idx2 on t2 (c1);
 
 --- SQL operation complete.|



--
This message was sent by Atlassian JIRA
(v7.6.3#76005)