Skip to content

[Enhancement] Judge a base table column change by the binding an MV query was built with, not by the names it reads #68841

Open

Description

@yujun777

Description

#68646 judges whether a base table column change reaches a materialized view by re-analysing the view's stored query and asking whether it reads a column of that name. That is a name-level reading of the plan the change left, and it is wrong in both directions: it misses a binding that moved into a name the view now answers for itself, and it invalidates a view for a name whose binding could not have moved. Every counterexample found in review is one of those two, and fixing one side has moved the reading to the other (measured: widening the alias branch so the first case below is caught puts the first needless invalidation back).

Both lists below are the same question asked of the plan after the change -- which column does this name read, before and after -- which the analysed plan does not answer on its own.

Missed invalidations (the view stays NORMAL holding rows the query no longer returns)

  1. A name that moved into an alias over another table's column:
    EXISTS (SELECT b.x AS flag, COUNT(*) n FROM changed_t c JOIN other_t b ON b.id = c.id WHERE c.id = o.id GROUP BY b.x, flag HAVING flag = 1)
    After a light DROP changed_t.flag the query returns a row the view does not hold, while the view stays NORMAL and in sync (measured: #4219437313).
  2. A definition stored before [improvement](mtmv) Judge a base table column change by the MV query it reaches #68646 keeps its QUALIFY references unqualified, so the same move happens there while the definition names no column the check can hold the change against (measured: #4214302765).

Needless invalidations (a whole rebuild and a dropped rewrite snapshot for a change nothing depends on)

  • An unused CTE producer read as a dependency (#4143552256, #4225713066).
  • A predicate an enclosing EXISTS ignores: a scalar subquery's filter, and a projection an EXISTS never compares (#4214302759, #4225713078).
  • ORDER BY resolving the output alias before the column of the same name (#4214302771).
  • An explicitly qualified reference to another table's column of the same name (#4214302779).

Directions (none chosen here)

Acceptance

Each case above pinned as a regression case in mtmv_p0/test_drop_unreferenced_column_mtmv, and each failing when the reading it covers is reverted.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment

Metadata

Metadata

Assignees

No one assigned

    Labels

    No labels
    No labels

    Type

    No type

    Projects

    No projects

      Milestone

      No milestone

      Relationships

      None yet

      Development

      No branches or pull requests

      Issue actions