[
https://issues.apache.org/jira/browse/CALCITE-6639?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17892038#comment-17892038
]
Mihai Budiu commented on CALCITE-6639:
--------------------------------------
The assertion failure comes from the fact that inferPredicates can bring
additional predicates from the relations joined into the join itself. For this
test case it brings some comparisons with constants, for example.
The PR I submitted does what you suggest: it inhibits this optimization for
ASOF joins.
Other databases, like DuckDB do not impose (at least in their documentation)
such strict constraints, but I haven't checked empirically whether they can
cope with other kinds of predicates for ASOF JOINs.
> Optimization that pulls up predicates causes ASOF JOIN validation failures
> --------------------------------------------------------------------------
>
> Key: CALCITE-6639
> URL: https://issues.apache.org/jira/browse/CALCITE-6639
> Project: Calcite
> Issue Type: Bug
> Components: core
> Affects Versions: 1.38.0
> Reporter: Mihai Budiu
> Assignee: Mihai Budiu
> Priority: Minor
> Labels: pull-request-available
>
> The following test added to the RelOptRulesTest causes an assertion failure:
> {code:java}
> @Test void testAsofOpt() {
> final String sql = "SELECT *\n" +
> "FROM (VALUES (NULL, 0), (1, NULL), (1, 0), (1, 1), (1, 2), (1, 3),
> (1, 4), (2, 3), (3, 4)) AS t1(k, t)\n" +
> "ASOF JOIN (VALUES (1, NULL), (1, 2), (1, 3), (2, 10), (2, 0)) AS
> t2(k, t)\n" +
> "MATCH_CONDITION t2.t < t1.t\n" +
> "ON t1.k = t2.k\n";
> sql(sql).withRule(CoreRules.PROJECT_REDUCE_EXPRESSIONS)
> .checkUnchanged();
> }
> {code}
> This test is taken straigt from asof.iq.
> This test fails with the following assertion failure:
> {code}
> java.lang.AssertionError
> at
> org.apache.calcite.rel.metadata.RelMdPredicates$JoinConditionBasedPredicateInference.inferPredicates(RelMdPredicates.java:850)
> at
> org.apache.calcite.rel.metadata.RelMdPredicates.getPredicates(RelMdPredicates.java:346)
> at
> org.apache.calcite.rel.metadata.janino.GeneratedMetadata_PredicatesHandler.getPredicates_$(Unknown
> Source)
> at
> org.apache.calcite.rel.metadata.janino.GeneratedMetadata_PredicatesHandler.getPredicates(Unknown
> Source)
> at
> org.apache.calcite.rel.metadata.RelMetadataQuery.getPulledUpPredicates(RelMetadataQuery.java:887)
> at
> org.apache.calcite.rel.rules.ReduceExpressionsRule$ProjectReduceExpressionsRule.onMatch(ReduceExpressionsRule.java:310)
> {code}
> Diagnosis: the definition of ASOF join implementation, inspired by the
> Snowflake variant of the operation, imposes some strong "syntactical"
> constraints on the predicate structure. In particular, each predicate in the
> equi-join part must compare expressions from the two joined relations.
> These constraints ensure that the ASOF join can be efficiently implemented.
> However, this definition inhibits some optimizations, such as pull-up
> predicates, which may rewrite predicates into a shape that does not satisfy
> the above rules.
> It is not clear what the right course of action is.
> One solution would be to relax the requirements on the ASOF join and to
> validate its shape only after optimizations. But then it is not clear that
> the resulting program would always be efficiently implementable - the
> original goal of ASOF.
> Another solution would be to inhibit optimizations from optimizing such joins.
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
