[ https://issues.apache.org/jira/browse/DRILL-6070?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Vitalii Diravka resolved DRILL-6070. ------------------------------------ Resolution: Resolved Assignee: Vitalii Diravka Fix Version/s: (was: Future) 1.13.0 This issue is resolved in context of DRILL-5851 and DRILL-4185. The query works for HashJoin, MergeJoin and NestedLoopJoin operators. > Hash join with empty tables should not do casting of data types to > INT<OPTIONAL> > -------------------------------------------------------------------------------- > > Key: DRILL-6070 > URL: https://issues.apache.org/jira/browse/DRILL-6070 > Project: Apache Drill > Issue Type: Bug > Reporter: Vitalii Diravka > Assignee: Vitalii Diravka > Priority: Major > Fix For: 1.13.0 > > Attachments: nullable1.json > > > LeftJoin query by leveraging HashJoin operator leads to error, but by using > MergeJoin works fine. > {code} > 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) > {code} > {code} > 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 > {code} > 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: > {code} > 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) > {code} > Note: nullable1.json is in attachments and empty0.json is an empty file -- This message was sent by Atlassian JIRA (v7.6.3#76005)