You are viewing a plain text version of this content. The canonical link for it is here.
Posted to notifications@shardingsphere.apache.org by pa...@apache.org on 2022/10/10 10:10:10 UTC
[shardingsphere] branch master updated: Optimize combine statement convert logic and SQLFederationRowEnumerator logic (#21444)
This is an automated email from the ASF dual-hosted git repository.
panjuan pushed a commit to branch master
in repository https://gitbox.apache.org/repos/asf/shardingsphere.git
The following commit(s) were added to refs/heads/master by this push:
new 865fd40dabf Optimize combine statement convert logic and SQLFederationRowEnumerator logic (#21444)
865fd40dabf is described below
commit 865fd40dabf3b3b967fef530d939fe5fc46af4f3
Author: Zhengqiang Duan <du...@apache.org>
AuthorDate: Mon Oct 10 18:09:58 2022 +0800
Optimize combine statement convert logic and SQLFederationRowEnumerator logic (#21444)
---
.../executor/FilterableTableScanExecutor.java | 18 +-
.../executor/TranslatableTableScanExecutor.java | 18 +-
.../row/SQLFederationRowEnumerator.java | 39 ++--
.../converter/SQLNodeConverterEngine.java | 22 +-
.../statement/select/SelectStatementConverter.java | 35 +++-
.../fixture/ClusterPersistRepositoryFixture.java | 1 -
.../fixture/ClusterPersistRepositoryFixture.java | 1 -
.../cases/dql/dql-integration-test-cases.xml | 96 ++++++++-
.../SQLNodeConverterEngineParameterizedTest.java | 11 +-
.../src/main/resources/case/dml/select-union.xml | 233 ++++++++++++++++++++-
.../resources/sql/supported/dml/select-union.xml | 13 +-
11 files changed, 411 insertions(+), 76 deletions(-)
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 8f1d374ce76..9e169b54e20 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
@@ -216,16 +216,30 @@ public final class FilterableTableScanExecutor implements TableScanExecutor {
return result;
}
- private AbstractEnumerable<Object[]> createEnumerable(final MergedResult mergedResult, final QueryResultMetaData metaData, final Collection<Statement> statements) {
+ private AbstractEnumerable<Object[]> createEnumerable(final MergedResult mergedResult, final QueryResultMetaData metaData, final Collection<Statement> statements) throws SQLException {
+ // TODO remove getRows when mergedResult support JDBC first method
+ Collection<Object[]> rows = getRows(mergedResult, metaData);
return new AbstractEnumerable<Object[]>() {
@Override
public Enumerator<Object[]> enumerator() {
- return new SQLFederationRowEnumerator(mergedResult, metaData, statements);
+ return new SQLFederationRowEnumerator(rows, statements);
}
};
}
+ private Collection<Object[]> getRows(final MergedResult mergedResult, final QueryResultMetaData metaData) throws SQLException {
+ Collection<Object[]> result = new LinkedList<>();
+ while (mergedResult.next()) {
+ Object[] currentRow = new Object[metaData.getColumnCount()];
+ for (int i = 0; i < metaData.getColumnCount(); i++) {
+ currentRow[i] = mergedResult.getValue(i + 1, Object.class);
+ }
+ result.add(currentRow);
+ }
+ return result;
+ }
+
private QueryContext createQueryContext(final Map<String, ShardingSphereDatabase> databases, final SqlString sqlString, final DatabaseType databaseType) {
String sql = sqlString.getSql().replace("\n", " ");
SQLStatement sqlStatement = new SQLStatementParserEngine(databaseType.getType(),
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 00d9c1c577b..0d7faa218f6 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
@@ -233,16 +233,30 @@ public final class TranslatableTableScanExecutor implements TableScanExecutor {
return result;
}
- private AbstractEnumerable<Object[]> createEnumerable(final MergedResult mergedResult, final QueryResultMetaData metaData, final Collection<Statement> statements) {
+ private AbstractEnumerable<Object[]> createEnumerable(final MergedResult mergedResult, final QueryResultMetaData metaData, final Collection<Statement> statements) throws SQLException {
+ // TODO remove getRows when mergedResult support JDBC first method
+ Collection<Object[]> rows = getRows(mergedResult, metaData);
return new AbstractEnumerable<Object[]>() {
@Override
public Enumerator<Object[]> enumerator() {
- return new SQLFederationRowEnumerator(mergedResult, metaData, statements);
+ return new SQLFederationRowEnumerator(rows, statements);
}
};
}
+ private Collection<Object[]> getRows(final MergedResult mergedResult, final QueryResultMetaData metaData) throws SQLException {
+ Collection<Object[]> result = new LinkedList<>();
+ while (mergedResult.next()) {
+ Object[] currentRow = new Object[metaData.getColumnCount()];
+ for (int i = 0; i < metaData.getColumnCount(); i++) {
+ currentRow[i] = mergedResult.getValue(i + 1, Object.class);
+ }
+ result.add(currentRow);
+ }
+ return result;
+ }
+
private QueryContext createQueryContext(final Map<String, ShardingSphereDatabase> databases, final SqlString sqlString, final DatabaseType databaseType) {
String sql = sqlString.getSql().replace("\n", " ");
SQLStatement sqlStatement = new SQLStatementParserEngine(databaseType.getType(),
diff --git a/kernel/sql-federation/executor/core/src/main/java/org/apache/shardingsphere/sqlfederation/row/SQLFederationRowEnumerator.java b/kernel/sql-federation/executor/core/src/main/java/org/apache/shardingsphere/sqlfederation/row/SQLFederationRowEnumerator.java
index 635d19a060b..c192b16f52d 100644
--- a/kernel/sql-federation/executor/core/src/main/java/org/apache/shardingsphere/sqlfederation/row/SQLFederationRowEnumerator.java
+++ b/kernel/sql-federation/executor/core/src/main/java/org/apache/shardingsphere/sqlfederation/row/SQLFederationRowEnumerator.java
@@ -17,30 +17,33 @@
package org.apache.shardingsphere.sqlfederation.row;
-import lombok.RequiredArgsConstructor;
import org.apache.calcite.linq4j.Enumerator;
-import org.apache.shardingsphere.infra.executor.sql.execute.result.query.QueryResultMetaData;
-import org.apache.shardingsphere.infra.merge.result.MergedResult;
import org.apache.shardingsphere.infra.util.exception.external.sql.type.wrapper.SQLWrapperException;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Collection;
+import java.util.Iterator;
/**
* SQL federation row enumerator.
*/
-@RequiredArgsConstructor
public final class SQLFederationRowEnumerator implements Enumerator<Object[]> {
- private final MergedResult queryResult;
-
- private final QueryResultMetaData metaData;
+ private final Collection<Object[]> rows;
private final Collection<Statement> statements;
+ private Iterator<Object[]> iterator;
+
private Object[] currentRow;
+ public SQLFederationRowEnumerator(final Collection<Object[]> rows, final Collection<Statement> statements) {
+ this.rows = rows;
+ this.statements = statements;
+ iterator = rows.iterator();
+ }
+
@Override
public Object[] current() {
return currentRow;
@@ -48,28 +51,15 @@ public final class SQLFederationRowEnumerator implements Enumerator<Object[]> {
@Override
public boolean moveNext() {
- try {
- return moveNext0();
- } catch (final SQLException ex) {
- throw new RuntimeException(ex);
- }
- }
-
- private boolean moveNext0() throws SQLException {
- if (queryResult.next()) {
- setCurrentRow();
+ if (iterator.hasNext()) {
+ currentRow = iterator.next();
return true;
}
+ currentRow = null;
+ iterator = rows.iterator();
return false;
}
- private void setCurrentRow() throws SQLException {
- currentRow = new Object[metaData.getColumnCount()];
- for (int i = 0; i < metaData.getColumnCount(); i++) {
- currentRow[i] = queryResult.getValue(i + 1, Object.class);
- }
- }
-
@Override
public void reset() {
}
@@ -81,6 +71,7 @@ public final class SQLFederationRowEnumerator implements Enumerator<Object[]> {
each.close();
}
currentRow = null;
+ iterator = rows.iterator();
} catch (final SQLException ex) {
throw new SQLWrapperException(ex);
}
diff --git a/kernel/sql-federation/optimizer/src/main/java/org/apache/shardingsphere/sqlfederation/optimizer/converter/SQLNodeConverterEngine.java b/kernel/sql-federation/optimizer/src/main/java/org/apache/shardingsphere/sqlfederation/optimizer/converter/SQLNodeConverterEngine.java
index 341269d7763..5cf2b04547c 100644
--- a/kernel/sql-federation/optimizer/src/main/java/org/apache/shardingsphere/sqlfederation/optimizer/converter/SQLNodeConverterEngine.java
+++ b/kernel/sql-federation/optimizer/src/main/java/org/apache/shardingsphere/sqlfederation/optimizer/converter/SQLNodeConverterEngine.java
@@ -19,17 +19,11 @@ package org.apache.shardingsphere.sqlfederation.optimizer.converter;
import lombok.AccessLevel;
import lombok.NoArgsConstructor;
-import org.apache.calcite.sql.SqlBasicCall;
import org.apache.calcite.sql.SqlNode;
-import org.apache.calcite.sql.parser.SqlParserPos;
-import org.apache.shardingsphere.sql.parser.sql.common.segment.dml.combine.CombineSegment;
import org.apache.shardingsphere.sql.parser.sql.common.statement.SQLStatement;
import org.apache.shardingsphere.sql.parser.sql.common.statement.dml.SelectStatement;
import org.apache.shardingsphere.sqlfederation.optimizer.converter.exception.OptimizationSQLNodeConvertException;
import org.apache.shardingsphere.sqlfederation.optimizer.converter.statement.select.SelectStatementConverter;
-import org.apache.shardingsphere.sqlfederation.optimizer.converter.type.CombineOperatorConverter;
-
-import java.util.Arrays;
/**
* SQL node converter engine.
@@ -45,22 +39,8 @@ public final class SQLNodeConverterEngine {
*/
public static SqlNode convert(final SQLStatement statement) {
if (statement instanceof SelectStatement) {
- SqlNode result = new SelectStatementConverter().convert((SelectStatement) statement);
- if (((SelectStatement) statement).getCombine().isPresent()) {
- return convert(result, (SelectStatement) statement);
- }
- return result;
+ return new SelectStatementConverter().convert((SelectStatement) statement);
}
throw new OptimizationSQLNodeConvertException(statement);
}
-
- private static SqlNode convert(final SqlNode sqlNode, final SelectStatement selectStatement) {
- if (selectStatement.getCombine().isPresent()) {
- CombineSegment combineSegment = selectStatement.getCombine().get();
- SqlNode combineSqlNode = new SqlBasicCall(CombineOperatorConverter.convert(combineSegment.getCombineType()),
- Arrays.asList(sqlNode, new SelectStatementConverter().convert(combineSegment.getSelectStatement())), SqlParserPos.ZERO);
- return convert(combineSqlNode, combineSegment.getSelectStatement());
- }
- return sqlNode;
- }
}
diff --git a/kernel/sql-federation/optimizer/src/main/java/org/apache/shardingsphere/sqlfederation/optimizer/converter/statement/select/SelectStatementConverter.java b/kernel/sql-federation/optimizer/src/main/java/org/apache/shardingsphere/sqlfederation/optimizer/converter/statement/select/SelectStatementConverter.java
index eb8d71cdee5..3648835265d 100644
--- a/kernel/sql-federation/optimizer/src/main/java/org/apache/shardingsphere/sqlfederation/optimizer/converter/statement/select/SelectStatementConverter.java
+++ b/kernel/sql-federation/optimizer/src/main/java/org/apache/shardingsphere/sqlfederation/optimizer/converter/statement/select/SelectStatementConverter.java
@@ -17,11 +17,13 @@
package org.apache.shardingsphere.sqlfederation.optimizer.converter.statement.select;
+import org.apache.calcite.sql.SqlBasicCall;
import org.apache.calcite.sql.SqlNode;
import org.apache.calcite.sql.SqlNodeList;
import org.apache.calcite.sql.SqlOrderBy;
import org.apache.calcite.sql.SqlSelect;
import org.apache.calcite.sql.parser.SqlParserPos;
+import org.apache.shardingsphere.sql.parser.sql.common.segment.dml.combine.CombineSegment;
import org.apache.shardingsphere.sql.parser.sql.common.segment.dml.pagination.limit.LimitSegment;
import org.apache.shardingsphere.sql.parser.sql.common.statement.dml.SelectStatement;
import org.apache.shardingsphere.sql.parser.sql.dialect.handler.dml.SelectStatementHandler;
@@ -34,7 +36,9 @@ import org.apache.shardingsphere.sqlfederation.optimizer.converter.segment.proje
import org.apache.shardingsphere.sqlfederation.optimizer.converter.segment.projection.ProjectionsConverter;
import org.apache.shardingsphere.sqlfederation.optimizer.converter.segment.where.WhereConverter;
import org.apache.shardingsphere.sqlfederation.optimizer.converter.statement.SQLStatementConverter;
+import org.apache.shardingsphere.sqlfederation.optimizer.converter.type.CombineOperatorConverter;
+import java.util.Arrays;
import java.util.Optional;
/**
@@ -44,20 +48,35 @@ public final class SelectStatementConverter implements SQLStatementConverter<Sel
@Override
public SqlNode convert(final SelectStatement selectStatement) {
+ SqlSelect sqlSelect = convertSelect(selectStatement);
+ SqlNode sqlCombine = convertCombine(sqlSelect, selectStatement);
+ SqlNodeList orderBy = selectStatement.getOrderBy().flatMap(optional -> new OrderByConverter().convert(optional)).orElse(SqlNodeList.EMPTY);
+ Optional<LimitSegment> limit = SelectStatementHandler.getLimitSegment(selectStatement);
+ if (limit.isPresent()) {
+ SqlNode offset = limit.get().getOffset().flatMap(optional -> new PaginationValueSQLConverter().convert(optional)).orElse(null);
+ SqlNode rowCount = limit.get().getRowCount().flatMap(optional -> new PaginationValueSQLConverter().convert(optional)).orElse(null);
+ return new SqlOrderBy(SqlParserPos.ZERO, sqlCombine, orderBy, offset, rowCount);
+ }
+ return !orderBy.isEmpty() ? new SqlOrderBy(SqlParserPos.ZERO, sqlCombine, orderBy, null, null) : sqlCombine;
+ }
+
+ private static SqlSelect convertSelect(final SelectStatement selectStatement) {
SqlNodeList distinct = new DistinctConverter().convert(selectStatement.getProjections()).orElse(null);
SqlNodeList projection = new ProjectionsConverter().convert(selectStatement.getProjections()).orElseThrow(IllegalStateException::new);
SqlNode from = new TableConverter().convert(selectStatement.getFrom()).orElse(null);
SqlNode where = selectStatement.getWhere().flatMap(optional -> new WhereConverter().convert(optional)).orElse(null);
SqlNodeList groupBy = selectStatement.getGroupBy().flatMap(optional -> new GroupByConverter().convert(optional)).orElse(null);
SqlNode having = selectStatement.getHaving().flatMap(optional -> new HavingConverter().convert(optional)).orElse(null);
- SqlNodeList orderBy = selectStatement.getOrderBy().flatMap(optional -> new OrderByConverter().convert(optional)).orElse(SqlNodeList.EMPTY);
- Optional<LimitSegment> limit = SelectStatementHandler.getLimitSegment(selectStatement);
- SqlSelect sqlSelect = new SqlSelect(SqlParserPos.ZERO, distinct, projection, from, where, groupBy, having, SqlNodeList.EMPTY, null, null, null, SqlNodeList.EMPTY);
- if (limit.isPresent()) {
- SqlNode offset = limit.get().getOffset().flatMap(optional -> new PaginationValueSQLConverter().convert(optional)).orElse(null);
- SqlNode rowCount = limit.get().getRowCount().flatMap(optional -> new PaginationValueSQLConverter().convert(optional)).orElse(null);
- return new SqlOrderBy(SqlParserPos.ZERO, sqlSelect, orderBy, offset, rowCount);
+ return new SqlSelect(SqlParserPos.ZERO, distinct, projection, from, where, groupBy, having, SqlNodeList.EMPTY, null, null, null, SqlNodeList.EMPTY);
+ }
+
+ private static SqlNode convertCombine(final SqlNode sqlNode, final SelectStatement selectStatement) {
+ if (selectStatement.getCombine().isPresent()) {
+ CombineSegment combineSegment = selectStatement.getCombine().get();
+ SqlNode combineSqlNode = new SqlBasicCall(CombineOperatorConverter.convert(combineSegment.getCombineType()),
+ Arrays.asList(sqlNode, convertSelect(combineSegment.getSelectStatement())), SqlParserPos.ZERO);
+ return convertCombine(combineSqlNode, combineSegment.getSelectStatement());
}
- return !orderBy.isEmpty() ? new SqlOrderBy(SqlParserPos.ZERO, sqlSelect, orderBy, null, null) : sqlSelect;
+ return sqlNode;
}
}
diff --git a/mode/type/cluster/core/src/test/java/org/apache/shardingsphere/mode/manager/cluster/coordinator/fixture/ClusterPersistRepositoryFixture.java b/mode/type/cluster/core/src/test/java/org/apache/shardingsphere/mode/manager/cluster/coordinator/fixture/ClusterPersistRepositoryFixture.java
index a379d9ab31e..3c6f7a93159 100644
--- a/mode/type/cluster/core/src/test/java/org/apache/shardingsphere/mode/manager/cluster/coordinator/fixture/ClusterPersistRepositoryFixture.java
+++ b/mode/type/cluster/core/src/test/java/org/apache/shardingsphere/mode/manager/cluster/coordinator/fixture/ClusterPersistRepositoryFixture.java
@@ -124,7 +124,6 @@ public final class ClusterPersistRepositoryFixture implements ClusterPersistRepo
@Override
public void watch(final String key, final DataChangedEventListener listener, final Executor executor) {
-
}
@Override
diff --git a/proxy/bootstrap/src/test/java/org/apache/shardingsphere/proxy/fixture/ClusterPersistRepositoryFixture.java b/proxy/bootstrap/src/test/java/org/apache/shardingsphere/proxy/fixture/ClusterPersistRepositoryFixture.java
index 9c95e5f2370..437ec156d15 100644
--- a/proxy/bootstrap/src/test/java/org/apache/shardingsphere/proxy/fixture/ClusterPersistRepositoryFixture.java
+++ b/proxy/bootstrap/src/test/java/org/apache/shardingsphere/proxy/fixture/ClusterPersistRepositoryFixture.java
@@ -65,7 +65,6 @@ public final class ClusterPersistRepositoryFixture implements ClusterPersistRepo
@Override
public void updateInTransaction(final String key, final String value) {
-
}
@Override
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 ad505a46604..77d0d0625bf 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
@@ -708,8 +708,100 @@
<assertion parameters="10000: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">
- <assertion parameters="10:int" expected-data-source-name="read_dataset" />
+ <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">
+ <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">
+ <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">
+ <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">
+ <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">
+ <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">
+ <assertion parameters="2500:int" expected-data-source-name="read_dataset" />
+ </test-case>
+
+ <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>
+
+ <test-case sql="SELECT * FROM t_order WHERE order_id > ? INTERSECT SELECT * FROM t_order WHERE order_id > ? ORDER BY order_id" db-types="PostgreSQL,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 > ? INTERSECT ALL 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">
+ <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 > ? INTERSECT 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">
+ <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 > ? INTERSECT ALL SELECT u.user_id FROM t_user u ORDER BY user_id" db-types="PostgreSQL,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 > ? INTERSECT SELECT u.user_id FROM t_user u ORDER BY user_id" db-types="PostgreSQL,openGauss" scenario-types="db">
+ <assertion parameters="2500:int" expected-data-source-name="read_dataset" />
+ </test-case>
+
+ <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>
+
+ <test-case sql="SELECT * FROM t_order WHERE order_id > ? EXCEPT SELECT * FROM t_order WHERE order_id > ? ORDER BY order_id" db-types="PostgreSQL,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 > ? EXCEPT ALL 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">
+ <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 > ? EXCEPT 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">
+ <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 > ? EXCEPT ALL SELECT u.user_id FROM t_user u ORDER BY user_id" db-types="PostgreSQL,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 > ? EXCEPT SELECT u.user_id FROM t_user u ORDER BY user_id" db-types="PostgreSQL,openGauss" scenario-types="db">
+ <assertion parameters="2500:int" expected-data-source-name="read_dataset" />
+ </test-case>
+
+ <test-case sql="SELECT * FROM t_order MINUS ALL SELECT * FROM t_order ORDER BY order_id" db-types="openGauss" scenario-types="db">
+ <assertion expected-data-source-name="read_dataset" />
+ </test-case>
+
+ <test-case sql="SELECT * FROM t_order WHERE order_id > ? MINUS SELECT * FROM t_order WHERE order_id > ? ORDER BY order_id" db-types="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 > ? MINUS ALL SELECT i.order_id, i.user_id FROM t_order_item i WHERE i.order_id > ? ORDER BY order_id" db-types="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 > ? MINUS SELECT i.order_id, i.user_id FROM t_order_item i WHERE i.order_id > ? ORDER BY order_id" db-types="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 > ? MINUS ALL SELECT u.user_id FROM t_user u ORDER BY user_id" db-types="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 > ? MINUS SELECT u.user_id FROM t_user u ORDER BY user_id" db-types="openGauss" scenario-types="db">
+ <assertion parameters="2500:int" expected-data-source-name="read_dataset" />
</test-case>
<!-- TODO FIX ME Expected: is "true" but was "1"-->
diff --git a/test/optimize/src/test/java/org/apache/shardingsphere/infra/federation/converter/parameterized/engine/SQLNodeConverterEngineParameterizedTest.java b/test/optimize/src/test/java/org/apache/shardingsphere/infra/federation/converter/parameterized/engine/SQLNodeConverterEngineParameterizedTest.java
index 5f9a925ea84..d65413da202 100644
--- a/test/optimize/src/test/java/org/apache/shardingsphere/infra/federation/converter/parameterized/engine/SQLNodeConverterEngineParameterizedTest.java
+++ b/test/optimize/src/test/java/org/apache/shardingsphere/infra/federation/converter/parameterized/engine/SQLNodeConverterEngineParameterizedTest.java
@@ -126,12 +126,19 @@ public final class SQLNodeConverterEngineParameterizedTest {
SUPPORTED_SQL_CASE_IDS.add("select_pagination_with_offset_fetch");
SUPPORTED_SQL_CASE_IDS.add("select_pagination_with_limit_offset_and_row_count");
SUPPORTED_SQL_CASE_IDS.add("select_pagination_with_limit_row_count");
- SUPPORTED_SQL_CASE_IDS.add("select_with_union");
- SUPPORTED_SQL_CASE_IDS.add("select_with_union_all");
SUPPORTED_SQL_CASE_IDS.add("select_union");
+ SUPPORTED_SQL_CASE_IDS.add("select_union_all");
+ SUPPORTED_SQL_CASE_IDS.add("select_union_all_order_by");
+ SUPPORTED_SQL_CASE_IDS.add("select_union_all_order_by_limit");
SUPPORTED_SQL_CASE_IDS.add("select_intersect");
+ SUPPORTED_SQL_CASE_IDS.add("select_intersect_order_by");
+ SUPPORTED_SQL_CASE_IDS.add("select_intersect_order_by_limit");
SUPPORTED_SQL_CASE_IDS.add("select_except");
+ SUPPORTED_SQL_CASE_IDS.add("select_except_order_by");
+ SUPPORTED_SQL_CASE_IDS.add("select_except_order_by_limit");
SUPPORTED_SQL_CASE_IDS.add("select_minus");
+ SUPPORTED_SQL_CASE_IDS.add("select_minus_order_by");
+ SUPPORTED_SQL_CASE_IDS.add("select_minus_order_by_limit");
}
// CHECKSTYLE:ON
diff --git a/test/parser/src/main/resources/case/dml/select-union.xml b/test/parser/src/main/resources/case/dml/select-union.xml
index ab9c67c199f..04bcdc2dcec 100644
--- a/test/parser/src/main/resources/case/dml/select-union.xml
+++ b/test/parser/src/main/resources/case/dml/select-union.xml
@@ -17,7 +17,7 @@
-->
<sql-parser-test-cases>
- <select sql-case-id="select_with_union">
+ <select sql-case-id="select_union">
<projections start-index="7" stop-index="7">
<shorthand-projection start-index="7" stop-index="7" />
</projections>
@@ -36,7 +36,7 @@
</combine>
</select>
- <select sql-case-id="select_with_union_all">
+ <select sql-case-id="select_union_all">
<projections start-index="7" stop-index="7">
<shorthand-projection start-index="7" stop-index="7" />
</projections>
@@ -55,25 +55,54 @@
</combine>
</select>
- <select sql-case-id="select_union">
- <projections start-index="7" stop-index="14">
- <column-projection name="order_id" start-index="7" stop-index="14" />
+ <select sql-case-id="select_union_all_order_by">
+ <projections start-index="7" stop-index="7">
+ <shorthand-projection start-index="7" stop-index="7" />
</projections>
<from>
- <simple-table name="t_order" start-index="21" stop-index="27" />
+ <simple-table name="table1" start-index="14" stop-index="19" />
</from>
- <combine combine-type="UNION" start-index="29" stop-index="67">
+ <combine combine-type="UNION_ALL" start-index="21" stop-index="50">
<select>
- <projections start-index="42" stop-index="49">
- <column-projection name="order_id" start-index="42" stop-index="49" />
+ <projections start-index="38" stop-index="38">
+ <shorthand-projection start-index="38" stop-index="38" />
</projections>
<from>
- <simple-table name="t_order_item" start-index="56" stop-index="67" />
+ <simple-table name="table2" start-index="45" stop-index="50" />
</from>
</select>
</combine>
+ <order-by>
+ <column-item name="id" start-index="61" stop-index="62" />
+ </order-by>
</select>
+ <select sql-case-id="select_union_all_order_by_limit">
+ <projections start-index="7" stop-index="7">
+ <shorthand-projection start-index="7" stop-index="7" />
+ </projections>
+ <from>
+ <simple-table name="table1" start-index="14" stop-index="19" />
+ </from>
+ <combine combine-type="UNION_ALL" start-index="21" stop-index="50">
+ <select>
+ <projections start-index="38" stop-index="38">
+ <shorthand-projection start-index="38" stop-index="38" />
+ </projections>
+ <from>
+ <simple-table name="table2" start-index="45" stop-index="50" />
+ </from>
+ </select>
+ </combine>
+ <order-by>
+ <column-item name="id" start-index="61" stop-index="62" />
+ </order-by>
+ <limit start-index="64" stop-index="73">
+ <offset value="1" start-index="70" stop-index="70" />
+ <row-count value="1" start-index="73" stop-index="73" />
+ </limit>
+ </select>
+
<select sql-case-id="select_intersect">
<projections start-index="7" stop-index="7">
<shorthand-projection start-index="7" stop-index="7" />
@@ -103,6 +132,74 @@
</combine>
</select>
+ <select sql-case-id="select_intersect_order_by">
+ <projections start-index="7" stop-index="7">
+ <shorthand-projection start-index="7" stop-index="7" />
+ </projections>
+ <from>
+ <simple-table name="table1" start-index="14" stop-index="19" />
+ </from>
+ <combine combine-type="INTERSECT" start-index="21" stop-index="50">
+ <select>
+ <projections start-index="38" stop-index="38">
+ <shorthand-projection start-index="38" stop-index="38" />
+ </projections>
+ <from>
+ <simple-table name="table2" start-index="45" stop-index="50" />
+ </from>
+ <combine combine-type="INTERSECT" start-index="52" stop-index="81">
+ <select>
+ <projections start-index="69" stop-index="69">
+ <shorthand-projection start-index="69" stop-index="69" />
+ </projections>
+ <from>
+ <simple-table name="table3" start-index="76" stop-index="81" />
+ </from>
+ </select>
+ </combine>
+ </select>
+ </combine>
+ <order-by>
+ <column-item name="id" start-index="92" stop-index="93" />
+ </order-by>
+ </select>
+
+ <select sql-case-id="select_intersect_order_by_limit">
+ <projections start-index="7" stop-index="7">
+ <shorthand-projection start-index="7" stop-index="7" />
+ </projections>
+ <from>
+ <simple-table name="table1" start-index="14" stop-index="19" />
+ </from>
+ <combine combine-type="INTERSECT" start-index="21" stop-index="50">
+ <select>
+ <projections start-index="38" stop-index="38">
+ <shorthand-projection start-index="38" stop-index="38" />
+ </projections>
+ <from>
+ <simple-table name="table2" start-index="45" stop-index="50" />
+ </from>
+ <combine combine-type="INTERSECT" start-index="52" stop-index="81">
+ <select>
+ <projections start-index="69" stop-index="69">
+ <shorthand-projection start-index="69" stop-index="69" />
+ </projections>
+ <from>
+ <simple-table name="table3" start-index="76" stop-index="81" />
+ </from>
+ </select>
+ </combine>
+ </select>
+ </combine>
+ <order-by>
+ <column-item name="id" start-index="92" stop-index="93" />
+ </order-by>
+ <limit start-index="95" stop-index="104">
+ <offset value="1" start-index="101" stop-index="101" />
+ <row-count value="1" start-index="104" stop-index="104" />
+ </limit>
+ </select>
+
<select sql-case-id="select_except">
<projections start-index="7" stop-index="7">
<shorthand-projection start-index="7" stop-index="7" />
@@ -132,6 +229,74 @@
</combine>
</select>
+ <select sql-case-id="select_except_order_by">
+ <projections start-index="7" stop-index="7">
+ <shorthand-projection start-index="7" stop-index="7" />
+ </projections>
+ <from>
+ <simple-table name="table1" start-index="14" stop-index="19" />
+ </from>
+ <combine combine-type="EXCEPT_ALL" start-index="21" stop-index="51">
+ <select>
+ <projections start-index="39" stop-index="39">
+ <shorthand-projection start-index="39" stop-index="39" />
+ </projections>
+ <from>
+ <simple-table name="table2" start-index="46" stop-index="51" />
+ </from>
+ <combine combine-type="EXCEPT_ALL" start-index="53" stop-index="83">
+ <select>
+ <projections start-index="71" stop-index="71">
+ <shorthand-projection start-index="71" stop-index="71" />
+ </projections>
+ <from>
+ <simple-table name="table3" start-index="78" stop-index="83" />
+ </from>
+ </select>
+ </combine>
+ </select>
+ </combine>
+ <order-by>
+ <column-item name="id" start-index="94" stop-index="95" />
+ </order-by>
+ </select>
+
+ <select sql-case-id="select_except_order_by_limit">
+ <projections start-index="7" stop-index="7">
+ <shorthand-projection start-index="7" stop-index="7" />
+ </projections>
+ <from>
+ <simple-table name="table1" start-index="14" stop-index="19" />
+ </from>
+ <combine combine-type="EXCEPT_ALL" start-index="21" stop-index="51">
+ <select>
+ <projections start-index="39" stop-index="39">
+ <shorthand-projection start-index="39" stop-index="39" />
+ </projections>
+ <from>
+ <simple-table name="table2" start-index="46" stop-index="51" />
+ </from>
+ <combine combine-type="EXCEPT_ALL" start-index="53" stop-index="83">
+ <select>
+ <projections start-index="71" stop-index="71">
+ <shorthand-projection start-index="71" stop-index="71" />
+ </projections>
+ <from>
+ <simple-table name="table3" start-index="78" stop-index="83" />
+ </from>
+ </select>
+ </combine>
+ </select>
+ </combine>
+ <order-by>
+ <column-item name="id" start-index="94" stop-index="95" />
+ </order-by>
+ <limit start-index="97" stop-index="106">
+ <offset value="1" start-index="103" stop-index="103" />
+ <row-count value="1" start-index="106" stop-index="106" />
+ </limit>
+ </select>
+
<select sql-case-id="select_minus">
<projections start-index="7" stop-index="7">
<shorthand-projection start-index="7" stop-index="7" />
@@ -151,6 +316,54 @@
</combine>
</select>
+ <select sql-case-id="select_minus_order_by">
+ <projections start-index="7" stop-index="7">
+ <shorthand-projection start-index="7" stop-index="7" />
+ </projections>
+ <from>
+ <simple-table name="table1" start-index="14" stop-index="19" />
+ </from>
+ <combine combine-type="MINUS" start-index="21" stop-index="46">
+ <select>
+ <projections start-index="34" stop-index="34">
+ <shorthand-projection start-index="34" stop-index="34" />
+ </projections>
+ <from>
+ <simple-table name="table2" start-index="41" stop-index="46" />
+ </from>
+ </select>
+ </combine>
+ <order-by>
+ <column-item name="id" start-index="57" stop-index="58" />
+ </order-by>
+ </select>
+
+ <select sql-case-id="select_minus_order_by_limit">
+ <projections start-index="7" stop-index="7">
+ <shorthand-projection start-index="7" stop-index="7" />
+ </projections>
+ <from>
+ <simple-table name="table1" start-index="14" stop-index="19" />
+ </from>
+ <combine combine-type="MINUS" start-index="21" stop-index="46">
+ <select>
+ <projections start-index="34" stop-index="34">
+ <shorthand-projection start-index="34" stop-index="34" />
+ </projections>
+ <from>
+ <simple-table name="table2" start-index="41" stop-index="46" />
+ </from>
+ </select>
+ </combine>
+ <order-by>
+ <column-item name="id" start-index="57" stop-index="58" />
+ </order-by>
+ <limit start-index="60" stop-index="69">
+ <offset value="1" start-index="66" stop-index="66" />
+ <row-count value="1" start-index="69" stop-index="69" />
+ </limit>
+ </select>
+
<select sql-case-id="select_sub_union">
<projections start-index="7" stop-index="7">
<shorthand-projection start-index="7" stop-index="7" />
diff --git a/test/parser/src/main/resources/sql/supported/dml/select-union.xml b/test/parser/src/main/resources/sql/supported/dml/select-union.xml
index e219b852eb8..871710ab64c 100644
--- a/test/parser/src/main/resources/sql/supported/dml/select-union.xml
+++ b/test/parser/src/main/resources/sql/supported/dml/select-union.xml
@@ -17,11 +17,18 @@
-->
<sql-cases>
- <sql-case id="select_with_union" value="SELECT * from table1 union select * from table2" db-types="MySQL,PostgreSQL,openGauss" />
- <sql-case id="select_with_union_all" value="SELECT * from table1 union all select * from table2" db-types="MySQL,PostgreSQL,openGauss" />
- <sql-case id="select_union" value="SELECT order_id FROM t_order UNION SELECT order_id FROM t_order_item" db-types="MySQL,PostgreSQL,openGauss" />
+ <sql-case id="select_union" value="SELECT * FROM table1 UNION SELECT * FROM table2" db-types="MySQL,PostgreSQL,openGauss" />
+ <sql-case id="select_union_all" value="SELECT * FROM table1 UNION ALL SELECT * FROM table2" db-types="MySQL,PostgreSQL,openGauss" />
+ <sql-case id="select_union_all_order_by" value="SELECT * FROM table1 UNION ALL SELECT * FROM table2 ORDER BY id" db-types="MySQL,PostgreSQL,openGauss" />
+ <sql-case id="select_union_all_order_by_limit" value="SELECT * FROM table1 UNION ALL SELECT * FROM table2 ORDER BY id LIMIT 1, 1" db-types="MySQL,openGauss" />
<sql-case id="select_intersect" value="SELECT * FROM table1 INTERSECT SELECT * FROM table2 INTERSECT SELECT * FROM table3" db-types="PostgreSQL,openGauss" />
+ <sql-case id="select_intersect_order_by" value="SELECT * FROM table1 INTERSECT SELECT * FROM table2 INTERSECT SELECT * FROM table3 ORDER BY id" db-types="PostgreSQL,openGauss" />
+ <sql-case id="select_intersect_order_by_limit" value="SELECT * FROM table1 INTERSECT SELECT * FROM table2 INTERSECT SELECT * FROM table3 ORDER BY id LIMIT 1, 1" db-types="openGauss" />
<sql-case id="select_except" value="SELECT * FROM table1 EXCEPT ALL SELECT * FROM table2 EXCEPT ALL SELECT * FROM table3" db-types="PostgreSQL,openGauss" />
+ <sql-case id="select_except_order_by" value="SELECT * FROM table1 EXCEPT ALL SELECT * FROM table2 EXCEPT ALL SELECT * FROM table3 ORDER BY id" db-types="PostgreSQL,openGauss" />
+ <sql-case id="select_except_order_by_limit" value="SELECT * FROM table1 EXCEPT ALL SELECT * FROM table2 EXCEPT ALL SELECT * FROM table3 ORDER BY id LIMIT 1, 1" db-types="openGauss" />
<sql-case id="select_minus" value="SELECT * FROM table1 MINUS SELECT * FROM table2" db-types="openGauss" />
+ <sql-case id="select_minus_order_by" value="SELECT * FROM table1 MINUS SELECT * FROM table2 ORDER BY id" db-types="openGauss" />
+ <sql-case id="select_minus_order_by_limit" value="SELECT * FROM table1 MINUS SELECT * FROM table2 ORDER BY id LIMIT 1, 1" db-types="openGauss" />
<sql-case id="select_sub_union" value="SELECT * FROM table1 UNION (SELECT * FROM table2 UNION SELECT * FROM table3)" db-types="MySQL,PostgreSQL,openGauss" />
</sql-cases>