Versions Compared

Old Version 2

changes.mady.by.user Vineet Garg

Saved on

compared with

New Version 3

changes.mady.by.user Vineet Garg

Saved on

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.

...

We plan to limit the scope with following assumption

  • Subqueries could only be top level expressions in select i.e. subqueries in complex expressions, aggregates, UDFs etc will not be supported for now. e.g. following queries will not run on HIVE

    Code Block
    languagesql
    -- subquery in non-simple expression
SELECT 1 + (SELECT SUM(ship_charge) FROM orders), customer.customer_num FROM customer

...

  •  
-- subquery in CASE
SELECT CASE WHEN (select count(*) from store_sales 
                  where ss_quantity between 1 and 20) > 409437
            THEN (select avg(ss_ext_list_price) 
                  from store_sales 
                  where ss_quantity between 1 and 20) 
            ELSE (select avg(ss_net_paid_inc_tax)
                  from store_sales
                  where ss_quantity between 1 and 20) end bucket1
FROM reason
WHERE r_reason_sk = 1

  • Scalar subquery can only return at most one row. HIVE will check for this case at runtime and throw an error if not satisfied. e.g. following query is invalid

    Code Block
    languagesql
    SELECT customer.customer_num,
	(SELECT ship_charge 
		FROM orders
		WHERE customer.customer_num = orders.customer_num
	) AS total_ship_chg
FROM customer

  • Scalar subquery can only have one column. HIVE will check for this case during compilation and throw an error. e.g following query is invalid

    Code Block
    languagesql
    SELECT customer.customer_num,
	(SELECT ship_charge, customer_num
		FROM orders LIMIT 1
	) AS total_ship_chg
FROM customer