[jira] [Commented] (HIVE-24029) MV fails for queries with subqueries

["Aman Sinha (Jira)" <ji...@apache.org>]

    [ https://issues.apache.org/jira/browse/HIVE-24029?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17472418#comment-17472418 ] 

Aman Sinha commented on HIVE-24029:
-----------------------------------

On a recent build, I don't see this issue.  The CMV statement succeeds and Describe shows the following:
{noformat}
0: jdbc:hive2://localhost:11050/default> describe q16;
...
INFO  : OK
+----------------------+----------------+----------+
|       col_name       |   data_type    | comment  |
+----------------------+----------------+----------+
| order count          | bigint         |          |
| total shipping cost  | decimal(17,2)  |          |
| total net profit     | decimal(17,2)  |          |
+----------------------+----------------+----------+
3 rows selected (0.191 seconds)
{noformat}


> 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)