[jira] [Created] (TRAFODION-2763) Left join with non-equi join predicate in ON clause may cause corefiles

["Suresh Subbiah (JIRA)" <ji...@apache.org>]

Suresh Subbiah created TRAFODION-2763:
-----------------------------------------

             Summary: Left join with non-equi join predicate in ON clause may cause corefiles
                 Key: TRAFODION-2763
                 URL: https://issues.apache.org/jira/browse/TRAFODION-2763
             Project: Apache Trafodion
          Issue Type: Bug
          Components: sql-exe
    Affects Versions: 2.1-incubating
            Reporter: Suresh Subbiah
            Assignee: Suresh Subbiah


A SELECT query with a left join, that has a non-qui join predicate in ON clause may cause the executor to crash.

For example this query will cause a corefile
create table tab2
(tab2_pk integer not null,
tab2_nn integer not null,
tab2_na integer,
primary key (tab2_pk));

create table tab3
(tab3_pk integer not null,
tab3_nn integer not null,
tab3_na integer,
primary key (tab3_pk));

insert into tab2 values (4,4,4);
insert into tab2 values (3,3,3);
insert into tab2 values (6,6,null);
insert into tab2 values (5,5,5);
insert into tab3 values (6, 6, null);
insert into tab3 values (5,5,5);
insert into tab3 values (7,7,7);

select tab2_pk, tab3_pk from tab2 left join tab3 on tab2_pk = tab3_pk
and tab2_pk > 4;

This is a regression due to pull request https://github.com/apache/incubator-trafodion/pull/1163 which addressed  [TRAFODION-2622]. 

With this change the non-equi join predicate is evaluate before the join is done, if the non-equi predicate is on the outer table alone. However VEG rewrite can cause the predicate tobe rewritten in terms of the inner row. That would cause a crashas the inner row is not available yet.

The fix is to ensure that the predicate is expressed in terms of the outer table alone, when we know that all conditions are met to make this optimization (evaluating non-equi join predicate for left joins before inner table is read). Thus is similar to what is already done for a left TSJ.

Existence of this problem can be verified with SHOWPLAN. Prior to the fix the predicate tab2_pk > 4, would be implemented with the column tab3_pk in the beforeJoinPred1_ and beforeJoinPred2_




--
This message was sent by Atlassian JIRA
(v6.4.14#64029)