Ruben Q L created CALCITE-7904:
----------------------------------
Summary: SingleValuesOptimizationRules give wrong results for ASOF
and SEMI joins and fail for LEFT ASOF join
Key: CALCITE-7904
URL: https://issues.apache.org/jira/browse/CALCITE-7904
Project: Calcite
Issue Type: Bug
Components: core
Reporter: Ruben Q L
Assignee: Ruben Q L
Fix For: 1.43.0
The rules in SingleValuesOptimizationRules (JOIN_LEFT_INSTANCE,
JOIN_RIGHT_INSTANCE, JOIN_LEFT_PROJECT_INSTANCE, JOIN_RIGHT_PROJECT_INSTANCE)
remove a Join when one of its inputs is a single-row Values. They replace it
with a Filter and Project on the other input. isJoinTransformable does not
handle three cases correctly:
1. ASOF JOIN (single-row Values on either side). The rewrite uses only the ON
condition and ignores the MATCH_CONDITION. Instead of the single closest match,
it returns every row that matches on the key.
Example:
{code:sql}
SELECT *
FROM (VALUES (1, 3), (1, 5), (2, 5)) AS t1(k, t)
ASOF JOIN (VALUES (1, 4)) AS t2(k, t)
MATCH_CONDITION t2.t < t1.t
ON t1.k = t2.k;
-- expected: (1, 5, 1, 4)
-- actual: (1, 3, 1, 4), (1, 5, 1, 4)
{code}
2. LEFT ASOF JOIN (single-row Values on either side). LEFT_ASOF counts as an
outer join, so no filter is applied. getRexTransformer has no LEFT_ASOF case,
so the right-side columns are not NULL-padded and keep their NOT NULL type. The
rewritten expression's row type then differs from the join's, whose right-side
columns are nullable, and planning fails.
Example:
{code:sql}
SELECT *
FROM (VALUES (1, 3), (1, 5), (2, 5)) AS t1(k, t)
LEFT ASOF JOIN (VALUES (1, 4)) AS t2(k, t)
MATCH_CONDITION t2.t < t1.t
ON t1.k = t2.k;
-- expected: (1, 3, null, null), (1, 5, 1, 4), (2, 5, null, null)
-- actual: java.lang.IllegalArgumentException: Type mismatch:
-- rel rowtype: RecordType(INTEGER NOT NULL K, INTEGER NOT NULL T, INTEGER NOT
NULL $f2, INTEGER NOT NULL $f3) NOT NULL
-- equiv rowtype: RecordType(INTEGER NOT NULL K, INTEGER NOT NULL T, INTEGER
K0, INTEGER T0) NOT NULL
-- at
org.apache.calcite.plan.volcano.VolcanoPlanner.register(VolcanoPlanner.java:594)
{code}
Note: even if we fixed the nullability issue, then the LEFT ASOF would still
fall into the "wrong result" scenario of the ASOF case above (due to
MATCH_CONDITION being ignore).
3. SEMI JOIN with the single-row Values as the left input. The rewrite emits
the Values row once for each matching row on the right, but a semi-join should
emit it at most once. Single-row Values on the right of a SEMI join is handled
correctly. For example, with the hr schema and
topDownGeneralDecorrelationEnabled=true (which produces a SEMI join for EXISTS).
Example:
{code:sql}
SELECT * FROM (VALUES (10)) AS t(x)
WHERE EXISTS (SELECT 1 FROM "hr"."emps" AS e WHERE e."deptno" = t.x);
-- expected: 10
-- actual: 10, 10, 10 (one row per employee in department 10)
{code}
The fix is to make isJoinTransformable exclude ASOF and LEFT ASOF joins on both
sides, and SEMI joins when the Values is the left input.
--
This message was sent by Atlassian Jira
(v10.3.26#10030026)
