2

If anyone would know what the issue here is please ? I am running this in Hive

 select * from a left join b
 on a.id=b.id and a.date between b.start_dte and b.end_dte

Error while compiling statement: FAILED: SemanticException line 0:undefined:-1 Both left and right aliases encountered in JOIN 'end_dte'

leftjoin
  • 36,950
  • 8
  • 57
  • 116
tom
  • 31
  • 3

1 Answers1

2

Hive does not support non-equi joins.

Try to move a.date between b.start_dte and b.end_dte to the WHERE clause:

 select * from a left join b on a.id=b.id 
  where (a.date between b.start_dte and b.end_dte) or b.id is null

or b.id is null is to allow not joined records (left join)

leftjoin
  • 36,950
  • 8
  • 57
  • 116
  • Good finding bro... – Koushik Roy Jan 12 '22 at 08:26
  • thanks but seems like this "or b.id is null" does not make any difference. I tried with where (a.date between b.start_dte and b.end_dte) and then where (a.date between b.start_dte and b.end_dte) or b.id is null and i am getting the same result – tom Feb 01 '22 at 22:31
  • @tom it can be. inner join can produce the same result as left join if all records joined – leftjoin Feb 02 '22 at 03:09