This is an automated email from the ASF dual-hosted git repository.
tuichenchuxin 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 c8f650abead Add ENUMERABLE_LIMIT_RULE to support select limit
statement when use sql federation (#21229)
c8f650abead is described below
commit c8f650abead1a93ba68c7e927cd4d823d95c65dd
Author: Zhengqiang Duan <[email protected]>
AuthorDate: Wed Sep 28 11:31:39 2022 +0800
Add ENUMERABLE_LIMIT_RULE to support select limit statement when use sql
federation (#21229)
Co-authored-by: shardingsphere <[email protected]>
---
.../advanced/AdvancedSQLFederationExecutor.java | 2 +-
.../metadata/expander/ShardingSphereViewExpander.java | 13 +++++++------
.../optimizer/util/SQLFederationPlannerUtil.java | 14 ++++++++------
.../sqlfederation/optimizer/SQLOptimizeEngineTest.java | 18 +++++++++++++++---
.../resources/cases/dql/dql-integration-test-cases.xml | 4 ++++
5 files changed, 35 insertions(+), 16 deletions(-)
diff --git
a/shardingsphere-kernel/shardingsphere-sql-federation/shardingsphere-sql-federation-executor/shardingsphere-sql-federation-executor-advanced/src/main/java/org/apache/shardingsphere/sqlfederation/advanced/AdvancedSQLFederationExecutor.java
b/shardingsphere-kernel/shardingsphere-sql-federation/shardingsphere-sql-federation-executor/shardingsphere-sql-federation-executor-advanced/src/main/java/org/apache/shardingsphere/sqlfederation/advanced/AdvancedSQLFederationExecutor.java
index af4d29fc5b7..a257f39985b 100644
---
a/shardingsphere-kernel/shardingsphere-sql-federation/shardingsphere-sql-federation-executor/shardingsphere-sql-federation-executor-advanced/src/main/java/org/apache/shardingsphere/sqlfederation/advanced/AdvancedSQLFederationExecutor.java
+++
b/shardingsphere-kernel/shardingsphere-sql-federation/shardingsphere-sql-federation-executor/shardingsphere-sql-federation-executor-advanced/src/main/java/org/apache/shardingsphere/sqlfederation/advanced/AdvancedSQLFederationExecutor.java
@@ -136,7 +136,7 @@ public final class AdvancedSQLFederationExecutor implements
SQLFederationExecuto
CalciteCatalogReader catalogReader =
SQLFederationPlannerUtil.createCatalogReader(schemaName, sqlFederationSchema,
JAVA_TYPE_FACTORY, connectionConfig);
SqlValidator validator =
SQLFederationPlannerUtil.createSqlValidator(catalogReader, JAVA_TYPE_FACTORY,
parserContext.getDatabaseType(), connectionConfig);
SqlToRelConverter converter =
SQLFederationPlannerUtil.createSqlToRelConverter(catalogReader, validator,
-
SQLFederationPlannerUtil.createRelOptCluster(JAVA_TYPE_FACTORY),
optimizerContext, true);
+
SQLFederationPlannerUtil.createRelOptCluster(JAVA_TYPE_FACTORY),
optimizerContext.getSqlParserRule(), parserContext.getDatabaseType(), true);
SQLOptimizeContext optimizeContext =
new SQLOptimizeEngine(converter,
SQLFederationPlannerUtil.createHepPlanner()).optimize(selectStatementContext.getSqlStatement());
Bindable<Object> executablePlan =
EnumerableInterpretable.toBindable(Collections.emptyMap(), null,
(EnumerableRel) optimizeContext.getBestPlan(), EnumerableRel.Prefer.ARRAY);
diff --git
a/shardingsphere-kernel/shardingsphere-sql-federation/shardingsphere-sql-federation-optimizer/src/main/java/org/apache/shardingsphere/sqlfederation/optimizer/metadata/expander/ShardingSphereViewExpander.java
b/shardingsphere-kernel/shardingsphere-sql-federation/shardingsphere-sql-federation-optimizer/src/main/java/org/apache/shardingsphere/sqlfederation/optimizer/metadata/expander/ShardingSphereViewExpander.java
index 2dd3d4874f8..d54dc22215a 100644
---
a/shardingsphere-kernel/shardingsphere-sql-federation/shardingsphere-sql-federation-optimizer/src/main/java/org/apache/shardingsphere/sqlfederation/optimizer/metadata/expander/ShardingSphereViewExpander.java
+++
b/shardingsphere-kernel/shardingsphere-sql-federation/shardingsphere-sql-federation-optimizer/src/main/java/org/apache/shardingsphere/sqlfederation/optimizer/metadata/expander/ShardingSphereViewExpander.java
@@ -23,10 +23,10 @@ import org.apache.calcite.rel.RelRoot;
import org.apache.calcite.rel.type.RelDataType;
import org.apache.calcite.sql.SqlNode;
import org.apache.calcite.sql2rel.SqlToRelConverter;
-import org.apache.shardingsphere.infra.database.type.dialect.MySQLDatabaseType;
+import org.apache.shardingsphere.infra.database.type.DatabaseType;
import org.apache.shardingsphere.infra.parser.sql.SQLStatementParserEngine;
+import org.apache.shardingsphere.parser.rule.SQLParserRule;
import org.apache.shardingsphere.sql.parser.sql.common.statement.SQLStatement;
-import
org.apache.shardingsphere.sqlfederation.optimizer.context.OptimizerContext;
import
org.apache.shardingsphere.sqlfederation.optimizer.converter.SQLNodeConverterEngine;
import org.checkerframework.checker.nullness.qual.Nullable;
@@ -38,15 +38,16 @@ import java.util.List;
@RequiredArgsConstructor
public final class ShardingSphereViewExpander implements ViewExpander {
- private final OptimizerContext optimizerContext;
+ private final SQLParserRule sqlParserRule;
+
+ private final DatabaseType databaseType;
private final SqlToRelConverter sqlToRelConverter;
@Override
public RelRoot expandView(final RelDataType rowType, final String
queryString, final List<String> schemaPath, @Nullable final List<String>
viewPath) {
- SQLStatement sqlStatement = new SQLStatementParserEngine(new
MySQLDatabaseType().getType(),
- optimizerContext.getSqlParserRule().getSqlStatementCache(),
optimizerContext.getSqlParserRule().getParseTreeCache(),
-
optimizerContext.getSqlParserRule().isSqlCommentParseEnabled()).parse(queryString,
false);
+ SQLStatement sqlStatement = new
SQLStatementParserEngine(databaseType.getType(),
sqlParserRule.getSqlStatementCache(),
+ sqlParserRule.getParseTreeCache(),
sqlParserRule.isSqlCommentParseEnabled()).parse(queryString, false);
SqlNode sqlNode = SQLNodeConverterEngine.convert(sqlStatement);
return sqlToRelConverter.convertQuery(sqlNode, true, true);
}
diff --git
a/shardingsphere-kernel/shardingsphere-sql-federation/shardingsphere-sql-federation-optimizer/src/main/java/org/apache/shardingsphere/sqlfederation/optimizer/util/SQLFederationPlannerUtil.java
b/shardingsphere-kernel/shardingsphere-sql-federation/shardingsphere-sql-federation-optimizer/src/main/java/org/apache/shardingsphere/sqlfederation/optimizer/util/SQLFederationPlannerUtil.java
index 3bdafe4706f..bdb95155070 100644
---
a/shardingsphere-kernel/shardingsphere-sql-federation/shardingsphere-sql-federation-optimizer/src/main/java/org/apache/shardingsphere/sqlfederation/optimizer/util/SQLFederationPlannerUtil.java
+++
b/shardingsphere-kernel/shardingsphere-sql-federation/shardingsphere-sql-federation-optimizer/src/main/java/org/apache/shardingsphere/sqlfederation/optimizer/util/SQLFederationPlannerUtil.java
@@ -49,7 +49,7 @@ import org.apache.calcite.sql2rel.SqlToRelConverter;
import org.apache.calcite.sql2rel.SqlToRelConverter.Config;
import org.apache.calcite.sql2rel.StandardConvertletTable;
import org.apache.shardingsphere.infra.database.type.DatabaseType;
-import
org.apache.shardingsphere.sqlfederation.optimizer.context.OptimizerContext;
+import org.apache.shardingsphere.parser.rule.SQLParserRule;
import
org.apache.shardingsphere.sqlfederation.optimizer.metadata.expander.ShardingSphereViewExpander;
import
org.apache.shardingsphere.sqlfederation.optimizer.metadata.translatable.TranslatableFilterRule;
import
org.apache.shardingsphere.sqlfederation.optimizer.metadata.translatable.TranslatableProjectFilterRule;
@@ -110,6 +110,7 @@ public final class SQLFederationPlannerUtil {
planner.addRelTraitDef(RelCollationTraitDef.INSTANCE);
planner.addRule(EnumerableRules.ENUMERABLE_CALC_RULE);
planner.addRule(EnumerableRules.ENUMERABLE_SORT_RULE);
+ planner.addRule(EnumerableRules.ENUMERABLE_LIMIT_RULE);
planner.addRule(EnumerableRules.ENUMERABLE_JOIN_RULE);
planner.addRule(EnumerableRules.ENUMERABLE_TABLE_SCAN_RULE);
planner.addRule(EnumerableRules.ENUMERABLE_AGGREGATE_RULE);
@@ -217,14 +218,15 @@ public final class SQLFederationPlannerUtil {
* @param catalogReader catalog reader
* @param validator validator
* @param cluster cluster
- * @param optimizerContext optimizer context
+ * @param sqlParserRule sql parser rule
+ * @param databaseType database type
* @param needsViewExpand whether sql needs view expand or not
* @return sql to rel converter
*/
- public static SqlToRelConverter createSqlToRelConverter(final
CalciteCatalogReader catalogReader, final SqlValidator validator,
- final
RelOptCluster cluster, final OptimizerContext optimizerContext, final boolean
needsViewExpand) {
- ViewExpander expander = needsViewExpand ? new
ShardingSphereViewExpander(optimizerContext,
- createSqlToRelConverter(catalogReader, validator, cluster,
optimizerContext, false)) : (rowType, queryString, schemaPath, viewPath) ->
null;
+ public static SqlToRelConverter createSqlToRelConverter(final
CalciteCatalogReader catalogReader, final SqlValidator validator, final
RelOptCluster cluster,
+ final
SQLParserRule sqlParserRule, final DatabaseType databaseType, final boolean
needsViewExpand) {
+ ViewExpander expander = needsViewExpand ? new
ShardingSphereViewExpander(sqlParserRule, databaseType,
+ createSqlToRelConverter(catalogReader, validator, cluster,
sqlParserRule, databaseType, false)) : (rowType, queryString, schemaPath,
viewPath) -> null;
Config converterConfig =
SqlToRelConverter.config().withTrimUnusedFields(true);
return new SqlToRelConverter(expander, validator, catalogReader,
cluster, StandardConvertletTable.INSTANCE, converterConfig);
}
diff --git
a/shardingsphere-kernel/shardingsphere-sql-federation/shardingsphere-sql-federation-optimizer/src/test/java/org/apache/shardingsphere/sqlfederation/optimizer/SQLOptimizeEngineTest.java
b/shardingsphere-kernel/shardingsphere-sql-federation/shardingsphere-sql-federation-optimizer/src/test/java/org/apache/shardingsphere/sqlfederation/optimizer/SQLOptimizeEngineTest.java
index 571a34decc9..a5d9b1ae2b5 100644
---
a/shardingsphere-kernel/shardingsphere-sql-federation/shardingsphere-sql-federation-optimizer/src/test/java/org/apache/shardingsphere/sqlfederation/optimizer/SQLOptimizeEngineTest.java
+++
b/shardingsphere-kernel/shardingsphere-sql-federation/shardingsphere-sql-federation-optimizer/src/test/java/org/apache/shardingsphere/sqlfederation/optimizer/SQLOptimizeEngineTest.java
@@ -35,7 +35,6 @@ import
org.apache.shardingsphere.infra.parser.ShardingSphereSQLParserEngine;
import org.apache.shardingsphere.parser.rule.SQLParserRule;
import
org.apache.shardingsphere.parser.rule.builder.DefaultSQLParserRuleConfigurationBuilder;
import org.apache.shardingsphere.sql.parser.sql.common.statement.SQLStatement;
-import
org.apache.shardingsphere.sqlfederation.optimizer.context.OptimizerContext;
import
org.apache.shardingsphere.sqlfederation.optimizer.metadata.translatable.TranslatableSchema;
import
org.apache.shardingsphere.sqlfederation.optimizer.util.SQLFederationPlannerUtil;
import org.hamcrest.MatcherAssert;
@@ -91,6 +90,8 @@ public final class SQLOptimizeEngineTest {
private static final String SELECT_UNION = "SELECT order_id, user_id FROM
t_order_federate UNION SELECT 1, user_id FROM t_user_info WHERE information =
'before'";
+ private static final String SELECT_LIMIT = "SELECT order_id, user_id FROM
t_order_federate LIMIT 1";
+
private static final String SCHEMA_NAME = "federate_jdbc";
private final SQLParserRule sqlParserRule = new SQLParserRule(new
DefaultSQLParserRuleConfigurationBuilder().build());
@@ -125,9 +126,10 @@ public final class SQLOptimizeEngineTest {
RelDataTypeFactory relDataTypeFactory = new JavaTypeFactoryImpl();
TranslatableSchema federationSchema = new
TranslatableSchema(SCHEMA_NAME, schema, null);
CalciteCatalogReader catalogReader =
SQLFederationPlannerUtil.createCatalogReader(SCHEMA_NAME, federationSchema,
relDataTypeFactory, connectionConfig);
- SqlValidator validator =
SQLFederationPlannerUtil.createSqlValidator(catalogReader, relDataTypeFactory,
new H2DatabaseType(), connectionConfig);
+ H2DatabaseType databaseType = new H2DatabaseType();
+ SqlValidator validator =
SQLFederationPlannerUtil.createSqlValidator(catalogReader, relDataTypeFactory,
databaseType, connectionConfig);
RelOptCluster cluster =
RelOptCluster.create(SQLFederationPlannerUtil.createVolcanoPlanner(), new
RexBuilder(relDataTypeFactory));
- return SQLFederationPlannerUtil.createSqlToRelConverter(catalogReader,
validator, cluster, mock(OptimizerContext.class), false);
+ return SQLFederationPlannerUtil.createSqlToRelConverter(catalogReader,
validator, cluster, mock(SQLParserRule.class), databaseType, false);
}
@Test
@@ -268,4 +270,14 @@ public final class SQLOptimizeEngineTest {
+ " TranslatableTableScan(table=[[federate_jdbc,
t_user_info]], fields=[[0]], filters=[[=(CAST($1):VARCHAR, 'before'), null]])"
+ LINE_SEPARATOR;
MatcherAssert.assertThat(actual, is(expected));
}
+
+ @Test
+ public void assertSelectLimit() {
+ ShardingSphereSQLParserEngine sqlParserEngine =
sqlParserRule.getSQLParserEngine(DatabaseTypeEngine.getTrunkDatabaseTypeName(new
H2DatabaseType()));
+ SQLStatement sqlStatement = sqlParserEngine.parse(SELECT_LIMIT, false);
+ String actual =
optimizeEngine.optimize(sqlStatement).getBestPlan().explain();
+ String expected = "EnumerableLimit(fetch=[1])" + LINE_SEPARATOR
+ + " TranslatableTableScan(table=[[federate_jdbc,
t_order_federate]], fields=[[0, 1]])" + LINE_SEPARATOR;
+ MatcherAssert.assertThat(actual, is(expected));
+ }
}
diff --git
a/shardingsphere-test/shardingsphere-integration-test/shardingsphere-integration-test-suite/src/test/resources/cases/dql/dql-integration-test-cases.xml
b/shardingsphere-test/shardingsphere-integration-test/shardingsphere-integration-test-suite/src/test/resources/cases/dql/dql-integration-test-cases.xml
index 68919251d5c..1f19ead6823 100644
---
a/shardingsphere-test/shardingsphere-integration-test/shardingsphere-integration-test-suite/src/test/resources/cases/dql/dql-integration-test-cases.xml
+++
b/shardingsphere-test/shardingsphere-integration-test/shardingsphere-integration-test-suite/src/test/resources/cases/dql/dql-integration-test-cases.xml
@@ -530,6 +530,10 @@
<assertion parameters="1000:int, 100000:int"
expected-data-source-name="read_dataset" />
</test-case>
+ <test-case sql="SELECT o.*, i.* FROM t_order_federate o,
t_order_item_federate i WHERE o.order_id = ? AND i.item_id = ? LIMIT ?"
scenario-types="db,dbtbl_with_readwrite_splitting,dbtbl_with_readwrite_splitting_and_encrypt,sharding_and_encrypt,encrypt_and_readwrite_splitting">
+ <assertion parameters="1000:int, 100000:int, 3:int"
expected-data-source-name="read_dataset" />
+ </test-case>
+
<test-case sql="SELECT user_id, CONCAT('SUM:', total, '.') AS content FROM
(SELECT user_id, SUM(order_id_sharding) AS total FROM t_order_federate_sharding
GROUP BY user_id HAVING SUM(order_id_sharding) > ?) AS temp ORDER BY
temp.user_id" db-types="MySQL,PostgreSQL" scenario-types="tbl">
<assertion parameters="1000:int" />
</test-case>
