[
https://issues.apache.org/jira/browse/TRAFODION-2763?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Suresh Subbiah resolved TRAFODION-2763.
---------------------------------------
Resolution: Fixed
> 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)
