[jira] [Commented] (TRAFODION-2021) Upsert returns compiler internal error 2235 when index includes primary key

["Hans Zeller (JIRA)" <ji...@apache.org>]

    [ https://issues.apache.org/jira/browse/TRAFODION-2021?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15313114#comment-15313114 ] 

Hans Zeller commented on TRAFODION-2021:
----------------------------------------

In this particular case, the extra nodes added by index maintenance and the WHERE clause interacted in a way that caused some missing characteristic inputs, leading to the generator error.

The fix is to add the required inputs not directly to the nodes, but as "outer references" to the BindScope, so that they get added automatically to any nodes created in that BindScope. Since the predicates get created by a method called at a higher level, I also needed to add a "remote control" feature for the BindScope of a MergeUpdate node.

> Upsert returns compiler internal error 2235 when index includes primary key
> ---------------------------------------------------------------------------
>
>                 Key: TRAFODION-2021
>                 URL: https://issues.apache.org/jira/browse/TRAFODION-2021
>             Project: Apache Trafodion
>          Issue Type: Bug
>          Components: sql-cmp
>    Affects Versions: 2.0-incubating
>            Reporter: Weishiun Tsai
>            Assignee: Hans Zeller
>
> As shown below, t2 and t3 were almost identical except for the primary key a in t3. Same indexes on (a, b) were created on both t2 and t3 prior to the data loading on both tables. Upsert using load worked fine on t2, but it returned compiler internal error on t3.
> >>set parserflags 1;
>  --- SQL operation complete.
>  >>create table t1 (a int not null not droppable primary key, b int, c int);
>  --- SQL operation complete.
>  >>insert into t1 values (1,1,1),(2,2,2),(3,3,3);
>  --- 3 row(s) inserted.
>  >>
>  >>create table t2 (a int not null not droppable, b int, c int);
>  --- SQL operation complete.
>  >>create index myidx2 on t2 (a, b);
>  --- SQL operation complete.
>  >>upsert using load into t2 select * from t1 where a <=2;
>  --- 2 row(s) inserted.
>  >>select count(*) from table(index_table myidx2);
>  (EXPR)
>  --------------------
>                     2
>  --- 1 row(s) selected.
>  >>
>  >>create table t3 (a int not null not droppable primary key, b int, c int);
>  --- SQL operation complete.
>  >>create index myidx3 on t3 (a, b);
>  --- SQL operation complete.
>  >>upsert using load into t3 select * from t1 where a <=2;
>  *** ERROR[7000] An internal error occurred in the code generator in file ../generator/GenPreCode.cpp at line 1317: valuesToBeBound.isEmpty().
>  *** ERROR[2235] Compiler Internal Error: An unknown error, originated from file ../generator/Generator.cpp at line 2872.
>  *** ERROR[8822] The statement was not prepared.
>  >>select count(*) from table(index_table myidx3);
>  (EXPR)
>  --------------------
>                     0
>  --- 1 row(s) selected.
>  >>
>  >>drop table t1 cascade;
>  --- SQL operation complete.
>  >>drop table t2 cascade;
>  --- SQL operation complete.
>  >>drop table t3 cascade;
>  --- SQL operation complete.
> -------------------------------------------------------------------------------------
> Here is the entire script to reproduce this problem:
>  set parserflags 1;
>  create table t1 (a int not null not droppable primary key, b int, c int);
>  insert into t1 values (1,1,1),(2,2,2),(3,3,3);
>  create table t2 (a int not null not droppable, b int, c int);
>  create index myidx2 on t2 (a, b);
>  upsert using load into t2 select * from t1 where a <=2;
>  select count(*) from table(index_table myidx2);
>  create table t3 (a int not null not droppable primary key, b int, c int);
>  create index myidx3 on t3 (a, b);
>  upsert using load into t3 select * from t1 where a <=2;
>  select count(*) from table(index_table myidx3);
>  drop table t1 cascade;
>  drop table t2 cascade;
>  drop table t3 cascade;



--
This message was sent by Atlassian JIRA
(v6.3.4#6332)