Arvid Heise created FLINK-40496:
-----------------------------------
Summary: 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
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)
