[
https://issues.apache.org/jira/browse/FLINK-41507?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=18146351#comment-18146351
]
Stefan Wang commented on FLINK-41507:
-------------------------------------
I've opened https://github.com/apache/flink/pull/29977 with a fix. Could a
committer assign this ticket to me?
> Streaming MAX / MIN above a LEFT JOIN with append-only inputs are planned
> without retraction and keep retracted values
> ----------------------------------------------------------------------------------------------------------------------
>
> Key: FLINK-41507
> URL: https://issues.apache.org/jira/browse/FLINK-41507
> Project: Flink
> Issue Type: Bug
> Components: Table SQL / Planner
> Reporter: Yaoxuan Wu
> Priority: Major
> Labels: correctness, pull-request-available
>
> {code:python}
> from collections import Counter
> from pyflink.common import RowKind
> from pyflink.table import TableEnvironment, EnvironmentSettings, DataTypes
> INT = lambda n: DataTypes.ROW([DataTypes.FIELD(n, DataTypes.INT())])
> def final(tbl):
> c = Counter()
> for r in tbl.execute().collect():
> c[tuple(r)] += 1 if r.get_row_kind() in (RowKind.INSERT,
> RowKind.UPDATE_AFTER) else -1
> return sorted(k for k, v in c.items() for _ in range(v))
> for mode in ('batch', 'streaming'):
> s = EnvironmentSettings.in_batch_mode() if mode == 'batch' else
> EnvironmentSettings.in_streaming_mode()
> t_env = TableEnvironment.create(s)
> t_env.get_config().set('parallelism.default', '1')
> t_env.create_temporary_view('a', t_env.from_elements([(3,), (-4,), (7,)],
> INT('x')))
> t_env.create_temporary_view('b', t_env.from_elements([(7,), (3,)],
> INT('y')))
> # rows of a without a match in b: only -4
> q = "SELECT MAX(x), COUNT(*), SUM(x) FROM (SELECT x FROM a LEFT JOIN b ON
> x = y WHERE y IS NULL)"
> print(mode, final(t_env.sql_query(q)))
> # batch [(-4, 1, -4)]
> # streaming [(3, 1, -4)]
> {code}
> Same on 2.0.1, 2.2.1, 2.3.0 and a 2.4-SNAPSHOT build. The left join first
> emits the null-padded rows {{(3, NULL)}} and {{(7, NULL)}}, then retracts
> them when the matching {{y}} arrives. {{WHERE y IS NULL}} passes both the
> inserts and the retractions. COUNT and SUM in the same {{GroupAggregate}}
> apply the retractions, but MAX keeps a retracted value. MIN has the same
> problem: with {{c = (-9), (-4)}} and {{d = (-9)}}, {{SELECT MIN(x) ... FROM c
> LEFT JOIN d ... WHERE y IS NULL}} returned -9 on 2.3.0. That run depends on
> arrival order: on 2.0 and the 2.4 snapshot it returned -4 in the same run.
> {{LEFT JOIN ... WHERE y IS NULL}} is the usual way to write an anti join. The
> Table API {{x.in_(sub).is_not_true}} is planned as the same left join, which
> is where we first saw this.
> Cause (planner, master):
> - {{AggregateUtil.deriveAggCallNeedRetractions}} plans {{MAX}} without
> retraction ({{MaxWithRetract}} -> {{Max}}) when
> {{getRelModifiedMonotonicity}} reports its output field as INCREASING, and
> {{MIN}} when it reports DECREASING.
> - {{FlinkRelMdModifiedMonotonicity.getRelModifiedMonotonicity(Join)}} returns
> {{null}} ("contains delete") only for ANTI joins and for inputs that contain
> deletes. For other join types with append-only inputs and constant join keys,
> it returns the inputs' field monotonicities (all CONSTANT). An outer join
> emits deletes even when both inputs are insert-only: it retracts null-padded
> rows. So LEFT / RIGHT / FULL joins should report {{null}} here.
> - The Calc ({{IS NULL(y)}}) keeps CONSTANT, and on a CONSTANT input the
> aggregate reports MAX as INCREASING and MIN as DECREASING, so both lose their
> retraction. The changelog modes in the plan are right ({{Join ...
> changelogMode=[I,UB,UA,D]}}, {{GroupAggregate ... changelogMode=[I,UA,D]}});
> only the per-call retraction flags are wrong.
--
This message was sent by Atlassian Jira
(v10.3.26#10030026)