Description

I tried to build on CALCITE-1389 work. However, finally I ended up creating a new alternative rule. The main reason is that I wanted to follow the paper more closely and not rely on triggering rules within the MV rewriting to find whether expressions are equivalent. Instead, we extract information from the query plan and the MVs plans using the new metadata providers proposed in CALCITE-1682, and then we use that information to validate and execute the rewriting.

I also implemented new unifying/rewriting logic within the rule, since existing unifying rules for aggregates were assuming that aggregate inputs in the query and the MV needed to be equivalent (same Volcano node). That condition can be relaxed because we verify in the rule, by using the new metadata providers as stated above, that the result for the query is contained within the MV.

I added multiple tests, but any feedback pointing to new tests that could be added to check correctness/coverage is welcome.

Algorithm can trigger multiple rewritings for the same query node. In addition, support for multiple usages of tables in query/MVs is supported.

A few extensions that will follow this issue:

Extend logic to filter relevant MVs for a given query node, so approach is scalable as number of MVs grows.

Produce rewritings using Union operators, e.g., a given query could be partially answered from the MV (year = 2014) and from the query (not(year=2014)). If the MV is stored e.g. in Druid, this rewriting might be beneficial. As with the other rewritings, decision on whether to finally use the rewriting should be cost-based.