[ https://issues.apache.org/jira/browse/DRILL-5263?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Serhii Harnyk updated DRILL-5263: --------------------------------- Description: Nested loop join operator in Drill supports only inner join and returns incorrect result for queries with left join. Drill should throw error in this case. Example: {code:sql} alter session set planner.enable_nljoin_for_scalar_only=false; select t2.dt, t1.fyq, t2.who, t2.event from t2 left join t1 on t2.dt between t1.dts and t1.dte order by t2.dt; {code} Result: {noformat} +-------------+----------+----------+--------------------+ | dt | fyq | who | event | +-------------+----------+----------+--------------------+ | 2016-12-26 | 2016-Q2 | aperson | had chrsitmas | | 2017-01-06 | 2016-Q3 | aperson | did somthing | | 2017-01-12 | 2016-Q3 | aperson | did somthing else | +-------------+----------+----------+--------------------+ {noformat} was: Nested loop join operator in Drill supports only inner join and returns incorrect result for queries with left join and non scalar sub-queries. Drill should throw error in this case. Example: {code:sql} alter session set planner.enable_nljoin_for_scalar_only=false; select t2.dt, t1.fyq, t2.who, t2.event from t2 left join t1 on t2.dt between t1.dts and t1.dte order by t2.dt; {code} Result: {noformat} +-------------+----------+----------+--------------------+ | dt | fyq | who | event | +-------------+----------+----------+--------------------+ | 2016-12-26 | 2016-Q2 | aperson | had chrsitmas | | 2017-01-06 | 2016-Q3 | aperson | did somthing | | 2017-01-12 | 2016-Q3 | aperson | did somthing else | +-------------+----------+----------+--------------------+ {noformat} > Prevent left nested loop join > ----------------------------- > > Key: DRILL-5263 > URL: https://issues.apache.org/jira/browse/DRILL-5263 > Project: Apache Drill > Issue Type: Bug > Reporter: Serhii Harnyk > Assignee: Serhii Harnyk > Attachments: tmp.tar.gz > > > Nested loop join operator in Drill supports only inner join and returns > incorrect result for queries with left join. Drill should throw error in this > case. > Example: > {code:sql} > alter session set planner.enable_nljoin_for_scalar_only=false; > select t2.dt, t1.fyq, t2.who, t2.event > from t2 > left join t1 on t2.dt between t1.dts and t1.dte > order by t2.dt; > {code} > Result: > {noformat} > +-------------+----------+----------+--------------------+ > | dt | fyq | who | event | > +-------------+----------+----------+--------------------+ > | 2016-12-26 | 2016-Q2 | aperson | had chrsitmas | > | 2017-01-06 | 2016-Q3 | aperson | did somthing | > | 2017-01-12 | 2016-Q3 | aperson | did somthing else | > +-------------+----------+----------+--------------------+ > {noformat} -- This message was sent by Atlassian JIRA (v6.3.15#6346)