[jira] [Commented] (HIVE-7966) CBO Trunk Merge: Hive Unit test Subquery test failure

["Laljo John Pullokkaran (JIRA)" <ji...@apache.org>]

    [ https://issues.apache.org/jira/browse/HIVE-7966?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=14120400#comment-14120400 ] 

Laljo John Pullokkaran commented on HIVE-7966:
----------------------------------------------

1.
select *
from (select *
      from src b
      where exists
          (select a.key
          from src a
          where b.value = a.value  and a.key = b.key and a.value > 'val_9')
     ) a
2.
select b.key, min(b.value)
from src b
group by b.key
having exists ( select a.key
                from src a
                where a.value > 'val_9' and a.value = min(b.value)
                )
3.
select p.p_partkey, li.l_suppkey
from (select distinct l_partkey as p_partkey from lineitem) p join lineitem li on p.p_partkey = li.l_partkey
where li.l_linenumber = 1 and
 li.l_orderkey in (select l_orderkey from lineitem where l_shipmode = 'AIR' and l_linenumber = li.l_linenumber)


4.
explain
select p_mfgr, p_name, avg(p_size)
from part
group by p_mfgr, p_name
having p_name in
  (select first_value(p_name) over(partition by p_mfgr order by p_size) from part)

5.
select *
from src b
where not exists
  (select a.key
  from src a
  where b.value = a.value and a.value > 'val_2'
  )

6.
select *
from src b
group by key, value
having not exists
  (select distinct a.key
  from src a
  where b.value = a.value and a.value > 'val_12'
  )

7.
select *
from T1_v where T1_v.key not in (select T2_v.key from T2_v)

8.
select b.p_mfgr, min(p_retailprice)
from part b
group by b.p_mfgr
having b.p_mfgr not in
  (select p_mfgr
  from part a
  group by p_mfgr
  having max(p_retailprice) - min(p_retailprice) > 600
  )

9.
explain
select p_mfgr, b.p_name, p_size
from part b
where b.p_name not in
  (select p_name
  from (select p_mfgr, p_name, p_size, rank() over(partition by p_mfgr order by p_size) as r from part) a
  where r <= 2 and b.p_mfgr = p_mfgr
  )

10.
select *
from cv3
where cv3.key in (select key from cv1)




> CBO Trunk Merge: Hive Unit test Subquery test failure
> -----------------------------------------------------
>
>                 Key: HIVE-7966
>                 URL: https://issues.apache.org/jira/browse/HIVE-7966
>             Project: Hive
>          Issue Type: Sub-task
>          Components: CBO
>            Reporter: Laljo John Pullokkaran
>            Assignee: Harish Butani
>




--
This message was sent by Atlassian JIRA
(v6.3.4#6332)