yujun777 opened a new issue, #68841:
URL: https://github.com/apache/doris/issues/68841
### 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](https://github.com/apache/doris/pull/68646#discussion_r4219437313)).
2. A definition stored before #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](https://github.com/apache/doris/pull/68646#discussion_r4214302765)).
### 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](https://github.com/apache/doris/pull/68646#discussion_r4143552256),
[#4225713066](https://github.com/apache/doris/pull/68646#discussion_r4225713066)).
- A predicate an enclosing `EXISTS` ignores: a scalar subquery's filter, and
a projection an `EXISTS` never compares
([#4214302759](https://github.com/apache/doris/pull/68646#discussion_r4214302759),
[#4225713078](https://github.com/apache/doris/pull/68646#discussion_r4225713078)).
- `ORDER BY` resolving the output alias before the column of the same name
([#4214302771](https://github.com/apache/doris/pull/68646#discussion_r4214302771)).
- An explicitly qualified reference to another table's column of the same
name
([#4214302779](https://github.com/apache/doris/pull/68646#discussion_r4214302779)).
### Directions (none chosen here)
- **Store the binding with the view**: a fingerprint of what each name
resolved to when the view was built (persisted, written at creation and each
refresh), compared against the plan the change left.
- **Judge before the change is published**, where the pre-change schema is
still the one a planner sees. That is the same place issue #68767 is about, and
a fix there would have the judgement run against both schemas.
- **Qualify every clause of a stored definition**, so that a name which
would silently rebind makes the re-analysis fail instead (the `QUALIFY` fix in
#68646 generalized). This closes the missed side only; the needless
invalidations are the other half.
### 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.
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: [email protected]
For queries about this service, please contact Infrastructure at:
[email protected]
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
