Details
-
Bug
-
Status: Closed
-
Major
-
Resolution: Fixed
-
None
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
Attachments
Issue Links
- blocks
-
-
- Closed
-
- links to