[
https://issues.apache.org/jira/browse/FLINK-40496?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
ASF GitHub Bot updated FLINK-40496:
-----------------------------------
Labels: pull-request-available (was: )
> CREATE OR ALTER MATERIALIZED TABLE ignores query column position, silently
> producing a schema whose column order disagrees with the query (ALTER ... AS
> is correct)
> -------------------------------------------------------------------------------------------------------------------------------------------------------------------
>
> Key: FLINK-40496
> URL: https://issues.apache.org/jira/browse/FLINK-40496
> Project: Flink
> Issue Type: Bug
> Components: Table SQL / Planner
> Affects Versions: 2.3.0
> Reporter: Arvid Heise
> Assignee: Arvid Heise
> Priority: Major
> Labels: pull-request-available
>
> h2. What happens
> When CREATE OR ALTER evolves a materialized table's query so a column is
> added anywhere but the end of the projection, the stored schema does not
> follow the query's column order: the new column is appended at the end and
> the pre-existing columns keep their positions. ALTER MATERIALIZED TABLE ...
> AS handles the same evolution correctly.
> Given mt = (a, b, c, d) from SELECT a, b, c, d FROM t1:
> {code:sql}
> CREATE OR ALTER MATERIALIZED TABLE mt AS SELECT a, b, c, 42 AS mid, d FROM t1;
> {code}
> Expected stored schema: (a, b, c, mid, d) -- the query projection order.
> Actual stored schema: (a, b, c, d, mid) -- mid appended at the end.
> This passes all DDL-time validation, including
> AlterMaterializedTableChangeOperation.validateChanges(); nothing rejects it.
> The mismatch only surfaces later, when the refresh runs a positional INSERT
> INTO mt <expandedQuery> (no column list): query position 4 (mid) binds to
> stored position 4 (d), and planning fails with a sink type mismatch
> (DynamicSinkUtils: "Incompatible types for sink column ... at position ...").
> h2. Root cause
> The two query-evolution paths use different diff methods, and only one diffs
> column position:
> * ALTER ... AS uses MaterializedTableUtils.buildSchemaTableChanges, which
> appends new columns and calls applyPositionChanges for name-matched columns,
> emitting modifyColumnPosition so existing columns flow around the appended
> one.
> * CREATE OR ALTER uses MaterializedTableUtils.validateAndExtractColumnChanges
> (via SqlCreateOrAlterMaterializedTableConverter.getSchemaTableChanges), which
> appends new columns but never diffs position. A comment there states position
> diffing is skipped on the assumption that reorders are handled on the ALTER
> MT AS path; that path is not reached for CREATE OR ALTER.
> By the time either method runs, the new schema's column order is the query
> projection order (an explicit DDL column list only overrides types/comments
> in place via MergeTableAsUtil.mergeColumns; the identifier-only form permutes
> exactly the query's columns), so the position must be honored.
> h2. Fix
> Make CREATE OR ALTER diff column position like ALTER ... AS: in
> validateAndExtractColumnChanges, emit modifyColumnPosition for name-matched
> columns whose index changed. Position each old column by its rank among the
> columns that survive into the new schema, so retained non-persisted columns
> (which CREATE OR ALTER keeps but the query projection omits) do not skew the
> comparison.
> With the fix, CREATE OR ALTER matches ALTER ... AS: inserting a column before
> the last column stores the correct order, and an insertion that would reorder
> existing physical columns relative to each other is rejected up front by
> validateChanges (the append-only guard) with a clear error, instead of
> silently miscompiling at refresh time.
> h2. Note
> Making the refresh INSERT column-name-qualified would avoid the miscompile,
> but the stored schema order would still disagree with the query, so it
> complements rather than replaces this fix.
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
