You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@drill.apache.org by WeiWan <we...@sunteng.com> on 2016/12/05 03:47:01 UTC
Join keys should be converted to target type
0: jdbc:drill:drillbit=localhost> set planner.in_subquery_threshold=20;
+-------+-----------------------------------------+
| ok | summary |
+-------+-----------------------------------------+
| true | planner.in_subquery_threshold updated. |
+-------+-----------------------------------------+
1 row selected (0.12 seconds)
0: jdbc:drill:drillbit=localhost> describe `test`;
+--------------+--------------------+--------------+
| COLUMN_NAME | DATA_TYPE | IS_NULLABLE |
+--------------+--------------------+--------------+
| time | BIGINT | NO |
| d1 | CHARACTER VARYING | NO |
| m1 | BIGINT | NO |
| m2 | BIGINT | NO |
+--------------+--------------------+--------------+
4 rows selected (0.16 seconds)
0: jdbc:drill:drillbit=localhost> set planner.in_subquery_threshold=20;
+-------+-----------------------------------------+
| ok | summary |
+-------+-----------------------------------------+
| true | planner.in_subquery_threshold updated. |
+-------+-----------------------------------------+
1 row selected (0.068 seconds)
0: jdbc:drill:drillbit=localhost> select count(*) from `test` where m1 in ('1','2','3','4','5','6','7','8','9','10','11','12','13','14','15','16','17','18','19','20');
Error: SYSTEM ERROR: AssertionError: Internal error: Cannot find common type for join keys $0 (type BIGINT) and $0 (type CHAR(2))
[Error Id: 70d0a0b3-a48b-489a-92fd-7cc934284f3d on bc41.clicki.cn:31010] (state=,code=0)
Regards
Flow Wei
Re: Join keys should be converted to target type
Posted by Gautam Parai <gp...@maprtech.com>.
Hi Flow,
This happens because Drill is using a hash join based plan where '1', '2'
are used to build the hash table(CHAR) which is probed using m1 which is
BIGINT. Hence, the complaint.
You could
a) rewrite the query as select count(*) from `test` where cast(m1 as
CHAR(2)) in ('1','2','3','4','5','6','7','8','9','10','11','12','13','
14','15','16','17','18','19','20'); or
b) set planner.in_subquery_threshold=21; // will invoke the filter-based
plan instead of the join based plan.
Gautam
On Sun, Dec 4, 2016 at 7:47 PM, WeiWan <we...@sunteng.com> wrote:
>
> 0: jdbc:drill:drillbit=localhost> set planner.in_subquery_threshold=20;
> +-------+-----------------------------------------+
> | ok | summary |
> +-------+-----------------------------------------+
> | true | planner.in_subquery_threshold updated. |
> +-------+-----------------------------------------+
> 1 row selected (0.12 seconds)
> 0: jdbc:drill:drillbit=localhost> describe `test`;
> +--------------+--------------------+--------------+
> | COLUMN_NAME | DATA_TYPE | IS_NULLABLE |
> +--------------+--------------------+--------------+
> | time | BIGINT | NO |
> | d1 | CHARACTER VARYING | NO |
> | m1 | BIGINT | NO |
> | m2 | BIGINT | NO |
> +--------------+--------------------+--------------+
> 4 rows selected (0.16 seconds)
> 0: jdbc:drill:drillbit=localhost> set planner.in_subquery_threshold=20;
> +-------+-----------------------------------------+
> | ok | summary |
> +-------+-----------------------------------------+
> | true | planner.in_subquery_threshold updated. |
> +-------+-----------------------------------------+
> 1 row selected (0.068 seconds)
> 0: jdbc:drill:drillbit=localhost> select count(*) from `test` where m1 in
> ('1','2','3','4','5','6','7','8','9','10','11','12','13','
> 14','15','16','17','18','19','20');
> Error: SYSTEM ERROR: AssertionError: Internal error: Cannot find common
> type for join keys $0 (type BIGINT) and $0 (type CHAR(2))
>
>
> [Error Id: 70d0a0b3-a48b-489a-92fd-7cc934284f3d on bc41.clicki.cn:31010]
> (state=,code=0)
>
>
>
> Regards
> Flow Wei
>
>
>
>