[HIVE-28050] Disable Incremental non aggregated materialized view rebuild in presence of delete operations

• Type: Bug
• Status: Closed
• Priority: Major
• Resolution: Fixed
• Fix Version/s: 4.0.0
• Component/s: Materialized views
• Labels: pull-request-available
• Assignee: Krisztian Kasa
• Reporter: Krisztian Kasa
• Created: 31/Jan/24 05:55
• Updated: 29/Mar/24 17:49
• Resolved: 12/Feb/24 07:18

Description

To support incremental rebuild of materialized views which definition does not have aggregate in presence of delete operations in any of its source tables the records of the source tables need to be uniquely identified and right joined with the records present in the view.
The join keys should be the unique columns of each table in the definition query but we can not determine which are those columns.

One possibility is to project ROW_IDs of each source table in the view definition but the writeId component is changing at delete.

Another way is to project columns of primary keys or unique keys but these constraints are not enforced in Hive.

Current implementation leads to data correctness issues:

set hive.support.concurrency=true;
set hive.txn.manager=org.apache.hadoop.hive.ql.lockmgr.DbTxnManager;

create table cmv_basetable_n6 (a int, b varchar(256), c decimal(10,2), d int) stored as orc TBLPROPERTIES ('transactional'='true');

insert into cmv_basetable_n6 values
 (1, 'bob', 10.30, 2),
 (1, 'alfred', 10.30, 2),
 (2, 'bob', 3.14, 3),
 (2, 'bonnie', 172342.2, 3),
 (3, 'calvin', 978.76, 3),
 (3, 'charlie', 9.8, 1);

create table cmv_basetable_2_n3 (a int, b varchar(256), c decimal(10,2), d int) stored as orc TBLPROPERTIES ('transactional'='true');

insert into cmv_basetable_2_n3 values
 (1, 'alfred', 10.30, 2),
 (3, 'calvin', 978.76, 3);

CREATE MATERIALIZED VIEW cmv_mat_view_n6
  TBLPROPERTIES ('transactional'='true') AS
  SELECT cmv_basetable_n6.a, cmv_basetable_2_n3.c
  FROM cmv_basetable_n6 JOIN cmv_basetable_2_n3 ON (cmv_basetable_n6.a = cmv_basetable_2_n3.a)
  WHERE cmv_basetable_2_n3.c > 10.0;

delete from cmv_basetable_n6 where b = 'bob';

explain cbo
alter materialized view cmv_mat_view_n6 rebuild;
alter materialized view cmv_mat_view_n6 rebuild;

select * from cmv_mat_view_n6;

3	978.76
3	978.76

but it should be

1	10.30
3	978.76
3	978.76

Issue Links

• blocks: HIVE-27924 Incremental rebuild goes wrong when inserts and deletes overlap between the source tables
• links to: GitHub Pull Request #5053