[
https://issues.apache.org/jira/browse/DRILL-6070?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Vitalii Diravka updated DRILL-6070:
-----------------------------------
Description:
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
was:
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.`/home/vitalii/IdeaProjects/drill-fork/exec/java-exec/target/test-classes/jsoninput/nullable1.json`
t1 left join
dfs.`/home/vitalii/IdeaProjects/drill-fork/exec/java-exec/src/test/resources/project/pushdown/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.`/home/vitalii/IdeaProjects/drill-fork/exec/java-exec/target/test-classes/jsoninput/nullable1.json`
t1 left join
dfs.`/home/vitalii/IdeaProjects/drill-fork/exec/java-exec/src/test/resources/project/pushdown/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}
> 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
> Fix For: Future
>
> 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
(v6.4.14#64029)
