[
https://issues.apache.org/jira/browse/FLINK-40496?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Arvid Heise updated FLINK-40496:
--------------------------------
Description:
h2. What happens
CREATE OR ALTER derives a materialized table's column changes differently from
ALTER ... AS and does not surface a query-driven column reorder to the
append-only validation. Reordering existing columns via CREATE OR ALTER is
therefore handled inconsistently:
* when the query text is unchanged (e.g. only a bare column list reorders the
columns), the reorder is silently applied - the stored column order is
rewritten with no error;
* when the query text changes, the same reorder is rejected.
The silent path leaves the stored schema in an order that no longer matches the
query. The refresh runs a positional INSERT INTO <table> <expandedQuery> with
no explicit column list, so the mismatch then miscompiles with a sink type
error, e.g. "Incompatible types for sink column ... at position ...".
Example - existing materialized table users_shops = (user_id, shop_id, ds,
order_cnt):
{code:sql}
CREATE OR ALTER MATERIALIZED TABLE users_shops (shop_id, user_id, ds, order_cnt)
AS SELECT user_id, shop_id, ds, COUNT(order_id) AS order_cnt FROM ...
{code}
The bare list reorders user_id/shop_id while the query is unchanged, so no
ModifyDefinitionQuery is produced and the append-only check is skipped: the
columns are silently reordered to (shop_id, user_id, ds, order_cnt).
h2. Fix
Derive the CREATE OR ALTER column diff from the query the same way ALTER ... AS
does, and apply the append-only column rules to every query-carrying alter
(CREATE OR ALTER and ALTER ... AS) regardless of whether the query text
changed. Reordering or retyping existing columns is then rejected consistently
across both statements.
was:
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.
> CREATE OR ALTER MATERIALIZED TABLE applies column-change rules inconsistently
> with ALTER ... AS and can silently reorder existing columns
> -----------------------------------------------------------------------------------------------------------------------------------------
>
> 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
> CREATE OR ALTER derives a materialized table's column changes differently
> from ALTER ... AS and does not surface a query-driven column reorder to the
> append-only validation. Reordering existing columns via CREATE OR ALTER is
> therefore handled inconsistently:
> * when the query text is unchanged (e.g. only a bare column list reorders the
> columns), the reorder is silently applied - the stored column order is
> rewritten with no error;
> * when the query text changes, the same reorder is rejected.
> The silent path leaves the stored schema in an order that no longer matches
> the query. The refresh runs a positional INSERT INTO <table> <expandedQuery>
> with no explicit column list, so the mismatch then miscompiles with a sink
> type error, e.g. "Incompatible types for sink column ... at position ...".
> Example - existing materialized table users_shops = (user_id, shop_id, ds,
> order_cnt):
> {code:sql}
> CREATE OR ALTER MATERIALIZED TABLE users_shops (shop_id, user_id, ds,
> order_cnt)
> AS SELECT user_id, shop_id, ds, COUNT(order_id) AS order_cnt FROM ...
> {code}
> The bare list reorders user_id/shop_id while the query is unchanged, so no
> ModifyDefinitionQuery is produced and the append-only check is skipped: the
> columns are silently reordered to (shop_id, user_id, ds, order_cnt).
> h2. Fix
> Derive the CREATE OR ALTER column diff from the query the same way ALTER ...
> AS does, and apply the append-only column rules to every query-carrying alter
> (CREATE OR ALTER and ALTER ... AS) regardless of whether the query text
> changed. Reordering or retyping existing columns is then rejected
> consistently across both statements.
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
