[CALCITE-2179] General improvements for materialized view rewriting rule

• Type: Improvement
• Status: Closed
• Priority: Major
• Resolution: Fixed
• Fix Version/s: 1.16.0
• Component/s: core
• Assignee: Jesús Camacho Rodríguez
• Reporter: Jesús Camacho Rodríguez
• Created: 14/Feb/18 23:04
• Updated: 27/Feb/24 22:24
• Resolved: 21/Feb/18 19:16

Description

This issue is for extending AbstractMaterializedViewRule rule:

• Support for rolling up date nodes. For instance, rewrite in the following case:

  Materialization:
  select "empid", floor(cast('1997-01-20' as timestamp) to month), count(*) + 1 as c, sum("empid") as s
  from "emps" group by "empid", floor(cast('1997-01-20' as timestamp) to month);
  Query:
  select floor(cast('1997-01-20' as timestamp) to year), sum("empid") as s
  from "emps" group by floor(cast('1997-01-20' as timestamp) to year);
  

• Add flag to enable/disable fast bail out for joins. By default it is true, and thus, we were only creating the rewriting in the minimal subtree of plan operators. For instance:

  View: (A JOIN B) JOIN C
  Query: (((A JOIN B) JOIN D) JOIN C) JOIN E
  

  We produce it at:

  ((A JOIN B) JOIN D) JOIN C
  

  But not at:

  (((A JOIN B) JOIN D) JOIN C) JOIN E
  

  This is important when the rule is used with the Volcano planner together with other rules, e.g. join reordering, as it prevents that the search space grows unnecessarily. However, if we use the rewriting rule in isolation, fast bail out can lead to missing rewriting opportunities (e.g. for bushy join trees).

• Possibility to provide a HepProgram to optimize query branch in union rewritings. Note that when we produce a partial rewriting with a Union, the branch that will execute the (partial) query can be fully rewritten so we can add the compensation predicate. (We cannot do the same for views because the expression might not be computable if the needed subexpressions are not available in the view output). If we use Volcano with a determined set of rules, this might not be needed, hence providing this program is optional.
• Multiple small fixes discovered while testing.

Issue Links

• is related to: CALCITE-5337 UnionPullUpConstantsRule produces an invalid plan when pulling up constants for nullable fields
• is related to: HIVE-18770 Additional tests and fixes for materialized view rewriting
• relates to: HIVE-26643 HiveUnionPullUpConstantsRule produces an invalid plan when pulling up constants for nullable fields