hive query with between in join working in llap but not in hive

Question

Below range query with join working fine in llap, but not in Hiveserver2/Hive.CLI.

Please suggest how to work with range join query in Hive.

Hive version : 1.2.1.2.6

HDP version : 2.6.0.3

Query:

select * from datahub.cgs_tmp_gre gre
INNER JOIN datahub.cgs_tmp_cgsrxclm_ev_ba ba
ON gre.guar_key = ba.guar_key and gre.serviced_dte = ba.serviced_dte
AND gre.ts between ba.obsv_start_ts and ba.obsv_stop_ts
AND gre.ts between ba.obsv_start_ts and ba.obsv_stop_ts
AND gre.phcy_claim_id=2;

Below is the error thrown when we run it in hive CLI or hive server 2:

Error: Error while compiling statement: FAILED: SemanticException Line 0:-1 Both left and right aliases encountered in JOIN 'obsv_stop_ts' (state=42000,code=40000)

Answer 1

This error is because you have the same join condition twice. between resolves to >= and <= and inequalities aren't supported in join conditions (before version 2.2.0). Move the condition to where clause and it should work.

select * --specify required columns rather than *
from datahub.cgs_tmp_gre gre
INNER JOIN datahub.cgs_tmp_cgsrxclm_ev_ba ba
ON gre.guar_key = ba.guar_key and gre.serviced_dte = ba.serviced_dte
WHERE gre.ts >= ba.obsv_start_ts and gre.ts <= ba.obsv_stop_ts --replaced between with >= and <=
AND gre.phcy_claim_id=2;