[CALCITE-5240] Enhance MaterializedViewRule so that it applies to rollup view for queries that contain a predicate on the rollup column

• Type: Improvement
• Status: Open
• Priority: Major
• Resolution: Unresolved
• Labels: pull-request-available
• Assignee: Thomas D'Silva
• Reporter: Thomas D'Silva
• Created: 17/Aug/22 19:58
• Updated: 03/Dec/22 03:58

Description

MaterializedViewRule is not applied when a view does not have a view predicate but the query contains a predicate. For eg. for the following materialized view 

SELECT   eventid,  floor(ts to minute), count(*) as cnt
FROM     events
GROUP BY eventid, floor(ts TO minute)

If we have the following query the view is not used. 

SELECT   floor(ts to minute), count(*)
FROM     events
WHERE    ts > timestamp'2018-01-01 00:02:30' AND ts <= timestamp'2018-01-01 00:05:30'
GROUP BY eventid, floor(ts TO minute)

If MaterializedViewRule is modified to automatically add the predicate  ts > timestamp'2018-01-01 00:03:00' AND ts < timestamp'2018-01-01 00:05:00' to the view then the following plan can be generated that uses the union rewriting feature to query both the table and the view efficiently.

EnumerableAggregate(group=[{0, 1}], EXPR$2=[$SUM0($2)])
  EnumerableUnion(all=[true])
    EnumerableAggregate(group=[{0, 1}], EXPR$2=[COUNT()])
      EnumerableCalc(expr#0..1=[{inputs}], expr#2=[FLAG(MINUTE)], expr#3=[FLOOR($t1, $t2)], expr#4=[Sarg[(2018-01-01 00:02:30..2018-01-01 00:03:00), [2018-01-01 00:05:00..2018-01-01 00:05:30]]], expr#5=[SEARCH($t1, $t4)], eventid=[$t0], $f1=[$t3], $condition=[$t5])
        EnumerableTableScan(table=[[hr, events]])
    EnumerableCalc(expr#0..2=[{inputs}], expr#3=[Sarg[[2018-01-01 00:03:00..2018-01-01 00:05:00)]], expr#4=[SEARCH($t1, $t3)], proj#0..2=[{exprs}], $condition=[$t4])
      EnumerableTableScan(table=[[hr, MV0]])

The mailing list has a discussion related to this
https://lists.apache.org/thread/c7v85fccpbobz44y1o4z7hklomrcl299

Issue Links

• is related to: CALCITE-5155 Custom time frames
• relates to: CALCITE-5364 In materialized view substitution, use custom time frames
• links to: GitHub Pull Request #2876