[
https://issues.apache.org/jira/browse/CALCITE-7904?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Ruben Q L resolved CALCITE-7904.
--------------------------------
Resolution: Fixed
Fixed via
[{{dd11957}}|https://github.com/apache/calcite/commit/dd119578d7492b976aac215a3830968a1ebbaaab]
> 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
> Priority: Major
> Labels: pull-request-available
> 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)
