[jira] [Created] (DRILL-4096) Incorrect result when we use coalesce in a join condition along with other filters

["Rahul Challapalli (JIRA)" <ji...@apache.org>]

Rahul Challapalli created DRILL-4096:
----------------------------------------

             Summary: Incorrect result when we use coalesce in a join condition along with other filters
                 Key: DRILL-4096
                 URL: https://issues.apache.org/jira/browse/DRILL-4096
             Project: Apache Drill
          Issue Type: Bug
          Components: Functions - Drill, Storage - Hive
    Affects Versions: 1.3.0
            Reporter: Rahul Challapalli
            Priority: Critical


git.commit.id.abbrev=447f8ba

The below query returns no results which is wrong based on the data set. Interestingly if we remove the second filter we get a cannot plan exception from drill. Will raise a different jira, if I cannot find an existing one
{code}
select * from hive.null_schemachange d, hive.onlynulls n where d.date_col = coalesce(n.date_col, date '2038-04-10', n.date_col) and d.date_col > '2015-01-01';
{code}

Hive DDL :
{code}
drop table if exists null_schemachange;
create external table null_schemachange (
  int_col int,
  bigint_col bigint,
  date_col date,
  time_col string,
  timestamp_col timestamp,
  interval_col string,
  varchar_col string,
  float_col float,
  double_col double,
  bool_col boolean
)
ROW FORMAT DELIMITED FIELDS TERMINATED BY "|"
LOCATION '/drill/testdata/hive_storage/null_schemachange.tbl'
TBLPROPERTIES ("serialization.null.format"="null");


drop table if exists onlynulls;
create external table onlynulls (
  int_col int,
  bigint_col bigint,
  date_col date,
  time_col string,
  timestamp_col timestamp,
  interval_col string,
  varchar_col string,
  float_col float,
  double_col double,
  bool_col boolean
)
ROW FORMAT DELIMITED FIELDS TERMINATED BY "|"
LOCATION '/drill/testdata/hive_storage/onlynulls.tbl'
TBLPROPERTIES ("serialization.null.format"="null");
{code}

The data files are attached



--
This message was sent by Atlassian JIRA
(v6.3.4#6332)