HIVE在使用spark引擎执行计划时存在的代码解析兼容性问题

题目引入：

请向用户推荐他的朋友收藏的商品，但是不要包括用户已经收藏过的商品。

--建表语句
DROP TABLE IF EXISTS friendship;
CREATE TABLE friendship (
     `user1_id` varchar(32) ,
     `user2_id` varchar(32)
) COMMENT '用户关系表'
    ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'
        NULL DEFINED AS ''
    LOCATION '/warehouse/sdc/rds/friendship';
DROP TABLE IF EXISTS user_favorites;
CREATE TABLE user_favorites (
        `user_id` varchar(32) ,
        `id` varchar(32)
)   COMMENT '用户收藏表'
    ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'
        NULL DEFINED AS ''
    LOCATION '/warehouse/sdc/rds/user_favorites';
--插入数据
INSERT overwrite table friendship VALUES ('1','2'),('1','3'),('1','4'),('1','6'),('2','1'),('2','3'),('2','4'),('2','5'),('6','1');
INSERT overwrite table user_favorites VALUES ('1','88'),('2','23'),('3','24'),('4','56'),('5','11'),('6','23'),('2','77'),('3','77'),('6','88');

SQL代码：

select need.user1_id,collect_set(need.new_id) rep_id
from
        (select distinct fs.user1_id,
                    uf.id
        from friendship fs left join user_favorites uf
            on fs.user1_id=uf.user_id ) lose
            right join
    (  select t1.user1_id,
              new_id
       from(
               select fs.user1_id,
                      all_id
               from friendship fs left join (
                   select user_id,
                          collect_set(id) all_id
                   from user_favorites
                   group by user_id ) uf on fs.user2_id=uf.user_id
           )t1
               lateral view explode(t1.all_id) tmp as new_id) need
            on need.user1_id=lose.user1_id and need.new_id=lose.id
    where lose.id is null and lose.user1_id is null
group by need.user1_id;

spark引擎执行结果：

注意：该结果有误，分析两表join后的结果，发现左表没有数据显示！

 最终结果没有起到过滤筛选的效果：

 更换为MR引擎执行：

结果如下图所示，该数据符合预期！

 结论：猜想spark引擎在执行计算时与hive解析出来的执行代码存在兼容性问题。