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