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

• Type: Bug
• Status: Open
• Priority: Major
• Resolution: Unresolved
• Affects Version/s: any
• Component/s: sql-cmp
• Assignee: zhang.lei
• Reporter: zhang.lei
• Created: 31/May/18 01:12
• Updated: 31/May/18 01:12

Description

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.|