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)
