Using function as a join condition

[faisal moeen <fm...@gmail.com>]

Hi there,

Is there a way to use a boolean function as a join condition? If not, is it
easy to develop this capability. I have implemented some spatio-temporal
functions as part of my master thesis but for bench-marking, I need to have
join capability.

e.g.

SELECT A.a,B.b
FROM A JOIN B OVER overlap(A.a,B.b)

-- 

Regards
Faisal Moeen

Re: Using function as a join condition

[James Taylor <ja...@apache.org>]

Hi Faisal,

That's an interesting idea - thanks for filing a JIRA for it. This
isn't currently supported, nor would it be feasible with out existing
equi-hash join mechanism which relies on 1) one side of the join being
small enough to fit into memory, and 2) being able to do a map lookup
from one side to the other through the join key specified in the ON
clause. Even if it was supported, it'd likely be too expensive to
actually execute, as the query engine doesn't have enough information
to optimize it. It would be interesting to abstract this in some way
in the definition of a built-in function.

I'd recommend an alternate approach for now: execute two separate
queries both with an ORDER BY expression and then do a merge sort on
the client as you iterate through the results of both queries. You can
use any arbitrary built-in function in the ORDER BY.

Thanks,
James

On Wed, Jun 18, 2014 at 7:11 PM, faisal moeen <fm...@gmail.com> wrote:
> Hi there,
>
> Is there a way to use a boolean function as a join condition? If not, is it
> easy to develop this capability. I have implemented some spatio-temporal
> functions as part of my master thesis but for bench-marking, I need to have
> join capability.
>
> e.g.
>
> SELECT A.a,B.b
> FROM A JOIN B OVER overlap(A.a,B.b)
>
> --
>
> Regards
> Faisal Moeen