[jira] [Created] (CALCITE-689) Add filter, to allow a materialized view to ignore invalid data

["Julian Hyde (JIRA)" <ji...@apache.org>]

Julian Hyde created CALCITE-689:
-----------------------------------

             Summary: Add filter, to allow a materialized view to ignore invalid data
                 Key: CALCITE-689
                 URL: https://issues.apache.org/jira/browse/CALCITE-689
             Project: Calcite
          Issue Type: Bug
            Reporter: Julian Hyde
            Assignee: Julian Hyde


A materialized view is a table that contains the result of executing a particular query. But what if the table contains extra rows? 

We propose to add a filter -- a RexNode condition, or a SQL WHERE clause -- that Calcite would apply whenever the materialized view was used.

One use case this would allow: invalidating parts of an MV, as their underlying data is modified, without invalidating the whole MV. Suppose that the materialized view covers sales to states CA, OR, WA in years 2013, 2014:

{code}sql: SELECT state, year, SUM(units) FROM sales WHERE state IN ('CA', 'OR', 'WA') AND year IN (2013, 2014) GROUP BY state, year{code}

Now suppose that one record in (CA, 2013) is modified. We don't want to re-create the MV. We can change the definition of the MV as follows:

{code}sql: SELECT state, year, SUM(units) FROM sales WHERE state IN ('OR', 'WA') AND year IN (2013, 2014) GROUP BY state, year
filter: state != 'CA'{code}

We have narrowed the set of cells that the MV claims to provide, and we have provided a filter to remove extra data. The filter makes it clear that the MV contains extra rows; some uses might decide they don't need the filter.



--
This message was sent by Atlassian JIRA
(v6.3.4#6332)