You are viewing a plain text version of this content. The canonical link for it is here.
Posted to notifications@shardingsphere.apache.org by su...@apache.org on 2022/11/04 13:43:07 UTC
[shardingsphere] branch master updated: Add more test case for sql federation select cross database join statement (#21926)
This is an automated email from the ASF dual-hosted git repository.
sunnianjun 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 c37e9d697db Add more test case for sql federation select cross database join statement (#21926)
c37e9d697db is described below
commit c37e9d697db7fd23123482d87b766c0ca1cb3c00
Author: Zhengqiang Duan <du...@apache.org>
AuthorDate: Fri Nov 4 21:43:00 2022 +0800
Add more test case for sql federation select cross database join statement (#21926)
* Add more test case for sql federation select cross database join statement
* Add unsigned filed in ShardingSphereColumn and implement MySQL unsigned load
* add sql types init logic for mysql unsigned field
* add sql types init logic for mysql unsigned field
---
.../advanced/AdvancedSQLFederationExecutor.java | 12 +++++---
.../executor/FilterableTableScanExecutor.java | 11 +++----
.../executor/TranslatableTableScanExecutor.java | 7 ++---
.../metadata/filter/FilterableDatabase.java | 2 +-
.../metadata/filter/FilterableSchema.java | 15 ++++-----
.../optimizer/metadata/filter/FilterableTable.java | 5 ++-
.../translatable/FederationTranslatableTable.java | 5 ++-
.../translatable/TranslatableDatabase.java | 9 +++---
.../metadata/translatable/TranslatableSchema.java | 15 ++++-----
.../optimizer/util/SQLFederationDataTypeUtil.java | 31 ++++++++++++++++---
.../optimizer/SQLOptimizeEngineTest.java | 5 +--
.../cases/dql/dql-integration-test-cases.xml | 36 ++++++++++++++++++++++
.../data/actual/init-sql/h2/actual-db_1-init.sql | 2 +-
.../data/actual/init-sql/mysql/01-actual-init.sql | 2 +-
.../actual/init-sql/opengauss/01-actual-init.sql | 2 +-
.../data/actual/init-sql/oracle/01-actual-init.sql | 2 +-
.../actual/init-sql/postgresql/01-actual-init.sql | 2 +-
.../actual/init-sql/sqlserver/01-actual-init.sql | 2 +-
.../data/expected/init-sql/h2/01-expected-init.sql | 2 +-
.../expected/init-sql/mysql/01-expected-init.sql | 2 +-
.../init-sql/opengauss/01-expected-init.sql | 2 +-
.../expected/init-sql/oracle/01-expected-init.sql | 2 +-
.../init-sql/postgresql/01-expected-init.sql | 2 +-
.../init-sql/sqlserver/01-expected-init.sql | 2 +-
24 files changed, 123 insertions(+), 54 deletions(-)
diff --git a/kernel/sql-federation/executor/advanced/src/main/java/org/apache/shardingsphere/sqlfederation/advanced/AdvancedSQLFederationExecutor.java b/kernel/sql-federation/executor/advanced/src/main/java/org/apache/shardingsphere/sqlfederation/advanced/AdvancedSQLFederationExecutor.java
index 89c7de23454..34f2541e069 100644
--- a/kernel/sql-federation/executor/advanced/src/main/java/org/apache/shardingsphere/sqlfederation/advanced/AdvancedSQLFederationExecutor.java
+++ b/kernel/sql-federation/executor/advanced/src/main/java/org/apache/shardingsphere/sqlfederation/advanced/AdvancedSQLFederationExecutor.java
@@ -33,6 +33,7 @@ import org.apache.calcite.sql2rel.SqlToRelConverter;
import org.apache.shardingsphere.infra.binder.statement.SQLStatementContext;
import org.apache.shardingsphere.infra.binder.statement.dml.SelectStatementContext;
import org.apache.shardingsphere.infra.config.props.ConfigurationProperties;
+import org.apache.shardingsphere.infra.database.type.DatabaseType;
import org.apache.shardingsphere.infra.executor.sql.execute.engine.driver.jdbc.JDBCExecutionUnit;
import org.apache.shardingsphere.infra.executor.sql.execute.engine.driver.jdbc.JDBCExecutor;
import org.apache.shardingsphere.infra.executor.sql.execute.engine.driver.jdbc.JDBCExecutorCallback;
@@ -40,6 +41,7 @@ import org.apache.shardingsphere.infra.executor.sql.execute.result.ExecuteResult
import org.apache.shardingsphere.infra.executor.sql.prepare.driver.DriverExecutionPrepareEngine;
import org.apache.shardingsphere.infra.metadata.ShardingSphereMetaData;
import org.apache.shardingsphere.infra.metadata.data.ShardingSphereData;
+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.decorator.model.ShardingSphereSchema;
import org.apache.shardingsphere.infra.util.eventbus.EventBusContext;
@@ -109,8 +111,9 @@ public final class AdvancedSQLFederationExecutor implements SQLFederationExecuto
final JDBCExecutorCallback<? extends ExecuteResult> callback, final SQLFederationExecutorContext federationContext) {
SQLStatementContext<?> sqlStatementContext = federationContext.getQueryContext().getSqlStatementContext();
Preconditions.checkArgument(sqlStatementContext instanceof SelectStatementContext, "SQL statement context must be select statement context.");
- ShardingSphereSchema schema = federationContext.getDatabases().get(databaseName.toLowerCase()).getSchema(schemaName);
- AbstractSchema sqlFederationSchema = createSQLFederationSchema(prepareEngine, schema, callback, federationContext);
+ ShardingSphereDatabase database = federationContext.getDatabases().get(databaseName.toLowerCase());
+ ShardingSphereSchema schema = database.getSchema(schemaName);
+ AbstractSchema sqlFederationSchema = createSQLFederationSchema(prepareEngine, database.getProtocolType(), schema, callback, federationContext);
Map<String, Object> parameters = createParameters(federationContext.getQueryContext().getParameters());
resultSet = execute((SelectStatementContext) sqlStatementContext, schema, sqlFederationSchema, parameters);
return resultSet;
@@ -125,13 +128,14 @@ public final class AdvancedSQLFederationExecutor implements SQLFederationExecuto
return result;
}
- private AbstractSchema createSQLFederationSchema(final DriverExecutionPrepareEngine<JDBCExecutionUnit, Connection> prepareEngine, final ShardingSphereSchema schema,
+ private AbstractSchema createSQLFederationSchema(final DriverExecutionPrepareEngine<JDBCExecutionUnit, Connection> prepareEngine, final DatabaseType protocolType,
+ final ShardingSphereSchema schema,
final JDBCExecutorCallback<? extends ExecuteResult> callback, final SQLFederationExecutorContext federationContext) {
TableScanExecutorContext executorContext = new TableScanExecutorContext(databaseName, schemaName, props, federationContext);
// TODO replace FilterableTableScanExecutor with TranslatableTableScanExecutor
TableScanExecutor executor = new FilterableTableScanExecutor(prepareEngine, jdbcExecutor, callback, optimizerContext, globalRuleMetaData, executorContext, data, eventBusContext);
// TODO replace FilterableSchema with TranslatableSchema
- return new FilterableSchema(schemaName, schema, JAVA_TYPE_FACTORY, executor);
+ return new FilterableSchema(schemaName, schema, protocolType, JAVA_TYPE_FACTORY, executor);
}
@SuppressWarnings("unchecked")
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 5bfc92793ef..579878959a7 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
@@ -63,7 +63,6 @@ import org.apache.shardingsphere.infra.metadata.data.ShardingSphereSchemaData;
import org.apache.shardingsphere.infra.metadata.data.ShardingSphereTableData;
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.decorator.model.ShardingSphereSchema;
import org.apache.shardingsphere.infra.metadata.database.schema.decorator.model.ShardingSphereTable;
import org.apache.shardingsphere.infra.parser.sql.SQLStatementParserEngine;
import org.apache.shardingsphere.infra.util.eventbus.EventBusContext;
@@ -114,7 +113,7 @@ public final class FilterableTableScanExecutor implements TableScanExecutor {
private final TableScanExecutorContext executorContext;
- private final ShardingSphereData shardingSphereData;
+ private final ShardingSphereData data;
private final EventBusContext eventBusContext;
@@ -171,7 +170,7 @@ public final class FilterableTableScanExecutor implements TableScanExecutor {
}
private Enumerable<Object[]> executeByShardingSphereData(final String databaseName, final String schemaName, final ShardingSphereTable table) {
- Optional<ShardingSphereTableData> tableData = Optional.ofNullable(shardingSphereData.getDatabaseData().get(databaseName)).map(optional -> optional.getSchemaData().get(schemaName))
+ Optional<ShardingSphereTableData> tableData = Optional.ofNullable(data.getDatabaseData().get(databaseName)).map(optional -> optional.getSchemaData().get(schemaName))
.map(ShardingSphereSchemaData::getTableData).map(shardingSphereData -> shardingSphereData.get(table.getName()));
return tableData.map(this::createMemoryEnumerator).orElseGet(this::createEmptyEnumerable);
}
@@ -223,10 +222,10 @@ public final class FilterableTableScanExecutor implements TableScanExecutor {
String databaseName = executorContext.getDatabaseName();
String schemaName = executorContext.getSchemaName();
CalciteConnectionConfig connectionConfig = new CalciteConnectionConfigImpl(optimizerContext.getParserContexts().get(databaseName).getDialectProps());
- ShardingSphereSchema schema = executorContext.getFederationContext().getDatabases().get(databaseName).getSchema(schemaName);
+ ShardingSphereDatabase database = executorContext.getFederationContext().getDatabases().get(databaseName);
CalciteCatalogReader catalogReader = SQLFederationPlannerUtil.createCatalogReader(schemaName,
- new FilterableSchema(schemaName, schema, JAVA_TYPE_FACTORY, null), new JavaTypeFactoryImpl(), connectionConfig);
- RelOptCluster relOptCluster = RelOptCluster.create(SQLFederationPlannerUtil.createVolcanoPlanner(), new RexBuilder(new JavaTypeFactoryImpl()));
+ new FilterableSchema(schemaName, database.getSchema(schemaName), database.getProtocolType(), JAVA_TYPE_FACTORY, null), JAVA_TYPE_FACTORY, connectionConfig);
+ RelOptCluster relOptCluster = RelOptCluster.create(SQLFederationPlannerUtil.createVolcanoPlanner(), new RexBuilder(JAVA_TYPE_FACTORY));
RelBuilder builder = RelFactories.LOGICAL_BUILDER.create(relOptCluster, catalogReader).scan(table.getName()).filter(scanContext.getFilterValues());
if (null != scanContext.getProjects()) {
builder.project(createProjections(scanContext.getProjects(), builder, table.getColumnNames()));
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 c5851bb85fd..8bafadeae5f 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
@@ -65,7 +65,6 @@ import org.apache.shardingsphere.infra.metadata.data.ShardingSphereSchemaData;
import org.apache.shardingsphere.infra.metadata.data.ShardingSphereTableData;
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.decorator.model.ShardingSphereSchema;
import org.apache.shardingsphere.infra.metadata.database.schema.decorator.model.ShardingSphereTable;
import org.apache.shardingsphere.infra.parser.sql.SQLStatementParserEngine;
import org.apache.shardingsphere.infra.util.eventbus.EventBusContext;
@@ -225,10 +224,10 @@ public final class TranslatableTableScanExecutor implements TableScanExecutor {
String databaseName = executorContext.getDatabaseName();
String schemaName = executorContext.getSchemaName();
CalciteConnectionConfig connectionConfig = new CalciteConnectionConfigImpl(optimizerContext.getParserContexts().get(databaseName).getDialectProps());
- ShardingSphereSchema schema = executorContext.getFederationContext().getDatabases().get(databaseName).getSchema(schemaName);
+ ShardingSphereDatabase database = executorContext.getFederationContext().getDatabases().get(databaseName);
CalciteCatalogReader catalogReader = SQLFederationPlannerUtil.createCatalogReader(schemaName,
- new FilterableSchema(schemaName, schema, JAVA_TYPE_FACTORY, null), new JavaTypeFactoryImpl(), connectionConfig);
- RelOptCluster relOptCluster = RelOptCluster.create(SQLFederationPlannerUtil.createVolcanoPlanner(), new RexBuilder(new JavaTypeFactoryImpl()));
+ new FilterableSchema(schemaName, database.getSchema(schemaName), database.getProtocolType(), JAVA_TYPE_FACTORY, null), JAVA_TYPE_FACTORY, connectionConfig);
+ RelOptCluster relOptCluster = RelOptCluster.create(SQLFederationPlannerUtil.createVolcanoPlanner(), new RexBuilder(JAVA_TYPE_FACTORY));
RelBuilder builder = RelFactories.LOGICAL_BUILDER.create(relOptCluster, catalogReader).scan(table.getName());
if (null != scanContext.getFilterValues()) {
builder.filter(createFilters(scanContext.getFilterValues()));
diff --git a/kernel/sql-federation/optimizer/src/main/java/org/apache/shardingsphere/sqlfederation/optimizer/metadata/filter/FilterableDatabase.java b/kernel/sql-federation/optimizer/src/main/java/org/apache/shardingsphere/sqlfederation/optimizer/metadata/filter/FilterableDatabase.java
index 77546eccbfa..81630166c8f 100644
--- a/kernel/sql-federation/optimizer/src/main/java/org/apache/shardingsphere/sqlfederation/optimizer/metadata/filter/FilterableDatabase.java
+++ b/kernel/sql-federation/optimizer/src/main/java/org/apache/shardingsphere/sqlfederation/optimizer/metadata/filter/FilterableDatabase.java
@@ -47,7 +47,7 @@ public final class FilterableDatabase extends AbstractSchema {
private Map<String, Schema> createSubSchemaMap(final ShardingSphereDatabase database, final JavaTypeFactory javaTypeFactory, final TableScanExecutor executor) {
Map<String, Schema> result = new LinkedHashMap<>(database.getSchemas().size(), 1);
for (Entry<String, ShardingSphereSchema> entry : database.getSchemas().entrySet()) {
- result.put(entry.getKey(), new FilterableSchema(entry.getKey(), entry.getValue(), javaTypeFactory, executor));
+ result.put(entry.getKey(), new FilterableSchema(entry.getKey(), entry.getValue(), database.getProtocolType(), javaTypeFactory, executor));
}
return result;
}
diff --git a/kernel/sql-federation/optimizer/src/main/java/org/apache/shardingsphere/sqlfederation/optimizer/metadata/filter/FilterableSchema.java b/kernel/sql-federation/optimizer/src/main/java/org/apache/shardingsphere/sqlfederation/optimizer/metadata/filter/FilterableSchema.java
index b5cd1452329..3b16de735a8 100644
--- a/kernel/sql-federation/optimizer/src/main/java/org/apache/shardingsphere/sqlfederation/optimizer/metadata/filter/FilterableSchema.java
+++ b/kernel/sql-federation/optimizer/src/main/java/org/apache/shardingsphere/sqlfederation/optimizer/metadata/filter/FilterableSchema.java
@@ -24,6 +24,7 @@ import org.apache.calcite.rel.type.RelDataTypeImpl;
import org.apache.calcite.schema.Table;
import org.apache.calcite.schema.impl.AbstractSchema;
import org.apache.calcite.schema.impl.ViewTable;
+import org.apache.shardingsphere.infra.database.type.DatabaseType;
import org.apache.shardingsphere.infra.metadata.database.schema.decorator.model.ShardingSphereSchema;
import org.apache.shardingsphere.infra.metadata.database.schema.decorator.model.ShardingSphereTable;
import org.apache.shardingsphere.infra.metadata.database.schema.decorator.model.ShardingSphereView;
@@ -45,26 +46,26 @@ public final class FilterableSchema extends AbstractSchema {
private final Map<String, Table> tableMap;
- public FilterableSchema(final String schemaName, final ShardingSphereSchema schema, final JavaTypeFactory javaTypeFactory, final TableScanExecutor executor) {
+ public FilterableSchema(final String schemaName, final ShardingSphereSchema schema, final DatabaseType protocolType, final JavaTypeFactory javaTypeFactory, final TableScanExecutor executor) {
name = schemaName;
- tableMap = createTableMap(schema, javaTypeFactory, executor);
+ tableMap = createTableMap(schema, protocolType, javaTypeFactory, executor);
}
- private Map<String, Table> createTableMap(final ShardingSphereSchema schema, final JavaTypeFactory javaTypeFactory, final TableScanExecutor executor) {
+ private Map<String, Table> createTableMap(final ShardingSphereSchema schema, final DatabaseType protocolType, final JavaTypeFactory javaTypeFactory, final TableScanExecutor executor) {
Map<String, Table> result = new LinkedHashMap<>(schema.getTables().size(), 1);
for (ShardingSphereTable each : schema.getTables().values()) {
if (schema.containsView(each.getName())) {
- result.put(each.getName(), getViewTable(schema, each, javaTypeFactory));
+ result.put(each.getName(), getViewTable(schema, protocolType, each, javaTypeFactory));
} else {
// TODO implement table statistic logic after using custom operators
- result.put(each.getName(), new FilterableTable(each, executor, new FederationStatistic()));
+ result.put(each.getName(), new FilterableTable(each, executor, new FederationStatistic(), protocolType));
}
}
return result;
}
- private static ViewTable getViewTable(final ShardingSphereSchema schema, final ShardingSphereTable table, final JavaTypeFactory javaTypeFactory) {
- RelDataType relDataType = SQLFederationDataTypeUtil.createRelDataType(table, javaTypeFactory);
+ private static ViewTable getViewTable(final ShardingSphereSchema schema, final DatabaseType protocolType, final ShardingSphereTable table, final JavaTypeFactory javaTypeFactory) {
+ RelDataType relDataType = SQLFederationDataTypeUtil.createRelDataType(table, protocolType, javaTypeFactory);
ShardingSphereView view = schema.getView(table.getName());
return new ViewTable(javaTypeFactory.getJavaClass(relDataType), RelDataTypeImpl.proto(relDataType), view.getViewDefinition(), Collections.emptyList(), Collections.emptyList());
}
diff --git a/kernel/sql-federation/optimizer/src/main/java/org/apache/shardingsphere/sqlfederation/optimizer/metadata/filter/FilterableTable.java b/kernel/sql-federation/optimizer/src/main/java/org/apache/shardingsphere/sqlfederation/optimizer/metadata/filter/FilterableTable.java
index c7f95c2e899..66feafe5359 100644
--- a/kernel/sql-federation/optimizer/src/main/java/org/apache/shardingsphere/sqlfederation/optimizer/metadata/filter/FilterableTable.java
+++ b/kernel/sql-federation/optimizer/src/main/java/org/apache/shardingsphere/sqlfederation/optimizer/metadata/filter/FilterableTable.java
@@ -26,6 +26,7 @@ import org.apache.calcite.rex.RexNode;
import org.apache.calcite.schema.ProjectableFilterableTable;
import org.apache.calcite.schema.Statistic;
import org.apache.calcite.schema.impl.AbstractTable;
+import org.apache.shardingsphere.infra.database.type.DatabaseType;
import org.apache.shardingsphere.infra.metadata.database.schema.decorator.model.ShardingSphereTable;
import org.apache.shardingsphere.sqlfederation.optimizer.executor.FilterableScanNodeExecutorContext;
import org.apache.shardingsphere.sqlfederation.optimizer.executor.TableScanExecutor;
@@ -46,9 +47,11 @@ public final class FilterableTable extends AbstractTable implements ProjectableF
private final FederationStatistic statistic;
+ private final DatabaseType protocolType;
+
@Override
public RelDataType getRowType(final RelDataTypeFactory typeFactory) {
- return SQLFederationDataTypeUtil.createRelDataType(table, typeFactory);
+ return SQLFederationDataTypeUtil.createRelDataType(table, protocolType, typeFactory);
}
@Override
diff --git a/kernel/sql-federation/optimizer/src/main/java/org/apache/shardingsphere/sqlfederation/optimizer/metadata/translatable/FederationTranslatableTable.java b/kernel/sql-federation/optimizer/src/main/java/org/apache/shardingsphere/sqlfederation/optimizer/metadata/translatable/FederationTranslatableTable.java
index 567c9645db8..4d8bbaf1510 100644
--- a/kernel/sql-federation/optimizer/src/main/java/org/apache/shardingsphere/sqlfederation/optimizer/metadata/translatable/FederationTranslatableTable.java
+++ b/kernel/sql-federation/optimizer/src/main/java/org/apache/shardingsphere/sqlfederation/optimizer/metadata/translatable/FederationTranslatableTable.java
@@ -34,6 +34,7 @@ import org.apache.calcite.schema.Schemas;
import org.apache.calcite.schema.Statistic;
import org.apache.calcite.schema.TranslatableTable;
import org.apache.calcite.schema.impl.AbstractTable;
+import org.apache.shardingsphere.infra.database.type.DatabaseType;
import org.apache.shardingsphere.infra.metadata.database.schema.decorator.model.ShardingSphereTable;
import org.apache.shardingsphere.infra.util.exception.external.sql.type.generic.UnsupportedSQLOperationException;
import org.apache.shardingsphere.sqlfederation.optimizer.executor.TableScanExecutor;
@@ -55,9 +56,11 @@ public final class FederationTranslatableTable extends AbstractTable implements
private final FederationStatistic statistic;
+ private final DatabaseType protocolType;
+
@Override
public RelDataType getRowType(final RelDataTypeFactory typeFactory) {
- return SQLFederationDataTypeUtil.createRelDataType(table, typeFactory);
+ return SQLFederationDataTypeUtil.createRelDataType(table, protocolType, typeFactory);
}
/**
diff --git a/kernel/sql-federation/optimizer/src/main/java/org/apache/shardingsphere/sqlfederation/optimizer/metadata/translatable/TranslatableDatabase.java b/kernel/sql-federation/optimizer/src/main/java/org/apache/shardingsphere/sqlfederation/optimizer/metadata/translatable/TranslatableDatabase.java
index e602275f515..0e2190eadb3 100644
--- a/kernel/sql-federation/optimizer/src/main/java/org/apache/shardingsphere/sqlfederation/optimizer/metadata/translatable/TranslatableDatabase.java
+++ b/kernel/sql-federation/optimizer/src/main/java/org/apache/shardingsphere/sqlfederation/optimizer/metadata/translatable/TranslatableDatabase.java
@@ -20,6 +20,7 @@ package org.apache.shardingsphere.sqlfederation.optimizer.metadata.translatable;
import lombok.Getter;
import org.apache.calcite.schema.Schema;
import org.apache.calcite.schema.impl.AbstractSchema;
+import org.apache.shardingsphere.infra.database.type.DatabaseType;
import org.apache.shardingsphere.infra.metadata.database.ShardingSphereDatabase;
import org.apache.shardingsphere.infra.metadata.database.schema.decorator.model.ShardingSphereSchema;
import org.apache.shardingsphere.sqlfederation.optimizer.executor.TableScanExecutor;
@@ -38,15 +39,15 @@ public final class TranslatableDatabase extends AbstractSchema {
private final Map<String, Schema> subSchemaMap;
- public TranslatableDatabase(final ShardingSphereDatabase database, final TableScanExecutor executor) {
+ public TranslatableDatabase(final ShardingSphereDatabase database, final DatabaseType protocolType, final TableScanExecutor executor) {
name = database.getName();
- subSchemaMap = createSubSchemaMap(database, executor);
+ subSchemaMap = createSubSchemaMap(database, protocolType, executor);
}
- private Map<String, Schema> createSubSchemaMap(final ShardingSphereDatabase database, final TableScanExecutor executor) {
+ private Map<String, Schema> createSubSchemaMap(final ShardingSphereDatabase database, final DatabaseType protocolType, final TableScanExecutor executor) {
Map<String, Schema> result = new LinkedHashMap<>(database.getSchemas().size(), 1);
for (Entry<String, ShardingSphereSchema> entry : database.getSchemas().entrySet()) {
- result.put(entry.getKey(), new TranslatableSchema(entry.getKey(), entry.getValue(), null, executor));
+ result.put(entry.getKey(), new TranslatableSchema(entry.getKey(), entry.getValue(), protocolType, null, executor));
}
return result;
}
diff --git a/kernel/sql-federation/optimizer/src/main/java/org/apache/shardingsphere/sqlfederation/optimizer/metadata/translatable/TranslatableSchema.java b/kernel/sql-federation/optimizer/src/main/java/org/apache/shardingsphere/sqlfederation/optimizer/metadata/translatable/TranslatableSchema.java
index 77dbe31e9b5..3392f654b7a 100644
--- a/kernel/sql-federation/optimizer/src/main/java/org/apache/shardingsphere/sqlfederation/optimizer/metadata/translatable/TranslatableSchema.java
+++ b/kernel/sql-federation/optimizer/src/main/java/org/apache/shardingsphere/sqlfederation/optimizer/metadata/translatable/TranslatableSchema.java
@@ -24,6 +24,7 @@ import org.apache.calcite.rel.type.RelDataTypeImpl;
import org.apache.calcite.schema.Table;
import org.apache.calcite.schema.impl.AbstractSchema;
import org.apache.calcite.schema.impl.ViewTable;
+import org.apache.shardingsphere.infra.database.type.DatabaseType;
import org.apache.shardingsphere.infra.metadata.database.schema.decorator.model.ShardingSphereSchema;
import org.apache.shardingsphere.infra.metadata.database.schema.decorator.model.ShardingSphereTable;
import org.apache.shardingsphere.infra.metadata.database.schema.decorator.model.ShardingSphereView;
@@ -45,26 +46,26 @@ public final class TranslatableSchema extends AbstractSchema {
private final Map<String, Table> tableMap;
- public TranslatableSchema(final String schemaName, final ShardingSphereSchema schema, final JavaTypeFactory javaTypeFactory, final TableScanExecutor executor) {
+ public TranslatableSchema(final String schemaName, final ShardingSphereSchema schema, final DatabaseType protocolType, final JavaTypeFactory javaTypeFactory, final TableScanExecutor executor) {
name = schemaName;
- tableMap = createTableMap(schema, javaTypeFactory, executor);
+ tableMap = createTableMap(schema, protocolType, javaTypeFactory, executor);
}
- private Map<String, Table> createTableMap(final ShardingSphereSchema schema, final JavaTypeFactory javaTypeFactory, final TableScanExecutor executor) {
+ private Map<String, Table> createTableMap(final ShardingSphereSchema schema, final DatabaseType protocolType, final JavaTypeFactory javaTypeFactory, final TableScanExecutor executor) {
Map<String, Table> result = new LinkedHashMap<>(schema.getTables().size(), 1);
for (ShardingSphereTable each : schema.getTables().values()) {
if (schema.containsView(each.getName())) {
- result.put(each.getName(), getViewTable(schema, each, javaTypeFactory));
+ result.put(each.getName(), getViewTable(schema, each, protocolType, javaTypeFactory));
} else {
// TODO implement table statistic logic after using custom operators
- result.put(each.getName(), new FederationTranslatableTable(each, executor, new FederationStatistic()));
+ result.put(each.getName(), new FederationTranslatableTable(each, executor, new FederationStatistic(), protocolType));
}
}
return result;
}
- private static ViewTable getViewTable(final ShardingSphereSchema schema, final ShardingSphereTable table, final JavaTypeFactory javaTypeFactory) {
- RelDataType relDataType = SQLFederationDataTypeUtil.createRelDataType(table, javaTypeFactory);
+ private static ViewTable getViewTable(final ShardingSphereSchema schema, final ShardingSphereTable table, final DatabaseType protocolType, final JavaTypeFactory javaTypeFactory) {
+ RelDataType relDataType = SQLFederationDataTypeUtil.createRelDataType(table, protocolType, javaTypeFactory);
ShardingSphereView view = schema.getView(table.getName());
return new ViewTable(javaTypeFactory.getJavaClass(relDataType), RelDataTypeImpl.proto(relDataType), view.getViewDefinition(), Collections.emptyList(), Collections.emptyList());
}
diff --git a/kernel/sql-federation/optimizer/src/main/java/org/apache/shardingsphere/sqlfederation/optimizer/util/SQLFederationDataTypeUtil.java b/kernel/sql-federation/optimizer/src/main/java/org/apache/shardingsphere/sqlfederation/optimizer/util/SQLFederationDataTypeUtil.java
index ca55a923cb7..485b23ce1ad 100644
--- a/kernel/sql-federation/optimizer/src/main/java/org/apache/shardingsphere/sqlfederation/optimizer/util/SQLFederationDataTypeUtil.java
+++ b/kernel/sql-federation/optimizer/src/main/java/org/apache/shardingsphere/sqlfederation/optimizer/util/SQLFederationDataTypeUtil.java
@@ -23,9 +23,14 @@ import org.apache.calcite.avatica.SqlType;
import org.apache.calcite.rel.type.RelDataType;
import org.apache.calcite.rel.type.RelDataTypeFactory;
import org.apache.calcite.rel.type.RelDataTypeFactory.Builder;
+import org.apache.shardingsphere.infra.database.type.DatabaseType;
+import org.apache.shardingsphere.infra.database.type.dialect.MySQLDatabaseType;
import org.apache.shardingsphere.infra.metadata.database.schema.decorator.model.ShardingSphereColumn;
import org.apache.shardingsphere.infra.metadata.database.schema.decorator.model.ShardingSphereTable;
+import java.math.BigInteger;
+import java.sql.Types;
+
/**
* SQL federation data type util.
*/
@@ -34,22 +39,38 @@ public final class SQLFederationDataTypeUtil {
/**
* Create rel data type.
- *
+ *
* @param table ShardingSphere table
+ * @param protocolType protocol type
* @param typeFactory type factory
* @return rel data type
*/
- public static RelDataType createRelDataType(final ShardingSphereTable table, final RelDataTypeFactory typeFactory) {
+ public static RelDataType createRelDataType(final ShardingSphereTable table, final DatabaseType protocolType, final RelDataTypeFactory typeFactory) {
Builder fieldInfoBuilder = typeFactory.builder();
for (ShardingSphereColumn each : table.getColumns().values()) {
- fieldInfoBuilder.add(each.getName(), getRelDataType(each, typeFactory));
+ fieldInfoBuilder.add(each.getName(), getRelDataType(protocolType, each, typeFactory));
}
return fieldInfoBuilder.build();
}
- private static RelDataType getRelDataType(final ShardingSphereColumn column, final RelDataTypeFactory typeFactory) {
- Class<?> sqlTypeClass = SqlType.valueOf(column.getDataType()).clazz;
+ private static RelDataType getRelDataType(final DatabaseType protocolType, final ShardingSphereColumn column, final RelDataTypeFactory typeFactory) {
+ Class<?> sqlTypeClass = getSqlTypeClass(protocolType, column);
RelDataType javaType = typeFactory.createJavaType(sqlTypeClass);
return typeFactory.createTypeWithNullability(javaType, true);
}
+
+ private static Class<?> getSqlTypeClass(final DatabaseType protocolType, final ShardingSphereColumn column) {
+ if (protocolType instanceof MySQLDatabaseType) {
+ if (Types.TINYINT == column.getDataType() || Types.SMALLINT == column.getDataType()) {
+ return Integer.class;
+ }
+ if (Types.INTEGER == column.getDataType()) {
+ return column.isUnsigned() ? Long.class : Integer.class;
+ }
+ if (Types.BIGINT == column.getDataType()) {
+ return column.isUnsigned() ? BigInteger.class : Long.class;
+ }
+ }
+ return SqlType.valueOf(column.getDataType()).clazz;
+ }
}
diff --git a/kernel/sql-federation/optimizer/src/test/java/org/apache/shardingsphere/sqlfederation/optimizer/SQLOptimizeEngineTest.java b/kernel/sql-federation/optimizer/src/test/java/org/apache/shardingsphere/sqlfederation/optimizer/SQLOptimizeEngineTest.java
index 455c60af3a4..90b70b5fe7a 100644
--- a/kernel/sql-federation/optimizer/src/test/java/org/apache/shardingsphere/sqlfederation/optimizer/SQLOptimizeEngineTest.java
+++ b/kernel/sql-federation/optimizer/src/test/java/org/apache/shardingsphere/sqlfederation/optimizer/SQLOptimizeEngineTest.java
@@ -26,6 +26,7 @@ import org.apache.calcite.rel.type.RelDataTypeFactory;
import org.apache.calcite.rex.RexBuilder;
import org.apache.calcite.sql.validate.SqlValidator;
import org.apache.calcite.sql2rel.SqlToRelConverter;
+import org.apache.shardingsphere.infra.database.type.DatabaseType;
import org.apache.shardingsphere.infra.database.type.DatabaseTypeEngine;
import org.apache.shardingsphere.infra.database.type.dialect.H2DatabaseType;
import org.apache.shardingsphere.infra.metadata.database.schema.decorator.model.ShardingSphereColumn;
@@ -126,9 +127,9 @@ public final class SQLOptimizeEngineTest {
private SqlToRelConverter createSqlToRelConverter(final ShardingSphereSchema schema) {
CalciteConnectionConfig connectionConfig = new CalciteConnectionConfigImpl(new Properties());
RelDataTypeFactory relDataTypeFactory = new JavaTypeFactoryImpl();
- TranslatableSchema federationSchema = new TranslatableSchema(SCHEMA_NAME, schema, new JavaTypeFactoryImpl(), null);
+ DatabaseType databaseType = DatabaseTypeEngine.getDatabaseType("H2");
+ TranslatableSchema federationSchema = new TranslatableSchema(SCHEMA_NAME, schema, databaseType, new JavaTypeFactoryImpl(), null);
CalciteCatalogReader catalogReader = SQLFederationPlannerUtil.createCatalogReader(SCHEMA_NAME, federationSchema, relDataTypeFactory, 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(SQLParserRule.class), databaseType, false);
diff --git a/test/integration-test/test-suite/src/test/resources/cases/dql/dql-integration-test-cases.xml b/test/integration-test/test-suite/src/test/resources/cases/dql/dql-integration-test-cases.xml
index 89b2c41e806..18719cbe0aa 100644
--- a/test/integration-test/test-suite/src/test/resources/cases/dql/dql-integration-test-cases.xml
+++ b/test/integration-test/test-suite/src/test/resources/cases/dql/dql-integration-test-cases.xml
@@ -685,6 +685,22 @@
<assertion parameters="10:int" expected-data-source-name="read_dataset" />
</test-case>
+ <test-case sql="SELECT * FROM t_order o CROSS JOIN t_merchant m WHERE o.user_id = ? ORDER BY o.order_id, 7 LIMIT 10, 10" db-types="openGauss" scenario-types="db">
+ <assertion parameters="10:int" expected-data-source-name="read_dataset" />
+ </test-case>
+
+ <test-case sql="SELECT * FROM t_order o LEFT JOIN t_merchant m ON o.merchant_id = m.merchant_id WHERE o.user_id = ? ORDER BY o.order_id, 7" db-types="MySQL,PostgreSQL,openGauss" scenario-types="db">
+ <assertion parameters="10:int" expected-data-source-name="read_dataset" />
+ </test-case>
+
+ <test-case sql="SELECT * FROM t_order o RIGHT JOIN t_merchant m ON o.order_id = m.merchant_id WHERE m.country_id = 1 ORDER BY m.merchant_id, 7" db-types="MySQL,PostgreSQL,openGauss" scenario-types="db">
+ <assertion expected-data-source-name="read_dataset" />
+ </test-case>
+
+ <test-case sql="SELECT * FROM t_order o FULL JOIN t_merchant m ON o.order_id = m.merchant_id where o.user_id = ? OR m.country_id = 1 ORDER BY o.order_id, 7" db-types="PostgreSQL,openGauss" scenario-types="db">
+ <assertion parameters="10:int" expected-data-source-name="read_dataset" />
+ </test-case>
+
<test-case sql="SELECT * FROM t_product p INNER JOIN t_product_detail d ON p.product_id = d.product_id WHERE p.product_id > ? ORDER BY p.product_id DESC" db-types="MySQL,PostgreSQL,openGauss" scenario-types="db">
<assertion parameters="10:int" expected-data-source-name="read_dataset" />
</test-case>
@@ -693,6 +709,26 @@
<assertion parameters="10:int" expected-data-source-name="read_dataset" />
</test-case>
+ <test-case sql="SELECT MIN(d.detail_id), MIN(p.category_id), p.product_id FROM t_product p INNER JOIN t_product_detail d ON p.product_id = d.product_id WHERE p.product_id = ? GROUP BY p.product_id" db-types="MySQL,PostgreSQL,openGauss" scenario-types="db">
+ <assertion parameters="10:int" expected-data-source-name="read_dataset" />
+ </test-case>
+
+ <test-case sql="SELECT * FROM t_product p CROSS JOIN t_product_detail d WHERE p.product_id = ? ORDER BY d.product_id, 7 LIMIT 10, 10" db-types="openGauss" scenario-types="db">
+ <assertion parameters="10:int" expected-data-source-name="read_dataset" />
+ </test-case>
+
+ <test-case sql="SELECT * FROM t_product p LEFT JOIN t_product_detail d ON d.product_id = p.product_id WHERE p.category_id = ? ORDER BY p.product_id, 7" db-types="MySQL,PostgreSQL,openGauss" scenario-types="db">
+ <assertion parameters="10:int" expected-data-source-name="read_dataset" />
+ </test-case>
+
+ <test-case sql="SELECT * FROM t_product p RIGHT JOIN t_product_detail d ON d.product_id = p.product_id WHERE d.detail_id = ? ORDER BY d.product_id, 7" db-types="MySQL,PostgreSQL,openGauss" scenario-types="db">
+ <assertion parameters="10:int" expected-data-source-name="read_dataset" />
+ </test-case>
+
+ <test-case sql="SELECT * FROM t_product p FULL JOIN t_product_detail d ON d.product_id = p.product_id WHERE d.detail_id = ? OR p.category_id = 10 ORDER BY d.product_id, 7" db-types="PostgreSQL,openGauss" scenario-types="db">
+ <assertion parameters="10:int" expected-data-source-name="read_dataset" />
+ </test-case>
+
<test-case sql="SELECT o.order_id, o.user_id, o.status, o.merchant_id, (SELECT t.merchant_name FROM t_merchant t WHERE t.merchant_id = o.merchant_id) AS merchant_name FROM t_order o WHERE o.order_id = ?" db-types="MySQL,PostgreSQL,openGauss" scenario-types="db">
<assertion parameters="1000:long" expected-data-source-name="read_dataset" />
</test-case>
diff --git a/test/integration-test/test-suite/src/test/resources/env/scenario/db/data/actual/init-sql/h2/actual-db_1-init.sql b/test/integration-test/test-suite/src/test/resources/env/scenario/db/data/actual/init-sql/h2/actual-db_1-init.sql
index 134c3ff6e50..8112557037a 100644
--- a/test/integration-test/test-suite/src/test/resources/env/scenario/db/data/actual/init-sql/h2/actual-db_1-init.sql
+++ b/test/integration-test/test-suite/src/test/resources/env/scenario/db/data/actual/init-sql/h2/actual-db_1-init.sql
@@ -21,7 +21,7 @@ DROP TABLE IF EXISTS t_order_item_federate;
DROP TABLE IF EXISTS t_order_federate_sharding;
DROP TABLE IF EXISTS t_order_item_federate_sharding;
-CREATE TABLE t_merchant (merchant_id INT PRIMARY KEY, country_id INT NOT NULL, merchant_name VARCHAR(50) NOT NULL, business_code VARCHAR(50) NOT NULL, telephone CHAR(11) NOT NULL, creation_date DATE NOT NULL);
+CREATE TABLE t_merchant (merchant_id INT PRIMARY KEY, country_id SMALLINT NOT NULL, merchant_name VARCHAR(50) NOT NULL, business_code VARCHAR(50) NOT NULL, telephone CHAR(11) NOT NULL, creation_date DATE NOT NULL);
CREATE TABLE t_product_detail (detail_id INT PRIMARY KEY, product_id INT NOT NULL, description VARCHAR(50) NOT NULL, creation_date DATE NOT NULL);
CREATE TABLE t_order_item_federate (item_id INT NOT NULL, order_id INT NOT NULL, user_id INT NOT NULL, status VARCHAR(45) NULL, PRIMARY KEY (item_id));
CREATE TABLE t_order_federate_sharding (order_id_sharding INT NOT NULL, user_id INT NOT NULL, status VARCHAR(45) NULL, PRIMARY KEY (order_id_sharding));
diff --git a/test/integration-test/test-suite/src/test/resources/env/scenario/db/data/actual/init-sql/mysql/01-actual-init.sql b/test/integration-test/test-suite/src/test/resources/env/scenario/db/data/actual/init-sql/mysql/01-actual-init.sql
index 578cacb8bd1..70ba2a12f0e 100644
--- a/test/integration-test/test-suite/src/test/resources/env/scenario/db/data/actual/init-sql/mysql/01-actual-init.sql
+++ b/test/integration-test/test-suite/src/test/resources/env/scenario/db/data/actual/init-sql/mysql/01-actual-init.sql
@@ -55,7 +55,7 @@ CREATE INDEX order_index_t_order ON db_0.t_order (order_id);
CREATE TABLE db_1.t_order (order_id BIGINT PRIMARY KEY, user_id INT NOT NULL, status VARCHAR(50) NOT NULL, merchant_id INT NOT NULL, remark VARCHAR(50) NOT NULL, creation_date DATE NOT NULL);
CREATE TABLE db_1.t_order_item (item_id BIGINT PRIMARY KEY, order_id BIGINT NOT NULL, user_id INT NOT NULL, product_id INT NOT NULL, quantity INT NOT NULL, creation_date DATE NOT NULL);
-CREATE TABLE db_1.t_merchant (merchant_id INT PRIMARY KEY, country_id INT NOT NULL, merchant_name VARCHAR(50) NOT NULL, business_code VARCHAR(50) NOT NULL, telephone CHAR(11) NOT NULL, creation_date DATE NOT NULL);
+CREATE TABLE db_1.t_merchant (merchant_id INT PRIMARY KEY, country_id SMALLINT NOT NULL, merchant_name VARCHAR(50) NOT NULL, business_code VARCHAR(50) NOT NULL, telephone CHAR(11) NOT NULL, creation_date DATE NOT NULL);
CREATE TABLE db_1.t_product_detail (detail_id INT PRIMARY KEY, product_id INT NOT NULL, description VARCHAR(50) NOT NULL, creation_date DATE NOT NULL);
CREATE TABLE db_1.t_product_category (category_id INT PRIMARY KEY, category_name VARCHAR(50) NOT NULL, parent_id INT NOT NULL, level TINYINT NOT NULL, creation_date DATE NOT NULL);
CREATE TABLE db_1.t_country (country_id SMALLINT PRIMARY KEY, country_name VARCHAR(50), continent_name VARCHAR(50), creation_date DATE NOT NULL);
diff --git a/test/integration-test/test-suite/src/test/resources/env/scenario/db/data/actual/init-sql/opengauss/01-actual-init.sql b/test/integration-test/test-suite/src/test/resources/env/scenario/db/data/actual/init-sql/opengauss/01-actual-init.sql
index 571e56d5656..f7a3d91cecb 100644
--- a/test/integration-test/test-suite/src/test/resources/env/scenario/db/data/actual/init-sql/opengauss/01-actual-init.sql
+++ b/test/integration-test/test-suite/src/test/resources/env/scenario/db/data/actual/init-sql/opengauss/01-actual-init.sql
@@ -79,7 +79,7 @@ DROP TABLE IF EXISTS t_order_item_federate_sharding;
CREATE TABLE t_order (order_id BIGINT PRIMARY KEY, user_id INT NOT NULL, status VARCHAR(50) NOT NULL, merchant_id INT NOT NULL, remark VARCHAR(50) NOT NULL, creation_date DATE NOT NULL);
CREATE TABLE t_order_item (item_id BIGINT PRIMARY KEY, order_id BIGINT NOT NULL, user_id INT NOT NULL, product_id INT NOT NULL, quantity INT NOT NULL, creation_date DATE NOT NULL);
-CREATE TABLE t_merchant (merchant_id INT PRIMARY KEY, country_id INT NOT NULL, merchant_name VARCHAR(50) NOT NULL, business_code VARCHAR(50) NOT NULL, telephone CHAR(11) NOT NULL, creation_date DATE NOT NULL);
+CREATE TABLE t_merchant (merchant_id INT PRIMARY KEY, country_id SMALLINT NOT NULL, merchant_name VARCHAR(50) NOT NULL, business_code VARCHAR(50) NOT NULL, telephone CHAR(11) NOT NULL, creation_date DATE NOT NULL);
CREATE TABLE t_product_detail (detail_id INT PRIMARY KEY, product_id INT NOT NULL, description VARCHAR(50) NOT NULL, creation_date DATE NOT NULL);
CREATE TABLE t_product_category (category_id INT PRIMARY KEY, category_name VARCHAR(50) NOT NULL, parent_id INT NOT NULL, level TINYINT NOT NULL, creation_date DATE NOT NULL);
CREATE TABLE t_country (country_id SMALLINT PRIMARY KEY, country_name VARCHAR(50), continent_name VARCHAR(50), creation_date DATE NOT NULL);
diff --git a/test/integration-test/test-suite/src/test/resources/env/scenario/db/data/actual/init-sql/oracle/01-actual-init.sql b/test/integration-test/test-suite/src/test/resources/env/scenario/db/data/actual/init-sql/oracle/01-actual-init.sql
index 5ca004bdee2..2a044abf7d3 100644
--- a/test/integration-test/test-suite/src/test/resources/env/scenario/db/data/actual/init-sql/oracle/01-actual-init.sql
+++ b/test/integration-test/test-suite/src/test/resources/env/scenario/db/data/actual/init-sql/oracle/01-actual-init.sql
@@ -52,7 +52,7 @@ CREATE INDEX order_index_t_order ON db_0.t_order (order_id);
CREATE TABLE db_1.t_order (order_id BIGINT PRIMARY KEY, user_id INT NOT NULL, status VARCHAR(50) NOT NULL, merchant_id INT NOT NULL, remark VARCHAR(50) NOT NULL, creation_date DATE NOT NULL);
CREATE TABLE db_1.t_order_item (item_id BIGINT PRIMARY KEY, order_id BIGINT NOT NULL, user_id INT NOT NULL, product_id INT NOT NULL, quantity INT NOT NULL, creation_date DATE NOT NULL);
-CREATE TABLE db_1.t_merchant (merchant_id INT PRIMARY KEY, country_id INT NOT NULL, merchant_name VARCHAR(50) NOT NULL, business_code VARCHAR(50) NOT NULL, telephone CHAR(11) NOT NULL, creation_date DATE NOT NULL);
+CREATE TABLE db_1.t_merchant (merchant_id INT PRIMARY KEY, country_id SMALLINT NOT NULL, merchant_name VARCHAR(50) NOT NULL, business_code VARCHAR(50) NOT NULL, telephone CHAR(11) NOT NULL, creation_date DATE NOT NULL);
CREATE TABLE db_1.t_product_detail (detail_id INT PRIMARY KEY, product_id INT NOT NULL, description VARCHAR(50) NOT NULL, creation_date DATE NOT NULL);
CREATE TABLE db_1.t_product_category (category_id INT PRIMARY KEY, category_name VARCHAR(50) NOT NULL, parent_id INT NOT NULL, level TINYINT NOT NULL, creation_date DATE NOT NULL);
CREATE TABLE db_1.t_country (country_id SMALLINT PRIMARY KEY, country_name VARCHAR(50), continent_name VARCHAR(50), creation_date DATE NOT NULL);
diff --git a/test/integration-test/test-suite/src/test/resources/env/scenario/db/data/actual/init-sql/postgresql/01-actual-init.sql b/test/integration-test/test-suite/src/test/resources/env/scenario/db/data/actual/init-sql/postgresql/01-actual-init.sql
index c6b7781932b..7bccb1569e9 100644
--- a/test/integration-test/test-suite/src/test/resources/env/scenario/db/data/actual/init-sql/postgresql/01-actual-init.sql
+++ b/test/integration-test/test-suite/src/test/resources/env/scenario/db/data/actual/init-sql/postgresql/01-actual-init.sql
@@ -79,7 +79,7 @@ DROP TABLE IF EXISTS t_order_item_federate_sharding;
CREATE TABLE t_order (order_id BIGINT PRIMARY KEY, user_id INT NOT NULL, status VARCHAR(50) NOT NULL, merchant_id INT NOT NULL, remark VARCHAR(50) NOT NULL, creation_date DATE NOT NULL);
CREATE TABLE t_order_item (item_id BIGINT PRIMARY KEY, order_id BIGINT NOT NULL, user_id INT NOT NULL, product_id INT NOT NULL, quantity INT NOT NULL, creation_date DATE NOT NULL);
-CREATE TABLE t_merchant (merchant_id INT PRIMARY KEY, country_id INT NOT NULL, merchant_name VARCHAR(50) NOT NULL, business_code VARCHAR(50) NOT NULL, telephone CHAR(11) NOT NULL, creation_date DATE NOT NULL);
+CREATE TABLE t_merchant (merchant_id INT PRIMARY KEY, country_id SMALLINT NOT NULL, merchant_name VARCHAR(50) NOT NULL, business_code VARCHAR(50) NOT NULL, telephone CHAR(11) NOT NULL, creation_date DATE NOT NULL);
CREATE TABLE t_product_detail (detail_id INT PRIMARY KEY, product_id INT NOT NULL, description VARCHAR(50) NOT NULL, creation_date DATE NOT NULL);
CREATE TABLE t_product_category (category_id INT PRIMARY KEY, category_name VARCHAR(50) NOT NULL, parent_id INT NOT NULL, level SMALLINT NOT NULL, creation_date DATE NOT NULL);
CREATE TABLE t_country (country_id SMALLINT PRIMARY KEY, country_name VARCHAR(50), continent_name VARCHAR(50), creation_date DATE NOT NULL);
diff --git a/test/integration-test/test-suite/src/test/resources/env/scenario/db/data/actual/init-sql/sqlserver/01-actual-init.sql b/test/integration-test/test-suite/src/test/resources/env/scenario/db/data/actual/init-sql/sqlserver/01-actual-init.sql
index cdb39ba2e02..037719ead1b 100644
--- a/test/integration-test/test-suite/src/test/resources/env/scenario/db/data/actual/init-sql/sqlserver/01-actual-init.sql
+++ b/test/integration-test/test-suite/src/test/resources/env/scenario/db/data/actual/init-sql/sqlserver/01-actual-init.sql
@@ -52,7 +52,7 @@ CREATE INDEX order_index_t_order ON db_0.t_order (order_id);
CREATE TABLE db_1.t_order (order_id BIGINT PRIMARY KEY, user_id INT NOT NULL, status VARCHAR(50) NOT NULL, merchant_id INT NOT NULL, remark VARCHAR(50) NOT NULL, creation_date DATE NOT NULL);
CREATE TABLE db_1.t_order_item (item_id BIGINT PRIMARY KEY, order_id BIGINT NOT NULL, user_id INT NOT NULL, product_id INT NOT NULL, quantity INT NOT NULL, creation_date DATE NOT NULL);
-CREATE TABLE db_1.t_merchant (merchant_id INT PRIMARY KEY, country_id INT NOT NULL, merchant_name VARCHAR(50) NOT NULL, business_code VARCHAR(50) NOT NULL, telephone CHAR(11) NOT NULL, creation_date DATE NOT NULL);
+CREATE TABLE db_1.t_merchant (merchant_id INT PRIMARY KEY, country_id SMALLINT NOT NULL, merchant_name VARCHAR(50) NOT NULL, business_code VARCHAR(50) NOT NULL, telephone CHAR(11) NOT NULL, creation_date DATE NOT NULL);
CREATE TABLE db_1.t_product_detail (detail_id INT PRIMARY KEY, product_id INT NOT NULL, description VARCHAR(50) NOT NULL, creation_date DATE NOT NULL);
CREATE TABLE db_1.t_product_category (category_id INT PRIMARY KEY, category_name VARCHAR(50) NOT NULL, parent_id INT NOT NULL, level TINYINT NOT NULL, creation_date DATE NOT NULL);
CREATE TABLE db_1.t_country (country_id SMALLINT PRIMARY KEY, country_name VARCHAR(50), continent_name VARCHAR(50), creation_date DATE NOT NULL);
diff --git a/test/integration-test/test-suite/src/test/resources/env/scenario/db/data/expected/init-sql/h2/01-expected-init.sql b/test/integration-test/test-suite/src/test/resources/env/scenario/db/data/expected/init-sql/h2/01-expected-init.sql
index 39e0f356211..e22624e9838 100644
--- a/test/integration-test/test-suite/src/test/resources/env/scenario/db/data/expected/init-sql/h2/01-expected-init.sql
+++ b/test/integration-test/test-suite/src/test/resources/env/scenario/db/data/expected/init-sql/h2/01-expected-init.sql
@@ -38,7 +38,7 @@ DROP TABLE IF EXISTS t_order_item_federate_sharding;
CREATE TABLE t_order (order_id BIGINT PRIMARY KEY, user_id INT NOT NULL, status VARCHAR(50) NOT NULL, merchant_id INT NOT NULL, remark VARCHAR(50) NOT NULL, creation_date DATE NOT NULL);
CREATE TABLE t_order_item (item_id BIGINT PRIMARY KEY, order_id BIGINT NOT NULL, user_id INT NOT NULL, product_id INT NOT NULL, quantity INT NOT NULL, creation_date DATE NOT NULL);
CREATE TABLE t_user (user_id INT PRIMARY KEY, user_name VARCHAR(50) NOT NULL, password VARCHAR(50) NOT NULL, email VARCHAR(50) NOT NULL, telephone CHAR(11) NOT NULL, creation_date DATE NOT NULL);
-CREATE TABLE t_merchant (merchant_id INT PRIMARY KEY, country_id INT NOT NULL, merchant_name VARCHAR(50) NOT NULL, business_code VARCHAR(50) NOT NULL, telephone CHAR(11) NOT NULL, creation_date DATE NOT NULL);
+CREATE TABLE t_merchant (merchant_id INT PRIMARY KEY, country_id SMALLINT NOT NULL, merchant_name VARCHAR(50) NOT NULL, business_code VARCHAR(50) NOT NULL, telephone CHAR(11) NOT NULL, creation_date DATE NOT NULL);
CREATE TABLE t_product (product_id INT PRIMARY KEY, product_name VARCHAR(50) NOT NULL, category_id INT NOT NULL, price DECIMAL NOT NULL, status VARCHAR(50) NOT NULL, creation_date DATE NOT NULL);
CREATE TABLE t_product_detail (detail_id INT PRIMARY KEY, product_id INT NOT NULL, description VARCHAR(50) NOT NULL, creation_date DATE NOT NULL);
CREATE TABLE t_product_category (category_id INT PRIMARY KEY, category_name VARCHAR(50) NOT NULL, parent_id INT NOT NULL, level TINYINT NOT NULL, creation_date DATE NOT NULL);
diff --git a/test/integration-test/test-suite/src/test/resources/env/scenario/db/data/expected/init-sql/mysql/01-expected-init.sql b/test/integration-test/test-suite/src/test/resources/env/scenario/db/data/expected/init-sql/mysql/01-expected-init.sql
index 81d4b5c21e3..d5109523e4c 100644
--- a/test/integration-test/test-suite/src/test/resources/env/scenario/db/data/expected/init-sql/mysql/01-expected-init.sql
+++ b/test/integration-test/test-suite/src/test/resources/env/scenario/db/data/expected/init-sql/mysql/01-expected-init.sql
@@ -24,7 +24,7 @@ CREATE DATABASE expected_dataset;
CREATE TABLE expected_dataset.t_order (order_id BIGINT PRIMARY KEY, user_id INT NOT NULL, status VARCHAR(50) NOT NULL, merchant_id INT NOT NULL, remark VARCHAR(50) NOT NULL, creation_date DATE NOT NULL);
CREATE TABLE expected_dataset.t_order_item (item_id BIGINT PRIMARY KEY, order_id BIGINT NOT NULL, user_id INT NOT NULL, product_id INT NOT NULL, quantity INT NOT NULL, creation_date DATE NOT NULL);
CREATE TABLE expected_dataset.t_user (user_id INT PRIMARY KEY, user_name VARCHAR(50) NOT NULL, password VARCHAR(50) NOT NULL, email VARCHAR(50) NOT NULL, telephone CHAR(11) NOT NULL, creation_date DATE NOT NULL);
-CREATE TABLE expected_dataset.t_merchant (merchant_id INT PRIMARY KEY, country_id INT NOT NULL, merchant_name VARCHAR(50) NOT NULL, business_code VARCHAR(50) NOT NULL, telephone CHAR(11) NOT NULL, creation_date DATE NOT NULL);
+CREATE TABLE expected_dataset.t_merchant (merchant_id INT PRIMARY KEY, country_id SMALLINT NOT NULL, merchant_name VARCHAR(50) NOT NULL, business_code VARCHAR(50) NOT NULL, telephone CHAR(11) NOT NULL, creation_date DATE NOT NULL);
CREATE TABLE expected_dataset.t_product (product_id INT PRIMARY KEY, product_name VARCHAR(50) NOT NULL, category_id INT NOT NULL, price DECIMAL NOT NULL, status VARCHAR(50) NOT NULL, creation_date DATE NOT NULL);
CREATE TABLE expected_dataset.t_product_detail (detail_id INT PRIMARY KEY, product_id INT NOT NULL, description VARCHAR(50) NOT NULL, creation_date DATE NOT NULL);
CREATE TABLE expected_dataset.t_product_category (category_id INT PRIMARY KEY, category_name VARCHAR(50) NOT NULL, parent_id INT NOT NULL, level TINYINT NOT NULL, creation_date DATE NOT NULL);
diff --git a/test/integration-test/test-suite/src/test/resources/env/scenario/db/data/expected/init-sql/opengauss/01-expected-init.sql b/test/integration-test/test-suite/src/test/resources/env/scenario/db/data/expected/init-sql/opengauss/01-expected-init.sql
index c9f19e2d282..40e34709777 100644
--- a/test/integration-test/test-suite/src/test/resources/env/scenario/db/data/expected/init-sql/opengauss/01-expected-init.sql
+++ b/test/integration-test/test-suite/src/test/resources/env/scenario/db/data/expected/init-sql/opengauss/01-expected-init.sql
@@ -45,7 +45,7 @@ DROP TABLE IF EXISTS t_order_item_federate_sharding;
CREATE TABLE t_order (order_id BIGINT PRIMARY KEY, user_id INT NOT NULL, status VARCHAR(50) NOT NULL, merchant_id INT NOT NULL, remark VARCHAR(50) NOT NULL, creation_date DATE NOT NULL);
CREATE TABLE t_order_item (item_id BIGINT PRIMARY KEY, order_id BIGINT NOT NULL, user_id INT NOT NULL, product_id INT NOT NULL, quantity INT NOT NULL, creation_date DATE NOT NULL);
CREATE TABLE t_user (user_id INT PRIMARY KEY, user_name VARCHAR(50) NOT NULL, password VARCHAR(50) NOT NULL, email VARCHAR(50) NOT NULL, telephone CHAR(11) NOT NULL, creation_date DATE NOT NULL);
-CREATE TABLE t_merchant (merchant_id INT PRIMARY KEY, country_id INT NOT NULL, merchant_name VARCHAR(50) NOT NULL, business_code VARCHAR(50) NOT NULL, telephone CHAR(11) NOT NULL, creation_date DATE NOT NULL);
+CREATE TABLE t_merchant (merchant_id INT PRIMARY KEY, country_id SMALLINT NOT NULL, merchant_name VARCHAR(50) NOT NULL, business_code VARCHAR(50) NOT NULL, telephone CHAR(11) NOT NULL, creation_date DATE NOT NULL);
CREATE TABLE t_product (product_id INT PRIMARY KEY, product_name VARCHAR(50) NOT NULL, category_id INT NOT NULL, price DECIMAL NOT NULL, status VARCHAR(50) NOT NULL, creation_date DATE NOT NULL);
CREATE TABLE t_product_detail (detail_id INT PRIMARY KEY, product_id INT NOT NULL, description VARCHAR(50) NOT NULL, creation_date DATE NOT NULL);
CREATE TABLE t_product_category (category_id INT PRIMARY KEY, category_name VARCHAR(50) NOT NULL, parent_id INT NOT NULL, level TINYINT NOT NULL, creation_date DATE NOT NULL);
diff --git a/test/integration-test/test-suite/src/test/resources/env/scenario/db/data/expected/init-sql/oracle/01-expected-init.sql b/test/integration-test/test-suite/src/test/resources/env/scenario/db/data/expected/init-sql/oracle/01-expected-init.sql
index eaa19cff511..63f7cc50dd3 100644
--- a/test/integration-test/test-suite/src/test/resources/env/scenario/db/data/expected/init-sql/oracle/01-expected-init.sql
+++ b/test/integration-test/test-suite/src/test/resources/env/scenario/db/data/expected/init-sql/oracle/01-expected-init.sql
@@ -21,7 +21,7 @@ CREATE SCHEMA expected_dataset;
CREATE TABLE expected_dataset.t_order (order_id BIGINT PRIMARY KEY, user_id INT NOT NULL, status VARCHAR(50) NOT NULL, merchant_id INT NOT NULL, remark VARCHAR(50) NOT NULL, creation_date DATE NOT NULL);
CREATE TABLE expected_dataset.t_order_item (item_id BIGINT PRIMARY KEY, order_id BIGINT NOT NULL, user_id INT NOT NULL, product_id INT NOT NULL, quantity INT NOT NULL, creation_date DATE NOT NULL);
CREATE TABLE expected_dataset.t_user (user_id INT PRIMARY KEY, user_name VARCHAR(50) NOT NULL, password VARCHAR(50) NOT NULL, email VARCHAR(50) NOT NULL, telephone CHAR(11) NOT NULL, creation_date DATE NOT NULL);
-CREATE TABLE expected_dataset.t_merchant (merchant_id INT PRIMARY KEY, country_id INT NOT NULL, merchant_name VARCHAR(50) NOT NULL, business_code VARCHAR(50) NOT NULL, telephone CHAR(11) NOT NULL, creation_date DATE NOT NULL);
+CREATE TABLE expected_dataset.t_merchant (merchant_id INT PRIMARY KEY, country_id SMALLINT NOT NULL, merchant_name VARCHAR(50) NOT NULL, business_code VARCHAR(50) NOT NULL, telephone CHAR(11) NOT NULL, creation_date DATE NOT NULL);
CREATE TABLE expected_dataset.t_product (product_id INT PRIMARY KEY, product_name VARCHAR(50) NOT NULL, category_id INT NOT NULL, price DECIMAL NOT NULL, status VARCHAR(50) NOT NULL, creation_date DATE NOT NULL);
CREATE TABLE expected_dataset.t_product_detail (detail_id INT PRIMARY KEY, product_id INT NOT NULL, description VARCHAR(50) NOT NULL, creation_date DATE NOT NULL);
CREATE TABLE expected_dataset.t_product_category (category_id INT PRIMARY KEY, category_name VARCHAR(50) NOT NULL, parent_id INT NOT NULL, level TINYINT NOT NULL, creation_date DATE NOT NULL);
diff --git a/test/integration-test/test-suite/src/test/resources/env/scenario/db/data/expected/init-sql/postgresql/01-expected-init.sql b/test/integration-test/test-suite/src/test/resources/env/scenario/db/data/expected/init-sql/postgresql/01-expected-init.sql
index 9122af7c0cc..167eadd8062 100644
--- a/test/integration-test/test-suite/src/test/resources/env/scenario/db/data/expected/init-sql/postgresql/01-expected-init.sql
+++ b/test/integration-test/test-suite/src/test/resources/env/scenario/db/data/expected/init-sql/postgresql/01-expected-init.sql
@@ -45,7 +45,7 @@ DROP TABLE IF EXISTS t_order_item_federate_sharding;
CREATE TABLE t_order (order_id BIGINT PRIMARY KEY, user_id INT NOT NULL, status VARCHAR(50) NOT NULL, merchant_id INT NOT NULL, remark VARCHAR(50) NOT NULL, creation_date DATE NOT NULL);
CREATE TABLE t_order_item (item_id BIGINT PRIMARY KEY, order_id BIGINT NOT NULL, user_id INT NOT NULL, product_id INT NOT NULL, quantity INT NOT NULL, creation_date DATE NOT NULL);
CREATE TABLE t_user (user_id INT PRIMARY KEY, user_name VARCHAR(50) NOT NULL, password VARCHAR(50) NOT NULL, email VARCHAR(50) NOT NULL, telephone CHAR(11) NOT NULL, creation_date DATE NOT NULL);
-CREATE TABLE t_merchant (merchant_id INT PRIMARY KEY, country_id INT NOT NULL, merchant_name VARCHAR(50) NOT NULL, business_code VARCHAR(50) NOT NULL, telephone CHAR(11) NOT NULL, creation_date DATE NOT NULL);
+CREATE TABLE t_merchant (merchant_id INT PRIMARY KEY, country_id SMALLINT NOT NULL, merchant_name VARCHAR(50) NOT NULL, business_code VARCHAR(50) NOT NULL, telephone CHAR(11) NOT NULL, creation_date DATE NOT NULL);
CREATE TABLE t_product (product_id INT PRIMARY KEY, product_name VARCHAR(50) NOT NULL, category_id INT NOT NULL, price DECIMAL NOT NULL, status VARCHAR(50) NOT NULL, creation_date DATE NOT NULL);
CREATE TABLE t_product_detail (detail_id INT PRIMARY KEY, product_id INT NOT NULL, description VARCHAR(50) NOT NULL, creation_date DATE NOT NULL);
CREATE TABLE t_product_category (category_id INT PRIMARY KEY, category_name VARCHAR(50) NOT NULL, parent_id INT NOT NULL, level SMALLINT NOT NULL, creation_date DATE NOT NULL);
diff --git a/test/integration-test/test-suite/src/test/resources/env/scenario/db/data/expected/init-sql/sqlserver/01-expected-init.sql b/test/integration-test/test-suite/src/test/resources/env/scenario/db/data/expected/init-sql/sqlserver/01-expected-init.sql
index 18540073275..98d2b4f7a2f 100644
--- a/test/integration-test/test-suite/src/test/resources/env/scenario/db/data/expected/init-sql/sqlserver/01-expected-init.sql
+++ b/test/integration-test/test-suite/src/test/resources/env/scenario/db/data/expected/init-sql/sqlserver/01-expected-init.sql
@@ -21,7 +21,7 @@ CREATE DATABASE expected_dataset;
CREATE TABLE expected_dataset.t_order (order_id BIGINT PRIMARY KEY, user_id INT NOT NULL, status VARCHAR(50) NOT NULL, merchant_id INT NOT NULL, remark VARCHAR(50) NOT NULL, creation_date DATE NOT NULL);
CREATE TABLE expected_dataset.t_order_item (item_id BIGINT PRIMARY KEY, order_id BIGINT NOT NULL, user_id INT NOT NULL, product_id INT NOT NULL, quantity INT NOT NULL, creation_date DATE NOT NULL);
CREATE TABLE expected_dataset.t_user (user_id INT PRIMARY KEY, user_name VARCHAR(50) NOT NULL, password VARCHAR(50) NOT NULL, email VARCHAR(50) NOT NULL, telephone CHAR(11) NOT NULL, creation_date DATE NOT NULL);
-CREATE TABLE expected_dataset.t_merchant (merchant_id INT PRIMARY KEY, country_id INT NOT NULL, merchant_name VARCHAR(50) NOT NULL, business_code VARCHAR(50) NOT NULL, telephone CHAR(11) NOT NULL, creation_date DATE NOT NULL);
+CREATE TABLE expected_dataset.t_merchant (merchant_id INT PRIMARY KEY, country_id SMALLINT NOT NULL, merchant_name VARCHAR(50) NOT NULL, business_code VARCHAR(50) NOT NULL, telephone CHAR(11) NOT NULL, creation_date DATE NOT NULL);
CREATE TABLE expected_dataset.t_product (product_id INT PRIMARY KEY, product_name VARCHAR(50) NOT NULL, category_id INT NOT NULL, price DECIMAL NOT NULL, status VARCHAR(50) NOT NULL, creation_date DATE NOT NULL);
CREATE TABLE expected_dataset.t_product_detail (detail_id INT PRIMARY KEY, product_id INT NOT NULL, description VARCHAR(50) NOT NULL, creation_date DATE NOT NULL);
CREATE TABLE expected_dataset.t_product_category (category_id INT PRIMARY KEY, category_name VARCHAR(50) NOT NULL, parent_id INT NOT NULL, level TINYINT NOT NULL, creation_date DATE NOT NULL);