Arina Ielchiieva created DRILL-5375: ---------------------------------------
Summary: Nested loop join: return correct result for left join Key: DRILL-5375 URL: https://issues.apache.org/jira/browse/DRILL-5375 Project: Apache Drill Issue Type: Bug Affects Versions: 1.8.0 Reporter: Arina Ielchiieva Assignee: Arina Ielchiieva Mini repro: 1. Create 2 Hive tables with data {code} CREATE TABLE t1 ( FYQ varchar(999), dts varchar(999), dte varchar(999) ) ROW FORMAT DELIMITED FIELDS TERMINATED BY ','; 2016-Q1,2016-06-01,2016-09-30 2016-Q2,2016-09-01,2016-12-31 2016-Q3,2017-01-01,2017-03-31 2016-Q4,2017-04-01,2017-06-30 CREATE TABLE t2 ( who varchar(999), event varchar(999), dt varchar(999) ) ROW FORMAT DELIMITED FIELDS TERMINATED BY ','; aperson,did somthing,2017-01-06 aperson,did somthing else,2017-01-12 aperson,had chrsitmas,2016-12-26 aperson,went wild,2016-01-01 {code} 2. Impala Query shows correct result {code} 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; +------------+---------+---------+-------------------+ | dt | fyq | who | event | +------------+---------+---------+-------------------+ | 2016-01-01 | NULL | aperson | went wild | | 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 | +------------+---------+---------+-------------------+ {code} 3. Drill query shows wrong results: {code} alter session set planner.enable_nljoin_for_scalar_only=false; use hive; 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; +-------------+----------+----------+--------------------+ | 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 | +-------------+----------+----------+--------------------+ 3 rows selected (2.523 seconds) {code} -- This message was sent by Atlassian JIRA (v6.3.15#6346)