Yaoxuan Wu created FLINK-41096:
----------------------------------
Summary: Wrong result when a Table with a non-equi correlated
scalar subquery is used twice in one query
Key: FLINK-41096
URL: https://issues.apache.org/jira/browse/FLINK-41096
Project: Flink
Issue Type: Bug
Components: Table SQL / Planner
Affects Versions: 2.3.0
Reporter: Yaoxuan Wu
{code:python}
from pyflink.table import EnvironmentSettings, TableEnvironment, DataTypes as D
from pyflink.table.expressions import col
t = TableEnvironment.create(EnvironmentSettings.in_batch_mode())
t.create_temporary_view('t', t.from_elements([(5, True), (6, True), (4, False)],
D.ROW([D.FIELD('k', D.INT()), D.FIELD('b', D.BOOLEAN())])))
q = t.sql_query("SELECT o.k, o.b, (SELECT SUM(i.k) FROM t i WHERE i.b <> o.b)
AS s FROM t o")
q.execute().print()
# (5, TRUE, 4), (6, TRUE, 4), (4, FALSE, 11) correct
q.union_all(q.filter(col('k') > 99)).execute().print()
# expected: (5, TRUE, 4), (6, TRUE, 4), (4, FALSE, 11)
# actual: (5, TRUE, NULL), (6, TRUE, NULL), (4, FALSE, NULL)
{code}
The second input is empty, so the result should equal {{q}}. The same with
{{minus_all}}, and in SQL when {{q}} is registered with
{{create_temporary_view}} and referenced twice. Correct with an equality
correlation ({{i.b = o.b}}), with a view created from SQL text ({{CREATE
TEMPORARY VIEW}}), and when {{q}} is used once.
Cause: both uses of {{q}} carry the same correlation variable {{$cor0}}. When
decorrelating the non-equi subquery, the planner builds the "distinct outer
values" input for both copies from the outer input of the second, filtered use:
{code}
HashJoin(LeftOuterJoin, where=[=(b, b0)]) -- first, unfiltered use
:- TableSourceScan(fields=[k, b])
+- HashAggregate(groupBy=[b0], Final_SUM)
+- NestedLoopJoin(InnerJoin, where=[<>(b, b0)])
:- TableSourceScan(fields=[k, b])
+- HashAggregate(groupBy=[b])
+- Calc(select=[b], where=[>(k, 99)]) -- outer values taken from
the other use
{code}
so the first use finds no subquery value for any row. This looks like
CALCITE-7045 (non-unique correlation ids break decorrelation).
Reproduces on 2.0.1, 2.2.1, 2.3.0 and master.
--
This message was sent by Atlassian Jira
(v10.3.26#10030026)
