[jira] [Created] (FLINK-40496) CREATE OR ALTER MATERIALIZED TABLE ignores query column position, silently producing a schema whose column order disagrees with the query (ALTER ... AS is correct)

["Arvid Heise (Jira)" <ji...@apache.org>]

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)