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 <[email protected]> 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
>
>
>
>
