[
https://issues.apache.org/jira/browse/CALCITE-6924?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17939848#comment-17939848
]
xiong duan commented on CALCITE-6924:
-------------------------------------
Could you briefly describe how to solve this problem? Adding the SEARCH
Operator to the Jdbc Condition list is a straightforward approach. Then, how
can we prevent the condition of the Filter from being passed to the JdbcJoin?
> FilterIntoJoinRule make JdbcJoin contains expressions that it does not support
> ------------------------------------------------------------------------------
>
> Key: CALCITE-6924
> URL: https://issues.apache.org/jira/browse/CALCITE-6924
> Project: Calcite
> Issue Type: Bug
> Reporter: xiong duan
> Priority: Major
>
> The unit test in JdbcTest:
> {code:java}
> @Test void testJdbcJoin() {
> CalciteAssert.that()
> .with(CalciteAssert.Config.JDBC_FOODMART)
> .query("select *\n"
> + "from \"foodmart\".\"store\" as p1\n"
> + "inner join \"foodmart\".\"store\" as p2 on true\n"
> + "where p1.\"store_id\" between 10 and 20 or p1.\"grocery_sqft\" =
> p2.\"grocery_sqft\"")
> .explainContains("PLAN=" +
> "JdbcToEnumerableConverter\n" +
> " JdbcJoin(condition=[OR(SEARCH($0, Sarg[[10..20]]), =($16,
> $40))], joinType=[inner])\n" +
> " JdbcTableScan(table=[[foodmart, store]])\n" +
> " JdbcTableScan(table=[[foodmart, store]])\n\n");
> }
> @Test void testJdbcJoin2() {
> CalciteAssert.that()
> .with(CalciteAssert.Config.JDBC_FOODMART)
> .query("select *\n"
> + "from \"foodmart\".\"store\" as p1\n"
> + "inner join \"foodmart\".\"store\" as p2\n"
> + "on p1.\"store_id\" between 10 and 20 or p1.\"grocery_sqft\" =
> p2.\"grocery_sqft\"")
> .explainContains("PLAN=" +
> "EnumerableNestedLoopJoin(condition=[OR(SEARCH($0, Sarg[[10..20]]),
> =($16, $40))], joinType=[inner])\n" +
> " JdbcToEnumerableConverter\n" +
> " JdbcTableScan(table=[[foodmart, store]])\n" +
> " JdbcToEnumerableConverter\n" +
> " JdbcTableScan(table=[[foodmart, store]])\n\n");
> } {code}
> The testJdbcJoin test generate :
> {code:java}
> JdbcJoin(condition=[OR(SEARCH($0, Sarg[[10..20]]), =($16, $40))],
> joinType=[inner]){code}
> But the JdbcJoinRule#canJoinOnCondition doesn't include the SEARCH operator,
> so the condition can't push down to JdbcJoin like testJdbcJoin2.
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
