You are viewing a plain text version of this content. The canonical link for it is here.
Posted to notifications@shardingsphere.apache.org by du...@apache.org on 2022/11/21 02:09:31 UTC
[shardingsphere] branch master updated: Refactor optimization unit test. Describe the case in xml file,… (#22241)
This is an automated email from the ASF dual-hosted git repository.
duanzhengqiang 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 74f99beb366 Refactor optimization unit test. Describe the case in xml file,… (#22241)
74f99beb366 is described below
commit 74f99beb36685b1df9aab3320087348547319bd4
Author: boyjoy1127 <10...@users.noreply.github.com>
AuthorDate: Mon Nov 21 10:09:21 2022 +0800
Refactor optimization unit test. Describe the case in xml file,… (#22241)
* test: refactor optimization unit test. Describe the case in xml file, and use junit parameterized method to test.
* fix: fix test bug in jdk19，due to javax.
* style: add final to the class.
Co-authored-by: boyjoy1127 <bo...@126.com>
---
kernel/sql-federation/optimizer/pom.xml | 25 ++-
.../optimizer/SQLOptimizeEngineTest.java | 231 +++------------------
.../sqlfederation/optimizer/common/TestCase.java | 41 ++++
.../optimizer/common/TestCaseAssertion.java | 37 ++++
.../sqlfederation/optimizer/common/TestCases.java | 36 ++++
.../optimizer/common/TestCasesLoader.java | 58 ++++++
.../resources/cases/federation-query-sql-cases.xml | 59 ++++++
7 files changed, 284 insertions(+), 203 deletions(-)
diff --git a/kernel/sql-federation/optimizer/pom.xml b/kernel/sql-federation/optimizer/pom.xml
index 8355fbb72da..cd2f9012a36 100644
--- a/kernel/sql-federation/optimizer/pom.xml
+++ b/kernel/sql-federation/optimizer/pom.xml
@@ -44,6 +44,10 @@
<artifactId>shardingsphere-parser-core</artifactId>
<version>${project.version}</version>
</dependency>
+ <dependency>
+ <groupId>org.apache.calcite</groupId>
+ <artifactId>calcite-core</artifactId>
+ </dependency>
<dependency>
<groupId>org.apache.shardingsphere</groupId>
<artifactId>shardingsphere-sql-parser-sql92</artifactId>
@@ -80,10 +84,25 @@
<version>${project.version}</version>
<scope>test</scope>
</dependency>
-
<dependency>
- <groupId>org.apache.calcite</groupId>
- <artifactId>calcite-core</artifactId>
+ <groupId>javax.xml.bind</groupId>
+ <artifactId>jaxb-api</artifactId>
+ <scope>test</scope>
+ </dependency>
+ <dependency>
+ <groupId>com.sun.xml.bind</groupId>
+ <artifactId>jaxb-core</artifactId>
+ <scope>test</scope>
+ </dependency>
+ <dependency>
+ <groupId>com.sun.xml.bind</groupId>
+ <artifactId>jaxb-impl</artifactId>
+ <scope>test</scope>
+ </dependency>
+ <dependency>
+ <groupId>javax.activation</groupId>
+ <artifactId>javax.activation-api</artifactId>
+ <scope>test</scope>
</dependency>
</dependencies>
<build>
diff --git a/kernel/sql-federation/optimizer/src/test/java/org/apache/shardingsphere/sqlfederation/optimizer/SQLOptimizeEngineTest.java b/kernel/sql-federation/optimizer/src/test/java/org/apache/shardingsphere/sqlfederation/optimizer/SQLOptimizeEngineTest.java
index 90b70b5fe7a..42a09b37bd5 100644
--- a/kernel/sql-federation/optimizer/src/test/java/org/apache/shardingsphere/sqlfederation/optimizer/SQLOptimizeEngineTest.java
+++ b/kernel/sql-federation/optimizer/src/test/java/org/apache/shardingsphere/sqlfederation/optimizer/SQLOptimizeEngineTest.java
@@ -17,6 +17,7 @@
package org.apache.shardingsphere.sqlfederation.optimizer;
+import lombok.SneakyThrows;
import org.apache.calcite.config.CalciteConnectionConfig;
import org.apache.calcite.config.CalciteConnectionConfigImpl;
import org.apache.calcite.jdbc.JavaTypeFactoryImpl;
@@ -36,70 +37,52 @@ import org.apache.shardingsphere.infra.parser.ShardingSphereSQLParserEngine;
import org.apache.shardingsphere.parser.rule.SQLParserRule;
import org.apache.shardingsphere.parser.rule.builder.DefaultSQLParserRuleConfigurationBuilder;
import org.apache.shardingsphere.sql.parser.sql.common.statement.SQLStatement;
+import org.apache.shardingsphere.sqlfederation.optimizer.common.TestCase;
+import org.apache.shardingsphere.sqlfederation.optimizer.common.TestCasesLoader;
import org.apache.shardingsphere.sqlfederation.optimizer.metadata.translatable.TranslatableSchema;
import org.apache.shardingsphere.sqlfederation.optimizer.util.SQLFederationPlannerUtil;
import org.junit.Before;
import org.junit.Test;
+import org.junit.runner.RunWith;
+import org.junit.runners.Parameterized;
+import org.junit.runners.Parameterized.Parameters;
+import javax.xml.bind.JAXBException;
+import java.io.IOException;
import java.sql.Types;
import java.util.Arrays;
+import java.util.Collection;
import java.util.Collections;
import java.util.HashMap;
import java.util.Map;
import java.util.Properties;
-import static org.hamcrest.CoreMatchers.is;
-import static org.hamcrest.MatcherAssert.assertThat;
+import static org.junit.Assert.assertEquals;
import static org.mockito.Mockito.mock;
+@RunWith(Parameterized.class)
public final class SQLOptimizeEngineTest {
- private static final String LINE_SEPARATOR = System.lineSeparator();
-
- private static final String SELECT_CROSS_JOIN_CONDITION = "SELECT t_order_federate.order_id, t_order_federate.user_id, t_user_info.user_id "
- + "FROM t_order_federate JOIN t_user_info ON t_order_federate.user_id = t_user_info.user_id "
- + "WHERE t_user_info.user_id = 13";
-
- private static final String SELECT_WHERE_ALL_FIELDS = "SELECT user_id, information FROM t_user_info WHERE user_id = 12";
-
- private static final String SELECT_WHERE_SINGLE_FIELD = "SELECT user_id FROM t_user_info WHERE user_id = 12";
-
- private static final String SELECT_CROSS_WHERE = "SELECT t_order_federate.order_id, t_order_federate.user_id, t_user_info.user_id "
- + "FROM t_order_federate , t_user_info "
- + "WHERE t_order_federate.user_id = t_user_info.user_id";
-
- private static final String SELECT_CROSS_JOIN = "SELECT t_order_federate.order_id, t_order_federate.user_id, t_user_info.user_id "
- + "FROM t_order_federate JOIN t_user_info "
- + "ON t_order_federate.user_id = t_user_info.user_id";
-
- private static final String SELECT_CROSS_WHERE_CONDITION = "SELECT t_order_federate.order_id, t_order_federate.user_id, t_user_info.user_id "
- + "FROM t_order_federate ,t_user_info "
- + "WHERE t_order_federate.user_id = t_user_info.user_id AND t_user_info.user_id = 13";
-
- private static final String SELECT_SUBQUERY_FROM = "SELECT user.user_id, user.information "
- + "FROM (SELECT * FROM t_user_info WHERE user_id > 1) as user ";
-
- private static final String SELECT_SUBQUERY_WHERE_EXIST = "SELECT t_order_federate.order_id, t_order_federate.user_id FROM t_order_federate "
- + "WHERE EXISTS (SELECT * FROM t_user_info WHERE t_order_federate.user_id = t_user_info.user_id)";
-
- private static final String SELECT_SUBQUERY_WHERE_IN = "SELECT t_order_federate.order_id, t_order_federate.user_id FROM t_order_federate "
- + "WHERE t_order_federate.user_id IN (SELECT t_user_info.user_id FROM t_user_info)";
-
- private static final String SELECT_SUBQUERY_WHERE_BETWEEN = "SELECT t_order_federate.order_id, t_order_federate.user_id FROM t_order_federate "
- + "WHERE user_id BETWEEN (SELECT user_id FROM t_user_info WHERE user_id = 1) "
- + "AND (SELECT user_id FROM t_user_info WHERE user_id = 3)";
+ private static final String SCHEMA_NAME = "federate_jdbc";
- private static final String SELECT_UNION = "SELECT order_id, user_id FROM t_order_federate UNION SELECT 1, user_id FROM t_user_info WHERE information = 'before'";
+ private final SQLParserRule sqlParserRule = new SQLParserRule(new DefaultSQLParserRuleConfigurationBuilder().build());
- private static final String SELECT_LIMIT = "SELECT order_id, user_id FROM t_order_federate LIMIT 1";
+ private SQLOptimizeEngine optimizeEngine;
- private static final String SELECT_AGGREGATION = "SELECT MAX(order_id), MIN(order_id), SUM(order_id), AVG(order_id), COUNT(1) FROM t_order_federate GROUP BY user_id";
+ private String sql;
- private static final String SCHEMA_NAME = "federate_jdbc";
+ private String expectedResult;
- private final SQLParserRule sqlParserRule = new SQLParserRule(new DefaultSQLParserRuleConfigurationBuilder().build());
+ public SQLOptimizeEngineTest(final TestCase testcase) {
+ sql = testcase.getSql();
+ expectedResult = testcase.getAssertion().getExpectedResult();
+ }
- private SQLOptimizeEngine optimizeEngine;
+ @SneakyThrows({IOException.class, JAXBException.class})
+ @Parameters
+ public static Collection<TestCase> data() {
+ return TestCasesLoader.getInstance().generate();
+ }
@Before
public void init() {
@@ -136,166 +119,14 @@ public final class SQLOptimizeEngineTest {
}
@Test
- public void assertSelectCrossJoinCondition() {
- ShardingSphereSQLParserEngine sqlParserEngine = sqlParserRule.getSQLParserEngine(DatabaseTypeEngine.getTrunkDatabaseTypeName(new H2DatabaseType()));
- SQLStatement sqlStatement = sqlParserEngine.parse(SELECT_CROSS_JOIN_CONDITION, false);
- String actual = optimizeEngine.optimize(sqlStatement).getBestPlan().explain();
- String expected = "EnumerableCalc(expr#0..4=[{inputs}], proj#0..1=[{exprs}], user_id0=[$t3])" + LINE_SEPARATOR
- + " EnumerableHashJoin(condition=[=($2, $4)], joinType=[inner])" + LINE_SEPARATOR
- + " EnumerableCalc(expr#0..1=[{inputs}], expr#2=[CAST($t1):VARCHAR], proj#0..2=[{exprs}])" + LINE_SEPARATOR
- + " TranslatableTableScan(table=[[federate_jdbc, t_order_federate]], fields=[[0, 1]])" + LINE_SEPARATOR
- + " EnumerableCalc(expr#0=[{inputs}], expr#1=[CAST($t0):VARCHAR], proj#0..1=[{exprs}])" + LINE_SEPARATOR
- + " TranslatableTableScan(table=[[federate_jdbc, t_user_info]], fields=[[0]], filters=[[=(CAST($0):INTEGER, 13), null]])" + LINE_SEPARATOR;
- assertThat(actual, is(expected));
- }
-
- @Test
- public void assertSelectWhereAllFields() {
- ShardingSphereSQLParserEngine sqlParserEngine = sqlParserRule.getSQLParserEngine(DatabaseTypeEngine.getTrunkDatabaseTypeName(new H2DatabaseType()));
- SQLStatement sqlStatement = sqlParserEngine.parse(SELECT_WHERE_ALL_FIELDS, false);
- String actual = optimizeEngine.optimize(sqlStatement).getBestPlan().explain();
- String expected = "TranslatableTableScan(table=[[federate_jdbc, t_user_info]], fields=[[0, 1]], filters=[[=(CAST($0):INTEGER, 12), null]])" + LINE_SEPARATOR;
- assertThat(actual, is(expected));
- }
-
- @Test
- public void assertSelectWhereSingleField() {
- ShardingSphereSQLParserEngine sqlParserEngine = sqlParserRule.getSQLParserEngine(DatabaseTypeEngine.getTrunkDatabaseTypeName(new H2DatabaseType()));
- SQLStatement sqlStatement = sqlParserEngine.parse(SELECT_WHERE_SINGLE_FIELD, false);
- String actual = optimizeEngine.optimize(sqlStatement).getBestPlan().explain();
- String expected = "TranslatableTableScan(table=[[federate_jdbc, t_user_info]], fields=[[0]], filters=[[=(CAST($0):INTEGER, 12)]])" + LINE_SEPARATOR;
- assertThat(actual, is(expected));
- }
-
- @Test
- public void assertSelectCrossWhere() {
- ShardingSphereSQLParserEngine sqlParserEngine = sqlParserRule.getSQLParserEngine(DatabaseTypeEngine.getTrunkDatabaseTypeName(new H2DatabaseType()));
- SQLStatement sqlStatement = sqlParserEngine.parse(SELECT_CROSS_WHERE, false);
- String actual = optimizeEngine.optimize(sqlStatement).getBestPlan().explain();
- String expected = "EnumerableCalc(expr#0..4=[{inputs}], proj#0..1=[{exprs}], user_id0=[$t3])" + LINE_SEPARATOR
- + " EnumerableHashJoin(condition=[=($2, $4)], joinType=[inner])" + LINE_SEPARATOR
- + " EnumerableCalc(expr#0..1=[{inputs}], expr#2=[CAST($t1):VARCHAR], proj#0..2=[{exprs}])" + LINE_SEPARATOR
- + " TranslatableTableScan(table=[[federate_jdbc, t_order_federate]], fields=[[0, 1]])" + LINE_SEPARATOR
- + " EnumerableCalc(expr#0=[{inputs}], expr#1=[CAST($t0):VARCHAR], proj#0..1=[{exprs}])" + LINE_SEPARATOR
- + " TranslatableTableScan(table=[[federate_jdbc, t_user_info]], fields=[[0]])" + LINE_SEPARATOR;
- assertThat(actual, is(expected));
- }
-
- @Test
- public void assertSelectCrossJoin() {
- ShardingSphereSQLParserEngine sqlParserEngine = sqlParserRule.getSQLParserEngine(DatabaseTypeEngine.getTrunkDatabaseTypeName(new H2DatabaseType()));
- SQLStatement sqlStatement = sqlParserEngine.parse(SELECT_CROSS_JOIN, false);
- String actual = optimizeEngine.optimize(sqlStatement).getBestPlan().explain();
- String expected = "EnumerableCalc(expr#0..4=[{inputs}], proj#0..1=[{exprs}], user_id0=[$t3])" + LINE_SEPARATOR
- + " EnumerableHashJoin(condition=[=($2, $4)], joinType=[inner])" + LINE_SEPARATOR
- + " EnumerableCalc(expr#0..1=[{inputs}], expr#2=[CAST($t1):VARCHAR], proj#0..2=[{exprs}])" + LINE_SEPARATOR
- + " TranslatableTableScan(table=[[federate_jdbc, t_order_federate]], fields=[[0, 1]])" + LINE_SEPARATOR
- + " EnumerableCalc(expr#0=[{inputs}], expr#1=[CAST($t0):VARCHAR], proj#0..1=[{exprs}])" + LINE_SEPARATOR
- + " TranslatableTableScan(table=[[federate_jdbc, t_user_info]], fields=[[0]])" + LINE_SEPARATOR;
- assertThat(actual, is(expected));
- }
-
- @Test
- public void assertSelectJoinWhere() {
- ShardingSphereSQLParserEngine sqlParserEngine = sqlParserRule.getSQLParserEngine(DatabaseTypeEngine.getTrunkDatabaseTypeName(new H2DatabaseType()));
- SQLStatement sqlStatement = sqlParserEngine.parse(SELECT_CROSS_WHERE_CONDITION, false);
- String actual = optimizeEngine.optimize(sqlStatement).getBestPlan().explain();
- String expected = "EnumerableCalc(expr#0..4=[{inputs}], proj#0..1=[{exprs}], user_id0=[$t3])" + LINE_SEPARATOR
- + " EnumerableHashJoin(condition=[=($2, $4)], joinType=[inner])" + LINE_SEPARATOR
- + " EnumerableCalc(expr#0..1=[{inputs}], expr#2=[CAST($t1):VARCHAR], proj#0..2=[{exprs}])" + LINE_SEPARATOR
- + " TranslatableTableScan(table=[[federate_jdbc, t_order_federate]], fields=[[0, 1]])" + LINE_SEPARATOR
- + " EnumerableCalc(expr#0=[{inputs}], expr#1=[CAST($t0):VARCHAR], proj#0..1=[{exprs}])" + LINE_SEPARATOR
- + " TranslatableTableScan(table=[[federate_jdbc, t_user_info]], fields=[[0]], filters=[[=(CAST($0):INTEGER, 13), null]])" + LINE_SEPARATOR;
- assertThat(actual, is(expected));
- }
-
- @Test
- public void assertSelectSubQueryFrom() {
- ShardingSphereSQLParserEngine sqlParserEngine = sqlParserRule.getSQLParserEngine(DatabaseTypeEngine.getTrunkDatabaseTypeName(new H2DatabaseType()));
- SQLStatement sqlStatement = sqlParserEngine.parse(SELECT_SUBQUERY_FROM, false);
- String actual = optimizeEngine.optimize(sqlStatement).getBestPlan().explain();
- String expected = "TranslatableTableScan(table=[[federate_jdbc, t_user_info]], fields=[[0, 1]], filters=[[>(CAST($0):INTEGER, 1), null]])" + LINE_SEPARATOR;
- assertThat(actual, is(expected));
- }
-
- @Test
- public void assertSelectSubQueryWhereExist() {
- ShardingSphereSQLParserEngine sqlParserEngine = sqlParserRule.getSQLParserEngine(DatabaseTypeEngine.getTrunkDatabaseTypeName(new H2DatabaseType()));
- SQLStatement sqlStatement = sqlParserEngine.parse(SELECT_SUBQUERY_WHERE_EXIST, false);
- String actual = optimizeEngine.optimize(sqlStatement).getBestPlan().explain();
- String expected = "EnumerableCalc(expr#0..3=[{inputs}], expr#4=[IS NOT NULL($t3)], proj#0..1=[{exprs}], $condition=[$t4])" + LINE_SEPARATOR
- + " EnumerableCorrelate(correlation=[$cor0], joinType=[left], requiredColumns=[{1}])" + LINE_SEPARATOR
- + " TranslatableTableScan(table=[[federate_jdbc, t_order_federate]], fields=[[0, 1, 2]])" + LINE_SEPARATOR
- + " EnumerableAggregate(group=[{}], agg#0=[MIN($0)])" + LINE_SEPARATOR
- + " EnumerableCalc(expr#0..1=[{inputs}], expr#2=[true], $f0=[$t2])" + LINE_SEPARATOR
- + " TranslatableTableScan(table=[[federate_jdbc, t_user_info]], fields=[[0, 1]], filters=[[=(CAST($cor0.user_id):VARCHAR, CAST($0):VARCHAR), null]])" + LINE_SEPARATOR;
- assertThat(actual, is(expected));
- }
-
- @Test
- public void assertSelectSubQueryWhereIn() {
- ShardingSphereSQLParserEngine sqlParserEngine = sqlParserRule.getSQLParserEngine(DatabaseTypeEngine.getTrunkDatabaseTypeName(new H2DatabaseType()));
- SQLStatement sqlStatement = sqlParserEngine.parse(SELECT_SUBQUERY_WHERE_IN, false);
- String actual = optimizeEngine.optimize(sqlStatement).getBestPlan().explain();
- String expected = "EnumerableCalc(expr#0..2=[{inputs}], proj#0..1=[{exprs}])" + LINE_SEPARATOR
- + " EnumerableHashJoin(condition=[=($1, $2)], joinType=[inner])" + LINE_SEPARATOR
- + " TranslatableTableScan(table=[[federate_jdbc, t_order_federate]], fields=[[0, 1]])" + LINE_SEPARATOR
- + " EnumerableAggregate(group=[{0}])" + LINE_SEPARATOR
- + " TranslatableTableScan(table=[[federate_jdbc, t_user_info]], fields=[[0]])" + LINE_SEPARATOR;
- assertThat(actual, is(expected));
- }
-
- @Test
- public void assertSelectSubQueryWhereBetween() {
- ShardingSphereSQLParserEngine sqlParserEngine = sqlParserRule.getSQLParserEngine(DatabaseTypeEngine.getTrunkDatabaseTypeName(new H2DatabaseType()));
- SQLStatement sqlStatement = sqlParserEngine.parse(SELECT_SUBQUERY_WHERE_BETWEEN, false);
- String actual = optimizeEngine.optimize(sqlStatement).getBestPlan().explain();
- String expected = "EnumerableCalc(expr#0..2=[{inputs}], proj#0..1=[{exprs}])" + LINE_SEPARATOR
- + " EnumerableNestedLoopJoin(condition=[<=($1, $2)], joinType=[inner])" + LINE_SEPARATOR
- + " EnumerableCalc(expr#0..2=[{inputs}], proj#0..1=[{exprs}])" + LINE_SEPARATOR
- + " EnumerableNestedLoopJoin(condition=[>=($1, $2)], joinType=[inner])" + LINE_SEPARATOR
- + " TranslatableTableScan(table=[[federate_jdbc, t_order_federate]], fields=[[0, 1]])" + LINE_SEPARATOR
- + " EnumerableAggregate(group=[{}], agg#0=[SINGLE_VALUE($0)])" + LINE_SEPARATOR
- + " TranslatableTableScan(table=[[federate_jdbc, t_user_info]], fields=[[0]], filters=[[=(CAST($0):INTEGER, 1)]])" + LINE_SEPARATOR
- + " EnumerableAggregate(group=[{}], agg#0=[SINGLE_VALUE($0)])" + LINE_SEPARATOR
- + " TranslatableTableScan(table=[[federate_jdbc, t_user_info]], fields=[[0]], filters=[[=(CAST($0):INTEGER, 3)]])" + LINE_SEPARATOR;
- assertThat(actual, is(expected));
- }
-
- @Test
- public void assertSelectUnion() {
- ShardingSphereSQLParserEngine sqlParserEngine = sqlParserRule.getSQLParserEngine(DatabaseTypeEngine.getTrunkDatabaseTypeName(new H2DatabaseType()));
- SQLStatement sqlStatement = sqlParserEngine.parse(SELECT_UNION, false);
- String actual = optimizeEngine.optimize(sqlStatement).getBestPlan().explain();
- String expected = "EnumerableUnion(all=[false])" + LINE_SEPARATOR
- + " TranslatableTableScan(table=[[federate_jdbc, t_order_federate]], fields=[[0, 1]])" + LINE_SEPARATOR
- + " EnumerableCalc(expr#0=[{inputs}], expr#1=['1':VARCHAR], EXPR$0=[$t1], user_id=[$t0])" + LINE_SEPARATOR
- + " TranslatableTableScan(table=[[federate_jdbc, t_user_info]], fields=[[0]], filters=[[=(CAST($1):VARCHAR, 'before'), null]])" + LINE_SEPARATOR;
- assertThat(actual, is(expected));
- }
-
- @Test
- public void assertSelectLimit() {
- ShardingSphereSQLParserEngine sqlParserEngine = sqlParserRule.getSQLParserEngine(DatabaseTypeEngine.getTrunkDatabaseTypeName(new H2DatabaseType()));
- SQLStatement sqlStatement = sqlParserEngine.parse(SELECT_LIMIT, false);
- String actual = optimizeEngine.optimize(sqlStatement).getBestPlan().explain();
- String expected = "EnumerableLimit(fetch=[1])" + LINE_SEPARATOR
- + " TranslatableTableScan(table=[[federate_jdbc, t_order_federate]], fields=[[0, 1]])" + LINE_SEPARATOR;
- assertThat(actual, is(expected));
+ public void test() {
+ assertEquals(expectedResult, execute(sql));
}
- @Test
- public void assertSelectAggregationFunction() {
+ private String execute(final String sql) {
ShardingSphereSQLParserEngine sqlParserEngine = sqlParserRule.getSQLParserEngine(DatabaseTypeEngine.getTrunkDatabaseTypeName(new H2DatabaseType()));
- SQLStatement sqlStatement = sqlParserEngine.parse(SELECT_AGGREGATION, false);
- String actual = optimizeEngine.optimize(sqlStatement).getBestPlan().explain();
- String expected =
- "EnumerableCalc(expr#0..5=[{inputs}], expr#6=[0], expr#7=[=($t4, $t6)], expr#8=[null:DECIMAL(19, 9)], "
- + "expr#9=[CASE($t7, $t8, $t3)], expr#10=[/($t9, $t4)], EXPR$0=[$t1], EXPR$1=[$t2], EXPR$2=[$t9], EXPR$3=[$t10], EXPR$4=[$t5])"
- + LINE_SEPARATOR
- + " EnumerableAggregate(group=[{0}], EXPR$0=[MAX($1)], EXPR$1=[MIN($1)], EXPR$2=[$SUM0($2)], agg#3=[COUNT($2)], EXPR$4=[COUNT()])" + LINE_SEPARATOR
- + " EnumerableCalc(expr#0..1=[{inputs}], expr#2=[CAST($t0):DECIMAL(19, 9)], user_id=[$t1], order_id=[$t0], $f2=[$t2])" + LINE_SEPARATOR
- + " TranslatableTableScan(table=[[federate_jdbc, t_order_federate]], fields=[[1, 0]])" + LINE_SEPARATOR;
- assertThat(actual, is(expected));
+ SQLStatement sqlStatement = sqlParserEngine.parse(sql, false);
+ String result = optimizeEngine.optimize(sqlStatement).getBestPlan().explain();
+ return result.replaceAll("\r|\n", "");
}
}
diff --git a/kernel/sql-federation/optimizer/src/test/java/org/apache/shardingsphere/sqlfederation/optimizer/common/TestCase.java b/kernel/sql-federation/optimizer/src/test/java/org/apache/shardingsphere/sqlfederation/optimizer/common/TestCase.java
new file mode 100644
index 00000000000..481488e4eaa
--- /dev/null
+++ b/kernel/sql-federation/optimizer/src/test/java/org/apache/shardingsphere/sqlfederation/optimizer/common/TestCase.java
@@ -0,0 +1,41 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one or more
+ * contributor license agreements. See the NOTICE file distributed with
+ * this work for additional information regarding copyright ownership.
+ * The ASF licenses this file to You under the Apache License, Version 2.0
+ * (the "License"); you may not use this file except in compliance with
+ * the License. You may obtain a copy of the License at
+ *
+ * http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing, software
+ * distributed under the License is distributed on an "AS IS" BASIS,
+ * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ * See the License for the specific language governing permissions and
+ * limitations under the License.
+ */
+
+package org.apache.shardingsphere.sqlfederation.optimizer.common;
+
+import lombok.Getter;
+import lombok.Setter;
+
+import javax.xml.bind.annotation.XmlAccessType;
+import javax.xml.bind.annotation.XmlAccessorType;
+import javax.xml.bind.annotation.XmlAttribute;
+import javax.xml.bind.annotation.XmlElement;
+
+/**
+ * JAXB definition of test case.
+ */
+@Getter
+@Setter
+@XmlAccessorType(XmlAccessType.FIELD)
+public final class TestCase {
+
+ @XmlAttribute(name = "sql")
+ private String sql;
+
+ @XmlElement(name = "assertion")
+ private TestCaseAssertion assertion;
+}
diff --git a/kernel/sql-federation/optimizer/src/test/java/org/apache/shardingsphere/sqlfederation/optimizer/common/TestCaseAssertion.java b/kernel/sql-federation/optimizer/src/test/java/org/apache/shardingsphere/sqlfederation/optimizer/common/TestCaseAssertion.java
new file mode 100644
index 00000000000..bbe2e4440a9
--- /dev/null
+++ b/kernel/sql-federation/optimizer/src/test/java/org/apache/shardingsphere/sqlfederation/optimizer/common/TestCaseAssertion.java
@@ -0,0 +1,37 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one or more
+ * contributor license agreements. See the NOTICE file distributed with
+ * this work for additional information regarding copyright ownership.
+ * The ASF licenses this file to You under the Apache License, Version 2.0
+ * (the "License"); you may not use this file except in compliance with
+ * the License. You may obtain a copy of the License at
+ *
+ * http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing, software
+ * distributed under the License is distributed on an "AS IS" BASIS,
+ * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ * See the License for the specific language governing permissions and
+ * limitations under the License.
+ */
+
+package org.apache.shardingsphere.sqlfederation.optimizer.common;
+
+import lombok.Getter;
+import lombok.Setter;
+
+import javax.xml.bind.annotation.XmlAccessType;
+import javax.xml.bind.annotation.XmlAccessorType;
+import javax.xml.bind.annotation.XmlAttribute;
+
+/**
+ * JAXB definition of test case assertion.
+ */
+@Getter
+@Setter
+@XmlAccessorType(XmlAccessType.FIELD)
+public final class TestCaseAssertion {
+
+ @XmlAttribute(name = "expected-result")
+ private String expectedResult;
+}
diff --git a/kernel/sql-federation/optimizer/src/test/java/org/apache/shardingsphere/sqlfederation/optimizer/common/TestCases.java b/kernel/sql-federation/optimizer/src/test/java/org/apache/shardingsphere/sqlfederation/optimizer/common/TestCases.java
new file mode 100644
index 00000000000..61509fcf9ff
--- /dev/null
+++ b/kernel/sql-federation/optimizer/src/test/java/org/apache/shardingsphere/sqlfederation/optimizer/common/TestCases.java
@@ -0,0 +1,36 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one or more
+ * contributor license agreements. See the NOTICE file distributed with
+ * this work for additional information regarding copyright ownership.
+ * The ASF licenses this file to You under the Apache License, Version 2.0
+ * (the "License"); you may not use this file except in compliance with
+ * the License. You may obtain a copy of the License at
+ *
+ * http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing, software
+ * distributed under the License is distributed on an "AS IS" BASIS,
+ * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ * See the License for the specific language governing permissions and
+ * limitations under the License.
+ */
+
+package org.apache.shardingsphere.sqlfederation.optimizer.common;
+
+import lombok.Getter;
+
+import javax.xml.bind.annotation.XmlElement;
+import javax.xml.bind.annotation.XmlRootElement;
+import java.util.Collection;
+import java.util.LinkedList;
+
+/**
+ * JAXB definition of test cases.
+ */
+@Getter
+@XmlRootElement(name = "test-cases")
+public final class TestCases {
+
+ @XmlElement(name = "test-case")
+ private final Collection<TestCase> testCases = new LinkedList<>();
+}
diff --git a/kernel/sql-federation/optimizer/src/test/java/org/apache/shardingsphere/sqlfederation/optimizer/common/TestCasesLoader.java b/kernel/sql-federation/optimizer/src/test/java/org/apache/shardingsphere/sqlfederation/optimizer/common/TestCasesLoader.java
new file mode 100644
index 00000000000..c843e359823
--- /dev/null
+++ b/kernel/sql-federation/optimizer/src/test/java/org/apache/shardingsphere/sqlfederation/optimizer/common/TestCasesLoader.java
@@ -0,0 +1,58 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one or more
+ * contributor license agreements. See the NOTICE file distributed with
+ * this work for additional information regarding copyright ownership.
+ * The ASF licenses this file to You under the Apache License, Version 2.0
+ * (the "License"); you may not use this file except in compliance with
+ * the License. You may obtain a copy of the License at
+ *
+ * http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing, software
+ * distributed under the License is distributed on an "AS IS" BASIS,
+ * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ * See the License for the specific language governing permissions and
+ * limitations under the License.
+ */
+
+package org.apache.shardingsphere.sqlfederation.optimizer.common;
+
+import javax.xml.bind.JAXBContext;
+import javax.xml.bind.JAXBException;
+import java.io.FileReader;
+import java.io.IOException;
+import java.net.URL;
+import java.util.Collection;
+import java.util.Objects;
+
+/**
+ * Integration test cases loader.
+ */
+public final class TestCasesLoader {
+
+ private static final TestCasesLoader INSTANCE = new TestCasesLoader();
+
+ /**
+ * Get singleton instance.
+ *
+ * @return singleton instance
+ */
+ public static TestCasesLoader getInstance() {
+ return INSTANCE;
+ }
+
+ /**
+ * Read a case file and generate a case object.
+ *
+ * @return collection of test cases
+ * @throws IOException exception for read file.
+ * @throws JAXBException exception for parse xml file.
+ */
+ public Collection<TestCase> generate() throws IOException, JAXBException {
+ URL url = Objects.requireNonNull(TestCasesLoader.class.getClassLoader().getResource("cases/federation-query-sql-cases.xml"));
+ try (FileReader reader = new FileReader(url.getFile())) {
+ TestCases testCases = (TestCases) JAXBContext.newInstance(TestCases.class).createUnmarshaller().unmarshal(reader);
+ return testCases.getTestCases();
+ }
+ }
+}
diff --git a/kernel/sql-federation/optimizer/src/test/resources/cases/federation-query-sql-cases.xml b/kernel/sql-federation/optimizer/src/test/resources/cases/federation-query-sql-cases.xml
new file mode 100644
index 00000000000..57fdea65399
--- /dev/null
+++ b/kernel/sql-federation/optimizer/src/test/resources/cases/federation-query-sql-cases.xml
@@ -0,0 +1,59 @@
+<?xml version="1.0"?>
+<!--
+ ~ Licensed to the Apache Software Foundation (ASF) under one or more
+ ~ contributor license agreements. See the NOTICE file distributed with
+ ~ this work for additional information regarding copyright ownership.
+ ~ The ASF licenses this file to You under the Apache License, Version 2.0
+ ~ (the "License"); you may not use this file except in compliance with
+ ~ the License. You may obtain a copy of the License at
+ ~
+ ~ http://www.apache.org/licenses/LICENSE-2.0
+ ~
+ ~ Unless required by applicable law or agreed to in writing, software
+ ~ distributed under the License is distributed on an "AS IS" BASIS,
+ ~ WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ ~ See the License for the specific language governing permissions and
+ ~ limitations under the License.
+ -->
+
+<test-cases>
+ <test-case sql="SELECT t_order_federate.order_id, t_order_federate.user_id, t_user_info.user_id FROM t_order_federate JOIN t_user_info ON t_order_federate.user_id = t_user_info.user_id WHERE t_user_info.user_id = 13">
+ <assertion expected-result="EnumerableCalc(expr#0..4=[{inputs}], proj#0..1=[{exprs}], user_id0=[$t3]) EnumerableHashJoin(condition=[=($2, $4)], joinType=[inner]) EnumerableCalc(expr#0..1=[{inputs}], expr#2=[CAST($t1):VARCHAR], proj#0..2=[{exprs}]) TranslatableTableScan(table=[[federate_jdbc, t_order_federate]], fields=[[0, 1]]) EnumerableCalc(expr#0=[{inputs}], expr#1=[CAST($t0):VARCHAR], proj#0..1=[{exprs}]) TranslatableTableScan(table=[[federate_jdbc, t_user_in [...]
+ </test-case>
+ <test-case sql="SELECT user_id, information FROM t_user_info WHERE user_id = 12">
+ <assertion expected-result="TranslatableTableScan(table=[[federate_jdbc, t_user_info]], fields=[[0, 1]], filters=[[=(CAST($0):INTEGER, 12), null]])"/>
+ </test-case>
+ <test-case sql="SELECT user_id FROM t_user_info WHERE user_id = 12">
+ <assertion expected-result="TranslatableTableScan(table=[[federate_jdbc, t_user_info]], fields=[[0]], filters=[[=(CAST($0):INTEGER, 12)]])"/>
+ </test-case>
+ <test-case sql="SELECT t_order_federate.order_id, t_order_federate.user_id, t_user_info.user_id FROM t_order_federate , t_user_info WHERE t_order_federate.user_id = t_user_info.user_id">
+ <assertion expected-result="EnumerableCalc(expr#0..4=[{inputs}], proj#0..1=[{exprs}], user_id0=[$t3]) EnumerableHashJoin(condition=[=($2, $4)], joinType=[inner]) EnumerableCalc(expr#0..1=[{inputs}], expr#2=[CAST($t1):VARCHAR], proj#0..2=[{exprs}]) TranslatableTableScan(table=[[federate_jdbc, t_order_federate]], fields=[[0, 1]]) EnumerableCalc(expr#0=[{inputs}], expr#1=[CAST($t0):VARCHAR], proj#0..1=[{exprs}]) TranslatableTableScan(table=[[federate_jdbc, t_user_in [...]
+ </test-case>
+ <test-case sql="SELECT t_order_federate.order_id, t_order_federate.user_id, t_user_info.user_id FROM t_order_federate JOIN t_user_info ON t_order_federate.user_id = t_user_info.user_id">
+ <assertion expected-result="EnumerableCalc(expr#0..4=[{inputs}], proj#0..1=[{exprs}], user_id0=[$t3]) EnumerableHashJoin(condition=[=($2, $4)], joinType=[inner]) EnumerableCalc(expr#0..1=[{inputs}], expr#2=[CAST($t1):VARCHAR], proj#0..2=[{exprs}]) TranslatableTableScan(table=[[federate_jdbc, t_order_federate]], fields=[[0, 1]]) EnumerableCalc(expr#0=[{inputs}], expr#1=[CAST($t0):VARCHAR], proj#0..1=[{exprs}]) TranslatableTableScan(table=[[federate_jdbc, t_user_in [...]
+ </test-case>
+ <test-case sql="SELECT t_order_federate.order_id, t_order_federate.user_id, t_user_info.user_id FROM t_order_federate ,t_user_info WHERE t_order_federate.user_id = t_user_info.user_id AND t_user_info.user_id = 13">
+ <assertion expected-result="EnumerableCalc(expr#0..4=[{inputs}], proj#0..1=[{exprs}], user_id0=[$t3]) EnumerableHashJoin(condition=[=($2, $4)], joinType=[inner]) EnumerableCalc(expr#0..1=[{inputs}], expr#2=[CAST($t1):VARCHAR], proj#0..2=[{exprs}]) TranslatableTableScan(table=[[federate_jdbc, t_order_federate]], fields=[[0, 1]]) EnumerableCalc(expr#0=[{inputs}], expr#1=[CAST($t0):VARCHAR], proj#0..1=[{exprs}]) TranslatableTableScan(table=[[federate_jdbc, t_user_in [...]
+ </test-case>
+ <test-case sql="SELECT user.user_id, user.information FROM (SELECT * FROM t_user_info WHERE user_id > 1) as user ">
+ <assertion expected-result="TranslatableTableScan(table=[[federate_jdbc, t_user_info]], fields=[[0, 1]], filters=[[>(CAST($0):INTEGER, 1), null]])"/>
+ </test-case>
+ <test-case sql="SELECT t_order_federate.order_id, t_order_federate.user_id FROM t_order_federate WHERE EXISTS (SELECT * FROM t_user_info WHERE t_order_federate.user_id = t_user_info.user_id)">
+ <assertion expected-result="EnumerableCalc(expr#0..3=[{inputs}], expr#4=[IS NOT NULL($t3)], proj#0..1=[{exprs}], $condition=[$t4]) EnumerableCorrelate(correlation=[$cor0], joinType=[left], requiredColumns=[{1}]) TranslatableTableScan(table=[[federate_jdbc, t_order_federate]], fields=[[0, 1, 2]]) EnumerableAggregate(group=[{}], agg#0=[MIN($0)]) EnumerableCalc(expr#0..1=[{inputs}], expr#2=[true], $f0=[$t2]) TranslatableTableScan(table=[[federate_jdbc, t_user_info [...]
+ </test-case>
+ <test-case sql="SELECT t_order_federate.order_id, t_order_federate.user_id FROM t_order_federate WHERE t_order_federate.user_id IN (SELECT t_user_info.user_id FROM t_user_info)">
+ <assertion expected-result="EnumerableCalc(expr#0..2=[{inputs}], proj#0..1=[{exprs}]) EnumerableHashJoin(condition=[=($1, $2)], joinType=[inner]) TranslatableTableScan(table=[[federate_jdbc, t_order_federate]], fields=[[0, 1]]) EnumerableAggregate(group=[{0}]) TranslatableTableScan(table=[[federate_jdbc, t_user_info]], fields=[[0]])"/>
+ </test-case>
+ <test-case sql="SELECT t_order_federate.order_id, t_order_federate.user_id FROM t_order_federate WHERE user_id BETWEEN (SELECT user_id FROM t_user_info WHERE user_id = 1) AND (SELECT user_id FROM t_user_info WHERE user_id = 3)">
+ <assertion expected-result="EnumerableCalc(expr#0..2=[{inputs}], proj#0..1=[{exprs}]) EnumerableNestedLoopJoin(condition=[<=($1, $2)], joinType=[inner]) EnumerableCalc(expr#0..2=[{inputs}], proj#0..1=[{exprs}]) EnumerableNestedLoopJoin(condition=[>=($1, $2)], joinType=[inner]) TranslatableTableScan(table=[[federate_jdbc, t_order_federate]], fields=[[0, 1]]) EnumerableAggregate(group=[{}], agg#0=[SINGLE_VALUE($0)]) TranslatableTableScan(table=[[f [...]
+ </test-case>
+ <test-case sql="SELECT order_id, user_id FROM t_order_federate UNION SELECT 1, user_id FROM t_user_info WHERE information = 'before'">
+ <assertion expected-result="EnumerableUnion(all=[false]) TranslatableTableScan(table=[[federate_jdbc, t_order_federate]], fields=[[0, 1]]) EnumerableCalc(expr#0=[{inputs}], expr#1=['1':VARCHAR], EXPR$0=[$t1], user_id=[$t0]) TranslatableTableScan(table=[[federate_jdbc, t_user_info]], fields=[[0]], filters=[[=(CAST($1):VARCHAR, 'before'), null]])"/>
+ </test-case>
+ <test-case sql="SELECT order_id, user_id FROM t_order_federate LIMIT 1">
+ <assertion expected-result="EnumerableLimit(fetch=[1]) TranslatableTableScan(table=[[federate_jdbc, t_order_federate]], fields=[[0, 1]])"/>
+ </test-case>
+ <test-case sql="SELECT MAX(order_id), MIN(order_id), SUM(order_id), AVG(order_id), COUNT(1) FROM t_order_federate GROUP BY user_id">
+ <assertion expected-result="EnumerableCalc(expr#0..5=[{inputs}], expr#6=[0], expr#7=[=($t4, $t6)], expr#8=[null:DECIMAL(19, 9)], expr#9=[CASE($t7, $t8, $t3)], expr#10=[/($t9, $t4)], EXPR$0=[$t1], EXPR$1=[$t2], EXPR$2=[$t9], EXPR$3=[$t10], EXPR$4=[$t5]) EnumerableAggregate(group=[{0}], EXPR$0=[MAX($1)], EXPR$1=[MIN($1)], EXPR$2=[$SUM0($2)], agg#3=[COUNT($2)], EXPR$4=[COUNT()]) EnumerableCalc(expr#0..1=[{inputs}], expr#2=[CAST($t0):DECIMAL(19, 9)], user_id=[$t1], order_id=[$t0] [...]
+ </test-case>
+</test-cases>