You are viewing a plain text version of this content. The canonical link for it is here.
Posted to notifications@shardingsphere.apache.org by tu...@apache.org on 2022/11/03 01:35:25 UTC
[shardingsphere] branch master updated: Add select from view test case for sql federation (#21883)
This is an automated email from the ASF dual-hosted git repository.
tuichenchuxin pushed a commit to branch master
in repository https://gitbox.apache.org/repos/asf/shardingsphere.git
The following commit(s) were added to refs/heads/master by this push:
new 5a766b080f5 Add select from view test case for sql federation (#21883)
5a766b080f5 is described below
commit 5a766b080f5e50dc50abab90b7978fe523cba9af
Author: Zhengqiang Duan <du...@apache.org>
AuthorDate: Thu Nov 3 09:35:19 2022 +0800
Add select from view test case for sql federation (#21883)
* Add select from view test case for sql federation
* format code
* Modify integration test case
* fix integration test
* fix integration test
* fix integration test
* fix integration test
* fix integration test
* modify count single table result
* add init sql for h2
* update integration test
* update integration test
* update integration test
* update integration test
* fix unit test
* update expected file
* update expected file
* adjust ShardingUnicastRoutingEngine logic
* update expected result
---
.../type/unicast/ShardingUnicastRoutingEngine.java | 21 +++--
.../rql/rule/SingleTableQueryResultSet.java | 3 +-
.../distsql/rql/SingleTableQueryResultSetTest.java | 30 +++----
.../atomic/storage/DockerStorageContainer.java | 6 +-
.../{expected-init.sql => 01-expected-init.sql} | 0
.../test/integration/engine/BaseITCase.java | 38 ++++++++-
.../cases/dql/dql-integration-test-cases.xml | 91 +++++++++++++++++-----
.../rdl_empty_rules/alter_single_table_rules.xml | 2 +-
.../cluster/alter_single_table_rules.xml | 2 +-
.../cluster/create_single_table_rules.xml | 4 +-
.../cluster/drop_single_table_rules.xml | 2 +-
.../rdl_empty_rules/create_single_table_rules.xml | 4 +-
.../rdl_empty_rules/drop_single_table_rules.xml | 2 +-
.../standalone/alter_single_table_rules.xml | 2 +-
.../standalone/create_single_table_rules.xml | 4 +-
.../standalone/drop_single_table_rules.xml | 2 +-
.../rql/dataset/db/count_single_table_rule.xml | 2 +-
.../cases/rql/dataset/db/show_single_tables.xml | 12 ++-
.../show_single_tables.xml | 8 +-
.../show_single_tables.xml | 4 +-
.../show_single_tables.xml | 6 +-
.../sharding_and_encrypt/show_single_tables.xml | 4 +-
.../actual/init-sql/h2/actual-logic_db-init.sql | 26 +++++++
.../actual/init-sql/mysql/actual-logic_db-init.sql | 26 +++++++
.../init-sql/opengauss/actual-logic_db-init.sql | 26 +++++++
.../init-sql/postgresql/actual-logic_db-init.sql | 26 +++++++
.../data/expected/init-sql/h2/01-expected-init.sql | 8 ++
.../expected/init-sql/mysql/01-expected-init.sql | 4 +
.../init-sql/opengauss/01-expected-init.sql | 8 ++
.../init-sql/postgresql/01-expected-init.sql | 8 ++
.../{expected-init.sql => 01-expected-init.sql} | 0
.../{expected-init.sql => 01-expected-init.sql} | 0
.../{expected-init.sql => 01-expected-init.sql} | 0
.../{expected-init.sql => 01-expected-init.sql} | 0
.../{expected-init.sql => 01-expected-init.sql} | 0
.../{expected-init.sql => 01-expected-init.sql} | 0
.../{expected-init.sql => 01-expected-init.sql} | 0
.../{expected-init.sql => 01-expected-init.sql} | 0
.../{expected-init.sql => 01-expected-init.sql} | 0
.../{expected-init.sql => 01-expected-init.sql} | 0
.../{expected-init.sql => 01-expected-init.sql} | 0
.../{expected-init.sql => 01-expected-init.sql} | 0
.../{expected-init.sql => 01-expected-init.sql} | 0
.../{expected-init.sql => 01-expected-init.sql} | 0
.../{expected-init.sql => 01-expected-init.sql} | 0
.../{expected-init.sql => 01-expected-init.sql} | 0
.../{expected-init.sql => 01-expected-init.sql} | 0
.../{expected-init.sql => 01-expected-init.sql} | 0
.../{expected-init.sql => 01-expected-init.sql} | 0
.../{expected-init.sql => 01-expected-init.sql} | 0
.../{expected-init.sql => 01-expected-init.sql} | 0
.../{expected-init.sql => 01-expected-init.sql} | 0
.../{expected-init.sql => 01-expected-init.sql} | 0
.../{expected-init.sql => 01-expected-init.sql} | 0
.../{expected-init.sql => 01-expected-init.sql} | 0
.../{expected-init.sql => 01-expected-init.sql} | 0
.../{expected-init.sql => 01-expected-init.sql} | 0
.../{expected-init.sql => 01-expected-init.sql} | 0
58 files changed, 308 insertions(+), 73 deletions(-)
diff --git a/features/sharding/core/src/main/java/org/apache/shardingsphere/sharding/route/engine/type/unicast/ShardingUnicastRoutingEngine.java b/features/sharding/core/src/main/java/org/apache/shardingsphere/sharding/route/engine/type/unicast/ShardingUnicastRoutingEngine.java
index c8fbce0b406..39924042dd8 100644
--- a/features/sharding/core/src/main/java/org/apache/shardingsphere/sharding/route/engine/type/unicast/ShardingUnicastRoutingEngine.java
+++ b/features/sharding/core/src/main/java/org/apache/shardingsphere/sharding/route/engine/type/unicast/ShardingUnicastRoutingEngine.java
@@ -20,6 +20,9 @@ package org.apache.shardingsphere.sharding.route.engine.type.unicast;
import com.google.common.collect.Sets;
import lombok.RequiredArgsConstructor;
import org.apache.shardingsphere.infra.binder.statement.SQLStatementContext;
+import org.apache.shardingsphere.infra.binder.statement.ddl.AlterViewStatementContext;
+import org.apache.shardingsphere.infra.binder.statement.ddl.CreateViewStatementContext;
+import org.apache.shardingsphere.infra.binder.statement.ddl.DropViewStatementContext;
import org.apache.shardingsphere.infra.binder.type.CursorAvailable;
import org.apache.shardingsphere.infra.context.ConnectionContext;
import org.apache.shardingsphere.infra.datanode.DataNode;
@@ -34,7 +37,7 @@ import org.apache.shardingsphere.sharding.rule.TableRule;
import java.util.ArrayList;
import java.util.Collection;
import java.util.Collections;
-import java.util.HashSet;
+import java.util.LinkedHashSet;
import java.util.List;
import java.util.Set;
import java.util.concurrent.ThreadLocalRandom;
@@ -55,9 +58,7 @@ public final class ShardingUnicastRoutingEngine implements ShardingRouteEngine {
@Override
public RouteContext route(final ShardingRule shardingRule) {
RouteContext result = new RouteContext();
- String dataSourceName = sqlStatementContext instanceof CursorAvailable
- ? shardingRule.getDataSourceNames().iterator().next()
- : getRandomDataSourceName(shardingRule.getDataSourceNames());
+ String dataSourceName = getDataSourceName(shardingRule.getDataSourceNames());
RouteMapper dataSourceMapper = new RouteMapper(dataSourceName, dataSourceName);
if (shardingRule.isAllBroadcastTables(logicTables)) {
List<RouteMapper> tableMappers = new ArrayList<>(logicTables.size());
@@ -82,6 +83,14 @@ public final class ShardingUnicastRoutingEngine implements ShardingRouteEngine {
return result;
}
+ private String getDataSourceName(final Collection<String> dataSourceNames) {
+ return sqlStatementContext instanceof CursorAvailable || isViewStatementContext(sqlStatementContext) ? dataSourceNames.iterator().next() : getRandomDataSourceName(dataSourceNames);
+ }
+
+ private boolean isViewStatementContext(final SQLStatementContext<?> sqlStatementContext) {
+ return sqlStatementContext instanceof CreateViewStatementContext || sqlStatementContext instanceof AlterViewStatementContext || sqlStatementContext instanceof DropViewStatementContext;
+ }
+
private void routeWithMultipleTables(final RouteContext routeContext, final ShardingRule shardingRule) {
List<RouteMapper> tableMappers = new ArrayList<>(logicTables.size());
Set<String> availableDataSourceNames = Collections.emptySet();
@@ -91,7 +100,7 @@ public final class ShardingUnicastRoutingEngine implements ShardingRouteEngine {
DataNode dataNode = tableRule.getActualDataNodes().get(0);
tableMappers.add(new RouteMapper(each, dataNode.getTableName()));
Set<String> currentDataSourceNames = tableRule.getActualDataNodes().stream().map(DataNode::getDataSourceName).collect(
- Collectors.toCollection(() -> new HashSet<>(tableRule.getActualDataSourceNames().size())));
+ Collectors.toCollection(() -> new LinkedHashSet<>(tableRule.getActualDataSourceNames().size())));
if (first) {
availableDataSourceNames = currentDataSourceNames;
first = false;
@@ -102,7 +111,7 @@ public final class ShardingUnicastRoutingEngine implements ShardingRouteEngine {
if (availableDataSourceNames.isEmpty()) {
throw new DataSourceIntersectionNotFoundException(logicTables);
}
- String dataSourceName = getRandomDataSourceName(availableDataSourceNames);
+ String dataSourceName = getDataSourceName(availableDataSourceNames);
routeContext.getRouteUnits().add(new RouteUnit(new RouteMapper(dataSourceName, dataSourceName), tableMappers));
}
diff --git a/proxy/backend/src/main/java/org/apache/shardingsphere/proxy/backend/handler/distsql/rql/rule/SingleTableQueryResultSet.java b/proxy/backend/src/main/java/org/apache/shardingsphere/proxy/backend/handler/distsql/rql/rule/SingleTableQueryResultSet.java
index 750ba705163..2f82827bf87 100644
--- a/proxy/backend/src/main/java/org/apache/shardingsphere/proxy/backend/handler/distsql/rql/rule/SingleTableQueryResultSet.java
+++ b/proxy/backend/src/main/java/org/apache/shardingsphere/proxy/backend/handler/distsql/rql/rule/SingleTableQueryResultSet.java
@@ -27,6 +27,7 @@ import org.apache.shardingsphere.sql.parser.sql.common.statement.SQLStatement;
import java.util.Arrays;
import java.util.Collection;
import java.util.Collections;
+import java.util.Comparator;
import java.util.Iterator;
import java.util.Objects;
import java.util.stream.Collectors;
@@ -47,7 +48,7 @@ public final class SingleTableQueryResultSet implements DatabaseDistSQLResultSet
if (null != showSingleTableStatement.getTableName()) {
singleTableRules = singleTableRules.filter(each -> showSingleTableStatement.getTableName().equals(each.getTableName()));
}
- data = singleTableRules.collect(Collectors.toList()).iterator();
+ data = singleTableRules.sorted(Comparator.comparing(DataNode::getTableName)).collect(Collectors.toList()).iterator();
}
@Override
diff --git a/proxy/backend/src/test/java/org/apache/shardingsphere/proxy/backend/handler/distsql/rql/SingleTableQueryResultSetTest.java b/proxy/backend/src/test/java/org/apache/shardingsphere/proxy/backend/handler/distsql/rql/SingleTableQueryResultSetTest.java
index f3e78b7fcb3..e5477b586ef 100644
--- a/proxy/backend/src/test/java/org/apache/shardingsphere/proxy/backend/handler/distsql/rql/SingleTableQueryResultSetTest.java
+++ b/proxy/backend/src/test/java/org/apache/shardingsphere/proxy/backend/handler/distsql/rql/SingleTableQueryResultSetTest.java
@@ -71,13 +71,13 @@ public final class SingleTableQueryResultSetTest {
Collection<Object> actual = resultSet.getRowData();
assertThat(actual.size(), is(2));
Iterator<Object> rowData = actual.iterator();
- assertThat(rowData.next(), is("t_order_item"));
- assertThat(rowData.next(), is("ds_2"));
+ assertThat(rowData.next(), is("t_order"));
+ assertThat(rowData.next(), is("ds_1"));
resultSet.next();
actual = resultSet.getRowData();
rowData = actual.iterator();
- assertThat(rowData.next(), is("t_order"));
- assertThat(rowData.next(), is("ds_1"));
+ assertThat(rowData.next(), is("t_order_item"));
+ assertThat(rowData.next(), is("ds_2"));
}
@Test
@@ -91,23 +91,23 @@ public final class SingleTableQueryResultSetTest {
Collection<Object> actual = resultSet.getRowData();
assertThat(actual.size(), is(2));
Iterator<Object> rowData = actual.iterator();
- assertThat(rowData.next(), is("t_order_item"));
- assertThat(rowData.next(), is("ds_2"));
- resultSet.next();
- actual = resultSet.getRowData();
- rowData = actual.iterator();
assertThat(rowData.next(), is("t_order"));
assertThat(rowData.next(), is("ds_1"));
resultSet.next();
actual = resultSet.getRowData();
rowData = actual.iterator();
- assertThat(rowData.next(), is("t_order_multiple"));
- assertThat(rowData.next(), is("ds_1_multiple"));
+ assertThat(rowData.next(), is("t_order_item"));
+ assertThat(rowData.next(), is("ds_2"));
resultSet.next();
actual = resultSet.getRowData();
rowData = actual.iterator();
assertThat(rowData.next(), is("t_order_item_multiple"));
assertThat(rowData.next(), is("ds_2_multiple"));
+ resultSet.next();
+ actual = resultSet.getRowData();
+ rowData = actual.iterator();
+ assertThat(rowData.next(), is("t_order_multiple"));
+ assertThat(rowData.next(), is("ds_1_multiple"));
}
@Test
@@ -118,13 +118,13 @@ public final class SingleTableQueryResultSetTest {
Collection<Object> actual = resultSet.getRowData();
assertThat(actual.size(), is(2));
Iterator<Object> rowData = actual.iterator();
- assertThat(rowData.next(), is("t_order_item"));
- assertThat(rowData.next(), is("ds_2"));
+ assertThat(rowData.next(), is("t_order"));
+ assertThat(rowData.next(), is("ds_1"));
resultSet.next();
actual = resultSet.getRowData();
rowData = actual.iterator();
- assertThat(rowData.next(), is("t_order"));
- assertThat(rowData.next(), is("ds_1"));
+ assertThat(rowData.next(), is("t_order_item"));
+ assertThat(rowData.next(), is("ds_2"));
}
private SingleTableRule mockSingleTableRule(final Map<String, Collection<DataNode>> singleTableDataNodeMap) {
diff --git a/test/integration-test/env/src/test/java/org/apache/shardingsphere/test/integration/env/container/atomic/storage/DockerStorageContainer.java b/test/integration-test/env/src/test/java/org/apache/shardingsphere/test/integration/env/container/atomic/storage/DockerStorageContainer.java
index 87652bbae8c..00f88775e18 100644
--- a/test/integration-test/env/src/test/java/org/apache/shardingsphere/test/integration/env/container/atomic/storage/DockerStorageContainer.java
+++ b/test/integration-test/env/src/test/java/org/apache/shardingsphere/test/integration/env/container/atomic/storage/DockerStorageContainer.java
@@ -72,8 +72,10 @@ public abstract class DockerStorageContainer extends DockerITContainer implement
if (Strings.isNullOrEmpty(scenario)) {
withClasspathResourceMapping("/env/" + databaseType.getType().toLowerCase() + "/01-initdb.sql", "/docker-entrypoint-initdb.d/01-initdb.sql", BindMode.READ_ONLY);
} else {
- withClasspathResourceMapping(new ScenarioDataPath(scenario).getInitSQLResourcePath(Type.ACTUAL, databaseType), "/docker-entrypoint-initdb.d/", BindMode.READ_ONLY);
- withClasspathResourceMapping(new ScenarioDataPath(scenario).getInitSQLResourcePath(Type.EXPECTED, databaseType), "/docker-entrypoint-initdb.d/", BindMode.READ_ONLY);
+ withClasspathResourceMapping(new ScenarioDataPath(scenario).getInitSQLResourcePath(Type.ACTUAL, databaseType) + "/01-actual-init.sql", "/docker-entrypoint-initdb.d/01-actual-init.sql",
+ BindMode.READ_ONLY);
+ withClasspathResourceMapping(new ScenarioDataPath(scenario).getInitSQLResourcePath(Type.EXPECTED, databaseType) + "/01-expected-init.sql",
+ "/docker-entrypoint-initdb.d/01-expected-init.sql", BindMode.READ_ONLY);
}
withExposedPorts(getExposedPort());
setWaitStrategy(new JdbcConnectionWaitStrategy(
diff --git a/test/integration-test/showprocesslist/src/test/resources/env/scenario/cluster_jdbc_proxy/data/expected/init-sql/mysql/expected-init.sql b/test/integration-test/showprocesslist/src/test/resources/env/scenario/cluster_jdbc_proxy/data/expected/init-sql/mysql/01-expected-init.sql
similarity index 100%
rename from test/integration-test/showprocesslist/src/test/resources/env/scenario/cluster_jdbc_proxy/data/expected/init-sql/mysql/expected-init.sql
rename to test/integration-test/showprocesslist/src/test/resources/env/scenario/cluster_jdbc_proxy/data/expected/init-sql/mysql/01-expected-init.sql
diff --git a/test/integration-test/test-suite/src/test/java/org/apache/shardingsphere/test/integration/engine/BaseITCase.java b/test/integration-test/test-suite/src/test/java/org/apache/shardingsphere/test/integration/engine/BaseITCase.java
index f72b82f03e3..4b33738253e 100644
--- a/test/integration-test/test-suite/src/test/java/org/apache/shardingsphere/test/integration/engine/BaseITCase.java
+++ b/test/integration-test/test-suite/src/test/java/org/apache/shardingsphere/test/integration/engine/BaseITCase.java
@@ -19,18 +19,28 @@ package org.apache.shardingsphere.test.integration.engine;
import lombok.AccessLevel;
import lombok.Getter;
+import org.apache.shardingsphere.infra.database.DefaultDatabase;
import org.apache.shardingsphere.infra.database.type.DatabaseType;
import org.apache.shardingsphere.test.integration.cases.assertion.IntegrationTestCase;
import org.apache.shardingsphere.test.integration.container.compose.ContainerComposer;
import org.apache.shardingsphere.test.integration.container.compose.ContainerComposerRegistry;
+import org.apache.shardingsphere.test.integration.env.runtime.scenario.path.ScenarioDataPath;
import org.apache.shardingsphere.test.integration.framework.param.model.ParameterizedArray;
import org.apache.shardingsphere.test.integration.framework.runner.ShardingSphereIntegrationTestParameterized;
+import org.h2.tools.RunScript;
import org.junit.AfterClass;
import org.junit.Before;
import org.junit.runner.RunWith;
import javax.sql.DataSource;
+import java.io.FileReader;
+import java.io.IOException;
+import java.sql.Connection;
+import java.sql.SQLException;
+import java.util.Collection;
+import java.util.HashSet;
import java.util.Map;
+import java.util.Optional;
import java.util.concurrent.atomic.AtomicInteger;
@RunWith(ShardingSphereIntegrationTestParameterized.class)
@@ -45,6 +55,8 @@ public abstract class BaseITCase {
private static final AtomicInteger COMPLETED_SUITES_COUNT = new AtomicInteger(0);
+ private static final Collection<String> INITIALIZED_SUITES = new HashSet<>();
+
private final String mode;
private final String scenario;
@@ -74,11 +86,35 @@ public abstract class BaseITCase {
}
@Before
- public void setUp() {
+ public void setUp() throws SQLException, IOException {
containerComposer.start();
actualDataSourceMap = containerComposer.getActualDataSourceMap();
targetDataSource = containerComposer.getTargetDataSource();
expectedDataSourceMap = containerComposer.getExpectedDataSourceMap();
+ executeLogicDatabaseInitSQLFileOnlyOnce(targetDataSource);
+ }
+
+ private void executeLogicDatabaseInitSQLFileOnlyOnce(final DataSource targetDataSource) throws SQLException, IOException {
+ Optional<String> logicDatabaseInitSQLFile = new ScenarioDataPath(getScenario()).findActualDatabaseInitSQLFile(DefaultDatabase.LOGIC_NAME, getDatabaseType());
+ if (!logicDatabaseInitSQLFile.isPresent()) {
+ return;
+ }
+ if (!INITIALIZED_SUITES.contains(getItKey())) {
+ synchronized (INITIALIZED_SUITES) {
+ if (!INITIALIZED_SUITES.contains(getItKey())) {
+ executeInitSQL(targetDataSource, logicDatabaseInitSQLFile.get());
+ INITIALIZED_SUITES.add(getItKey());
+ }
+ }
+ }
+ }
+
+ private void executeInitSQL(final DataSource dataSource, final String logicDatabaseInitSQLFile) throws SQLException, IOException {
+ try (
+ Connection connection = dataSource.getConnection();
+ FileReader reader = new FileReader(logicDatabaseInitSQLFile)) {
+ RunScript.execute(connection, reader);
+ }
}
@AfterClass
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 77d0d0625bf..409236583fa 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
@@ -644,27 +644,28 @@
<assertion parameters="0:int" expected-data-source-name="shadow_dataset" />
</test-case>
- <test-case sql="SELECT * FROM t_order o INNER JOIN t_order_item i ON o.order_id = i.order_id WHERE o.user_id = ? ORDER BY o.order_id, 7" db-types="PostgreSQL,openGauss" scenario-types="db">
+ <test-case sql="SELECT * FROM t_order o INNER JOIN t_order_item i ON o.order_id = i.order_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 INNER JOIN t_order_item i ON o.order_id = i.order_id WHERE o.user_id = ? ORDER BY o.order_id, 7 LIMIT 1, 2" db-types="openGauss" scenario-types="db">
+ <test-case sql="SELECT * FROM t_order o INNER JOIN t_order_item i ON o.order_id = i.order_id WHERE o.user_id = ? ORDER BY o.order_id, 7 LIMIT 1, 2" db-types="MySQL,openGauss" scenario-types="db">
<assertion parameters="10:int" expected-data-source-name="read_dataset" />
</test-case>
- <test-case sql="SELECT MIN(o.order_id), MIN(o.merchant_id), i.product_id FROM t_order o INNER JOIN t_order_item i ON o.order_id = i.order_id WHERE o.user_id = ? GROUP BY i.product_id" db-types="PostgreSQL,openGauss" scenario-types="db">
+ <test-case sql="SELECT MIN(o.order_id), MIN(o.merchant_id), i.product_id FROM t_order o INNER JOIN t_order_item i ON o.order_id = i.order_id WHERE o.user_id = ? GROUP BY i.product_id" db-types="MySQL,PostgreSQL,openGauss" scenario-types="db">
<assertion parameters="10:int" expected-data-source-name="read_dataset" />
</test-case>
+ <!-- FIXME: In MySQL, JOIN, CROSS JOIN, and INNER JOIN are syntactic equivalents -->
<test-case sql="SELECT * FROM t_order o CROSS JOIN t_order_item i 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_order_item i ON o.order_id = i.item_id WHERE o.user_id = ? ORDER BY o.order_id, 7" db-types="PostgreSQL,openGauss" scenario-types="db">
+ <test-case sql="SELECT * FROM t_order o LEFT JOIN t_order_item i ON o.order_id = i.item_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_order_item i ON o.order_id = i.item_id WHERE i.user_id = ? ORDER BY i.item_id, 7" db-types="PostgreSQL,openGauss" scenario-types="db">
+ <test-case sql="SELECT * FROM t_order o RIGHT JOIN t_order_item i ON o.order_id = i.item_id WHERE i.user_id = ? ORDER BY i.item_id, 7" db-types="MySQL,PostgreSQL,openGauss" scenario-types="db">
<assertion parameters="10:int" expected-data-source-name="read_dataset" />
</test-case>
@@ -672,66 +673,67 @@
<assertion parameters="10:int, 10:int" expected-data-source-name="read_dataset" />
</test-case>
- <test-case sql="SELECT * FROM t_order o INNER JOIN t_merchant m ON o.merchant_id = m.merchant_id WHERE o.user_id = ? ORDER BY o.order_id" db-types="PostgreSQL,openGauss" scenario-types="db">
+ <test-case sql="SELECT * FROM t_order o INNER JOIN t_merchant m ON o.merchant_id = m.merchant_id WHERE o.user_id = ? ORDER BY o.order_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_order o INNER JOIN t_merchant m ON o.merchant_id = m.merchant_id WHERE o.user_id = ? ORDER BY o.order_id LIMIT 1, 2" db-types="openGauss" scenario-types="db">
+ <test-case sql="SELECT * FROM t_order o INNER JOIN t_merchant m ON o.merchant_id = m.merchant_id WHERE o.user_id = ? ORDER BY o.order_id LIMIT 1, 2" db-types="MySQL,openGauss" scenario-types="db">
<assertion parameters="10:int" expected-data-source-name="read_dataset" />
</test-case>
- <test-case sql="SELECT MIN(o.order_id), MIN(o.merchant_id), MIN(m.merchant_name) FROM t_order o INNER JOIN t_merchant m ON o.merchant_id = m.merchant_id WHERE o.user_id = ? GROUP BY m.merchant_id" db-types="PostgreSQL,openGauss" scenario-types="db">
+ <test-case sql="SELECT MIN(o.order_id), MIN(o.merchant_id), MIN(m.merchant_name) FROM t_order o INNER JOIN t_merchant m ON o.merchant_id = m.merchant_id WHERE o.user_id = ? GROUP BY m.merchant_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 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="PostgreSQL,openGauss" scenario-types="db">
+ <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>
- <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 LIMIT 2, 5" db-types="openGauss" scenario-types="db">
+ <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 LIMIT 2, 5" db-types="MySQL,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="PostgreSQL,openGauss" scenario-types="db">
+ <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:int" expected-data-source-name="read_dataset" />
</test-case>
- <test-case sql="SELECT * FROM (SELECT o.* FROM t_order o WHERE o.user_id IN (10, 11, 12)) AS t, t_order_item i WHERE t.order_id = i.order_id AND t.order_id > ? ORDER BY item_id" db-types="PostgreSQL,openGauss" scenario-types="db">
+ <test-case sql="SELECT * FROM (SELECT o.* FROM t_order o WHERE o.user_id IN (10, 11, 12)) AS t, t_order_item i WHERE t.order_id = i.order_id AND t.order_id > ? ORDER BY item_id" db-types="MySQL,PostgreSQL,openGauss" scenario-types="db">
<assertion parameters="1200:int" expected-data-source-name="read_dataset" />
</test-case>
- <test-case sql="SELECT * FROM t_order o WHERE o.order_id IN (SELECT i.order_id FROM t_order_item i INNER JOIN t_product p ON i.product_id = p.product_id WHERE p.product_id = ?) ORDER BY order_id" db-types="PostgreSQL,openGauss" scenario-types="db">
+ <test-case sql="SELECT * FROM t_order o WHERE o.order_id IN (SELECT i.order_id FROM t_order_item i INNER JOIN t_product p ON i.product_id = p.product_id WHERE p.product_id = ?) ORDER BY order_id" db-types="MySQL,PostgreSQL,openGauss" scenario-types="db">
<assertion parameters="10:int" expected-data-source-name="read_dataset" />
</test-case>
- <test-case sql="SELECT MAX(p.price) AS max_price, MIN(p.price) AS min_price, SUM(p.price) AS sum_price, AVG(p.price) AS avg_price, COUNT(1) AS count FROM t_order o INNER JOIN t_order_item i ON o.order_id = i.order_id INNER JOIN t_product p ON i.product_id = p.product_id GROUP BY o.order_id HAVING SUM(p.price) > ? ORDER BY max_price" db-types="PostgreSQL,openGauss" scenario-types="db">
+ <test-case sql="SELECT MAX(p.price) AS max_price, MIN(p.price) AS min_price, SUM(p.price) AS sum_price, AVG(p.price) AS avg_price, COUNT(1) AS count FROM t_order o INNER JOIN t_order_item i ON o.order_id = i.order_id INNER JOIN t_product p ON i.product_id = p.product_id GROUP BY o.order_id HAVING SUM(p.price) > ? ORDER BY max_price" db-types="MySQL,PostgreSQL,openGauss" scenario-types="db">
<assertion parameters="10000:int" expected-data-source-name="read_dataset" />
</test-case>
- <test-case sql="SELECT * FROM t_order UNION ALL SELECT * FROM t_order ORDER BY order_id LIMIT 5, 5" db-types="openGauss" scenario-types="db">
+ <test-case sql="SELECT * FROM t_order UNION ALL SELECT * FROM t_order ORDER BY order_id LIMIT 5, 5" db-types="MySQL,openGauss" scenario-types="db">
<assertion expected-data-source-name="read_dataset" />
</test-case>
- <test-case sql="SELECT * FROM t_order WHERE order_id > ? UNION SELECT * FROM t_order WHERE order_id > ? ORDER BY order_id LIMIT 5, 5" db-types="openGauss" scenario-types="db">
+ <test-case sql="SELECT * FROM t_order WHERE order_id > ? UNION SELECT * FROM t_order WHERE order_id > ? ORDER BY order_id LIMIT 5, 5" db-types="MySQL,openGauss" scenario-types="db">
<assertion parameters="2000:int, 1500:int" expected-data-source-name="read_dataset" />
</test-case>
- <test-case sql="SELECT o.order_id, o.user_id FROM t_order o WHERE o.order_id > ? UNION ALL SELECT i.order_id, i.user_id FROM t_order_item i WHERE i.order_id > ? ORDER BY order_id LIMIT 5, 5" db-types="openGauss" scenario-types="db">
+ <test-case sql="SELECT o.order_id, o.user_id FROM t_order o WHERE o.order_id > ? UNION ALL SELECT i.order_id, i.user_id FROM t_order_item i WHERE i.order_id > ? ORDER BY order_id LIMIT 5, 5" db-types="MySQL,openGauss" scenario-types="db">
<assertion parameters="2500:int, 2500:int" expected-data-source-name="read_dataset" />
</test-case>
- <test-case sql="SELECT o.order_id, o.user_id FROM t_order o WHERE o.order_id > ? UNION SELECT i.order_id, i.user_id FROM t_order_item i WHERE i.order_id > ? ORDER BY order_id" db-types="PostgreSQL,openGauss" scenario-types="db">
+ <test-case sql="SELECT o.order_id, o.user_id FROM t_order o WHERE o.order_id > ? UNION SELECT i.order_id, i.user_id FROM t_order_item i WHERE i.order_id > ? ORDER BY order_id" db-types="MySQL,PostgreSQL,openGauss" scenario-types="db">
<assertion parameters="2500:int, 2500:int" expected-data-source-name="read_dataset" />
</test-case>
- <test-case sql="SELECT o.user_id FROM t_order o WHERE o.order_id > ? UNION ALL SELECT u.user_id FROM t_user u ORDER BY user_id LIMIT 5, 5" db-types="openGauss" scenario-types="db">
+ <test-case sql="SELECT o.user_id FROM t_order o WHERE o.order_id > ? UNION ALL SELECT u.user_id FROM t_user u ORDER BY user_id LIMIT 5, 5" db-types="MySQL,openGauss" scenario-types="db">
<assertion parameters="2500:int" expected-data-source-name="read_dataset" />
</test-case>
- <test-case sql="SELECT o.user_id FROM t_order o WHERE o.order_id > ? UNION SELECT u.user_id FROM t_user u ORDER BY user_id" db-types="PostgreSQL,openGauss" scenario-types="db">
+ <test-case sql="SELECT o.user_id FROM t_order o WHERE o.order_id > ? UNION SELECT u.user_id FROM t_user u ORDER BY user_id" db-types="MySQL,PostgreSQL,openGauss" scenario-types="db">
<assertion parameters="2500:int" expected-data-source-name="read_dataset" />
</test-case>
+ <!-- TODO support MySQL INTERSECT clause -->
<test-case sql="SELECT * FROM t_order INTERSECT ALL SELECT * FROM t_order ORDER BY order_id LIMIT 5, 5" db-types="openGauss" scenario-types="db">
<assertion expected-data-source-name="read_dataset" />
</test-case>
@@ -756,6 +758,7 @@
<assertion parameters="2500:int" expected-data-source-name="read_dataset" />
</test-case>
+ <!-- TODO support MySQL EXCEPT clause -->
<test-case sql="SELECT * FROM t_order EXCEPT ALL SELECT * FROM t_order ORDER BY order_id" db-types="PostgreSQL,openGauss" scenario-types="db">
<assertion expected-data-source-name="read_dataset" />
</test-case>
@@ -809,4 +812,52 @@
<!-- <assertion parameters="1:int" expected-data-source-name="prod_dataset" />-->
<!-- <assertion parameters="0:int" expected-data-source-name="shadow_dataset" />-->
<!-- </test-case>-->
+
+ <test-case sql="SELECT * FROM t_order_item_join_view" db-types="MySQL,PostgreSQL,openGauss" scenario-types="db">
+ <assertion expected-data-source-name="read_dataset" />
+ </test-case>
+
+ <test-case sql="SELECT * FROM t_order_item_join_view WHERE order_id > ?" db-types="MySQL,PostgreSQL,openGauss" scenario-types="db">
+ <assertion parameters="1000:int" expected-data-source-name="read_dataset" />
+ </test-case>
+
+ <test-case sql="SELECT * FROM t_order_item_join_view LIMIT 1, 2" db-types="MySQL,openGauss" scenario-types="db">
+ <assertion expected-data-source-name="read_dataset" />
+ </test-case>
+
+ <test-case sql="SELECT * FROM t_order_subquery_view ORDER BY order_id" db-types="MySQL,PostgreSQL,openGauss" scenario-types="db">
+ <assertion expected-data-source-name="read_dataset" />
+ </test-case>
+
+ <test-case sql="SELECT * FROM t_order_subquery_view WHERE order_id = ?" db-types="MySQL,PostgreSQL,openGauss" scenario-types="db">
+ <assertion parameters="1201:int" expected-data-source-name="read_dataset" />
+ </test-case>
+
+ <test-case sql="SELECT * FROM t_order_subquery_view ORDER BY order_id LIMIT 1, 2" db-types="MySQL,openGauss" scenario-types="db">
+ <assertion expected-data-source-name="read_dataset" />
+ </test-case>
+
+ <test-case sql="SELECT * FROM t_order_aggregation_view" db-types="MySQL,PostgreSQL,openGauss" scenario-types="db">
+ <assertion expected-data-source-name="read_dataset" />
+ </test-case>
+
+ <test-case sql="SELECT * FROM t_order_aggregation_view WHERE max_price = ?" db-types="MySQL,PostgreSQL,openGauss" scenario-types="db">
+ <assertion parameters="8200:int" expected-data-source-name="read_dataset" />
+ </test-case>
+
+ <test-case sql="SELECT * FROM t_order_aggregation_view LIMIT 1, 2" db-types="MySQL,openGauss" scenario-types="db">
+ <assertion expected-data-source-name="read_dataset" />
+ </test-case>
+
+ <test-case sql="SELECT * FROM t_order_union_view ORDER BY order_id" db-types="MySQL,PostgreSQL,openGauss" scenario-types="db">
+ <assertion expected-data-source-name="read_dataset" />
+ </test-case>
+
+ <test-case sql="SELECT * FROM t_order_union_view WHERE order_id = ?" db-types="MySQL,PostgreSQL,openGauss" scenario-types="db">
+ <assertion parameters="2500:int" expected-data-source-name="read_dataset" />
+ </test-case>
+
+ <test-case sql="SELECT * FROM t_order_union_view ORDER BY order_id LIMIT 1, 2" db-types="MySQL,openGauss" scenario-types="db">
+ <assertion expected-data-source-name="read_dataset" />
+ </test-case>
</integration-test-cases>
diff --git a/test/integration-test/test-suite/src/test/resources/cases/rdl/dataset/rdl_empty_rules/alter_single_table_rules.xml b/test/integration-test/test-suite/src/test/resources/cases/rdl/dataset/rdl_empty_rules/alter_single_table_rules.xml
index 484357834d2..0c56700bbfd 100644
--- a/test/integration-test/test-suite/src/test/resources/cases/rdl/dataset/rdl_empty_rules/alter_single_table_rules.xml
+++ b/test/integration-test/test-suite/src/test/resources/cases/rdl/dataset/rdl_empty_rules/alter_single_table_rules.xml
@@ -21,8 +21,8 @@
<column name="resource_name" />
</metadata>
<row values="t_country| ds_2" />
+ <row values="t_product_category| ds_2" />
<row values="t_user| ds_0" />
<row values="temp_alter| ds_0" />
- <row values="t_product_category| ds_2" />
</dataset>
diff --git a/test/integration-test/test-suite/src/test/resources/cases/rdl/dataset/rdl_empty_rules/cluster/alter_single_table_rules.xml b/test/integration-test/test-suite/src/test/resources/cases/rdl/dataset/rdl_empty_rules/cluster/alter_single_table_rules.xml
index 484357834d2..0c56700bbfd 100644
--- a/test/integration-test/test-suite/src/test/resources/cases/rdl/dataset/rdl_empty_rules/cluster/alter_single_table_rules.xml
+++ b/test/integration-test/test-suite/src/test/resources/cases/rdl/dataset/rdl_empty_rules/cluster/alter_single_table_rules.xml
@@ -21,8 +21,8 @@
<column name="resource_name" />
</metadata>
<row values="t_country| ds_2" />
+ <row values="t_product_category| ds_2" />
<row values="t_user| ds_0" />
<row values="temp_alter| ds_0" />
- <row values="t_product_category| ds_2" />
</dataset>
diff --git a/test/integration-test/test-suite/src/test/resources/cases/rdl/dataset/rdl_empty_rules/cluster/create_single_table_rules.xml b/test/integration-test/test-suite/src/test/resources/cases/rdl/dataset/rdl_empty_rules/cluster/create_single_table_rules.xml
index 61733b93110..9e6d324f2af 100644
--- a/test/integration-test/test-suite/src/test/resources/cases/rdl/dataset/rdl_empty_rules/cluster/create_single_table_rules.xml
+++ b/test/integration-test/test-suite/src/test/resources/cases/rdl/dataset/rdl_empty_rules/cluster/create_single_table_rules.xml
@@ -20,9 +20,9 @@
<column name="table_name" />
<column name="resource_name" />
</metadata>
- <row values="temp| ds_1" />
<row values="t_country| ds_2" />
- <row values="t_user| ds_0" />
<row values="t_product_category| ds_2" />
+ <row values="t_user| ds_0" />
+ <row values="temp| ds_1" />
</dataset>
diff --git a/test/integration-test/test-suite/src/test/resources/cases/rdl/dataset/rdl_empty_rules/cluster/drop_single_table_rules.xml b/test/integration-test/test-suite/src/test/resources/cases/rdl/dataset/rdl_empty_rules/cluster/drop_single_table_rules.xml
index 5fac1318fc5..3958a37ebe3 100644
--- a/test/integration-test/test-suite/src/test/resources/cases/rdl/dataset/rdl_empty_rules/cluster/drop_single_table_rules.xml
+++ b/test/integration-test/test-suite/src/test/resources/cases/rdl/dataset/rdl_empty_rules/cluster/drop_single_table_rules.xml
@@ -21,7 +21,7 @@
<column name="resource_name" />
</metadata>
<row values="t_country| ds_2" />
- <row values="t_user| ds_0" />
<row values="t_product_category| ds_2" />
+ <row values="t_user| ds_0" />
</dataset>
diff --git a/test/integration-test/test-suite/src/test/resources/cases/rdl/dataset/rdl_empty_rules/create_single_table_rules.xml b/test/integration-test/test-suite/src/test/resources/cases/rdl/dataset/rdl_empty_rules/create_single_table_rules.xml
index 61733b93110..9e6d324f2af 100644
--- a/test/integration-test/test-suite/src/test/resources/cases/rdl/dataset/rdl_empty_rules/create_single_table_rules.xml
+++ b/test/integration-test/test-suite/src/test/resources/cases/rdl/dataset/rdl_empty_rules/create_single_table_rules.xml
@@ -20,9 +20,9 @@
<column name="table_name" />
<column name="resource_name" />
</metadata>
- <row values="temp| ds_1" />
<row values="t_country| ds_2" />
- <row values="t_user| ds_0" />
<row values="t_product_category| ds_2" />
+ <row values="t_user| ds_0" />
+ <row values="temp| ds_1" />
</dataset>
diff --git a/test/integration-test/test-suite/src/test/resources/cases/rdl/dataset/rdl_empty_rules/drop_single_table_rules.xml b/test/integration-test/test-suite/src/test/resources/cases/rdl/dataset/rdl_empty_rules/drop_single_table_rules.xml
index 5fac1318fc5..3958a37ebe3 100644
--- a/test/integration-test/test-suite/src/test/resources/cases/rdl/dataset/rdl_empty_rules/drop_single_table_rules.xml
+++ b/test/integration-test/test-suite/src/test/resources/cases/rdl/dataset/rdl_empty_rules/drop_single_table_rules.xml
@@ -21,7 +21,7 @@
<column name="resource_name" />
</metadata>
<row values="t_country| ds_2" />
- <row values="t_user| ds_0" />
<row values="t_product_category| ds_2" />
+ <row values="t_user| ds_0" />
</dataset>
diff --git a/test/integration-test/test-suite/src/test/resources/cases/rdl/dataset/rdl_empty_rules/standalone/alter_single_table_rules.xml b/test/integration-test/test-suite/src/test/resources/cases/rdl/dataset/rdl_empty_rules/standalone/alter_single_table_rules.xml
index 484357834d2..0c56700bbfd 100644
--- a/test/integration-test/test-suite/src/test/resources/cases/rdl/dataset/rdl_empty_rules/standalone/alter_single_table_rules.xml
+++ b/test/integration-test/test-suite/src/test/resources/cases/rdl/dataset/rdl_empty_rules/standalone/alter_single_table_rules.xml
@@ -21,8 +21,8 @@
<column name="resource_name" />
</metadata>
<row values="t_country| ds_2" />
+ <row values="t_product_category| ds_2" />
<row values="t_user| ds_0" />
<row values="temp_alter| ds_0" />
- <row values="t_product_category| ds_2" />
</dataset>
diff --git a/test/integration-test/test-suite/src/test/resources/cases/rdl/dataset/rdl_empty_rules/standalone/create_single_table_rules.xml b/test/integration-test/test-suite/src/test/resources/cases/rdl/dataset/rdl_empty_rules/standalone/create_single_table_rules.xml
index bbfbe43b33c..9e6d324f2af 100644
--- a/test/integration-test/test-suite/src/test/resources/cases/rdl/dataset/rdl_empty_rules/standalone/create_single_table_rules.xml
+++ b/test/integration-test/test-suite/src/test/resources/cases/rdl/dataset/rdl_empty_rules/standalone/create_single_table_rules.xml
@@ -21,8 +21,8 @@
<column name="resource_name" />
</metadata>
<row values="t_country| ds_2" />
- <row values="temp| ds_1" />
- <row values="t_user| ds_0" />
<row values="t_product_category| ds_2" />
+ <row values="t_user| ds_0" />
+ <row values="temp| ds_1" />
</dataset>
diff --git a/test/integration-test/test-suite/src/test/resources/cases/rdl/dataset/rdl_empty_rules/standalone/drop_single_table_rules.xml b/test/integration-test/test-suite/src/test/resources/cases/rdl/dataset/rdl_empty_rules/standalone/drop_single_table_rules.xml
index 5fac1318fc5..3958a37ebe3 100644
--- a/test/integration-test/test-suite/src/test/resources/cases/rdl/dataset/rdl_empty_rules/standalone/drop_single_table_rules.xml
+++ b/test/integration-test/test-suite/src/test/resources/cases/rdl/dataset/rdl_empty_rules/standalone/drop_single_table_rules.xml
@@ -21,7 +21,7 @@
<column name="resource_name" />
</metadata>
<row values="t_country| ds_2" />
- <row values="t_user| ds_0" />
<row values="t_product_category| ds_2" />
+ <row values="t_user| ds_0" />
</dataset>
diff --git a/test/integration-test/test-suite/src/test/resources/cases/rql/dataset/db/count_single_table_rule.xml b/test/integration-test/test-suite/src/test/resources/cases/rql/dataset/db/count_single_table_rule.xml
index 62bf401e882..de44ecc6a2f 100644
--- a/test/integration-test/test-suite/src/test/resources/cases/rql/dataset/db/count_single_table_rule.xml
+++ b/test/integration-test/test-suite/src/test/resources/cases/rql/dataset/db/count_single_table_rule.xml
@@ -21,5 +21,5 @@
<column name="database"/>
<column name="count"/>
</metadata>
- <row values="single_table| db| 7"/>
+ <row values="single_table| db| 11"/>
</dataset>
diff --git a/test/integration-test/test-suite/src/test/resources/cases/rql/dataset/db/show_single_tables.xml b/test/integration-test/test-suite/src/test/resources/cases/rql/dataset/db/show_single_tables.xml
index bfe7a782c3d..64ccc21b110 100644
--- a/test/integration-test/test-suite/src/test/resources/cases/rql/dataset/db/show_single_tables.xml
+++ b/test/integration-test/test-suite/src/test/resources/cases/rql/dataset/db/show_single_tables.xml
@@ -20,11 +20,15 @@
<column name="table_name" />
<column name="resource_name" />
</metadata>
+ <row values="t_merchant| ds_1" />
+ <row values="t_order_aggregation_view| ds_0" />
+ <row values="t_order_federate| ds_0" />
+ <row values="t_order_item_federate| ds_1" />
+ <row values="t_order_item_join_view| ds_0" />
+ <row values="t_order_subquery_view| ds_0" />
+ <row values="t_order_union_view| ds_0" />
+ <row values="t_product| ds_0" />
<row values="t_product_detail| ds_1" />
<row values="t_single_table| ds_0" />
<row values="t_user| ds_0" />
- <row values="t_order_item_federate| ds_1" />
- <row values="t_order_federate| ds_0" />
- <row values="t_product| ds_0" />
- <row values="t_merchant| ds_1" />
</dataset>
diff --git a/test/integration-test/test-suite/src/test/resources/cases/rql/dataset/dbtbl_with_readwrite_splitting/show_single_tables.xml b/test/integration-test/test-suite/src/test/resources/cases/rql/dataset/dbtbl_with_readwrite_splitting/show_single_tables.xml
index 40a7eb4143e..3ae8548eb52 100644
--- a/test/integration-test/test-suite/src/test/resources/cases/rql/dataset/dbtbl_with_readwrite_splitting/show_single_tables.xml
+++ b/test/integration-test/test-suite/src/test/resources/cases/rql/dataset/dbtbl_with_readwrite_splitting/show_single_tables.xml
@@ -20,11 +20,11 @@
<column name="table_name" />
<column name="resource_name" />
</metadata>
+ <row values="t_merchant| readwrite_ds_1" />
+ <row values="t_order_federate| readwrite_ds_0" />
+ <row values="t_order_item_federate| readwrite_ds_1" />
+ <row values="t_product| readwrite_ds_0" />
<row values="t_product_detail| readwrite_ds_1" />
<row values="t_single_table| readwrite_ds_0" />
<row values="t_user| readwrite_ds_0" />
- <row values="t_order_item_federate| readwrite_ds_1" />
- <row values="t_order_federate| readwrite_ds_0" />
- <row values="t_product| readwrite_ds_0" />
- <row values="t_merchant| readwrite_ds_1" />
</dataset>
diff --git a/test/integration-test/test-suite/src/test/resources/cases/rql/dataset/dbtbl_with_readwrite_splitting_and_encrypt/show_single_tables.xml b/test/integration-test/test-suite/src/test/resources/cases/rql/dataset/dbtbl_with_readwrite_splitting_and_encrypt/show_single_tables.xml
index f77e7591d1a..bc6977b9210 100644
--- a/test/integration-test/test-suite/src/test/resources/cases/rql/dataset/dbtbl_with_readwrite_splitting_and_encrypt/show_single_tables.xml
+++ b/test/integration-test/test-suite/src/test/resources/cases/rql/dataset/dbtbl_with_readwrite_splitting_and_encrypt/show_single_tables.xml
@@ -20,9 +20,9 @@
<column name="table_name"/>
<column name="resource_name"/>
</metadata>
- <row values="t_single_table| readwrite_ds_0"/>
+ <row values="t_order_federate| readwrite_ds_0"/>
<row values="t_order_item_federate| readwrite_ds_1"/>
+ <row values="t_single_table| readwrite_ds_0"/>
<row values="t_user_encrypt_federate| readwrite_ds_1"/>
- <row values="t_order_federate| readwrite_ds_0"/>
<row values="t_user_info| readwrite_ds_1"/>
</dataset>
diff --git a/test/integration-test/test-suite/src/test/resources/cases/rql/dataset/encrypt_and_readwrite_splitting/show_single_tables.xml b/test/integration-test/test-suite/src/test/resources/cases/rql/dataset/encrypt_and_readwrite_splitting/show_single_tables.xml
index 716b852a395..88e6b2d8ddf 100644
--- a/test/integration-test/test-suite/src/test/resources/cases/rql/dataset/encrypt_and_readwrite_splitting/show_single_tables.xml
+++ b/test/integration-test/test-suite/src/test/resources/cases/rql/dataset/encrypt_and_readwrite_splitting/show_single_tables.xml
@@ -20,14 +20,14 @@
<column name="table_name"/>
<column name="resource_name"/>
</metadata>
+ <row values="t_order_federate| readwrite_ds"/>
<row values="t_order_federate_sharding| readwrite_ds"/>
+ <row values="t_order_item_federate| readwrite_ds"/>
<row values="t_order_item_federate_sharding| readwrite_ds"/>
<row values="t_single_table| readwrite_ds"/>
<row values="t_user| readwrite_ds"/>
- <row values="t_user_item| readwrite_ds"/>
- <row values="t_order_item_federate| readwrite_ds"/>
- <row values="t_order_federate| readwrite_ds"/>
<row values="t_user_encrypt_federate| readwrite_ds"/>
<row values="t_user_encrypt_federate_sharding| readwrite_ds"/>
<row values="t_user_info| readwrite_ds"/>
+ <row values="t_user_item| readwrite_ds"/>
</dataset>
diff --git a/test/integration-test/test-suite/src/test/resources/cases/rql/dataset/sharding_and_encrypt/show_single_tables.xml b/test/integration-test/test-suite/src/test/resources/cases/rql/dataset/sharding_and_encrypt/show_single_tables.xml
index 5531f66e830..536a421db03 100644
--- a/test/integration-test/test-suite/src/test/resources/cases/rql/dataset/sharding_and_encrypt/show_single_tables.xml
+++ b/test/integration-test/test-suite/src/test/resources/cases/rql/dataset/sharding_and_encrypt/show_single_tables.xml
@@ -20,9 +20,9 @@
<column name="table_name"/>
<column name="resource_name"/>
</metadata>
- <row values="t_single_table| encrypt_ds_0"/>
- <row values="t_order_item_federate| encrypt_ds_1"/>
<row values="t_order_federate| encrypt_ds_0"/>
+ <row values="t_order_item_federate| encrypt_ds_1"/>
+ <row values="t_single_table| encrypt_ds_0"/>
<row values="t_user_encrypt_federate| encrypt_ds_1"/>
<row values="t_user_info| encrypt_ds_1"/>
</dataset>
diff --git a/test/integration-test/test-suite/src/test/resources/env/scenario/db/data/actual/init-sql/h2/actual-logic_db-init.sql b/test/integration-test/test-suite/src/test/resources/env/scenario/db/data/actual/init-sql/h2/actual-logic_db-init.sql
new file mode 100644
index 00000000000..1c24b1695f6
--- /dev/null
+++ b/test/integration-test/test-suite/src/test/resources/env/scenario/db/data/actual/init-sql/h2/actual-logic_db-init.sql
@@ -0,0 +1,26 @@
+--
+-- 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.
+--
+
+DROP VIEW IF EXISTS t_order_item_join_view;
+DROP VIEW IF EXISTS t_order_subquery_view;
+DROP VIEW IF EXISTS t_order_aggregation_view;
+DROP VIEW IF EXISTS t_order_union_view;
+
+CREATE VIEW t_order_item_join_view AS SELECT o.order_id, o.user_id, i.item_id FROM t_order o INNER JOIN t_order_item i ON o.order_id = i.order_id WHERE o.user_id = 10 ORDER BY o.order_id;
+CREATE VIEW t_order_subquery_view AS SELECT * FROM t_order o WHERE o.order_id IN (SELECT i.order_id FROM t_order_item i INNER JOIN t_product p ON i.product_id = p.product_id WHERE p.product_id = 10);
+CREATE VIEW t_order_aggregation_view AS SELECT MAX(p.price) AS max_price, MIN(p.price) AS min_price, SUM(p.price) AS sum_price, AVG(p.price) AS avg_price, COUNT(1) AS count FROM t_order o INNER JOIN t_order_item i ON o.order_id = i.order_id INNER JOIN t_product p ON i.product_id = p.product_id GROUP BY o.order_id HAVING SUM(p.price) > 10000 ORDER BY max_price;
+CREATE VIEW t_order_union_view AS SELECT * FROM t_order WHERE order_id > 2000 UNION SELECT * FROM t_order WHERE order_id > 1500;
diff --git a/test/integration-test/test-suite/src/test/resources/env/scenario/db/data/actual/init-sql/mysql/actual-logic_db-init.sql b/test/integration-test/test-suite/src/test/resources/env/scenario/db/data/actual/init-sql/mysql/actual-logic_db-init.sql
new file mode 100644
index 00000000000..1c24b1695f6
--- /dev/null
+++ b/test/integration-test/test-suite/src/test/resources/env/scenario/db/data/actual/init-sql/mysql/actual-logic_db-init.sql
@@ -0,0 +1,26 @@
+--
+-- 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.
+--
+
+DROP VIEW IF EXISTS t_order_item_join_view;
+DROP VIEW IF EXISTS t_order_subquery_view;
+DROP VIEW IF EXISTS t_order_aggregation_view;
+DROP VIEW IF EXISTS t_order_union_view;
+
+CREATE VIEW t_order_item_join_view AS SELECT o.order_id, o.user_id, i.item_id FROM t_order o INNER JOIN t_order_item i ON o.order_id = i.order_id WHERE o.user_id = 10 ORDER BY o.order_id;
+CREATE VIEW t_order_subquery_view AS SELECT * FROM t_order o WHERE o.order_id IN (SELECT i.order_id FROM t_order_item i INNER JOIN t_product p ON i.product_id = p.product_id WHERE p.product_id = 10);
+CREATE VIEW t_order_aggregation_view AS SELECT MAX(p.price) AS max_price, MIN(p.price) AS min_price, SUM(p.price) AS sum_price, AVG(p.price) AS avg_price, COUNT(1) AS count FROM t_order o INNER JOIN t_order_item i ON o.order_id = i.order_id INNER JOIN t_product p ON i.product_id = p.product_id GROUP BY o.order_id HAVING SUM(p.price) > 10000 ORDER BY max_price;
+CREATE VIEW t_order_union_view AS SELECT * FROM t_order WHERE order_id > 2000 UNION SELECT * FROM t_order WHERE order_id > 1500;
diff --git a/test/integration-test/test-suite/src/test/resources/env/scenario/db/data/actual/init-sql/opengauss/actual-logic_db-init.sql b/test/integration-test/test-suite/src/test/resources/env/scenario/db/data/actual/init-sql/opengauss/actual-logic_db-init.sql
new file mode 100644
index 00000000000..1c24b1695f6
--- /dev/null
+++ b/test/integration-test/test-suite/src/test/resources/env/scenario/db/data/actual/init-sql/opengauss/actual-logic_db-init.sql
@@ -0,0 +1,26 @@
+--
+-- 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.
+--
+
+DROP VIEW IF EXISTS t_order_item_join_view;
+DROP VIEW IF EXISTS t_order_subquery_view;
+DROP VIEW IF EXISTS t_order_aggregation_view;
+DROP VIEW IF EXISTS t_order_union_view;
+
+CREATE VIEW t_order_item_join_view AS SELECT o.order_id, o.user_id, i.item_id FROM t_order o INNER JOIN t_order_item i ON o.order_id = i.order_id WHERE o.user_id = 10 ORDER BY o.order_id;
+CREATE VIEW t_order_subquery_view AS SELECT * FROM t_order o WHERE o.order_id IN (SELECT i.order_id FROM t_order_item i INNER JOIN t_product p ON i.product_id = p.product_id WHERE p.product_id = 10);
+CREATE VIEW t_order_aggregation_view AS SELECT MAX(p.price) AS max_price, MIN(p.price) AS min_price, SUM(p.price) AS sum_price, AVG(p.price) AS avg_price, COUNT(1) AS count FROM t_order o INNER JOIN t_order_item i ON o.order_id = i.order_id INNER JOIN t_product p ON i.product_id = p.product_id GROUP BY o.order_id HAVING SUM(p.price) > 10000 ORDER BY max_price;
+CREATE VIEW t_order_union_view AS SELECT * FROM t_order WHERE order_id > 2000 UNION SELECT * FROM t_order WHERE order_id > 1500;
diff --git a/test/integration-test/test-suite/src/test/resources/env/scenario/db/data/actual/init-sql/postgresql/actual-logic_db-init.sql b/test/integration-test/test-suite/src/test/resources/env/scenario/db/data/actual/init-sql/postgresql/actual-logic_db-init.sql
new file mode 100644
index 00000000000..1c24b1695f6
--- /dev/null
+++ b/test/integration-test/test-suite/src/test/resources/env/scenario/db/data/actual/init-sql/postgresql/actual-logic_db-init.sql
@@ -0,0 +1,26 @@
+--
+-- 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.
+--
+
+DROP VIEW IF EXISTS t_order_item_join_view;
+DROP VIEW IF EXISTS t_order_subquery_view;
+DROP VIEW IF EXISTS t_order_aggregation_view;
+DROP VIEW IF EXISTS t_order_union_view;
+
+CREATE VIEW t_order_item_join_view AS SELECT o.order_id, o.user_id, i.item_id FROM t_order o INNER JOIN t_order_item i ON o.order_id = i.order_id WHERE o.user_id = 10 ORDER BY o.order_id;
+CREATE VIEW t_order_subquery_view AS SELECT * FROM t_order o WHERE o.order_id IN (SELECT i.order_id FROM t_order_item i INNER JOIN t_product p ON i.product_id = p.product_id WHERE p.product_id = 10);
+CREATE VIEW t_order_aggregation_view AS SELECT MAX(p.price) AS max_price, MIN(p.price) AS min_price, SUM(p.price) AS sum_price, AVG(p.price) AS avg_price, COUNT(1) AS count FROM t_order o INNER JOIN t_order_item i ON o.order_id = i.order_id INNER JOIN t_product p ON i.product_id = p.product_id GROUP BY o.order_id HAVING SUM(p.price) > 10000 ORDER BY max_price;
+CREATE VIEW t_order_union_view AS SELECT * FROM t_order WHERE order_id > 2000 UNION SELECT * FROM t_order WHERE order_id > 1500;
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 bddf46a3c4d..97a88ff1533 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
@@ -23,6 +23,10 @@ DROP TABLE IF EXISTS t_product;
DROP TABLE IF EXISTS t_product_detail;
DROP TABLE IF EXISTS t_product_category;
DROP TABLE IF EXISTS t_country;
+DROP VIEW IF EXISTS t_order_item_join_view;
+DROP VIEW IF EXISTS t_order_subquery_view;
+DROP VIEW IF EXISTS t_order_aggregation_view;
+DROP VIEW IF EXISTS t_order_union_view;
-- TODO replace these tables with standard tables
DROP TABLE IF EXISTS t_single_table;
DROP TABLE IF EXISTS t_broadcast_table;
@@ -39,6 +43,10 @@ CREATE TABLE t_product (product_id INT PRIMARY KEY, product_name VARCHAR(50) NOT
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 INT NOT NULL, creation_date DATE NOT NULL);
CREATE TABLE t_country (country_id INT PRIMARY KEY, country_name VARCHAR(50), continent_name VARCHAR(50), creation_date DATE NOT NULL);
+CREATE VIEW t_order_item_join_view AS SELECT o.order_id, o.user_id, i.item_id FROM t_order o INNER JOIN t_order_item i ON o.order_id = i.order_id WHERE o.user_id = 10 ORDER BY o.order_id;
+CREATE VIEW t_order_subquery_view AS SELECT * FROM t_order o WHERE o.order_id IN (SELECT i.order_id FROM t_order_item i INNER JOIN t_product p ON i.product_id = p.product_id WHERE p.product_id = 10);
+CREATE VIEW t_order_aggregation_view AS SELECT MAX(p.price) AS max_price, MIN(p.price) AS min_price, SUM(p.price) AS sum_price, AVG(p.price) AS avg_price, COUNT(1) AS count FROM t_order o INNER JOIN t_order_item i ON o.order_id = i.order_id INNER JOIN t_product p ON i.product_id = p.product_id GROUP BY o.order_id HAVING SUM(p.price) > 10000 ORDER BY max_price;
+CREATE VIEW t_order_union_view AS SELECT * FROM t_order WHERE order_id > 2000 UNION SELECT * FROM t_order WHERE order_id > 1500;
-- TODO replace these tables with standard tables
CREATE TABLE t_single_table (single_id INT NOT NULL, id INT NOT NULL, status VARCHAR(45) NULL, PRIMARY KEY (single_id));
CREATE TABLE t_broadcast_table (id INT NOT NULL, status VARCHAR(45) NULL, PRIMARY KEY (id));
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 aa7dae8d015..ac114705f3e 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
@@ -29,6 +29,10 @@ CREATE TABLE expected_dataset.t_product (product_id INT PRIMARY KEY, product_nam
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 INT NOT NULL, creation_date DATE NOT NULL);
CREATE TABLE expected_dataset.t_country (country_id INT PRIMARY KEY, country_name VARCHAR(50), continent_name VARCHAR(50), creation_date DATE NOT NULL);
+CREATE VIEW expected_dataset.t_order_item_join_view AS SELECT o.order_id, o.user_id, i.item_id FROM expected_dataset.t_order o INNER JOIN expected_dataset.t_order_item i ON o.order_id = i.order_id WHERE o.user_id = 10 ORDER BY o.order_id;
+CREATE VIEW expected_dataset.t_order_subquery_view AS SELECT * FROM expected_dataset.t_order o WHERE o.order_id IN (SELECT i.order_id FROM expected_dataset.t_order_item i INNER JOIN expected_dataset.t_product p ON i.product_id = p.product_id WHERE p.product_id = 10);
+CREATE VIEW expected_dataset.t_order_aggregation_view AS SELECT MAX(p.price) AS max_price, MIN(p.price) AS min_price, SUM(p.price) AS sum_price, AVG(p.price) AS avg_price, COUNT(1) AS count FROM expected_dataset.t_order o INNER JOIN expected_dataset.t_order_item i ON o.order_id = i.order_id INNER JOIN expected_dataset.t_product p ON i.product_id = p.product_id GROUP BY o.order_id HAVING SUM(p.price) > 10000 ORDER BY max_price;
+CREATE VIEW expected_dataset.t_order_union_view AS SELECT * FROM expected_dataset.t_order WHERE order_id > 2000 UNION SELECT * FROM expected_dataset.t_order WHERE order_id > 1500;
-- TODO replace these tables with standard tables
CREATE TABLE expected_dataset.t_single_table (single_id INT NOT NULL, id INT NOT NULL, status VARCHAR(45) NULL, PRIMARY KEY (single_id));
CREATE TABLE expected_dataset.t_broadcast_table (id INT NOT NULL, status VARCHAR(45) NULL, PRIMARY KEY (id));
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 3abbcd96c34..1bd6fd4b467 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
@@ -30,6 +30,10 @@ DROP TABLE IF EXISTS t_product;
DROP TABLE IF EXISTS t_product_detail;
DROP TABLE IF EXISTS t_product_category;
DROP TABLE IF EXISTS t_country;
+DROP VIEW IF EXISTS t_order_item_join_view;
+DROP VIEW IF EXISTS t_order_subquery_view;
+DROP VIEW IF EXISTS t_order_aggregation_view;
+DROP VIEW IF EXISTS t_order_union_view;
-- TODO replace these tables with standard tables
DROP TABLE IF EXISTS t_single_table;
DROP TABLE IF EXISTS t_broadcast_table;
@@ -46,6 +50,10 @@ CREATE TABLE t_product (product_id INT PRIMARY KEY, product_name VARCHAR(50) NOT
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 INT NOT NULL, creation_date DATE NOT NULL);
CREATE TABLE t_country (country_id INT PRIMARY KEY, country_name VARCHAR(50), continent_name VARCHAR(50), creation_date DATE NOT NULL);
+CREATE VIEW t_order_item_join_view AS SELECT o.order_id, o.user_id, i.item_id FROM t_order o INNER JOIN t_order_item i ON o.order_id = i.order_id WHERE o.user_id = 10 ORDER BY o.order_id;
+CREATE VIEW t_order_subquery_view AS SELECT * FROM t_order o WHERE o.order_id IN (SELECT i.order_id FROM t_order_item i INNER JOIN t_product p ON i.product_id = p.product_id WHERE p.product_id = 10);
+CREATE VIEW t_order_aggregation_view AS SELECT MAX(p.price) AS max_price, MIN(p.price) AS min_price, SUM(p.price) AS sum_price, AVG(p.price) AS avg_price, COUNT(1) AS count FROM t_order o INNER JOIN t_order_item i ON o.order_id = i.order_id INNER JOIN t_product p ON i.product_id = p.product_id GROUP BY o.order_id HAVING SUM(p.price) > 10000 ORDER BY max_price;
+CREATE VIEW t_order_union_view AS SELECT * FROM t_order WHERE order_id > 2000 UNION SELECT * FROM t_order WHERE order_id > 1500;
-- TODO replace these tables with standard tables
CREATE TABLE t_single_table (single_id INT NOT NULL, id INT NOT NULL, status VARCHAR(45) NULL, PRIMARY KEY (single_id));
CREATE TABLE t_broadcast_table (id INT NOT NULL, status VARCHAR(45) NULL, PRIMARY KEY (id));
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 3abbcd96c34..1bd6fd4b467 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
@@ -30,6 +30,10 @@ DROP TABLE IF EXISTS t_product;
DROP TABLE IF EXISTS t_product_detail;
DROP TABLE IF EXISTS t_product_category;
DROP TABLE IF EXISTS t_country;
+DROP VIEW IF EXISTS t_order_item_join_view;
+DROP VIEW IF EXISTS t_order_subquery_view;
+DROP VIEW IF EXISTS t_order_aggregation_view;
+DROP VIEW IF EXISTS t_order_union_view;
-- TODO replace these tables with standard tables
DROP TABLE IF EXISTS t_single_table;
DROP TABLE IF EXISTS t_broadcast_table;
@@ -46,6 +50,10 @@ CREATE TABLE t_product (product_id INT PRIMARY KEY, product_name VARCHAR(50) NOT
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 INT NOT NULL, creation_date DATE NOT NULL);
CREATE TABLE t_country (country_id INT PRIMARY KEY, country_name VARCHAR(50), continent_name VARCHAR(50), creation_date DATE NOT NULL);
+CREATE VIEW t_order_item_join_view AS SELECT o.order_id, o.user_id, i.item_id FROM t_order o INNER JOIN t_order_item i ON o.order_id = i.order_id WHERE o.user_id = 10 ORDER BY o.order_id;
+CREATE VIEW t_order_subquery_view AS SELECT * FROM t_order o WHERE o.order_id IN (SELECT i.order_id FROM t_order_item i INNER JOIN t_product p ON i.product_id = p.product_id WHERE p.product_id = 10);
+CREATE VIEW t_order_aggregation_view AS SELECT MAX(p.price) AS max_price, MIN(p.price) AS min_price, SUM(p.price) AS sum_price, AVG(p.price) AS avg_price, COUNT(1) AS count FROM t_order o INNER JOIN t_order_item i ON o.order_id = i.order_id INNER JOIN t_product p ON i.product_id = p.product_id GROUP BY o.order_id HAVING SUM(p.price) > 10000 ORDER BY max_price;
+CREATE VIEW t_order_union_view AS SELECT * FROM t_order WHERE order_id > 2000 UNION SELECT * FROM t_order WHERE order_id > 1500;
-- TODO replace these tables with standard tables
CREATE TABLE t_single_table (single_id INT NOT NULL, id INT NOT NULL, status VARCHAR(45) NULL, PRIMARY KEY (single_id));
CREATE TABLE t_broadcast_table (id INT NOT NULL, status VARCHAR(45) NULL, PRIMARY KEY (id));
diff --git a/test/integration-test/test-suite/src/test/resources/env/scenario/encrypt_shadow/data/expected/init-sql/mysql/expected-init.sql b/test/integration-test/test-suite/src/test/resources/env/scenario/encrypt_shadow/data/expected/init-sql/mysql/01-expected-init.sql
similarity index 100%
rename from test/integration-test/test-suite/src/test/resources/env/scenario/encrypt_shadow/data/expected/init-sql/mysql/expected-init.sql
rename to test/integration-test/test-suite/src/test/resources/env/scenario/encrypt_shadow/data/expected/init-sql/mysql/01-expected-init.sql
diff --git a/test/integration-test/test-suite/src/test/resources/env/scenario/encrypt_shadow/data/expected/init-sql/opengauss/expected-init.sql b/test/integration-test/test-suite/src/test/resources/env/scenario/encrypt_shadow/data/expected/init-sql/opengauss/01-expected-init.sql
similarity index 100%
rename from test/integration-test/test-suite/src/test/resources/env/scenario/encrypt_shadow/data/expected/init-sql/opengauss/expected-init.sql
rename to test/integration-test/test-suite/src/test/resources/env/scenario/encrypt_shadow/data/expected/init-sql/opengauss/01-expected-init.sql
diff --git a/test/integration-test/test-suite/src/test/resources/env/scenario/encrypt_shadow/data/expected/init-sql/oracle/expected-init.sql b/test/integration-test/test-suite/src/test/resources/env/scenario/encrypt_shadow/data/expected/init-sql/oracle/01-expected-init.sql
similarity index 100%
rename from test/integration-test/test-suite/src/test/resources/env/scenario/encrypt_shadow/data/expected/init-sql/oracle/expected-init.sql
rename to test/integration-test/test-suite/src/test/resources/env/scenario/encrypt_shadow/data/expected/init-sql/oracle/01-expected-init.sql
diff --git a/test/integration-test/test-suite/src/test/resources/env/scenario/encrypt_shadow/data/expected/init-sql/postgresql/expected-init.sql b/test/integration-test/test-suite/src/test/resources/env/scenario/encrypt_shadow/data/expected/init-sql/postgresql/01-expected-init.sql
similarity index 100%
rename from test/integration-test/test-suite/src/test/resources/env/scenario/encrypt_shadow/data/expected/init-sql/postgresql/expected-init.sql
rename to test/integration-test/test-suite/src/test/resources/env/scenario/encrypt_shadow/data/expected/init-sql/postgresql/01-expected-init.sql
diff --git a/test/integration-test/test-suite/src/test/resources/env/scenario/encrypt_shadow/data/expected/init-sql/sqlserver/expected-init.sql b/test/integration-test/test-suite/src/test/resources/env/scenario/encrypt_shadow/data/expected/init-sql/sqlserver/01-expected-init.sql
similarity index 100%
rename from test/integration-test/test-suite/src/test/resources/env/scenario/encrypt_shadow/data/expected/init-sql/sqlserver/expected-init.sql
rename to test/integration-test/test-suite/src/test/resources/env/scenario/encrypt_shadow/data/expected/init-sql/sqlserver/01-expected-init.sql
diff --git a/test/integration-test/test-suite/src/test/resources/env/scenario/readwrite_splitting_and_shadow/data/expected/init-sql/mysql/expected-init.sql b/test/integration-test/test-suite/src/test/resources/env/scenario/readwrite_splitting_and_shadow/data/expected/init-sql/mysql/01-expected-init.sql
similarity index 100%
rename from test/integration-test/test-suite/src/test/resources/env/scenario/readwrite_splitting_and_shadow/data/expected/init-sql/mysql/expected-init.sql
rename to test/integration-test/test-suite/src/test/resources/env/scenario/readwrite_splitting_and_shadow/data/expected/init-sql/mysql/01-expected-init.sql
diff --git a/test/integration-test/test-suite/src/test/resources/env/scenario/readwrite_splitting_and_shadow/data/expected/init-sql/opengauss/expected-init.sql b/test/integration-test/test-suite/src/test/resources/env/scenario/readwrite_splitting_and_shadow/data/expected/init-sql/opengauss/01-expected-init.sql
similarity index 100%
rename from test/integration-test/test-suite/src/test/resources/env/scenario/readwrite_splitting_and_shadow/data/expected/init-sql/opengauss/expected-init.sql
rename to test/integration-test/test-suite/src/test/resources/env/scenario/readwrite_splitting_and_shadow/data/expected/init-sql/opengauss/01-expected-init.sql
diff --git a/test/integration-test/test-suite/src/test/resources/env/scenario/readwrite_splitting_and_shadow/data/expected/init-sql/oracle/expected-init.sql b/test/integration-test/test-suite/src/test/resources/env/scenario/readwrite_splitting_and_shadow/data/expected/init-sql/oracle/01-expected-init.sql
similarity index 100%
rename from test/integration-test/test-suite/src/test/resources/env/scenario/readwrite_splitting_and_shadow/data/expected/init-sql/oracle/expected-init.sql
rename to test/integration-test/test-suite/src/test/resources/env/scenario/readwrite_splitting_and_shadow/data/expected/init-sql/oracle/01-expected-init.sql
diff --git a/test/integration-test/test-suite/src/test/resources/env/scenario/readwrite_splitting_and_shadow/data/expected/init-sql/postgresql/expected-init.sql b/test/integration-test/test-suite/src/test/resources/env/scenario/readwrite_splitting_and_shadow/data/expected/init-sql/postgresql/01-expected-init.sql
similarity index 100%
rename from test/integration-test/test-suite/src/test/resources/env/scenario/readwrite_splitting_and_shadow/data/expected/init-sql/postgresql/expected-init.sql
rename to test/integration-test/test-suite/src/test/resources/env/scenario/readwrite_splitting_and_shadow/data/expected/init-sql/postgresql/01-expected-init.sql
diff --git a/test/integration-test/test-suite/src/test/resources/env/scenario/readwrite_splitting_and_shadow/data/expected/init-sql/sqlserver/expected-init.sql b/test/integration-test/test-suite/src/test/resources/env/scenario/readwrite_splitting_and_shadow/data/expected/init-sql/sqlserver/01-expected-init.sql
similarity index 100%
rename from test/integration-test/test-suite/src/test/resources/env/scenario/readwrite_splitting_and_shadow/data/expected/init-sql/sqlserver/expected-init.sql
rename to test/integration-test/test-suite/src/test/resources/env/scenario/readwrite_splitting_and_shadow/data/expected/init-sql/sqlserver/01-expected-init.sql
diff --git a/test/integration-test/test-suite/src/test/resources/env/scenario/shadow/data/expected/init-sql/mysql/expected-init.sql b/test/integration-test/test-suite/src/test/resources/env/scenario/shadow/data/expected/init-sql/mysql/01-expected-init.sql
similarity index 100%
rename from test/integration-test/test-suite/src/test/resources/env/scenario/shadow/data/expected/init-sql/mysql/expected-init.sql
rename to test/integration-test/test-suite/src/test/resources/env/scenario/shadow/data/expected/init-sql/mysql/01-expected-init.sql
diff --git a/test/integration-test/test-suite/src/test/resources/env/scenario/shadow/data/expected/init-sql/opengauss/expected-init.sql b/test/integration-test/test-suite/src/test/resources/env/scenario/shadow/data/expected/init-sql/opengauss/01-expected-init.sql
similarity index 100%
rename from test/integration-test/test-suite/src/test/resources/env/scenario/shadow/data/expected/init-sql/opengauss/expected-init.sql
rename to test/integration-test/test-suite/src/test/resources/env/scenario/shadow/data/expected/init-sql/opengauss/01-expected-init.sql
diff --git a/test/integration-test/test-suite/src/test/resources/env/scenario/shadow/data/expected/init-sql/oracle/expected-init.sql b/test/integration-test/test-suite/src/test/resources/env/scenario/shadow/data/expected/init-sql/oracle/01-expected-init.sql
similarity index 100%
rename from test/integration-test/test-suite/src/test/resources/env/scenario/shadow/data/expected/init-sql/oracle/expected-init.sql
rename to test/integration-test/test-suite/src/test/resources/env/scenario/shadow/data/expected/init-sql/oracle/01-expected-init.sql
diff --git a/test/integration-test/test-suite/src/test/resources/env/scenario/shadow/data/expected/init-sql/postgresql/expected-init.sql b/test/integration-test/test-suite/src/test/resources/env/scenario/shadow/data/expected/init-sql/postgresql/01-expected-init.sql
similarity index 100%
rename from test/integration-test/test-suite/src/test/resources/env/scenario/shadow/data/expected/init-sql/postgresql/expected-init.sql
rename to test/integration-test/test-suite/src/test/resources/env/scenario/shadow/data/expected/init-sql/postgresql/01-expected-init.sql
diff --git a/test/integration-test/test-suite/src/test/resources/env/scenario/shadow/data/expected/init-sql/sqlserver/expected-init.sql b/test/integration-test/test-suite/src/test/resources/env/scenario/shadow/data/expected/init-sql/sqlserver/01-expected-init.sql
similarity index 100%
rename from test/integration-test/test-suite/src/test/resources/env/scenario/shadow/data/expected/init-sql/sqlserver/expected-init.sql
rename to test/integration-test/test-suite/src/test/resources/env/scenario/shadow/data/expected/init-sql/sqlserver/01-expected-init.sql
diff --git a/test/integration-test/test-suite/src/test/resources/env/scenario/sharding_and_shadow/data/expected/init-sql/mysql/expected-init.sql b/test/integration-test/test-suite/src/test/resources/env/scenario/sharding_and_shadow/data/expected/init-sql/mysql/01-expected-init.sql
similarity index 100%
rename from test/integration-test/test-suite/src/test/resources/env/scenario/sharding_and_shadow/data/expected/init-sql/mysql/expected-init.sql
rename to test/integration-test/test-suite/src/test/resources/env/scenario/sharding_and_shadow/data/expected/init-sql/mysql/01-expected-init.sql
diff --git a/test/integration-test/test-suite/src/test/resources/env/scenario/sharding_and_shadow/data/expected/init-sql/opengauss/expected-init.sql b/test/integration-test/test-suite/src/test/resources/env/scenario/sharding_and_shadow/data/expected/init-sql/opengauss/01-expected-init.sql
similarity index 100%
rename from test/integration-test/test-suite/src/test/resources/env/scenario/sharding_and_shadow/data/expected/init-sql/opengauss/expected-init.sql
rename to test/integration-test/test-suite/src/test/resources/env/scenario/sharding_and_shadow/data/expected/init-sql/opengauss/01-expected-init.sql
diff --git a/test/integration-test/test-suite/src/test/resources/env/scenario/sharding_and_shadow/data/expected/init-sql/oracle/expected-init.sql b/test/integration-test/test-suite/src/test/resources/env/scenario/sharding_and_shadow/data/expected/init-sql/oracle/01-expected-init.sql
similarity index 100%
rename from test/integration-test/test-suite/src/test/resources/env/scenario/sharding_and_shadow/data/expected/init-sql/oracle/expected-init.sql
rename to test/integration-test/test-suite/src/test/resources/env/scenario/sharding_and_shadow/data/expected/init-sql/oracle/01-expected-init.sql
diff --git a/test/integration-test/test-suite/src/test/resources/env/scenario/sharding_and_shadow/data/expected/init-sql/postgresql/expected-init.sql b/test/integration-test/test-suite/src/test/resources/env/scenario/sharding_and_shadow/data/expected/init-sql/postgresql/01-expected-init.sql
similarity index 100%
rename from test/integration-test/test-suite/src/test/resources/env/scenario/sharding_and_shadow/data/expected/init-sql/postgresql/expected-init.sql
rename to test/integration-test/test-suite/src/test/resources/env/scenario/sharding_and_shadow/data/expected/init-sql/postgresql/01-expected-init.sql
diff --git a/test/integration-test/test-suite/src/test/resources/env/scenario/sharding_and_shadow/data/expected/init-sql/sqlserver/expected-init.sql b/test/integration-test/test-suite/src/test/resources/env/scenario/sharding_and_shadow/data/expected/init-sql/sqlserver/01-expected-init.sql
similarity index 100%
rename from test/integration-test/test-suite/src/test/resources/env/scenario/sharding_and_shadow/data/expected/init-sql/sqlserver/expected-init.sql
rename to test/integration-test/test-suite/src/test/resources/env/scenario/sharding_and_shadow/data/expected/init-sql/sqlserver/01-expected-init.sql
diff --git a/test/integration-test/test-suite/src/test/resources/env/scenario/sharding_encrypt_shadow/data/expected/init-sql/mysql/expected-init.sql b/test/integration-test/test-suite/src/test/resources/env/scenario/sharding_encrypt_shadow/data/expected/init-sql/mysql/01-expected-init.sql
similarity index 100%
rename from test/integration-test/test-suite/src/test/resources/env/scenario/sharding_encrypt_shadow/data/expected/init-sql/mysql/expected-init.sql
rename to test/integration-test/test-suite/src/test/resources/env/scenario/sharding_encrypt_shadow/data/expected/init-sql/mysql/01-expected-init.sql
diff --git a/test/integration-test/test-suite/src/test/resources/env/scenario/sharding_encrypt_shadow/data/expected/init-sql/opengauss/expected-init.sql b/test/integration-test/test-suite/src/test/resources/env/scenario/sharding_encrypt_shadow/data/expected/init-sql/opengauss/01-expected-init.sql
similarity index 100%
rename from test/integration-test/test-suite/src/test/resources/env/scenario/sharding_encrypt_shadow/data/expected/init-sql/opengauss/expected-init.sql
rename to test/integration-test/test-suite/src/test/resources/env/scenario/sharding_encrypt_shadow/data/expected/init-sql/opengauss/01-expected-init.sql
diff --git a/test/integration-test/test-suite/src/test/resources/env/scenario/sharding_encrypt_shadow/data/expected/init-sql/oracle/expected-init.sql b/test/integration-test/test-suite/src/test/resources/env/scenario/sharding_encrypt_shadow/data/expected/init-sql/oracle/01-expected-init.sql
similarity index 100%
rename from test/integration-test/test-suite/src/test/resources/env/scenario/sharding_encrypt_shadow/data/expected/init-sql/oracle/expected-init.sql
rename to test/integration-test/test-suite/src/test/resources/env/scenario/sharding_encrypt_shadow/data/expected/init-sql/oracle/01-expected-init.sql
diff --git a/test/integration-test/test-suite/src/test/resources/env/scenario/sharding_encrypt_shadow/data/expected/init-sql/postgresql/expected-init.sql b/test/integration-test/test-suite/src/test/resources/env/scenario/sharding_encrypt_shadow/data/expected/init-sql/postgresql/01-expected-init.sql
similarity index 100%
rename from test/integration-test/test-suite/src/test/resources/env/scenario/sharding_encrypt_shadow/data/expected/init-sql/postgresql/expected-init.sql
rename to test/integration-test/test-suite/src/test/resources/env/scenario/sharding_encrypt_shadow/data/expected/init-sql/postgresql/01-expected-init.sql
diff --git a/test/integration-test/test-suite/src/test/resources/env/scenario/sharding_encrypt_shadow/data/expected/init-sql/sqlserver/expected-init.sql b/test/integration-test/test-suite/src/test/resources/env/scenario/sharding_encrypt_shadow/data/expected/init-sql/sqlserver/01-expected-init.sql
similarity index 100%
rename from test/integration-test/test-suite/src/test/resources/env/scenario/sharding_encrypt_shadow/data/expected/init-sql/sqlserver/expected-init.sql
rename to test/integration-test/test-suite/src/test/resources/env/scenario/sharding_encrypt_shadow/data/expected/init-sql/sqlserver/01-expected-init.sql
diff --git a/test/integration-test/transaction/src/test/resources/env/scenario/default/data/expected/init-sql/mysql/expected-init.sql b/test/integration-test/transaction/src/test/resources/env/scenario/default/data/expected/init-sql/mysql/01-expected-init.sql
similarity index 100%
rename from test/integration-test/transaction/src/test/resources/env/scenario/default/data/expected/init-sql/mysql/expected-init.sql
rename to test/integration-test/transaction/src/test/resources/env/scenario/default/data/expected/init-sql/mysql/01-expected-init.sql
diff --git a/test/integration-test/transaction/src/test/resources/env/scenario/default/data/expected/init-sql/opengauss/expected-init.sql b/test/integration-test/transaction/src/test/resources/env/scenario/default/data/expected/init-sql/opengauss/01-expected-init.sql
similarity index 100%
rename from test/integration-test/transaction/src/test/resources/env/scenario/default/data/expected/init-sql/opengauss/expected-init.sql
rename to test/integration-test/transaction/src/test/resources/env/scenario/default/data/expected/init-sql/opengauss/01-expected-init.sql
diff --git a/test/integration-test/transaction/src/test/resources/env/scenario/default/data/expected/init-sql/postgresql/expected-init.sql b/test/integration-test/transaction/src/test/resources/env/scenario/default/data/expected/init-sql/postgresql/01-expected-init.sql
similarity index 100%
rename from test/integration-test/transaction/src/test/resources/env/scenario/default/data/expected/init-sql/postgresql/expected-init.sql
rename to test/integration-test/transaction/src/test/resources/env/scenario/default/data/expected/init-sql/postgresql/01-expected-init.sql