You are viewing a plain text version of this content. The canonical link for it is here.
Posted to notifications@shardingsphere.apache.org by pa...@apache.org on 2023/05/25 07:33:06 UTC
[shardingsphere] branch master updated: Refactor SQLNodeConverterEngineIT for sql which calcite parser not support (#25882)
This is an automated email from the ASF dual-hosted git repository.
panjuan 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 2ce744fce93 Refactor SQLNodeConverterEngineIT for sql which calcite parser not support (#25882)
2ce744fce93 is described below
commit 2ce744fce93e24fca57ec158d1193cebc2f3ab20
Author: Zhengqiang Duan <du...@apache.org>
AuthorDate: Thu May 25 15:32:58 2023 +0800
Refactor SQLNodeConverterEngineIT for sql which calcite parser not support (#25882)
* Refactor SQLNodeConverterEngineIT for sql which calcite not support
* optimize code style
---
.../sqlfederation/SQLDialectFactory.java | 5 +-
.../executor/FilterableTableScanExecutor.java | 2 +-
.../executor/TranslatableTableScanExecutor.java | 8 +-
.../impl/ShorthandProjectionConverter.java | 6 +-
test/it/optimizer/pom.xml | 5 +
.../test/it/optimize/SQLNodeConverterEngineIT.java | 245 ---------------------
.../converter/SQLNodeConverterEngineIT.java | 105 +++++++++
.../SQLNodeConverterTestCaseLoaderCallback.java | 76 +++++++
.../converter/cases/SQLNodeConverterTestCases.java | 48 ++++
.../cases/SQLNodeConverterTestCasesRegistry.java | 45 ++++
.../cases/jaxb/RootSQLNodeConverterTestCases.java | 72 ++++++
.../cases/jaxb/SQLNodeConverterTestCase.java | 46 ++++
.../test/resources/converter/select-aggregate.xml | 50 +++++
.../src/test/resources/converter/select-join.xml | 48 ++++
14 files changed, 505 insertions(+), 256 deletions(-)
diff --git a/kernel/sql-federation/executor/core/src/main/java/org/apache/shardingsphere/sqlfederation/SQLDialectFactory.java b/kernel/sql-federation/executor/core/src/main/java/org/apache/shardingsphere/sqlfederation/SQLDialectFactory.java
index 16ac528f9bd..d610ef8a885 100644
--- a/kernel/sql-federation/executor/core/src/main/java/org/apache/shardingsphere/sqlfederation/SQLDialectFactory.java
+++ b/kernel/sql-federation/executor/core/src/main/java/org/apache/shardingsphere/sqlfederation/SQLDialectFactory.java
@@ -24,7 +24,6 @@ import org.apache.calcite.sql.dialect.MssqlSqlDialect;
import org.apache.calcite.sql.dialect.MysqlSqlDialect;
import org.apache.calcite.sql.dialect.OracleSqlDialect;
import org.apache.calcite.sql.dialect.PostgresqlSqlDialect;
-import org.apache.shardingsphere.infra.database.type.DatabaseType;
import java.util.HashMap;
import java.util.Map;
@@ -53,7 +52,7 @@ public final class SQLDialectFactory {
* @param databaseType database type
* @return SQL dialect
*/
- public static SqlDialect getSQLDialect(final DatabaseType databaseType) {
- return SQL_DIALECTS_REGISTRY.getOrDefault(databaseType.getType(), MysqlSqlDialect.DEFAULT);
+ public static SqlDialect getSQLDialect(final String databaseType) {
+ return SQL_DIALECTS_REGISTRY.getOrDefault(databaseType, MysqlSqlDialect.DEFAULT);
}
}
diff --git a/kernel/sql-federation/executor/core/src/main/java/org/apache/shardingsphere/sqlfederation/executor/FilterableTableScanExecutor.java b/kernel/sql-federation/executor/core/src/main/java/org/apache/shardingsphere/sqlfederation/executor/FilterableTableScanExecutor.java
index 96d471fa90e..86f138ba394 100644
--- a/kernel/sql-federation/executor/core/src/main/java/org/apache/shardingsphere/sqlfederation/executor/FilterableTableScanExecutor.java
+++ b/kernel/sql-federation/executor/core/src/main/java/org/apache/shardingsphere/sqlfederation/executor/FilterableTableScanExecutor.java
@@ -137,7 +137,7 @@ public final class FilterableTableScanExecutor implements TableScanExecutor {
if (databaseType.getSystemSchemas().contains(schemaName)) {
return executeByShardingSphereData(databaseName, schemaName, table);
}
- SqlString sqlString = createSQLString(table, (FilterableScanNodeExecutorContext) scanContext, SQLDialectFactory.getSQLDialect(databaseType));
+ SqlString sqlString = createSQLString(table, (FilterableScanNodeExecutorContext) scanContext, SQLDialectFactory.getSQLDialect(databaseType.getType()));
SQLFederationExecutorContext federationContext = executorContext.getFederationContext();
QueryContext queryContext = createQueryContext(federationContext.getMetaData(), sqlString, databaseType, federationContext.getQueryContext().isUseCache());
ShardingSphereDatabase database = federationContext.getMetaData().getDatabase(databaseName);
diff --git a/kernel/sql-federation/executor/core/src/main/java/org/apache/shardingsphere/sqlfederation/executor/TranslatableTableScanExecutor.java b/kernel/sql-federation/executor/core/src/main/java/org/apache/shardingsphere/sqlfederation/executor/TranslatableTableScanExecutor.java
index eb7b0482747..a3ae7028341 100644
--- a/kernel/sql-federation/executor/core/src/main/java/org/apache/shardingsphere/sqlfederation/executor/TranslatableTableScanExecutor.java
+++ b/kernel/sql-federation/executor/core/src/main/java/org/apache/shardingsphere/sqlfederation/executor/TranslatableTableScanExecutor.java
@@ -40,10 +40,8 @@ import org.apache.calcite.rex.RexNode;
import org.apache.calcite.sql.SqlDialect;
import org.apache.calcite.sql.util.SqlString;
import org.apache.calcite.tools.RelBuilder;
-import org.apache.shardingsphere.infra.session.query.QueryContext;
import org.apache.shardingsphere.infra.binder.SQLStatementContextFactory;
import org.apache.shardingsphere.infra.binder.statement.SQLStatementContext;
-import org.apache.shardingsphere.infra.session.connection.ConnectionContext;
import org.apache.shardingsphere.infra.connection.kernel.KernelProcessor;
import org.apache.shardingsphere.infra.database.type.DatabaseType;
import org.apache.shardingsphere.infra.database.type.DatabaseTypeEngine;
@@ -72,6 +70,8 @@ import org.apache.shardingsphere.infra.metadata.database.ShardingSphereDatabase;
import org.apache.shardingsphere.infra.metadata.database.rule.ShardingSphereRuleMetaData;
import org.apache.shardingsphere.infra.metadata.database.schema.model.ShardingSphereTable;
import org.apache.shardingsphere.infra.parser.sql.SQLStatementParserEngine;
+import org.apache.shardingsphere.infra.session.connection.ConnectionContext;
+import org.apache.shardingsphere.infra.session.query.QueryContext;
import org.apache.shardingsphere.infra.util.exception.external.sql.type.wrapper.SQLWrapperException;
import org.apache.shardingsphere.sql.parser.sql.common.statement.SQLStatement;
import org.apache.shardingsphere.sqlfederation.SQLDialectFactory;
@@ -137,7 +137,7 @@ public final class TranslatableTableScanExecutor implements TableScanExecutor {
if (databaseType.getSystemSchemas().contains(schemaName)) {
return executeByScalarShardingSphereData(databaseName, schemaName, table);
}
- SqlString sqlString = createSQLString(table, (TranslatableScanNodeExecutorContext) scanContext, SQLDialectFactory.getSQLDialect(databaseType));
+ SqlString sqlString = createSQLString(table, (TranslatableScanNodeExecutorContext) scanContext, SQLDialectFactory.getSQLDialect(databaseType.getType()));
SQLFederationExecutorContext federationContext = executorContext.getFederationContext();
QueryContext queryContext = createQueryContext(federationContext.getMetaData(), sqlString, databaseType, federationContext.getQueryContext().isUseCache());
ShardingSphereDatabase database = federationContext.getMetaData().getDatabase(databaseName);
@@ -223,7 +223,7 @@ public final class TranslatableTableScanExecutor implements TableScanExecutor {
if (databaseType.getSystemSchemas().contains(schemaName)) {
return executeByShardingSphereData(databaseName, schemaName, table);
}
- SqlString sqlString = createSQLString(table, (TranslatableScanNodeExecutorContext) scanContext, SQLDialectFactory.getSQLDialect(databaseType));
+ SqlString sqlString = createSQLString(table, (TranslatableScanNodeExecutorContext) scanContext, SQLDialectFactory.getSQLDialect(databaseType.getType()));
SQLFederationExecutorContext federationContext = executorContext.getFederationContext();
QueryContext queryContext = createQueryContext(federationContext.getMetaData(), sqlString, databaseType, federationContext.getQueryContext().isUseCache());
ShardingSphereDatabase database = federationContext.getMetaData().getDatabase(databaseName);
diff --git a/kernel/sql-federation/optimizer/src/main/java/org/apache/shardingsphere/sqlfederation/optimizer/converter/segment/projection/impl/ShorthandProjectionConverter.java b/kernel/sql-federation/optimizer/src/main/java/org/apache/shardingsphere/sqlfederation/optimizer/converter/segment/projection/impl/ShorthandProjectionConverter.java
index 43ab10fbb3e..54d5cad37ce 100644
--- a/kernel/sql-federation/optimizer/src/main/java/org/apache/shardingsphere/sqlfederation/optimizer/converter/segment/projection/impl/ShorthandProjectionConverter.java
+++ b/kernel/sql-federation/optimizer/src/main/java/org/apache/shardingsphere/sqlfederation/optimizer/converter/segment/projection/impl/ShorthandProjectionConverter.java
@@ -37,9 +37,9 @@ public final class ShorthandProjectionConverter implements SQLSegmentConverter<S
if (null == segment) {
return Optional.empty();
}
- if (!segment.getOwner().isPresent()) {
- return Optional.of(SqlIdentifier.star(SqlParserPos.ZERO));
+ if (segment.getOwner().isPresent()) {
+ return Optional.of(SqlIdentifier.star(Arrays.asList(segment.getOwner().get().getIdentifier().getValue(), ""), SqlParserPos.ZERO, ImmutableList.of(SqlParserPos.ZERO, SqlParserPos.ZERO)));
}
- return Optional.of(SqlIdentifier.star(Arrays.asList(segment.getOwner().get().getIdentifier().getValue(), ""), SqlParserPos.ZERO, ImmutableList.of(SqlParserPos.ZERO)));
+ return Optional.of(SqlIdentifier.star(SqlParserPos.ZERO));
}
}
diff --git a/test/it/optimizer/pom.xml b/test/it/optimizer/pom.xml
index 53095d053bf..0ec15580977 100644
--- a/test/it/optimizer/pom.xml
+++ b/test/it/optimizer/pom.xml
@@ -37,6 +37,11 @@
<artifactId>shardingsphere-sql-federation-optimizer</artifactId>
<version>${project.version}</version>
</dependency>
+ <dependency>
+ <groupId>org.apache.shardingsphere</groupId>
+ <artifactId>shardingsphere-sql-federation-executor-core</artifactId>
+ <version>${project.version}</version>
+ </dependency>
<dependency>
<groupId>org.apache.shardingsphere</groupId>
diff --git a/test/it/optimizer/src/test/java/org/apache/shardingsphere/test/it/optimize/SQLNodeConverterEngineIT.java b/test/it/optimizer/src/test/java/org/apache/shardingsphere/test/it/optimize/SQLNodeConverterEngineIT.java
deleted file mode 100644
index 5f97301cd94..00000000000
--- a/test/it/optimizer/src/test/java/org/apache/shardingsphere/test/it/optimize/SQLNodeConverterEngineIT.java
+++ /dev/null
@@ -1,245 +0,0 @@
-/*
- * Licensed to the Apache Software Foundation (ASF) under one or more
- * contributor license agreements. See the NOTICE file distributed with
- * this work for additional information regarding copyright ownership.
- * The ASF licenses this file to You under the Apache License, Version 2.0
- * (the "License"); you may not use this file except in compliance with
- * the License. You may obtain a copy of the License at
- *
- * http://www.apache.org/licenses/LICENSE-2.0
- *
- * Unless required by applicable law or agreed to in writing, software
- * distributed under the License is distributed on an "AS IS" BASIS,
- * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
- * See the License for the specific language governing permissions and
- * limitations under the License.
- */
-
-package org.apache.shardingsphere.test.it.optimize;
-
-import lombok.SneakyThrows;
-import org.apache.calcite.avatica.util.Casing;
-import org.apache.calcite.config.CalciteConnectionConfig;
-import org.apache.calcite.config.CalciteConnectionConfigImpl;
-import org.apache.calcite.config.CalciteConnectionProperty;
-import org.apache.calcite.sql.SqlNode;
-import org.apache.calcite.sql.parser.SqlParseException;
-import org.apache.calcite.sql.parser.SqlParser;
-import org.apache.calcite.sql.parser.SqlParser.Config;
-import org.apache.calcite.sql.parser.impl.SqlParserImpl;
-import org.apache.calcite.util.Litmus;
-import org.apache.shardingsphere.infra.util.spi.type.typed.TypedSPILoader;
-import org.apache.shardingsphere.sql.parser.api.CacheOption;
-import org.apache.shardingsphere.sql.parser.api.SQLParserEngine;
-import org.apache.shardingsphere.sql.parser.api.SQLStatementVisitorEngine;
-import org.apache.shardingsphere.sql.parser.sql.common.statement.SQLStatement;
-import org.apache.shardingsphere.sqlfederation.optimizer.context.parser.dialect.OptimizerSQLDialectBuilder;
-import org.apache.shardingsphere.sqlfederation.optimizer.converter.SQLNodeConverterEngine;
-import org.apache.shardingsphere.test.it.sql.parser.internal.InternalSQLParserTestParameter;
-import org.apache.shardingsphere.test.it.sql.parser.internal.cases.parser.SQLParserTestCases;
-import org.apache.shardingsphere.test.it.sql.parser.internal.cases.parser.registry.SQLParserTestCasesRegistry;
-import org.apache.shardingsphere.test.it.sql.parser.internal.cases.sql.SQLCases;
-import org.apache.shardingsphere.test.it.sql.parser.internal.cases.sql.registry.SQLCasesRegistry;
-import org.apache.shardingsphere.test.it.sql.parser.internal.cases.sql.type.SQLCaseType;
-import org.junit.jupiter.api.extension.ExtensionContext;
-import org.junit.jupiter.params.ParameterizedTest;
-import org.junit.jupiter.params.provider.Arguments;
-import org.junit.jupiter.params.provider.ArgumentsProvider;
-import org.junit.jupiter.params.provider.ArgumentsSource;
-
-import java.util.Arrays;
-import java.util.Collection;
-import java.util.HashSet;
-import java.util.LinkedList;
-import java.util.Properties;
-import java.util.stream.Collectors;
-import java.util.stream.Stream;
-
-import static org.junit.jupiter.api.Assertions.assertTrue;
-
-class SQLNodeConverterEngineIT {
-
- private static final SQLCases SQL_CASES = SQLCasesRegistry.getInstance().getCases();
-
- private static final SQLParserTestCases SQL_PARSER_TEST_CASES = SQLParserTestCasesRegistry.getInstance().getCases();
-
- private static final String SELECT_STATEMENT_PREFIX = "SELECT";
-
- @ParameterizedTest(name = "{0}")
- @ArgumentsSource(TestCaseArgumentsProvider.class)
- void assertConvert(final String sqlCaseId, final SQLCaseType sqlCaseType, final String databaseType) {
- String sql = SQL_CASES.getSQL(sqlCaseId, sqlCaseType, SQL_PARSER_TEST_CASES.get(sqlCaseId).getParameters());
- SqlNode actual = SQLNodeConverterEngine.convert(parseSQLStatement(databaseType, sql));
- SqlNode expected = parseSQLNode(databaseType, sql);
- assertTrue(actual.equalsDeep(expected, Litmus.THROW));
- }
-
- private SQLStatement parseSQLStatement(final String databaseType, final String sql) {
- return new SQLStatementVisitorEngine(databaseType, true).visit(new SQLParserEngine(databaseType, new CacheOption(128, 1024L)).parse(sql, false));
- }
-
- @SneakyThrows(SqlParseException.class)
- private SqlNode parseSQLNode(final String databaseType, final String sql) {
- return SqlParser.create(sql, createConfiguration(databaseType)).parseQuery();
- }
-
- private Config createConfiguration(final String databaseType) {
- CalciteConnectionConfig connectionConfig = new CalciteConnectionConfigImpl(createSQLDialectProperties(databaseType));
- return SqlParser.config().withLex(connectionConfig.lex()).withUnquotedCasing(Casing.UNCHANGED)
- .withIdentifierMaxLength(SqlParser.DEFAULT_IDENTIFIER_MAX_LENGTH).withConformance(connectionConfig.conformance()).withParserFactory(SqlParserImpl.FACTORY);
- }
-
- private Properties createSQLDialectProperties(final String databaseType) {
- Properties result = new Properties();
- result.setProperty(CalciteConnectionProperty.TIME_ZONE.camelName(), "UTC");
- result.putAll(TypedSPILoader.getService(OptimizerSQLDialectBuilder.class, databaseType).build());
- return result;
- }
-
- private static class TestCaseArgumentsProvider implements ArgumentsProvider {
-
- private final Collection<String> supportedSQLCaseIDs = getSupportedSQLCaseIDs();
-
- // TODO remove the method when all SQL statement support convert to SQL node
- // CHECKSTYLE:OFF
- private Collection<String> getSupportedSQLCaseIDs() {
- Collection<String> result = new HashSet<>();
- result.add("select_with_join_table_subquery");
- result.add("select_with_projection_subquery");
- result.add("select_with_in_subquery_condition");
- result.add("select_with_between_and_subquery_condition");
- result.add("select_with_exist_subquery_condition");
- result.add("select_with_not_exist_subquery_condition");
- result.add("select_with_simple_table");
- result.add("select_group_by_with_limit");
- result.add("select_left_outer_join_related_with_alias");
- result.add("select_right_outer_join_related_with_alias");
- result.add("select_alias_as_keyword");
- result.add("select_avg");
- result.add("select_between_with_single_table");
- result.add("select_distinct_with_single_count_group_by");
- result.add("select_bit_xor");
- result.add("select_with_schema");
- result.add("select_with_same_table_name_and_alias");
- result.add("select_count_like_concat");
- result.add("select_order_by_asc_and_index_desc");
- result.add("select_group_by_with_having_count");
- result.add("select_constant_without_table");
- result.add("select_count_with_binding_tables_with_join");
- result.add("select_join_using");
- result.add("select_count_with_escape_character");
- result.add("select_group_by_with_order_by_and_limit");
- result.add("select_count_with_sub");
- result.add("select_current_user");
- result.add("select_database");
- result.add("select_distinct_with_count_calculation");
- result.add("select_count_like_escape");
- result.add("select_with_projection_subquery_and_multiple_parameters");
- result.add("select_group_concat");
- result.add("select_cast_function");
- result.add("select_position");
- result.add("select_mod_function");
- result.add("select_pagination_with_offset");
- result.add("select_pagination_with_row_count");
- result.add("select_pagination_with_top");
- result.add("select_pagination_with_top_percent_with_ties");
- result.add("select_pagination_with_row_number");
- result.add("select_pagination_with_limit_with_back_quotes");
- result.add("select_pagination_with_limit_and_offset_keyword");
- result.add("select_pagination_with_offset_and_limit");
- result.add("select_pagination_with_offset_and_limit_all");
- result.add("select_pagination_with_top_for_greater_than");
- result.add("select_pagination_with_top_percent_with_ties_for_greater_than");
- result.add("select_pagination_with_top_for_greater_than_and_equal");
- result.add("select_pagination_with_top_percent_with_ties_for_greater_than_and_equal");
- result.add("select_pagination_with_row_number_for_greater_than");
- result.add("select_pagination_with_row_number_for_greater_than_and_equal");
- result.add("select_pagination_with_row_number_not_at_end");
- result.add("select_pagination_with_fetch_first_with_row_number");
- result.add("select_pagination_with_offset_fetch");
- result.add("select_pagination_with_limit_offset_and_row_count");
- result.add("select_pagination_with_limit_row_count");
- result.add("select_pagination_with_limit_fetch_count");
- result.add("select_with_null_keyword_in_projection");
- result.add("select_union");
- result.add("select_union_all");
- result.add("select_union_all_order_by");
- result.add("select_union_all_order_by_limit");
- result.add("select_intersect");
- result.add("select_intersect_order_by");
- result.add("select_intersect_order_by_limit");
- result.add("select_except");
- result.add("select_except_order_by");
- result.add("select_except_order_by_limit");
- result.add("select_minus");
- result.add("select_minus_order_by");
- result.add("select_minus_order_by_limit");
- result.add("select_union_intersect");
- result.add("select_union_except");
- result.add("select_union_intersect_except");
- result.add("select_except_union");
- result.add("select_except_intersect");
- result.add("select_except_intersect_union");
- result.add("select_sub_union");
- result.add("select_projections_with_expr");
- result.add("select_projections_with_only_expr");
- result.add("select_natural_join");
- result.add("select_natural_inner_join");
- result.add("select_natural_left_join");
- result.add("select_natural_right_join");
- result.add("select_natural_full_join");
- result.add("select_order_by_for_nulls_first");
- result.add("select_order_by_for_nulls_last");
- result.add("select_char");
- result.add("select_weight_string");
- result.add("select_trim");
- result.add("select_trim_with_both");
- result.add("select_with_trim_expr");
- result.add("select_with_trim_expr_and_both");
- result.add("select_with_trim_expr_from_expr");
- result.add("select_with_trim_expr_from_expr_and_both");
- result.add("select_extract");
- result.add("select_where_with_bit_expr_with_mod_sign");
- result.add("select_with_spatial_function");
- result.add("select_from_dual");
- result.add("select_substring");
- result.add("select_where_with_bit_expr_with_plus_interval");
- result.add("select_where_with_bit_expr_with_minus_interval");
- result.add("select_where_with_predicate_with_in_subquery");
- result.add("select_where_with_boolean_primary_with_is");
- result.add("select_where_with_boolean_primary_with_is_not");
- result.add("select_where_with_boolean_primary_with_comparison_subquery");
- result.add("select_not_between_with_single_table");
- result.add("select_not_in_with_single_table");
- result.add("select_where_with_expr_with_is");
- result.add("select_where_with_expr_with_is_not");
- result.add("select_where_with_expr_with_not");
- result.add("select_where_with_expr_with_or_sign");
- return result;
- }
- // CHECKSTYLE:ON
-
- @Override
- public Stream<? extends Arguments> provideArguments(final ExtensionContext extensionContext) {
- return getTestParameters("MySQL", "PostgreSQL", "openGauss").stream();
- }
-
- private Collection<Arguments> getTestParameters(final String... databaseTypes) {
- Collection<Arguments> result = new LinkedList<>();
- for (InternalSQLParserTestParameter each : SQL_CASES.generateTestParameters(Arrays.stream(databaseTypes).collect(Collectors.toSet()))) {
- if (!isPlaceholderWithoutParameter(each) && isSupportedSQLCase(each)) {
- result.add(Arguments.of(each.getSqlCaseId(), each.getSqlCaseType(), "H2".equals(each.getDatabaseType()) ? "MySQL" : each.getDatabaseType()));
- }
- }
- return result;
- }
-
- private boolean isPlaceholderWithoutParameter(final InternalSQLParserTestParameter testParam) {
- return SQLCaseType.PLACEHOLDER == testParam.getSqlCaseType() && SQL_PARSER_TEST_CASES.get(testParam.getSqlCaseId()).getParameters().isEmpty();
- }
-
- private boolean isSupportedSQLCase(final InternalSQLParserTestParameter testParam) {
- return testParam.getSqlCaseId().toUpperCase().startsWith(SELECT_STATEMENT_PREFIX) && supportedSQLCaseIDs.contains(testParam.getSqlCaseId());
- }
- }
-}
diff --git a/test/it/optimizer/src/test/java/org/apache/shardingsphere/test/it/optimizer/converter/SQLNodeConverterEngineIT.java b/test/it/optimizer/src/test/java/org/apache/shardingsphere/test/it/optimizer/converter/SQLNodeConverterEngineIT.java
new file mode 100644
index 00000000000..a639e689b3f
--- /dev/null
+++ b/test/it/optimizer/src/test/java/org/apache/shardingsphere/test/it/optimizer/converter/SQLNodeConverterEngineIT.java
@@ -0,0 +1,105 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one or more
+ * contributor license agreements. See the NOTICE file distributed with
+ * this work for additional information regarding copyright ownership.
+ * The ASF licenses this file to You under the Apache License, Version 2.0
+ * (the "License"); you may not use this file except in compliance with
+ * the License. You may obtain a copy of the License at
+ *
+ * http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing, software
+ * distributed under the License is distributed on an "AS IS" BASIS,
+ * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ * See the License for the specific language governing permissions and
+ * limitations under the License.
+ */
+
+package org.apache.shardingsphere.test.it.optimizer.converter;
+
+import lombok.extern.slf4j.Slf4j;
+import org.apache.shardingsphere.sql.parser.api.CacheOption;
+import org.apache.shardingsphere.sql.parser.api.SQLParserEngine;
+import org.apache.shardingsphere.sql.parser.api.SQLStatementVisitorEngine;
+import org.apache.shardingsphere.sql.parser.sql.common.statement.SQLStatement;
+import org.apache.shardingsphere.sqlfederation.SQLDialectFactory;
+import org.apache.shardingsphere.sqlfederation.optimizer.converter.SQLNodeConverterEngine;
+import org.apache.shardingsphere.test.it.optimizer.converter.cases.SQLNodeConverterTestCases;
+import org.apache.shardingsphere.test.it.optimizer.converter.cases.SQLNodeConverterTestCasesRegistry;
+import org.apache.shardingsphere.test.it.sql.parser.internal.InternalSQLParserTestParameter;
+import org.apache.shardingsphere.test.it.sql.parser.internal.cases.parser.SQLParserTestCases;
+import org.apache.shardingsphere.test.it.sql.parser.internal.cases.parser.registry.SQLParserTestCasesRegistry;
+import org.apache.shardingsphere.test.it.sql.parser.internal.cases.sql.SQLCases;
+import org.apache.shardingsphere.test.it.sql.parser.internal.cases.sql.registry.SQLCasesRegistry;
+import org.apache.shardingsphere.test.it.sql.parser.internal.cases.sql.type.SQLCaseType;
+import org.junit.jupiter.api.extension.ExtensionContext;
+import org.junit.jupiter.params.ParameterizedTest;
+import org.junit.jupiter.params.provider.Arguments;
+import org.junit.jupiter.params.provider.ArgumentsProvider;
+import org.junit.jupiter.params.provider.ArgumentsSource;
+
+import java.util.Arrays;
+import java.util.Collection;
+import java.util.LinkedList;
+import java.util.stream.Collectors;
+import java.util.stream.Stream;
+
+import static org.hamcrest.MatcherAssert.assertThat;
+import static org.hamcrest.Matchers.is;
+
+@Slf4j
+class SQLNodeConverterEngineIT {
+
+ private static final SQLCases SQL_CASES = SQLCasesRegistry.getInstance().getCases();
+
+ private static final SQLParserTestCases SQL_PARSER_TEST_CASES = SQLParserTestCasesRegistry.getInstance().getCases();
+
+ private static final SQLNodeConverterTestCases SQL_NODE_CONVERTER_TEST_CASES = SQLNodeConverterTestCasesRegistry.getInstance().getCases();
+
+ private static final String SELECT_STATEMENT_PREFIX = "SELECT";
+
+ @ParameterizedTest(name = "{0} ({1}) -> {2}")
+ @ArgumentsSource(TestCaseArgumentsProvider.class)
+ void assertConvert(final String sqlCaseId, final SQLCaseType sqlCaseType, final String databaseType) {
+ String expected;
+ try {
+ expected = SQL_NODE_CONVERTER_TEST_CASES.get(sqlCaseId, sqlCaseType, databaseType).getExpectedSQL();
+ } catch (IllegalStateException ex) {
+ log.warn(ex.getMessage());
+ return;
+ }
+ String sql = SQL_CASES.getSQL(sqlCaseId, sqlCaseType, SQL_PARSER_TEST_CASES.get(sqlCaseId).getParameters());
+ String actual = SQLNodeConverterEngine.convert(parseSQLStatement(databaseType, sql)).toSqlString(SQLDialectFactory.getSQLDialect(databaseType)).getSql().replace("\n", " ");
+ assertThat(actual, is(expected));
+ }
+
+ private SQLStatement parseSQLStatement(final String databaseType, final String sql) {
+ return new SQLStatementVisitorEngine(databaseType, true).visit(new SQLParserEngine(databaseType, new CacheOption(128, 1024L)).parse(sql, false));
+ }
+
+ private static class TestCaseArgumentsProvider implements ArgumentsProvider {
+
+ @Override
+ public Stream<? extends Arguments> provideArguments(final ExtensionContext extensionContext) {
+ return getTestParameters("MySQL", "PostgreSQL", "openGauss").stream();
+ }
+
+ private Collection<Arguments> getTestParameters(final String... databaseTypes) {
+ Collection<Arguments> result = new LinkedList<>();
+ for (InternalSQLParserTestParameter each : SQL_CASES.generateTestParameters(Arrays.stream(databaseTypes).collect(Collectors.toSet()))) {
+ if (!isPlaceholderWithoutParameter(each) && isSupportedSQLCase(each)) {
+ result.add(Arguments.of(each.getSqlCaseId(), each.getSqlCaseType(), "H2".equals(each.getDatabaseType()) ? "MySQL" : each.getDatabaseType()));
+ }
+ }
+ return result;
+ }
+
+ private boolean isPlaceholderWithoutParameter(final InternalSQLParserTestParameter testParam) {
+ return SQLCaseType.PLACEHOLDER == testParam.getSqlCaseType() && SQL_PARSER_TEST_CASES.get(testParam.getSqlCaseId()).getParameters().isEmpty();
+ }
+
+ private boolean isSupportedSQLCase(final InternalSQLParserTestParameter testParam) {
+ return testParam.getSqlCaseId().toUpperCase().startsWith(SELECT_STATEMENT_PREFIX);
+ }
+ }
+}
diff --git a/test/it/optimizer/src/test/java/org/apache/shardingsphere/test/it/optimizer/converter/cases/SQLNodeConverterTestCaseLoaderCallback.java b/test/it/optimizer/src/test/java/org/apache/shardingsphere/test/it/optimizer/converter/cases/SQLNodeConverterTestCaseLoaderCallback.java
new file mode 100644
index 00000000000..c4c0ca4d4a8
--- /dev/null
+++ b/test/it/optimizer/src/test/java/org/apache/shardingsphere/test/it/optimizer/converter/cases/SQLNodeConverterTestCaseLoaderCallback.java
@@ -0,0 +1,76 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one or more
+ * contributor license agreements. See the NOTICE file distributed with
+ * this work for additional information regarding copyright ownership.
+ * The ASF licenses this file to You under the Apache License, Version 2.0
+ * (the "License"); you may not use this file except in compliance with
+ * the License. You may obtain a copy of the License at
+ *
+ * http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing, software
+ * distributed under the License is distributed on an "AS IS" BASIS,
+ * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ * See the License for the specific language governing permissions and
+ * limitations under the License.
+ */
+
+package org.apache.shardingsphere.test.it.optimizer.converter.cases;
+
+import com.google.common.base.Preconditions;
+import org.apache.shardingsphere.test.it.optimizer.converter.cases.jaxb.RootSQLNodeConverterTestCases;
+import org.apache.shardingsphere.test.it.optimizer.converter.cases.jaxb.SQLNodeConverterTestCase;
+import org.apache.shardingsphere.test.it.sql.parser.internal.loader.CaseFileLoader;
+import org.apache.shardingsphere.test.it.sql.parser.internal.loader.CaseLoaderCallback;
+
+import javax.xml.bind.JAXBContext;
+import javax.xml.bind.JAXBException;
+import java.io.File;
+import java.io.IOException;
+import java.io.InputStream;
+import java.nio.file.Files;
+import java.nio.file.Paths;
+import java.util.Collection;
+import java.util.HashMap;
+import java.util.HashSet;
+import java.util.Map;
+
+/**
+ * SQL node converter test case loader callback.
+ */
+public final class SQLNodeConverterTestCaseLoaderCallback implements CaseLoaderCallback<SQLNodeConverterTestCase> {
+
+ @Override
+ public Map<String, SQLNodeConverterTestCase> loadFromJar(final File jarFile, final String rootDirectory) throws JAXBException {
+ Map<String, SQLNodeConverterTestCase> result = new HashMap<>(Short.MAX_VALUE, 1F);
+ for (String each : CaseFileLoader.loadFileNamesFromJar(jarFile, rootDirectory)) {
+ Map<String, SQLNodeConverterTestCase> testCases = createTestCases(Thread.currentThread().getContextClassLoader().getResourceAsStream(each));
+ checkDuplicatedTestCases(testCases, result);
+ result.putAll(testCases);
+ }
+ return result;
+ }
+
+ @Override
+ public Map<String, SQLNodeConverterTestCase> loadFromDirectory(final String rootDirectory) throws IOException, JAXBException {
+ Map<String, SQLNodeConverterTestCase> result = new HashMap<>(Short.MAX_VALUE, 1F);
+ for (File each : CaseFileLoader.loadFilesFromDirectory(rootDirectory)) {
+ try (InputStream inputStream = Files.newInputStream(Paths.get(each.toURI()))) {
+ Map<String, SQLNodeConverterTestCase> testCases = createTestCases(inputStream);
+ checkDuplicatedTestCases(testCases, result);
+ result.putAll(testCases);
+ }
+ }
+ return result;
+ }
+
+ private Map<String, SQLNodeConverterTestCase> createTestCases(final InputStream inputStream) throws JAXBException {
+ return ((RootSQLNodeConverterTestCases) JAXBContext.newInstance(RootSQLNodeConverterTestCases.class).createUnmarshaller().unmarshal(inputStream)).getTestCases();
+ }
+
+ private void checkDuplicatedTestCases(final Map<String, SQLNodeConverterTestCase> newTestCases, final Map<String, SQLNodeConverterTestCase> existedTestCases) {
+ Collection<String> caseKeys = new HashSet<>(newTestCases.keySet());
+ caseKeys.retainAll(existedTestCases.keySet());
+ Preconditions.checkState(caseKeys.isEmpty(), "Find duplicated SQL Case keys: %s", caseKeys);
+ }
+}
diff --git a/test/it/optimizer/src/test/java/org/apache/shardingsphere/test/it/optimizer/converter/cases/SQLNodeConverterTestCases.java b/test/it/optimizer/src/test/java/org/apache/shardingsphere/test/it/optimizer/converter/cases/SQLNodeConverterTestCases.java
new file mode 100644
index 00000000000..d597c1683d7
--- /dev/null
+++ b/test/it/optimizer/src/test/java/org/apache/shardingsphere/test/it/optimizer/converter/cases/SQLNodeConverterTestCases.java
@@ -0,0 +1,48 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one or more
+ * contributor license agreements. See the NOTICE file distributed with
+ * this work for additional information regarding copyright ownership.
+ * The ASF licenses this file to You under the Apache License, Version 2.0
+ * (the "License"); you may not use this file except in compliance with
+ * the License. You may obtain a copy of the License at
+ *
+ * http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing, software
+ * distributed under the License is distributed on an "AS IS" BASIS,
+ * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ * See the License for the specific language governing permissions and
+ * limitations under the License.
+ */
+
+package org.apache.shardingsphere.test.it.optimizer.converter.cases;
+
+import com.google.common.base.Preconditions;
+import lombok.RequiredArgsConstructor;
+import org.apache.shardingsphere.test.it.optimizer.converter.cases.jaxb.SQLNodeConverterTestCase;
+import org.apache.shardingsphere.test.it.sql.parser.internal.cases.sql.type.SQLCaseType;
+
+import java.util.Map;
+
+/**
+ * SQL node converter test cases.
+ */
+@RequiredArgsConstructor
+public final class SQLNodeConverterTestCases {
+
+ private final Map<String, SQLNodeConverterTestCase> cases;
+
+ /**
+ * Get SQL node converter test case.
+ *
+ * @param sqlCaseId SQL case ID
+ * @param sqlCaseType sql case type
+ * @param databaseType database type
+ * @return SQL node converter test case
+ */
+ public SQLNodeConverterTestCase get(final String sqlCaseId, final SQLCaseType sqlCaseType, final String databaseType) {
+ String caseKey = sqlCaseId + "_" + sqlCaseType + "_" + databaseType;
+ Preconditions.checkState(cases.containsKey(caseKey), "Can not find SQL node convert case id %s, case type %s for %s.", sqlCaseId, sqlCaseType, databaseType);
+ return cases.get(caseKey);
+ }
+}
diff --git a/test/it/optimizer/src/test/java/org/apache/shardingsphere/test/it/optimizer/converter/cases/SQLNodeConverterTestCasesRegistry.java b/test/it/optimizer/src/test/java/org/apache/shardingsphere/test/it/optimizer/converter/cases/SQLNodeConverterTestCasesRegistry.java
new file mode 100644
index 00000000000..3acd5163ddc
--- /dev/null
+++ b/test/it/optimizer/src/test/java/org/apache/shardingsphere/test/it/optimizer/converter/cases/SQLNodeConverterTestCasesRegistry.java
@@ -0,0 +1,45 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one or more
+ * contributor license agreements. See the NOTICE file distributed with
+ * this work for additional information regarding copyright ownership.
+ * The ASF licenses this file to You under the Apache License, Version 2.0
+ * (the "License"); you may not use this file except in compliance with
+ * the License. You may obtain a copy of the License at
+ *
+ * http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing, software
+ * distributed under the License is distributed on an "AS IS" BASIS,
+ * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ * See the License for the specific language governing permissions and
+ * limitations under the License.
+ */
+
+package org.apache.shardingsphere.test.it.optimizer.converter.cases;
+
+import lombok.Getter;
+import org.apache.shardingsphere.test.it.sql.parser.internal.loader.CaseLoaderTemplate;
+
+/**
+ * SQL node converter test cases registry.
+ */
+@Getter
+public final class SQLNodeConverterTestCasesRegistry {
+
+ private static final SQLNodeConverterTestCasesRegistry INSTANCE = new SQLNodeConverterTestCasesRegistry();
+
+ private final SQLNodeConverterTestCases cases;
+
+ private SQLNodeConverterTestCasesRegistry() {
+ cases = new SQLNodeConverterTestCases(CaseLoaderTemplate.load("converter/", new SQLNodeConverterTestCaseLoaderCallback()));
+ }
+
+ /**
+ * Get instance.
+ *
+ * @return got instance
+ */
+ public static SQLNodeConverterTestCasesRegistry getInstance() {
+ return INSTANCE;
+ }
+}
diff --git a/test/it/optimizer/src/test/java/org/apache/shardingsphere/test/it/optimizer/converter/cases/jaxb/RootSQLNodeConverterTestCases.java b/test/it/optimizer/src/test/java/org/apache/shardingsphere/test/it/optimizer/converter/cases/jaxb/RootSQLNodeConverterTestCases.java
new file mode 100644
index 00000000000..dbc7ab15a3a
--- /dev/null
+++ b/test/it/optimizer/src/test/java/org/apache/shardingsphere/test/it/optimizer/converter/cases/jaxb/RootSQLNodeConverterTestCases.java
@@ -0,0 +1,72 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one or more
+ * contributor license agreements. See the NOTICE file distributed with
+ * this work for additional information regarding copyright ownership.
+ * The ASF licenses this file to You under the Apache License, Version 2.0
+ * (the "License"); you may not use this file except in compliance with
+ * the License. You may obtain a copy of the License at
+ *
+ * http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing, software
+ * distributed under the License is distributed on an "AS IS" BASIS,
+ * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ * See the License for the specific language governing permissions and
+ * limitations under the License.
+ */
+
+package org.apache.shardingsphere.test.it.optimizer.converter.cases.jaxb;
+
+import com.google.common.base.Splitter;
+import com.google.common.base.Strings;
+import lombok.Getter;
+import org.apache.shardingsphere.test.it.sql.parser.internal.cases.sql.type.SQLCaseType;
+
+import javax.xml.bind.annotation.XmlElement;
+import javax.xml.bind.annotation.XmlRootElement;
+import java.util.Arrays;
+import java.util.Collection;
+import java.util.HashMap;
+import java.util.LinkedList;
+import java.util.List;
+import java.util.Map;
+import java.util.stream.Collectors;
+
+/**
+ * SQL node converter test cases for XML root tag.
+ */
+@XmlRootElement(name = "sql-node-converter-test-cases")
+@Getter
+public final class RootSQLNodeConverterTestCases {
+
+ @XmlElement(name = "test-cases")
+ private final List<SQLNodeConverterTestCase> testCases = new LinkedList<>();
+
+ /**
+ * Get all SQL node converter test cases.
+ *
+ * @return SQL node converter test cases
+ */
+ public Map<String, SQLNodeConverterTestCase> getTestCases() {
+ Map<String, SQLNodeConverterTestCase> result = new HashMap<>(testCases.size(), 1F);
+ for (SQLNodeConverterTestCase each : testCases) {
+ Collection<String> databaseTypes = getDatabaseTypes(each);
+ for (String sqlCaseType : getSQLCaseTypes(each)) {
+ for (String databaseType : databaseTypes) {
+ result.put(each.getSqlCaseId() + "_" + sqlCaseType + "_" + databaseType, each);
+ }
+ }
+ }
+ return result;
+ }
+
+ private static Collection<String> getDatabaseTypes(final SQLNodeConverterTestCase each) {
+ return Strings.isNullOrEmpty(each.getDatabaseTypes()) ? Arrays.asList("MySQL", "PostgreSQL", "openGauss", "Oracle", "SQLServer")
+ : Splitter.on(",").trimResults().splitToList(each.getDatabaseTypes());
+ }
+
+ private static Collection<String> getSQLCaseTypes(final SQLNodeConverterTestCase each) {
+ return Strings.isNullOrEmpty(each.getSqlCaseTypes()) ? Arrays.stream(SQLCaseType.values()).map(Enum::name).collect(Collectors.toList())
+ : Splitter.on(",").trimResults().splitToList(each.getSqlCaseTypes());
+ }
+}
diff --git a/test/it/optimizer/src/test/java/org/apache/shardingsphere/test/it/optimizer/converter/cases/jaxb/SQLNodeConverterTestCase.java b/test/it/optimizer/src/test/java/org/apache/shardingsphere/test/it/optimizer/converter/cases/jaxb/SQLNodeConverterTestCase.java
new file mode 100644
index 00000000000..e0b7a5f3b7e
--- /dev/null
+++ b/test/it/optimizer/src/test/java/org/apache/shardingsphere/test/it/optimizer/converter/cases/jaxb/SQLNodeConverterTestCase.java
@@ -0,0 +1,46 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one or more
+ * contributor license agreements. See the NOTICE file distributed with
+ * this work for additional information regarding copyright ownership.
+ * The ASF licenses this file to You under the Apache License, Version 2.0
+ * (the "License"); you may not use this file except in compliance with
+ * the License. You may obtain a copy of the License at
+ *
+ * http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing, software
+ * distributed under the License is distributed on an "AS IS" BASIS,
+ * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ * See the License for the specific language governing permissions and
+ * limitations under the License.
+ */
+
+package org.apache.shardingsphere.test.it.optimizer.converter.cases.jaxb;
+
+import lombok.Getter;
+import lombok.Setter;
+
+import javax.xml.bind.annotation.XmlAccessType;
+import javax.xml.bind.annotation.XmlAccessorType;
+import javax.xml.bind.annotation.XmlAttribute;
+
+/**
+ * SQL node converter test case.
+ */
+@XmlAccessorType(XmlAccessType.FIELD)
+@Getter
+@Setter
+public final class SQLNodeConverterTestCase {
+
+ @XmlAttribute(name = "sql-case-id")
+ private String sqlCaseId;
+
+ @XmlAttribute(name = "expected-sql")
+ private String expectedSQL;
+
+ @XmlAttribute(name = "db-types")
+ private String databaseTypes;
+
+ @XmlAttribute(name = "sql-case-types")
+ private String sqlCaseTypes;
+}
diff --git a/test/it/optimizer/src/test/resources/converter/select-aggregate.xml b/test/it/optimizer/src/test/resources/converter/select-aggregate.xml
new file mode 100644
index 00000000000..25aa93c76fe
--- /dev/null
+++ b/test/it/optimizer/src/test/resources/converter/select-aggregate.xml
@@ -0,0 +1,50 @@
+<?xml version="1.0" encoding="UTF-8"?>
+<!--
+ ~ Licensed to the Apache Software Foundation (ASF) under one or more
+ ~ contributor license agreements. See the NOTICE file distributed with
+ ~ this work for additional information regarding copyright ownership.
+ ~ The ASF licenses this file to You under the Apache License, Version 2.0
+ ~ (the "License"); you may not use this file except in compliance with
+ ~ the License. You may obtain a copy of the License at
+ ~
+ ~ http://www.apache.org/licenses/LICENSE-2.0
+ ~
+ ~ Unless required by applicable law or agreed to in writing, software
+ ~ distributed under the License is distributed on an "AS IS" BASIS,
+ ~ WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ ~ See the License for the specific language governing permissions and
+ ~ limitations under the License.
+ -->
+
+<sql-node-converter-test-cases>
+ <test-cases sql-case-id="select_sum" expected-sql="SELECT SUM(`user_id`) AS `user_id_sum` FROM `t_order`" db-types="MySQL" />
+ <test-cases sql-case-id="select_sum" expected-sql="SELECT SUM("user_id") AS "user_id_sum" FROM "t_order"" db-types="PostgreSQL,openGauss" />
+ <test-cases sql-case-id="select_sum_column" expected-sql="SELECT SUM(`t_order`.`user_id`) AS `user_id_sum` FROM `t_order`" db-types="MySQL" />
+ <test-cases sql-case-id="select_sum_column" expected-sql="SELECT SUM("t_order"."user_id") AS "user_id_sum" FROM "t_order"" db-types="PostgreSQL,openGauss" />
+ <test-cases sql-case-id="select_count" expected-sql="SELECT COUNT(*) AS `orders_count` FROM `t_order`" db-types="MySQL" />
+ <test-cases sql-case-id="select_count" expected-sql="SELECT COUNT(*) AS "orders_count" FROM "t_order"" db-types="PostgreSQL,openGauss" />
+ <test-cases sql-case-id="select_count_with_sub" expected-sql="SELECT COUNT(*) AS `orders_count` FROM `t_order` WHERE `order_id` > 1 - 1" db-types="MySQL" />
+ <test-cases sql-case-id="select_count_with_sub" expected-sql="SELECT COUNT(*) AS "orders_count" FROM "t_order" WHERE "order_id" > 1 - 1" db-types="PostgreSQL,openGauss" />
+ <test-cases sql-case-id="select_count_with_sub_with_whitespace" expected-sql="SELECT COUNT(*) AS `orders_count` FROM `t_order` WHERE `order_id` > 1 - 1" db-types="MySQL" />
+ <test-cases sql-case-id="select_count_with_sub_with_whitespace" expected-sql="SELECT COUNT(*) AS "orders_count" FROM "t_order" WHERE "order_id" > 1 - 1" db-types="PostgreSQL,openGauss" />
+ <test-cases sql-case-id="select_max" expected-sql="SELECT MAX(`user_id`) AS `max_user_id` FROM `t_order`" db-types="MySQL" />
+ <test-cases sql-case-id="select_max" expected-sql="SELECT MAX("user_id") AS "max_user_id" FROM "t_order"" db-types="PostgreSQL,openGauss" />
+ <test-cases sql-case-id="select_min" expected-sql="SELECT MIN(`user_id`) AS `min_user_id` FROM `t_order`" db-types="MySQL" />
+ <test-cases sql-case-id="select_min" expected-sql="SELECT MIN("user_id") AS "min_user_id" FROM "t_order"" db-types="PostgreSQL,openGauss" />
+ <test-cases sql-case-id="select_avg" expected-sql="SELECT AVG(`user_id`) AS `user_id_avg` FROM `t_order`" db-types="MySQL" />
+ <test-cases sql-case-id="select_avg" expected-sql="SELECT AVG("user_id") AS "user_id_avg" FROM "t_order"" db-types="PostgreSQL,openGauss" />
+ <test-cases sql-case-id="select_count_with_binding_tables_without_join" expected-sql="SELECT COUNT(*) AS `items_count` FROM `t_order` AS `o`, `t_order_item` AS `i` WHERE `o`.`user_id` = `i`.`user_id` AND `o`.`order_id` = `i`.`order_id` AND `o`.`user_id` IN (1, 2) AND `o`.`order_id` BETWEEN ASYMMETRIC 9 AND 10" db-types="MySQL" sql-case-types="LITERAL" />
+ <test-cases sql-case-id="select_count_with_binding_tables_without_join" expected-sql="SELECT COUNT(*) AS `items_count` FROM `t_order` AS `o`, `t_order_item` AS `i` WHERE `o`.`user_id` = `i`.`user_id` AND `o`.`order_id` = `i`.`order_id` AND `o`.`user_id` IN (?, ?) AND `o`.`order_id` BETWEEN ASYMMETRIC ? AND ?" db-types="MySQL" sql-case-types="PLACEHOLDER" />
+ <test-cases sql-case-id="select_count_with_binding_tables_without_join" expected-sql="SELECT COUNT(*) AS "items_count" FROM "t_order" AS "o", "t_order_item" AS "i" WHERE "o"."user_id" = "i"."user_id" AND "o"."order_id" = "i"."order_id" AND "o"."user_id" IN (1, 2) AND "o"."order_id" BETWEEN ASYMMETRIC 9 AND 10" db-typ [...]
+ <test-cases sql-case-id="select_count_with_binding_tables_without_join" expected-sql="SELECT COUNT(*) AS "items_count" FROM "t_order" AS "o", "t_order_item" AS "i" WHERE "o"."user_id" = "i"."user_id" AND "o"."order_id" = "i"."order_id" AND "o"."user_id" IN (?, ?) AND "o"."order_id" BETWEEN ASYMMETRIC ? AND ?" db-type [...]
+ <test-cases sql-case-id="select_count_with_binding_tables_with_join" expected-sql="SELECT COUNT(*) AS `items_count` FROM `t_order` AS `o` INNER JOIN `t_order_item` AS `i` ON `o`.`user_id` = `i`.`user_id` AND `o`.`order_id` = `i`.`order_id` WHERE `o`.`user_id` IN (1, 2) AND `o`.`order_id` BETWEEN ASYMMETRIC 9 AND 10" db-types="MySQL" sql-case-types="LITERAL" />
+ <test-cases sql-case-id="select_count_with_binding_tables_with_join" expected-sql="SELECT COUNT(*) AS `items_count` FROM `t_order` AS `o` INNER JOIN `t_order_item` AS `i` ON `o`.`user_id` = `i`.`user_id` AND `o`.`order_id` = `i`.`order_id` WHERE `o`.`user_id` IN (?, ?) AND `o`.`order_id` BETWEEN ASYMMETRIC ? AND ?" db-types="MySQL" sql-case-types="PLACEHOLDER" />
+ <test-cases sql-case-id="select_count_with_binding_tables_with_join" expected-sql="SELECT COUNT(*) AS "items_count" FROM "t_order" AS "o" INNER JOIN "t_order_item" AS "i" ON "o"."user_id" = "i"."user_id" AND "o"."order_id" = "i"."order_id" WHERE "o"."user_id" IN (1, 2) AND "o"."order_id" BETWEEN ASYMMETRIC 9 AND 10" [...]
+ <test-cases sql-case-id="select_count_with_binding_tables_with_join" expected-sql="SELECT COUNT(*) AS "items_count" FROM "t_order" AS "o" INNER JOIN "t_order_item" AS "i" ON "o"."user_id" = "i"."user_id" AND "o"."order_id" = "i"."order_id" WHERE "o"."user_id" IN (?, ?) AND "o"."order_id" BETWEEN ASYMMETRIC ? AND ?" d [...]
+ <test-cases sql-case-id="select_count_with_escape_character" expected-sql="SELECT COUNT(`order_id`) AS `orders_count` FROM `t_order`" db-types="MySQL" />
+ <test-cases sql-case-id="select_count_with_in_clause" expected-sql="SELECT COUNT(*) FROM `t_order` WHERE `last_value` IN (1, 2)" db-types="MySQL" sql-case-types="LITERAL" />
+ <test-cases sql-case-id="select_count_with_in_clause" expected-sql="SELECT COUNT(*) FROM `t_order` WHERE `last_value` IN (?, ?)" db-types="MySQL" sql-case-types="PLACEHOLDER" />
+ <test-cases sql-case-id="select_count_with_not_in_clause" expected-sql="SELECT COUNT(*) FROM `t_order` WHERE `category` IN (1, 2) AND `last_value` NOT IN (7, 9)" db-types="MySQL" sql-case-types="LITERAL" />
+ <test-cases sql-case-id="select_count_with_not_in_clause" expected-sql="SELECT COUNT(*) FROM `t_order` WHERE `category` IN (?, ?) AND `last_value` NOT IN (?, ?)" db-types="MySQL" sql-case-types="PLACEHOLDER" />
+ <test-cases sql-case-id="select_bit_xor" expected-sql="SELECT BIT_XOR(`user_id`) FROM `t_order`" db-types="MySQL" />
+</sql-node-converter-test-cases>
diff --git a/test/it/optimizer/src/test/resources/converter/select-join.xml b/test/it/optimizer/src/test/resources/converter/select-join.xml
new file mode 100644
index 00000000000..8aec090d367
--- /dev/null
+++ b/test/it/optimizer/src/test/resources/converter/select-join.xml
@@ -0,0 +1,48 @@
+<?xml version="1.0" encoding="UTF-8"?>
+<!--
+ ~ Licensed to the Apache Software Foundation (ASF) under one or more
+ ~ contributor license agreements. See the NOTICE file distributed with
+ ~ this work for additional information regarding copyright ownership.
+ ~ The ASF licenses this file to You under the Apache License, Version 2.0
+ ~ (the "License"); you may not use this file except in compliance with
+ ~ the License. You may obtain a copy of the License at
+ ~
+ ~ http://www.apache.org/licenses/LICENSE-2.0
+ ~
+ ~ Unless required by applicable law or agreed to in writing, software
+ ~ distributed under the License is distributed on an "AS IS" BASIS,
+ ~ WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ ~ See the License for the specific language governing permissions and
+ ~ limitations under the License.
+ -->
+
+<sql-node-converter-test-cases>
+ <test-cases sql-case-id="select_inner_join_related_with_alias" expected-sql="SELECT `i`.* FROM `t_order` AS `o` INNER JOIN `t_order_item` AS `i` ON `o`.`order_id` = `i`.`order_id` WHERE `o`.`order_id` = 1000" db-types="MySQL" sql-case-types="LITERAL" />
+ <test-cases sql-case-id="select_inner_join_related_with_alias" expected-sql="SELECT `i`.* FROM `t_order` AS `o` INNER JOIN `t_order_item` AS `i` ON `o`.`order_id` = `i`.`order_id` WHERE `o`.`order_id` = ?" db-types="MySQL" sql-case-types="PLACEHOLDER" />
+ <test-cases sql-case-id="select_inner_join_related_with_alias" expected-sql="SELECT "i".* FROM "t_order" AS "o" INNER JOIN "t_order_item" AS "i" ON "o"."order_id" = "i"."order_id" WHERE "o"."order_id" = 1000" db-types="PostgreSQL,openGauss" sql-case-types="LITERAL" />
+ <test-cases sql-case-id="select_inner_join_related_with_alias" expected-sql="SELECT "i".* FROM "t_order" AS "o" INNER JOIN "t_order_item" AS "i" ON "o"."order_id" = "i"."order_id" WHERE "o"."order_id" = ?" db-types="PostgreSQL,openGauss" sql-case-types="PLACEHOLDER" />
+ <test-cases sql-case-id="select_inner_join_related_with_name" expected-sql="SELECT `t_order_item`.* FROM `t_order` INNER JOIN `t_order_item` ON `t_order`.`order_id` = `t_order_item`.`order_id` WHERE `t_order`.`order_id` = 1000" db-types="MySQL" sql-case-types="LITERAL" />
+ <test-cases sql-case-id="select_inner_join_related_with_name" expected-sql="SELECT `t_order_item`.* FROM `t_order` INNER JOIN `t_order_item` ON `t_order`.`order_id` = `t_order_item`.`order_id` WHERE `t_order`.`order_id` = ?" db-types="MySQL" sql-case-types="PLACEHOLDER" />
+ <test-cases sql-case-id="select_inner_join_related_with_name" expected-sql="SELECT "t_order_item".* FROM "t_order" INNER JOIN "t_order_item" ON "t_order"."order_id" = "t_order_item"."order_id" WHERE "t_order"."order_id" = 1000" db-types="PostgreSQL,openGauss" sql-case-types="LITERAL" />
+ <test-cases sql-case-id="select_inner_join_related_with_name" expected-sql="SELECT "t_order_item".* FROM "t_order" INNER JOIN "t_order_item" ON "t_order"."order_id" = "t_order_item"."order_id" WHERE "t_order"."order_id" = ?" db-types="PostgreSQL,openGauss" sql-case-types="PLACEHOLDER" />
+ <test-cases sql-case-id="select_join_using" expected-sql="SELECT `i`.* FROM `t_order` AS `o` INNER JOIN `t_order_item` AS `i` USING (`order_id`) WHERE `o`.`order_id` = 1000" db-types="MySQL" sql-case-types="LITERAL" />
+ <test-cases sql-case-id="select_join_using" expected-sql="SELECT `i`.* FROM `t_order` AS `o` INNER JOIN `t_order_item` AS `i` USING (`order_id`) WHERE `o`.`order_id` = ?" db-types="MySQL" sql-case-types="PLACEHOLDER" />
+ <test-cases sql-case-id="select_join_using" expected-sql="SELECT "i".* FROM "t_order" AS "o" INNER JOIN "t_order_item" AS "i" USING ("order_id") WHERE "o"."order_id" = 1000" db-types="PostgreSQL,openGauss" sql-case-types="LITERAL" />
+ <test-cases sql-case-id="select_join_using" expected-sql="SELECT "i".* FROM "t_order" AS "o" INNER JOIN "t_order_item" AS "i" USING ("order_id") WHERE "o"."order_id" = ?" db-types="PostgreSQL,openGauss" sql-case-types="PLACEHOLDER" />
+ <test-cases sql-case-id="select_left_outer_join_related_with_alias" expected-sql="SELECT `d`.`department_id`, `e`.`last_name` FROM `departments` AS `d` LEFT JOIN `employees` AS `e` ON `d`.`department_id` = `e`.`department_id` ORDER BY `d`.`department_id`, `e`.`last_name`" db-types="MySQL" />
+ <test-cases sql-case-id="select_right_outer_join_related_with_alias" expected-sql="SELECT `d`.`department_id`, `e`.`last_name` FROM `departments` AS `d` RIGHT JOIN `employees` AS `e` ON `d`.`department_id` = `e`.`department_id` ORDER BY `d`.`department_id`, `e`.`last_name`" db-types="MySQL" />
+ <test-cases sql-case-id="select_natural_join" expected-sql="SELECT * FROM `t_order` AS `o` NATURAL INNER JOIN `t_order_item` AS `i` WHERE `o`.`order_id` = 1" db-types="MySQL" sql-case-types="LITERAL" />
+ <test-cases sql-case-id="select_natural_join" expected-sql="SELECT * FROM `t_order` AS `o` NATURAL INNER JOIN `t_order_item` AS `i` WHERE `o`.`order_id` = ?" db-types="MySQL" sql-case-types="PLACEHOLDER" />
+ <test-cases sql-case-id="select_natural_join" expected-sql="SELECT * FROM "t_order" AS "o" NATURAL INNER JOIN "t_order_item" AS "i" WHERE "o"."order_id" = 1" db-types="PostgreSQL,openGauss" sql-case-types="LITERAL" />
+ <test-cases sql-case-id="select_natural_join" expected-sql="SELECT * FROM "t_order" AS "o" NATURAL INNER JOIN "t_order_item" AS "i" WHERE "o"."order_id" = ?" db-types="PostgreSQL,openGauss" sql-case-types="PLACEHOLDER" />
+ <test-cases sql-case-id="select_natural_inner_join" expected-sql="SELECT * FROM `t_order` AS `o` NATURAL INNER JOIN `t_order_item` AS `i` WHERE `o`.`order_id` = 1" db-types="MySQL" sql-case-types="LITERAL" />
+ <test-cases sql-case-id="select_natural_inner_join" expected-sql="SELECT * FROM `t_order` AS `o` NATURAL INNER JOIN `t_order_item` AS `i` WHERE `o`.`order_id` = ?" db-types="MySQL" sql-case-types="PLACEHOLDER" />
+ <test-cases sql-case-id="select_natural_inner_join" expected-sql="SELECT * FROM "t_order" AS "o" NATURAL INNER JOIN "t_order_item" AS "i" WHERE "o"."order_id" = 1" db-types="PostgreSQL,openGauss" sql-case-types="LITERAL" />
+ <test-cases sql-case-id="select_natural_inner_join" expected-sql="SELECT * FROM "t_order" AS "o" NATURAL INNER JOIN "t_order_item" AS "i" WHERE "o"."order_id" = ?" db-types="PostgreSQL,openGauss" sql-case-types="PLACEHOLDER" />
+ <test-cases sql-case-id="select_natural_right_join" expected-sql="SELECT * FROM `t_order` AS `o` NATURAL RIGHT JOIN `t_order_item` AS `i` WHERE `o`.`order_id` = 1" db-types="MySQL" sql-case-types="LITERAL" />
+ <test-cases sql-case-id="select_natural_right_join" expected-sql="SELECT * FROM `t_order` AS `o` NATURAL RIGHT JOIN `t_order_item` AS `i` WHERE `o`.`order_id` = ?" db-types="MySQL" sql-case-types="PLACEHOLDER" />
+ <test-cases sql-case-id="select_natural_right_join" expected-sql="SELECT * FROM "t_order" AS "o" NATURAL RIGHT JOIN "t_order_item" AS "i" WHERE "o"."order_id" = 1" db-types="PostgreSQL,openGauss" sql-case-types="LITERAL" />
+ <test-cases sql-case-id="select_natural_right_join" expected-sql="SELECT * FROM "t_order" AS "o" NATURAL RIGHT JOIN "t_order_item" AS "i" WHERE "o"."order_id" = ?" db-types="PostgreSQL,openGauss" sql-case-types="PLACEHOLDER" />
+ <test-cases sql-case-id="select_natural_full_join" expected-sql="SELECT * FROM "t_order" AS "o" NATURAL FULL JOIN "t_order_item" AS "i" WHERE "o"."order_id" = 1" db-types="PostgreSQL,openGauss" sql-case-types="LITERAL" />
+ <test-cases sql-case-id="select_natural_full_join" expected-sql="SELECT * FROM "t_order" AS "o" NATURAL FULL JOIN "t_order_item" AS "i" WHERE "o"."order_id" = ?" db-types="PostgreSQL,openGauss" sql-case-types="PLACEHOLDER" />
+</sql-node-converter-test-cases>