Uploaded image for project: 'Hive'
  1. Hive
  2. HIVE-22806

Missing materialized view rewrite in case the filter is further narrowed

    XMLWordPrintableJSON

    Details

    • Type: Bug
    • Status: Resolved
    • Priority: Major
    • Resolution: Duplicate
    • Affects Version/s: None
    • Fix Version/s: 4.0.0
    • Component/s: Materialized views
    • Labels:
      None

      Description

      I was checking some basic things when I've noticed that mv rewriting doesn't kick in for some cases:

      explain
      SELECT empid, deptname
      FROM emps
      JOIN depts
        using (deptno)
      WHERE hire_date >= 600
          AND hire_date <= 1200    -- depending on the presence of this condition the rewrite may not happen
      ;
      

      qtest:

      
      set hive.support.concurrency=true;
      set hive.txn.manager=org.apache.hadoop.hive.ql.lockmgr.DbTxnManager;
      set hive.strict.checks.cartesian.product=false;
      set hive.stats.fetch.column.stats=true;
      set hive.materializedview.rewriting=true;
      
      -- create some tables
      CREATE TABLE emps (
        empid INT,
        deptno INT,
        name VARCHAR(256),
        salary FLOAT,
        hire_date int)
      STORED AS ORC
      TBLPROPERTIES ('transactional'='true');
       
      CREATE TABLE depts (
        deptno INT,
        deptname VARCHAR(256),
        locationid INT)
      STORED AS ORC
      TBLPROPERTIES ('transactional'='true');
      
      -- load data
      insert into emps values (100, 10, 'Bill', 10000, 1000), (200, 20, 'Eric', 8000, 500),
        (150, 10, 'Sebastian', 7000, null), (110, 10, 'Theodore', 10000, 250), (120, 10, 'Bill', 10000, 250)
        ;
      insert into depts values (10, 'Sales', 10), (30, 'Marketing', null), (20, 'HR', 20);
      
      
      alter table emps add constraint pk1 primary key (empid) disable novalidate rely;
      alter table depts add constraint pk2 primary key (deptno) disable novalidate rely;
      alter table emps add constraint fk1 foreign key (deptno) references depts(deptno) disable novalidate rely;
      
      -- create mv
      CREATE MATERIALIZED VIEW mv1
      AS
      SELECT empid, deptname, hire_date
      FROM emps JOIN depts
        using (deptno)
        -- ON (emps.deptno = depts.deptno)
      WHERE hire_date >= 500;
      
      
      -- expected to see that materialzed view is being used; however it doesnt:
      explain
      SELECT empid, deptname
      FROM emps
      JOIN depts
        using (deptno)
      WHERE hire_date >= 600
          AND hire_date <= 1200 
      ;
      
      
      -- now we can see that the materialzed view is being used:
      explain
      SELECT empid, deptname
      FROM emps
      JOIN depts
        using (deptno)
      WHERE hire_date >= 600
      --    AND hire_date <= 1200  
      ;
      

        Attachments

          Issue Links

            Activity

              People

              • Assignee:
                Unassigned
                Reporter:
                kgyrtkirk Zoltan Haindrich
              • Votes:
                0 Vote for this issue
                Watchers:
                1 Start watching this issue

                Dates

                • Created:
                  Updated:
                  Resolved: