[
https://issues.apache.org/jira/browse/HIVE-15211?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15939303#comment-15939303
]
Caleb Jones edited comment on HIVE-15211 at 3/23/17 10:14 PM:
--------------------------------------------------------------
Will UDFs be supported in complex expressions in the ON clause?
I hit this limitation when I had two tables I wanted to join on based on the
intersection of array columns.
{noformat}
create table tbl_a (
val string,
ids array<string>
);
create table tbl_b (
val string,
ids array<string>
);
add jar hdfs:///brickhouse-0.7.1-SNAPSHOT.jar;
select a.val, b.val
from tbl_a as a
join tbl_b as b
on (size(intersect_array(a.ids, b.ids)) > 0);
{noformat}
An intersection size > 0 is a somewhat trivial case, but if requirements were
such that the intersection size was needed within a specific range it makes
this query much more difficult.
was (Author: calebjones):
Will UDFs be supported in complex expressions in the ON clause?
I hit this limitation when I had two tables I wanted to join on based on the
intersection of array columns.
{noformat}
create table tbl_a (
val string,
ids array<string>
);
create table tbl_b (
val string,
ids array<string>
);
add jar hdfs:///brickhouse-0.7.1-SNAPSHOT.jar;
select a.val, b.val
from tbl_a as a
join tbl_b as b
on (size(intersect_array(a.ids, b.ids)) > 0);
{noformat}
> Provide support for complex expressions in ON clauses for INNER joins
> ---------------------------------------------------------------------
>
> Key: HIVE-15211
> URL: https://issues.apache.org/jira/browse/HIVE-15211
> Project: Hive
> Issue Type: Bug
> Components: CBO, Parser
> Affects Versions: 2.2.0
> Reporter: Jesus Camacho Rodriguez
> Assignee: Jesus Camacho Rodriguez
> Labels: TODOC2.2
> Fix For: 2.2.0
>
> Attachments: HIVE-15211.01.patch, HIVE-15211.patch
>
>
> Currently, we have some restrictions on the predicates that we can use in ON
> clauses for inner joins (we have those restrictions for outer joins too, but
> we will tackle that in a follow-up). Semantically equivalent queries can be
> expressed if the predicate is introduced in the WHERE clause, but we would
> like that user can express it both in ON and WHERE clause, as in standard SQL.
> This patch is an extension to overcome these restrictions for inner joins.
> It will allow to write queries that currently fail in Hive such as:
> {code:sql}
> -- Disjunctions
> SELECT *
> FROM src1 JOIN src
> ON (src1.key=src.key
> OR src1.value between 100 and 102
> OR src.value between 100 and 102)
> LIMIT 10;
> -- Conjunction with multiple inputs references in one side
> SELECT *
> FROM src1 JOIN src
> ON (src1.key+src.key >= 100
> AND src1.key+src.key <= 102)
> LIMIT 10;
> -- Conjunct with no references
> SELECT *
> FROM src1 JOIN src
> ON (src1.value between 100 and 102
> AND src.value between 100 and 102
> AND true)
> LIMIT 10;
> {code}
--
This message was sent by Atlassian JIRA
(v6.3.15#6346)
