Hive SQL Is Incompatible with SQL2003 Standards

This document describes the incompatibility issues between Hive SQL and SQL2003.

• The view cannot be written in having.

  For example:

  select c_last_name
        ,c_first_name
        ,s_store_name
        ,sum(netpaid) paid
  from ssales
  where i_color = 'chiffon'
  group by c_last_name
          ,c_first_name
          ,s_store_name
  having sum(netpaid) > (select 0.05*avg(netpaid) from ssales);

  Error message:

  Error: Error while compiling statement: FAILED:ParseException line 46:23 cannot recognize input near 'select' '0.05' '*' in expression specification (state=42000,code=40000)

• The exists query entity association is not supported.

  For example:

  select
         o_orderpriority,
         count(*) as order_count
  from
         orders
  where
         o_orderdate >= date '1996-09-01'
         and o_orderdate < date '1996-09-01' + interval '3' month
         and exists (
                select
                       *
                from
                       lineitem
                where
                       l_orderkey = o_orderkey
                       and l_commitdate < l_receiptdate
         )
  group by
         o_orderpriority
  order by
         o_orderpriority;

  Error message:

  Error: Error while compiling statement: FAILED: SemanticException [Error 10250]: Line 1:181 Invalid SubQuery expression 'l_receiptdate': For Exists/Not Exists operator SubQuery must be Correlated. (state=42000,code=10250)

• The Having does not support sub-query.

  For example:

  select
         ps_partkey,
         sum(ps_supplycost * ps_availqty) as value
  from
         partsupp,
         supplier,
         nation
  where
         ps_suppkey = s_suppkey
         and s_nationkey = n_nationkey
         and n_name = 'SAUDI ARABIA'
  group by
         ps_partkey having
                sum(ps_supplycost * ps_availqty) > (
                       select
                              sum(ps_supplycost * ps_availqty) * 0.0001000000
                       from
                              partsupp,
                              supplier,
                              nation
                       where
                              ps_suppkey = s_suppkey
                              and s_nationkey = n_nationkey
                              and n_name = 'SAUDI ARABIA'
                )
  order by
         value desc;

  Error message:

  Error: Error while compiling statement: FAILED: SemanticException Line 0:-1 Unsupported SubQuery Expression ''SAUDI ARABIA'': Only SubQuery expressions that are top level conjuncts are allowed (state=42000,code=40000)

• Multiple query results cannot be displayed as multiple fields.

  For example:

  select
         c_count,
         count(*) as custdist
  from
         (
                select
                       c_custkey,
                       count(o_orderkey)
                from
                       customer left outer join orders on
                              c_custkey = o_custkey
                              and o_comment not like '%pending%requests%'
                group by
                       c_custkey
         ) as c_orders (c_custkey, c_count)
  group by
         c_count
  order by
         custdist desc,
         c_count desc;

  Error message:

  Error: Error while compiling statement: FAILED: ParseException line 1:213 missing EOF at '(' near 'c_orders' (state=42000,code=40000)

• The query results cannot be compared as fields.

  For example:

  select
         sum(l_extendedprice) / 7.0 as avg_yearly
  from
         lineitem,
         part
  where
         p_partkey = l_partkey
         and p_brand = 'Brand#25'
         and p_container = 'MED JAR'
         and l_quantity < (
                select
                       0.2 * avg(l_quantity)
                from
                       lineitem
                where
                       l_partkey = p_partkey
         );

  Error message:

  Error: Error while compiling statement: FAILED: SemanticException [Error 10249]: Line 14:4 Unsupported SubQuery Expression 'ps_suppkey': Correlating expression contains ambiguous column references. (state=42000,code=10249)

• The multi-table association query does not support the sub-query filter by not in or in.

  For example:

  select
   p_brand,
   p_type,
   p_size,
   count(distinct ps_suppkey) as supplier_cnt
  from
   partsupp,
   part
  where
   p_partkey = ps_partkey
   and p_brand <> 'Brand#12'
   and p_type not like 'PROMO PLATED%'
   and p_size in (25, 2, 43, 9, 35, 36, 48, 24)
   and ps_suppkey in (
    select
     s_suppkey as ps_suppkey
    from
     supplier
    where
     s_comment like '%Customer%Complaints%'
   )
  group by
   p_brand,
   p_type,
   p_size
  order by
   supplier_cnt desc,
   p_brand,
   p_type,
   p_size;
  

  Error message:

  Error: Error while compiling statement: FAILED: SemanticException [Error 10249]: Line 14:4 Unsupported SubQuery Expression 'ps_suppkey': Correlating expression contains ambiguous column references. (state=42000,code=10249)

• The multi-table association does not support filtering of not in and in sub-queries.

  For example:

  select
         c_name,
         c_custkey,
         o_orderkey,
         o_orderdate,
         o_totalprice,
         sum(l_quantity)
  from
         customer,
         orders,
         lineitem
  where
         o_orderkey in (
                select
                       l_orderkey
                from
                       lineitem
                group by
                       l_orderkey having
                              sum(l_quantity) > 315
         )
         and c_custkey = o_custkey
         and o_orderkey = l_orderkey
  group by
         c_name,
         c_custkey,
         o_orderkey,
         o_orderdate,
         o_totalprice
  order by
         o_totalprice desc,
         o_orderdate
  limit 100;

  Error message:

  Error: Error while compiling statement: FAILED: SemanticException [Error 10249]: Line 13:0 Unsupported SubQuery Expression 'o_orderkey': Correlating expression contains ambiguous column references. (state=42000,code=10249)

• The association conditions do not support multiple exists query entities.

  For example:

  select
         s_name,
         count(*) as numwait
  from
         supplier,
         lineitem l1,
         orders,
         nation
  where
         s_suppkey = l1.l_suppkey
         and o_orderkey = l1.l_orderkey
         and o_orderstatus = 'F'
         and l1.l_receiptdate > l1.l_commitdate
         and exists (
                select
                       *
                from
                       lineitem l2
                where
                       l2.l_orderkey = l1.l_orderkey
                       and l2.l_suppkey <> l1.l_suppkey
         )
         and not exists (
                select
                       *
                from
                       lineitem l3
                where
                       l3.l_orderkey = l1.l_orderkey
                       and l3.l_suppkey <> l1.l_suppkey
                       and l3.l_receiptdate > l3.l_commitdate
         )
         and s_nationkey = n_nationkey
         and n_name = 'VIETNAM'
  group by
         s_name
  order by
         numwait desc,
         s_name
  limit 100;

  Error message:

  Error: Error while compiling statement: FAILED: SemanticException [Error 10249]: Line 23:8 Unsupported SubQuery Expression 'l_commitdate': Only 1 SubQuery expression is supported. (state=42000,code=10249)

• The multi-level in-nested sub-query is not supported.

  For example:

  select i_item_id item_id,
           sum(sr_return_quantity) sr_item_qty
    from store_returns,
         item,
         date_dim
    where sr_item_sk = i_item_sk
    and   d_date    in
           (select d_date
           from date_dim
           where d_week_seq in
                   (select d_week_seq
                   from date_dim
             where d_date in ('1998-01-02','1998-10-15','1998-11-10')))
    and   sr_returned_date_sk   = d_date_sk
    group by i_item_id),
    cr_items as
    (select i_item_id item_id,
           sum(cr_return_quantity) cr_item_qty
    from catalog_returns,
         item,
         date_dim
    where cr_item_sk = i_item_sk);

  Error message:

  Unsupported SubQuery Expression 'd_week_seq': SubQuery cannot use the table alias: date_dim; this is also an alias in the Outer Query and SubQuery contains a unqualified column reference (state=42000,code=10249)