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

Query returns incorrect result or does not fail when cast with is null is used in filter condition

    XMLWordPrintableJSON

Details

    • Bug
    • Status: Resolved
    • Major
    • Resolution: Fixed
    • 1.16.0
    • 1.17.0
    • None
    • None

    Description

      1. For the case when a query contains filter with a cast which cannot be done with is null, the query does not fail:

      select * from dfs.tmp.`a.json` as t where cast(t.a as integer) is null;
      +---+
      | a |
      +---+
      +---+
      No rows selected (0.142 seconds)
      

      where

      cat /tmp/a.json
      {"a":"aaa"}
      

      But for the case when this condition is specified in project, query, as it is expected, fails:

      select cast(t.a as integer) is null from dfs.tmp.`a.json` t;
      Error: SYSTEM ERROR: NumberFormatException: aaa
      
      Fragment 0:0
      
      Please, refer to logs for more information.
      
      [Error Id: ed3982ce-a12f-4d63-bc6e-cafddf28cc24 on user515050-pc:31010] (state=,code=0)
      

      This is a regression, for Drill 1.15 the first and the second queries are failed:

      select * from dfs.tmp.`a.json` as t where cast(t.a as integer) is null;
      Error: SYSTEM ERROR: NumberFormatException: aaa
      
      Fragment 0:0
      
      Please, refer to logs for more information.
      
      [Error Id: 2f878f15-ddaa-48cd-9dfb-45c04db39048 on user515050-pc:31010] (state=,code=0)
      

      2. For the case when drill.exec.functions.cast_empty_string_to_null is enabled, this issue will cause wrong results:

      alter system set `drill.exec.functions.cast_empty_string_to_null`=true;
      
      select * from dfs.tmp.`a1.json` t where cast(t.a as integer) is null;
      +---+
      | a |
      +---+
      +---+
      No rows selected (1.759 seconds)
      

      where

      cat /tmp/a1.json 
      {"a":"1"}
      {"a":""}
      

      Result for Drill 1.15.0:

      select * from dfs.tmp.`a1.json` t where cast(t.a as integer) is null;
      +----+
      | a  |
      +----+
      |    |
      +----+
      1 row selected (1.724 seconds)
      

      Attachments

        Issue Links

          Activity

            People

              volodymyr Vova Vysotskyi
              volodymyr Vova Vysotskyi
              Votes:
              0 Vote for this issue
              Watchers:
              3 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved: