Hive SQL Is Incompatible with SQL2003 Standards

Updated on 2022-09-14 GMT+08:00

View PDF

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)

Parent topic: FAQ

Previous topic: Problem performing GSS wrap Message Is Displayed Due to IBM JDK Exceptions

Next topic: Hive Development Guide (Normal Mode)