Uploaded image for project: 'Apache Drill'
  1. Apache Drill
  2. DRILL-4096

Incorrect result when we use coalesce in a join condition along with other filters

    XMLWordPrintableJSON

    Details

    • Type: Bug
    • Status: Open
    • Priority: Critical
    • Resolution: Unresolved
    • Affects Version/s: 1.3.0
    • Fix Version/s: None
    • Labels:
      None

      Description

      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

      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';
      

      Hive DDL :

      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");
      

      The data files are attached

        Attachments

          Activity

            People

            • Assignee:
              Unassigned
              Reporter:
              rkins Rahul Kumar Challapalli
            • Votes:
              0 Vote for this issue
              Watchers:
              2 Start watching this issue

              Dates

              • Created:
                Updated: