Algebraic Transformations Page 1 © 2013

Algebraic Transformations Page 1 © 2013 Hortonworks HIVE-784: Sub Query in Where or Having clause
HIVE-5555: Alt. Join Syntax; Join conditions in the Where Clause<br>

Sub Query transformation Page 2 © 2013 Hortonworks Support for In, Not In, Exists, Not Exists in Where or Having clause
But lots of restrictions
Sub Query predicate must be a top level conjunct
Only 1 Sub Query predicate
No Sub Query nesting
Correlation condition must be valid join conditions
And many more: See Spec on HIVE-784; 17 Restrictions so far.
Transformation at a high level are:
In/Exists => Left Outer Join
Not In/Exists => Left Outer Join + null check + null count for Not In
Correlation converted to Gby in Sub Query
In spite of long list of Restrictions, possibly useful
See HIVE-784 for TPCH Queries Q4, Q15, Q16, Q18 written with SQs
TPCDS Query 45<br>

Some Examples Page 3 © 2013 Hortonworks -- non agg, corr
select *
from src b
where b.key in
(select a.key
from src a
where b.value = a.value and a.key > '9'
)
;
-- non agg, non corr
select key, count(*)
from src
group by key
having count(*) in (select count(*) from src s1 where s1.key > '9' group by s1.key )
;
-- tpch Q4
select o_orderpriority, count(*) as order_count
from orders o
where
unix_timestamp(o_orderdate, 'yyyy-MM-dd') >= unix_timestamp('1993-07-01', 'yyyy-MM-dd')
and unix_timestamp(o_orderdate, 'yyyy-MM-dd') < unix_timestamp('1993-10-01', 'yyyy-MM-dd')
and exists (
select *
from lineitem
where
l_orderkey = o.o_orderkey
and l_commitdate < l_receiptdate
)
group by o_orderpriority
order by o_orderpriority;<br>