Rajesh Balamohan created HIVE-24029: ---------------------------------------
Summary: MV fails for queries with subqueries Key: HIVE-24029 URL: https://issues.apache.org/jira/browse/HIVE-24029 Project: Hive Issue Type: Sub-task Components: Materialized views Reporter: Rajesh Balamohan {noformat} explain create materialized view q16 as select count(distinct cs_order_number) as `order count` ,sum(cs_ext_ship_cost) as `total shipping cost` ,sum(cs_net_profit) as `total net profit` from catalog_sales cs1 ,date_dim ,customer_address ,call_center where d_date between '1999-4-01' and (cast('1999-4-01' as date) + 60 days) and cs1.cs_ship_date_sk = d_date_sk and cs1.cs_ship_addr_sk = ca_address_sk and ca_state = 'IL' and cs1.cs_call_center_sk = cc_call_center_sk and cc_county in ('Richland County','Bronx County','Maverick County','Mesa County', 'Raleigh County' ) and exists (select * from catalog_sales cs2 where cs1.cs_order_number = cs2.cs_order_number and cs1.cs_warehouse_sk <> cs2.cs_warehouse_sk) and not exists(select * from catalog_returns cr1 where cs1.cs_order_number = cr1.cr_order_number) {noformat} Error {noformat} Error: Error while compiling statement: FAILED: SemanticException [Error 10249]: Line 24:8 Unsupported SubQuery Expression 'cr_order_number': Only 1 SubQuery expression is supported. (state=42000,code=10249) {noformat} -- This message was sent by Atlassian Jira (v8.3.4#803005)