This is an automated email from the ASF dual-hosted git repository.
strongduanmu pushed a commit to branch master
in repository https://gitbox.apache.org/repos/asf/shardingsphere.git
The following commit(s) were added to refs/heads/master by this push:
new bd5bce8e592 Fix correlated project pushdown for SQL federation IN
subqueries (#38405)
bd5bce8e592 is described below
commit bd5bce8e5920e8ed393aa25113d9d036e9b18377
Author: ym0506 <[email protected]>
AuthorDate: Tue Jun 9 14:55:00 2026 +0900
Fix correlated project pushdown for SQL federation IN subqueries (#38405)
* sql-federation: skip correlated project pushdown
* test: add sql federation e2e regression case
* test: narrow sql federation e2e regression case
---
.../transformation/PushProjectIntoScanRule.java | 21 ++++++++++++++++++++-
.../compiler/it/SQLStatementCompilerIT.java | 11 +++++++++++
.../transformation/PushProjectIntoScanRuleTest.java | 11 +++++++++++
...l-select-sub-query-for-db-tbl-sql-federation.xml | 5 +++++
4 files changed, 47 insertions(+), 1 deletion(-)
diff --git
a/kernel/sql-federation/compiler/src/main/java/org/apache/shardingsphere/sqlfederation/compiler/planner/rule/transformation/PushProjectIntoScanRule.java
b/kernel/sql-federation/compiler/src/main/java/org/apache/shardingsphere/sqlfederation/compiler/planner/rule/transformation/PushProjectIntoScanRule.java
index 614c4fb20ea..482524ba1e0 100644
---
a/kernel/sql-federation/compiler/src/main/java/org/apache/shardingsphere/sqlfederation/compiler/planner/rule/transformation/PushProjectIntoScanRule.java
+++
b/kernel/sql-federation/compiler/src/main/java/org/apache/shardingsphere/sqlfederation/compiler/planner/rule/transformation/PushProjectIntoScanRule.java
@@ -23,6 +23,8 @@ import org.apache.calcite.plan.RelRule;
import org.apache.calcite.rel.logical.LogicalProject;
import org.apache.calcite.rel.rules.TransformationRule;
import org.apache.calcite.rex.RexCall;
+import org.apache.calcite.rex.RexCorrelVariable;
+import org.apache.calcite.rex.RexFieldAccess;
import org.apache.calcite.rex.RexNode;
import org.apache.calcite.rex.RexSubQuery;
import
org.apache.shardingsphere.sqlfederation.compiler.rel.operator.logical.LogicalScan;
@@ -55,13 +57,30 @@ public final class PushProjectIntoScanRule extends
RelRule<PushProjectIntoScanRu
}
LogicalProject logicalProject = call.rel(0);
for (RexNode each : logicalProject.getProjects()) {
- if (each instanceof RexSubQuery || containsCastFunction(each)) {
+ if (each instanceof RexSubQuery || containsCorrelate(each) ||
containsCastFunction(each)) {
return false;
}
}
return true;
}
+ private boolean containsCorrelate(final RexNode rexNode) {
+ if (rexNode instanceof RexCorrelVariable) {
+ return true;
+ }
+ if (rexNode instanceof RexFieldAccess) {
+ return containsCorrelate(((RexFieldAccess)
rexNode).getReferenceExpr());
+ }
+ if (rexNode instanceof RexCall) {
+ for (RexNode each : ((RexCall) rexNode).getOperands()) {
+ if (containsCorrelate(each)) {
+ return true;
+ }
+ }
+ }
+ return false;
+ }
+
private boolean containsCastFunction(final RexNode rexNode) {
return rexNode instanceof RexCall &&
CASE_FUNCTION_NAME.equalsIgnoreCase(((RexCall)
rexNode).getOperator().getName());
}
diff --git
a/kernel/sql-federation/compiler/src/test/java/org/apache/shardingsphere/sqlfederation/compiler/compiler/it/SQLStatementCompilerIT.java
b/kernel/sql-federation/compiler/src/test/java/org/apache/shardingsphere/sqlfederation/compiler/compiler/it/SQLStatementCompilerIT.java
index 02d6a730d99..bfd4bc8212b 100644
---
a/kernel/sql-federation/compiler/src/test/java/org/apache/shardingsphere/sqlfederation/compiler/compiler/it/SQLStatementCompilerIT.java
+++
b/kernel/sql-federation/compiler/src/test/java/org/apache/shardingsphere/sqlfederation/compiler/compiler/it/SQLStatementCompilerIT.java
@@ -37,6 +37,7 @@ import
org.apache.shardingsphere.sqlfederation.compiler.metadata.schema.SQLFeder
import
org.apache.shardingsphere.sqlfederation.compiler.rel.converter.SQLFederationRelConverter;
import
org.apache.shardingsphere.sqlfederation.compiler.sql.function.mysql.MySQLOperatorTable;
import org.junit.jupiter.api.BeforeEach;
+import org.junit.jupiter.api.Test;
import org.junit.jupiter.api.extension.ExtensionContext;
import org.junit.jupiter.params.ParameterizedTest;
import org.junit.jupiter.params.provider.Arguments;
@@ -54,7 +55,9 @@ import java.util.Properties;
import java.util.stream.Stream;
import static org.hamcrest.MatcherAssert.assertThat;
+import static org.hamcrest.Matchers.containsString;
import static org.hamcrest.Matchers.is;
+import static org.junit.jupiter.api.Assertions.assertDoesNotThrow;
import static org.mockito.Mockito.mock;
class SQLStatementCompilerIT {
@@ -241,6 +244,14 @@ class SQLStatementCompilerIT {
Collections.emptyList(), Collections.emptyList());
}
+ @Test
+ void assertCompileWhenCorrelatedInSubqueryProjectsOuterColumn() {
+ SQLStatement sqlStatement =
sqlParserRule.getSQLParserEngine(TypedSPILoader.getService(DatabaseType.class,
"MySQL"))
+ .parse("SELECT o.order_id FROM t_order o WHERE o.user_id IN
(SELECT o.user_id FROM t_order_item i)", false);
+ String actual = assertDoesNotThrow(() ->
sqlStatementCompiler.compile(sqlStatement,
"MySQL").getPhysicalPlan().explain().replaceAll(System.lineSeparator(), " "));
+ assertThat(actual,
containsString("EnumerableScan(table=[[federate_jdbc, t_order_item]],
sql=[SELECT * FROM `federate_jdbc`.`t_order_item`]"));
+ }
+
@ParameterizedTest(name = "{0}")
@ArgumentsSource(TestCaseArgumentsProvider.class)
void assertCompile(final TestCase testcase) {
diff --git
a/kernel/sql-federation/compiler/src/test/java/org/apache/shardingsphere/sqlfederation/compiler/planner/rule/transformation/PushProjectIntoScanRuleTest.java
b/kernel/sql-federation/compiler/src/test/java/org/apache/shardingsphere/sqlfederation/compiler/planner/rule/transformation/PushProjectIntoScanRuleTest.java
index f6b88fa152f..13ea5544ed2 100644
---
a/kernel/sql-federation/compiler/src/test/java/org/apache/shardingsphere/sqlfederation/compiler/planner/rule/transformation/PushProjectIntoScanRuleTest.java
+++
b/kernel/sql-federation/compiler/src/test/java/org/apache/shardingsphere/sqlfederation/compiler/planner/rule/transformation/PushProjectIntoScanRuleTest.java
@@ -21,6 +21,8 @@ import org.apache.calcite.plan.RelOptRuleCall;
import org.apache.calcite.plan.RelOptTable;
import org.apache.calcite.rel.logical.LogicalProject;
import org.apache.calcite.rex.RexCall;
+import org.apache.calcite.rex.RexCorrelVariable;
+import org.apache.calcite.rex.RexFieldAccess;
import org.apache.calcite.rex.RexNode;
import org.apache.calcite.rex.RexSubQuery;
import org.apache.calcite.sql.SqlOperator;
@@ -76,6 +78,15 @@ class PushProjectIntoScanRuleTest {
assertFalse(rule.matches(call));
}
+ @Test
+ void assertNotMatchWhenProjectContainsCorrelate() {
+ mockQualifiedName("public", "t_order");
+ RexFieldAccess rexFieldAccess = mock(RexFieldAccess.class);
+
when(rexFieldAccess.getReferenceExpr()).thenReturn(mock(RexCorrelVariable.class));
+
when(logicalProject.getProjects()).thenReturn(Collections.singletonList(rexFieldAccess));
+ assertFalse(rule.matches(call));
+ }
+
@Test
void assertNotMatchWhenProjectContainsCastFunction() {
mockQualifiedName("public", "t_order");
diff --git
a/test/e2e/sql/src/test/resources/cases/dql/cases/db_tbl_sql_federation/e2e-dql-select-sub-query-for-db-tbl-sql-federation.xml
b/test/e2e/sql/src/test/resources/cases/dql/cases/db_tbl_sql_federation/e2e-dql-select-sub-query-for-db-tbl-sql-federation.xml
index dfad979093d..6fe6f5320ea 100644
---
a/test/e2e/sql/src/test/resources/cases/dql/cases/db_tbl_sql_federation/e2e-dql-select-sub-query-for-db-tbl-sql-federation.xml
+++
b/test/e2e/sql/src/test/resources/cases/dql/cases/db_tbl_sql_federation/e2e-dql-select-sub-query-for-db-tbl-sql-federation.xml
@@ -25,6 +25,11 @@
<assertion parameters="1200:int"
expected-data-source-name="read_dataset" />
</test-case>-->
+ <test-case sql="SELECT o.order_id FROM (SELECT order_id, user_id FROM
t_order WHERE order_id = ?) o WHERE o.user_id IN (SELECT o.user_id FROM
t_order_item i) ORDER BY order_id"
+ db-types="MySQL,PostgreSQL,openGauss"
scenario-types="db_tbl_sql_federation">
+ <assertion parameters="1000:int"
expected-data-source-name="read_dataset" />
+ </test-case>
+
<test-case sql="SELECT * FROM t_order o WHERE o.order_id IN (SELECT
i.order_id FROM t_order_item i INNER JOIN t_product p ON i.product_id =
p.product_id WHERE p.product_id = ?) ORDER BY order_id"
db-types="MySQL,PostgreSQL,openGauss" scenario-types="db_tbl_sql_federation">
<assertion parameters="10:int"
expected-data-source-name="read_dataset" />
</test-case>
