[ https://issues.apache.org/jira/browse/HIVE-15211?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15939303#comment-15939303 ]
Caleb Jones commented on HIVE-15211: ------------------------------------ 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)