Xia Zhao created HIVE-21545:
-------------------------------
Summary: Left Semi Join: right table can be referenced in select?
Key: HIVE-21545
URL: https://issues.apache.org/jira/browse/HIVE-21545
Project: Hive
Issue Type: Bug
Affects Versions: 1.1.0
Reporter: Xia Zhao
In the below left semi join queries:
create table movies (id int, title string);
insert into movies values (1, "Toy Story"), (2, "Star Wars"), (3, "Harry
Potter");
create table ratings (user_id int, movie_id int, rating decimal(2,1));
insert into ratings values (1, 1, 5.0), (2, 1, 4.5), (1, 2, 5.0), (3, 3, 5.0);
Q1: without reference to table alias, the right hand table column user_id is
referenced in SELECT without prompting errors and results available. This is
NOT expected left semi join behaviour. "The restrictions of using LEFT SEMI
JOIN are that the right-hand-side table should only be referenced in the join
condition (ON-clause), but not in WHERE- or SELECT-clauses etc."
SELECT user_id, count(id) from movies
left SEMI JOIN ratings
on id=movie_id and rating = 5
group by user_id;
Q2: with reference to table alias, errors prompts when referencing right hand
table column user_id in SELECT. Expected left semi join behaviour.
SELECT r.user_id, count(m.id) from movies m
left SEMI JOIN ratings r
on m.id=r.movie_id and r.rating = 5
group by r.user_id;
--
This message was sent by Atlassian JIRA
(v7.6.3#76005)
