[HIVE-24027] Add support for `intersect` keyword in MV

• Type: Sub-task
• Status: Closed
• Priority: Major
• Resolution: Fixed
• Fix Version/s: 4.0.0-alpha-1
• Component/s: Materialized views
• Assignee: Krisztian Kasa
• Reporter: Rajesh Balamohan
• Created: 11/Aug/20 22:27
• Updated: 27/Feb/24 22:23
• Resolved: 07/Jan/21 15:43

Description

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

This query fails with the following error msg

Error: Error while compiling statement: FAILED: SemanticException Cannot enable automatic rewriting for materialized view. Statement has unsupported operator: intersect. (state=42000,code=40000)

Issue Links

• Is contained by: HIVE-24274 Implement Query Text based MaterializedView rewrite