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

Hash join with empty tables should not do casting of data types to INT<OPTIONAL>

    Details

    • Type: Bug
    • Status: Closed
    • Priority: Major
    • Resolution: Resolved
    • Affects Version/s: None
    • Fix Version/s: 1.13.0
    • Component/s: None
    • Labels:
      None

      Description

      LeftJoin query by leveraging HashJoin operator leads to error, but by using MergeJoin works fine.

      0: jdbc:drill:zk=local> alter session set `planner.enable_hashjoin` = true;
      +-------+-----------------------------------+
      |  ok   |              summary              |
      +-------+-----------------------------------+
      | true  | planner.enable_hashjoin updated.  |
      +-------+-----------------------------------+
      1 row selected (0.078 seconds)
      0: jdbc:drill:zk=local> alter session set `planner.enable_mergejoin` = false;
      +-------+------------------------------------+
      |  ok   |              summary               |
      +-------+------------------------------------+
      | true  | planner.enable_mergejoin updated.  |
      +-------+------------------------------------+
      1 row selected (0.079 seconds)
      0: jdbc:drill:zk=local> select t1.a1, t1.b1, t2.a2, t2.b2 from dfs.`/tmp/nullable1.json` t1 left join dfs.`/tmp/empty0.json` t2 on t1.b1 = t2.b2;
      Error: SYSTEM ERROR: DrillRuntimeException: Join only supports implicit casts between 1. Numeric data
       2. Varchar, Varbinary data 3. Date, Timestamp data Left type: VARCHAR, Right type: INT. Add explicit casts to avoid this error
      
      Fragment 0:0
      
      [Error Id: 2cfc662f-48c2-4e62-a2ea-5a0f33d64c9b on vitalii-pc:31010] (state=,code=0)
      
      00-00    Screen : rowType = RecordType(ANY a1, ANY b1, ANY a2, ANY b2): rowcount = 1.0, cumulative cost = {2.1 rows, 20.1 cpu, 0.0 io, 0.0 network, 17.6 memory}, id = 930
      00-01      Project(a1=[$0], b1=[$1], a2=[$2], b2=[$3]) : rowType = RecordType(ANY a1, ANY b1, ANY a2, ANY b2): rowcount = 1.0, cumulative cost = {2.0 rows, 20.0 cpu, 0.0 io, 0.0 network, 17.6 memory}, id = 929
      00-02        Project(a1=[$1], b1=[$0], a2=[$3], b2=[$2]) : rowType = RecordType(ANY a1, ANY b1, ANY a2, ANY b2): rowcount = 1.0, cumulative cost = {2.0 rows, 20.0 cpu, 0.0 io, 0.0 network, 17.6 memory}, id = 928
      00-03          HashJoin(condition=[=($0, $2)], joinType=[left]) : rowType = RecordType(ANY b1, ANY a1, ANY b2, ANY a2): rowcount = 1.0, cumulative cost = {2.0 rows, 20.0 cpu, 0.0 io, 0.0 network, 17.6 memory}, id = 927
      00-05            Scan(groupscan=[EasyGroupScan [selectionRoot=file:/home/vitalii/IdeaProjects/drill-fork/exec/java-exec/target/test-classes/jsoninput/nullable1.json, numFiles=1, columns=[`b1`, `a1`], files=[file:/home/vitalii/IdeaProjects/drill-fork/exec/java-exec/target/test-classes/jsoninput/nullable1.json]]]) : rowType = RecordType(ANY b1, ANY a1): rowcount = 1.0, cumulative cost = {0.0 rows, 0.0 cpu, 0.0 io, 0.0 network, 0.0 memory}, id = 925
      00-04            Scan(groupscan=[EasyGroupScan [selectionRoot=file:/home/vitalii/IdeaProjects/drill-fork/exec/java-exec/src/test/resources/project/pushdown/empty0.json, numFiles=1, columns=[`b2`, `a2`], files=[file:/home/vitalii/IdeaProjects/drill-fork/exec/java-exec/src/test/resources/project/pushdown/empty0.json]]]) : rowType = RecordType(ANY b2, ANY a2): rowcount = 1.0, cumulative cost = {0.0 rows, 0.0 cpu, 0.0 io, 0.0 network, 0.0 memory}, id = 926
      
      

      Left join with empty tables should not do casting of data types to INT<OPTIONAL>.
      The result should be the same as for MergeJoin operator:

      0: jdbc:drill:zk=local> alter session set `planner.enable_hashjoin` = false;
      +-------+-----------------------------------+
      |  ok   |              summary              |
      +-------+-----------------------------------+
      | true  | planner.enable_hashjoin updated.  |
      +-------+-----------------------------------+
      1 row selected (0.087 seconds)
      0: jdbc:drill:zk=local> alter session set `planner.enable_mergejoin` = true;
      +-------+------------------------------------+
      |  ok   |              summary               |
      +-------+------------------------------------+
      | true  | planner.enable_mergejoin updated.  |
      +-------+------------------------------------+
      1 row selected (0.073 seconds)
      0: jdbc:drill:zk=local> select t1.a1, t1.b1, t2.a2, t2.b2 from dfs./tmp/nullable1.json` t1 left join dfs.`/tmp/empty0.json` t2 on t1.b1 = t2.b2;
      +-----+-------+-------+-------+
      | a1  |  b1   |  a2   |  b2   |
      +-----+-------+-------+-------+
      | 1   | abc   | null  | null  |
      | 2   | null  | null  | null  |
      +-----+-------+-------+-------+
      2 rows selected (0.624 seconds)
      

      Note: nullable1.json is in attachments and empty0.json is an empty file

        Attachments

        1. nullable1.json
          0.0 kB
          Vitalii Diravka

          Issue Links

            Activity

              People

              • Assignee:
                vitalii Vitalii Diravka
                Reporter:
                vitalii Vitalii Diravka
              • Votes:
                0 Vote for this issue
                Watchers:
                1 Start watching this issue

                Dates

                • Created:
                  Updated:
                  Resolved: