[jira] [Comment Edited] (CALCITE-6216) The JDBC adapter is unable to push down queries when there is a join involved in the query.

["xjlem (Jira)" <ji...@apache.org>]

    [ https://issues.apache.org/jira/browse/CALCITE-6216?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17809721#comment-17809721 ] 

xjlem edited comment on CALCITE-6216 at 1/23/24 3:40 AM:
---------------------------------------------------------

I'm sorry, I may have misled you. Indeed, joins across data sources should not be pushed down. The core issue with the Jira mentioned above is that the following logic:
{code:java}
LogicalAggregate(group=[\{0}], pv=[COUNT($1)])
   LogicalFilter(condition=[=($0, 1)])
      LogicalProject(sku_id=[$2], pv=[$3])
         JdbcTableScan(table=[[db2, sku_flow]]) {code}
will be transformed into:
{code:java}
EnumerableAggregate(group=[\{2}], pv=[COUNT($3)])
   EnumerableCalc(expr#0..4=[\{inputs}], expr#5=[1], expr#6=[=($t2, $t5)], proj#0..4=[\{exprs}], $condition=[$t6])
     JdbcToEnumerableConverter
        JdbcTableScan(table=[[db2, sku_flow]]){code}
Operations like filter and aggregate cannot be pushed down to the data source for execution. Calcite will execute a 
{code:java}
select * from db2.sku_flow{code}
{{query}} in the data source, and operations like filter and aggregate are executed in memory.


was (Author: xjlem):
I'm sorry, I may have misled you. Indeed, joins across data sources should not be pushed down. The core issue with the Jira mentioned above is that the following logic:
LogicalAggregate(group=[\{0}], pv=[COUNT($1)])
      LogicalFilter(condition=[=($0, 1)])
        LogicalProject(sku_id=[$2], pv=[$3])
          JdbcTableScan(table=[[db2, sku_flow]]) 
will be transformed into:
EnumerableAggregate(group=[\{2}], pv=[COUNT($3)])
      EnumerableCalc(expr#0..4=[\{inputs}], expr#5=[1], expr#6=[=($t2, $t5)], proj#0..4=[\{exprs}], $condition=[$t6])
        JdbcToEnumerableConverter
          JdbcTableScan(table=[[db2, sku_flow]])
Operations like filter and aggregate cannot be pushed down to the data source for execution. Calcite will execute a 
select * from db2.sku_flow
{{ query}} in the data source, and operations like filter and aggregate are executed in memory.

> The JDBC adapter is unable to push down queries when there is a join involved in the query.
> -------------------------------------------------------------------------------------------
>
>                 Key: CALCITE-6216
>                 URL: https://issues.apache.org/jira/browse/CALCITE-6216
>             Project: Calcite
>          Issue Type: Improvement
>          Components: jdbc-adapter
>            Reporter: xjlem
>            Priority: Major
>
> I want to know why the description of JdbcToEnumerableConverterRule in the JDBC rule includes "JdbcConvention out" information while others do not, which leads to different data sources sharing the same JDBC rule.
> for example：
> config:
> {code:java}
> {
>   "defaultSchema": "db1",
>   "schemas": [
>     {
>       "factory": "org.apache.calcite.adapter.jdbc.JdbcSchema$Factory",
>       "name": "db1",
>       "operand": {
>         "jdbcDriver": "com.mysql.jdbc.Driver",
>         "jdbcPassword": "",
>         "jdbcUrl": "",
>         "jdbcUser": ""
>       },
>       "type": "custom"
>     },
>     {
>       "factory": "org.apache.calcite.adapter.jdbc.JdbcSchema$Factory",
>       "name": "db2",
>       "operand": {
>         "jdbcDriver": "com.mysql.jdbc.Driver",
>         "jdbcPassword": "",
>         "jdbcUrl": "",
>         "jdbcUser": ""
>       },
>       "type": "custom"
>     }
>   ],
>   "version": "1.0"
> } {code}
> sql：
> {code:java}
> SELECT pv / sales, pv, sales
> FROM (
>     SELECT sku_id, COUNT(*) AS sales
>     FROM db1.sku_sales
>     WHERE SKU_ID = 1
>     GROUP BY sku_id
> ) sku_sales
>     FULL JOIN (
>         SELECT sku_id, COUNT(pv) AS pv
>         FROM db2.sku_flow
>         WHERE SKU_ID = 1
>         GROUP BY sku_id
>     ) sku_flow
>     ON sku_sales.sku_id = sku_flow.sku_id{code}
> it will convert to logic relNode:
> {code:java}
> LogicalProject(EXPR$0=[/($3, $1)], pv=[$3], sales=[$1])
>   LogicalJoin(condition=[=($0, $2)], joinType=[full])
>     LogicalAggregate(group=[{0}], sales=[COUNT()])
>       LogicalFilter(condition=[=($0, 1)])
>         LogicalProject(sku_id=[$2])
>           JdbcTableScan(table=[[db1, sku_sales]])
>     LogicalAggregate(group=[{0}], pv=[COUNT($1)])
>       LogicalFilter(condition=[=($0, 1)])
>         LogicalProject(sku_id=[$2], pv=[$3])
>           JdbcTableScan(table=[[db2, sku_flow]]) {code}
> optimize:
> When registering the rule, the rule for the DB2 convention will not be registered because the jdbcRule has the same description as the rule for the DB1 convention, causing the addRule method to return false. 
> org.apache.calcite.adapter.jdbc.JdbcConvention#register
> {code:java}
> @Override public void register(RelOptPlanner planner) {
>   for (RelOptRule rule : JdbcRules.rules(this)) {
>     planner.addRule(rule);
>   }
>   planner.addRule(FilterSetOpTransposeRule.INSTANCE);
>   planner.addRule(ProjectRemoveRule.INSTANCE);
> } {code}
> after optimize:
> {code:java}
> EnumerableCalc(expr#0..3=[{inputs}], expr#4=[/($t3, $t1)], EXPR$0=[$t4], pv=[$t3], sales=[$t1])
>   EnumerableJoin(condition=[=($0, $2)], joinType=[full])
>     JdbcToEnumerableConverter
>       JdbcAggregate(group=[{2}], sales=[COUNT()])
>         JdbcFilter(condition=[=($2, 1)])
>           JdbcTableScan(table=[[db1, sku_sales]])
>     EnumerableAggregate(group=[{2}], pv=[COUNT($3)])
>       EnumerableCalc(expr#0..4=[{inputs}], expr#5=[1], expr#6=[=($t2, $t5)], proj#0..4=[{exprs}], $condition=[$t6])
>         JdbcToEnumerableConverter
>           JdbcTableScan(table=[[db2, sku_flow]])
>  {code}
> As a result, we can see that the push down for DB2 fails.
>    



--
This message was sent by Atlassian Jira
(v8.20.10#820010)