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)