Rajesh Balamohan created HIVE-24027:
---------------------------------------
Summary: Add support for `intersect` keyword in MV
Key: HIVE-24027
URL: https://issues.apache.org/jira/browse/HIVE-24027
Project: Hive
Issue Type: Sub-task
Components: Materialized views
Reporter: Rajesh Balamohan
{noformat}
explain create materialized view mv as select distinct c_last_name,
c_first_name, d_date
from store_sales, date_dim, customer
where store_sales.ss_sold_date_sk = date_dim.d_date_sk
and store_sales.ss_customer_sk = customer.c_customer_sk
and d_month_seq between 1186 and 1186 + 11
intersect
select distinct c_last_name, c_first_name, d_date
from catalog_sales, date_dim, customer
where catalog_sales.cs_sold_date_sk = date_dim.d_date_sk
and catalog_sales.cs_bill_customer_sk = customer.c_customer_sk
and d_month_seq between 1186 and 1186 + 11
intersect
select distinct c_last_name, c_first_name, d_date
from web_sales, date_dim, customer
where web_sales.ws_sold_date_sk = date_dim.d_date_sk
and web_sales.ws_bill_customer_sk = customer.c_customer_sk
and d_month_seq between 1186 and 1186 + 11
{noformat}
This query fails with the following error msg
{noformat}
Error: Error while compiling statement: FAILED: SemanticException Cannot enable
automatic rewriting for materialized view. Statement has unsupported operator:
intersect. (state=42000,code=40000)
{noformat}
--
This message was sent by Atlassian Jira
(v8.3.4#803005)
