--! qt:dataset:src_cbo
set hive.cbo.enable=true;
set hive.exec.check.crossproducts=false;

set hive.stats.fetch.column.stats=true;
set hive.auto.convert.join=false;

-- 18. SubQueries Not Exists
-- distinct, corr
select * 
from src_cbo b 
where not exists 
  (select distinct a.key 
  from src_cbo a 
  where b.value = a.value and a.value > 'val_2'
  )
;

-- no agg, corr, having
select * 
from src_cbo b 
group by key, value
having not exists 
  (select a.key 
  from src_cbo a 
  where b.value = a.value  and a.key = b.key and a.value > 'val_12'
  )
;

-- 19. SubQueries Exists
-- view test
create view cv1_n2 as 
select * 
from src_cbo b 
where exists
  (select a.key 
  from src_cbo a 
  where b.value = a.value  and a.key = b.key and a.value > 'val_9')
;

select * from cv1_n2
;

-- sq in from
select * 
from (select * 
      from src_cbo b 
      where exists 
          (select a.key 
          from src_cbo a 
          where b.value = a.value  and a.key = b.key and a.value > 'val_9')
     ) a
;

-- sq in from, having
select *
from (select b.key, count(*) 
  from src_cbo b 
  group by b.key
  having exists 
    (select a.key 
    from src_cbo a 
    where a.key = b.key and a.value > 'val_9'
    )
) a
;