[
https://issues.apache.org/jira/browse/HIVE-24029?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Aman Sinha resolved HIVE-24029.
-------------------------------
Resolution: Cannot Reproduce
Resolving based on above.
> 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
> Priority: Major
>
> {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.20.1#820001)
