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/12/01 00:31:00 UTC
[shardingsphere] branch master updated: test: add test case for federation optimization test. (#22545)
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 50854e100ac test: add test case for federation optimization test. (#22545)
50854e100ac is described below
commit 50854e100ac4002e0e4cc25ff6925df2673a6ff4
Author: boyjoy1127 <10...@users.noreply.github.com>
AuthorDate: Thu Dec 1 08:30:44 2022 +0800
test: add test case for federation optimization test. (#22545)
Co-authored-by: boyjoy1127 <bo...@126.com>
---
.../optimizer/SQLOptimizeEngineTest.java | 4 +-
.../resources/cases/federation-query-sql-cases.xml | 47 ++++++++++++++++++++++
2 files changed, 49 insertions(+), 2 deletions(-)
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 9ba52c19364..e94f88bff91 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
@@ -87,7 +87,7 @@ public final class SQLOptimizeEngineTest {
@Before
public void init() {
Map<String, ShardingSphereTable> tables = new HashMap<>(2, 1);
- tables.put("t_order_federate", createOrderTableMetaData());
+ tables.put("t_order_federate", createOrderFederationTableMetaData());
tables.put("t_user_info", createUserInfoTableMetaData());
tables.put("t_order", createTOrderTableMetaData());
tables.put("t_order_item", createTOrderItemTableMetaData());
@@ -99,7 +99,7 @@ public final class SQLOptimizeEngineTest {
optimizeEngine = new SQLOptimizeEngine(converter, SQLFederationPlannerUtil.createHepPlanner());
}
- private ShardingSphereTable createOrderTableMetaData() {
+ private ShardingSphereTable createOrderFederationTableMetaData() {
ShardingSphereColumn orderIdColumn = new ShardingSphereColumn("order_id", Types.VARCHAR, true, false, false, true, false);
ShardingSphereColumn userIdColumn = new ShardingSphereColumn("user_id", Types.VARCHAR, false, false, false, true, false);
ShardingSphereColumn statusColumn = new ShardingSphereColumn("status", Types.VARCHAR, false, false, false, true, false);
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
index 4c26e2c9e14..92039547195 100644
--- 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
@@ -81,4 +81,51 @@
<assertion expected-result="EnumerableCalc(expr#0..9=[{inputs}], proj#0..2=[{exprs}], item_id=[$t4], order_id1=[$t5], user_id0=[$t6], status0=[$t7], remarks=[$t8]) EnumerableHashJoin(condition=[=($3, $9)], joinType=[inner]) EnumerableCalc(expr#0..2=[{inputs}], expr#3=[CAST($t0):INTEGER], proj#0..3=[{exprs}]) TranslatableTableScan(table=[[federate_jdbc, t_order_federate]], fields=[[0, 1, 2]]) EnumerableCalc(expr#0..4=[{inputs}], expr#5=[CAST($t0):INTEGER], proj#0..5=[{ [...]
</test-case>
+ <test-case sql="SELECT i.* FROM t_order o JOIN t_order_item i ON o.user_id = i.user_id AND o.order_id = i.order_id WHERE o.user_id IN (10, 11) AND o.order_id BETWEEN 1000 AND 1909 ORDER BY i.item_id">
+ <assertion expected-result="EnumerableSort(sort0=[$0], dir0=[ASC]) EnumerableCalc(expr#0..7=[{inputs}], item_id=[$t2], order_id=[$t3], user_id=[$t4], product_id=[$t5], quantity=[$t6], creation_date=[$t7]) EnumerableHashJoin(condition=[AND(=($1, $4), =($0, $3))], joinType=[inner]) TranslatableTableScan(table=[[federate_jdbc, t_order]], fields=[[0, 1]], filters=[[AND(SEARCH($1, Sarg[10, 11]), SEARCH($0, Sarg[[1000..1909]])), null]]) TranslatableTableScan(table=[[feder [...]
+ </test-case>
+
+ <test-case sql="SELECT i.* FROM t_order o JOIN t_order_item i ON o.user_id = i.user_id AND o.order_id = i.order_id ORDER BY i.item_id">
+ <assertion expected-result="EnumerableSort(sort0=[$0], dir0=[ASC]) EnumerableCalc(expr#0..7=[{inputs}], item_id=[$t2], order_id=[$t3], user_id=[$t4], product_id=[$t5], quantity=[$t6], creation_date=[$t7]) EnumerableHashJoin(condition=[AND(=($1, $4), =($0, $3))], joinType=[inner]) TranslatableTableScan(table=[[federate_jdbc, t_order]], fields=[[0, 1]]) TranslatableTableScan(table=[[federate_jdbc, t_order_item]], fields=[[0, 1, 2, 3, 4, 5]])" />
+ </test-case>
+
+ <test-case sql="SELECT i.* FROM t_order o JOIN t_order_item i ON o.order_id=i.order_id AND o.user_id = i.user_id WHERE o.order_id = 1000">
+ <assertion expected-result="EnumerableCalc(expr#0..7=[{inputs}], item_id=[$t2], order_id=[$t3], user_id=[$t4], product_id=[$t5], quantity=[$t6], creation_date=[$t7]) EnumerableHashJoin(condition=[AND(=($0, $3), =($1, $4))], joinType=[inner]) TranslatableTableScan(table=[[federate_jdbc, t_order]], fields=[[0, 1]], filters=[[=(CAST($0):BIGINT, 1000), null]]) TranslatableTableScan(table=[[federate_jdbc, t_order_item]], fields=[[0, 1, 2, 3, 4, 5]], filters=[[=(CAST($1):BIGINT, [...]
+ </test-case>
+
+ <test-case sql="SELECT i.* FROM t_order o FORCE INDEX(order_index) JOIN t_order_item i ON o.order_id=i.order_id AND o.user_id = i.user_id AND o.order_id = 1000">
+ <assertion expected-result="EnumerableCalc(expr#0..7=[{inputs}], item_id=[$t2], order_id=[$t3], user_id=[$t4], product_id=[$t5], quantity=[$t6], creation_date=[$t7]) EnumerableHashJoin(condition=[AND(=($0, $3), =($1, $4))], joinType=[inner]) TranslatableTableScan(table=[[federate_jdbc, t_order]], fields=[[0, 1]], filters=[[=(CAST($0):BIGINT, 1000), null]]) TranslatableTableScan(table=[[federate_jdbc, t_order_item]], fields=[[0, 1, 2, 3, 4, 5]], filters=[[=(CAST($1):BIGINT, [...]
+ </test-case>
+
+ <test-case sql="SELECT i.* FROM t_order o FORCE INDEX(order_index) JOIN t_order_item i ON o.order_id=i.order_id AND o.user_id = i.user_id AND o.order_id in (1000,1001)">
+ <assertion expected-result="EnumerableCalc(expr#0..7=[{inputs}], item_id=[$t2], order_id=[$t3], user_id=[$t4], product_id=[$t5], quantity=[$t6], creation_date=[$t7]) EnumerableHashJoin(condition=[AND(=($0, $3), =($1, $4))], joinType=[inner]) TranslatableTableScan(table=[[federate_jdbc, t_order]], fields=[[0, 1]], filters=[[SEARCH($0, Sarg[1000L:JavaType(long), 1001L:JavaType(long)]:JavaType(long)), null]]) TranslatableTableScan(table=[[federate_jdbc, t_order_item]], fields [...]
+ </test-case>
+
+ <test-case sql="SELECT i.* FROM t_order o FORCE INDEX(order_index) JOIN t_order_item i ON o.order_id=i.order_id AND o.user_id = i.user_id AND o.order_id in (1000,1001)">
+ <assertion expected-result="EnumerableCalc(expr#0..7=[{inputs}], item_id=[$t2], order_id=[$t3], user_id=[$t4], product_id=[$t5], quantity=[$t6], creation_date=[$t7]) EnumerableHashJoin(condition=[AND(=($0, $3), =($1, $4))], joinType=[inner]) TranslatableTableScan(table=[[federate_jdbc, t_order]], fields=[[0, 1]], filters=[[SEARCH($0, Sarg[1000L:JavaType(long), 1001L:JavaType(long)]:JavaType(long)), null]]) TranslatableTableScan(table=[[federate_jdbc, t_order_item]], fields [...]
+ </test-case>
+
+ <test-case sql="SELECT COUNT(*) AS items_count FROM t_order o, t_order_item i WHERE o.user_id = i.user_id AND o.order_id = i.order_id AND o.user_id IN (10, 11) AND o.order_id BETWEEN 1000 AND 1909">
+ <assertion expected-result="EnumerableAggregate(group=[{}], items_count=[COUNT()]) EnumerableHashJoin(condition=[AND(=($1, $8), =($0, $7))], joinType=[inner]) TranslatableTableScan(table=[[federate_jdbc, t_order]], fields=[[0, 1, 2, 3, 4, 5]], filters=[[AND(SEARCH($1, Sarg[10, 11]), SEARCH($0, Sarg[[1000..1909]])), null, null, null, null, null]]) TranslatableTableScan(table=[[federate_jdbc, t_order_item]], fields=[[0, 1, 2, 3, 4, 5]], filters=[[AND(SEARCH($2, Sarg[10, 11]) [...]
+ </test-case>
+
+ <test-case sql="SELECT COUNT(*) AS items_count FROM t_order o JOIN t_order_item i ON o.user_id = i.user_id AND o.order_id = i.order_id WHERE o.user_id IN (10, 11) AND o.order_id BETWEEN 1000 AND 1909">
+ <assertion expected-result="EnumerableAggregate(group=[{}], items_count=[COUNT()]) EnumerableHashJoin(condition=[AND(=($1, $8), =($0, $7))], joinType=[inner]) TranslatableTableScan(table=[[federate_jdbc, t_order]], fields=[[0, 1, 2, 3, 4, 5]], filters=[[AND(SEARCH($1, Sarg[10, 11]), SEARCH($0, Sarg[[1000..1909]])), null, null, null, null, null]]) TranslatableTableScan(table=[[federate_jdbc, t_order_item]], fields=[[0, 1, 2, 3, 4, 5]], filters=[[AND(SEARCH($2, Sarg[10, 11]) [...]
+ </test-case>
+
+ <test-case sql="SELECT i.* FROM t_order o JOIN t_order_item i ON o.user_id = i.user_id AND o.order_id = i.order_id WHERE (o.order_id = 1000 OR o.order_id = 1100) AND o.user_id = 11">
+ <assertion expected-result="EnumerableCalc(expr#0..7=[{inputs}], item_id=[$t2], order_id=[$t3], user_id=[$t4], product_id=[$t5], quantity=[$t6], creation_date=[$t7]) EnumerableHashJoin(condition=[AND(=($1, $4), =($0, $3))], joinType=[inner]) TranslatableTableScan(table=[[federate_jdbc, t_order]], fields=[[0, 1]], filters=[[AND(SEARCH(CAST($0):BIGINT, Sarg[1000L:BIGINT, 1100L:BIGINT]:BIGINT), =(CAST($1):INTEGER, 11)), null]]) TranslatableTableScan(table=[[federate_jdbc, t_o [...]
+ </test-case>
+
+ <test-case sql="SELECT i.* FROM t_order o JOIN t_order_item i ON o.user_id = i.user_id AND o.order_id = i.order_id WHERE o.user_id IN (10, 19) AND o.order_id BETWEEN 1000 AND 1909 ORDER BY i.item_id DESC">
+ <assertion expected-result="EnumerableSort(sort0=[$0], dir0=[DESC]) EnumerableCalc(expr#0..7=[{inputs}], item_id=[$t2], order_id=[$t3], user_id=[$t4], product_id=[$t5], quantity=[$t6], creation_date=[$t7]) EnumerableHashJoin(condition=[AND(=($1, $4), =($0, $3))], joinType=[inner]) TranslatableTableScan(table=[[federate_jdbc, t_order]], fields=[[0, 1]], filters=[[AND(SEARCH($1, Sarg[10, 19]), SEARCH($0, Sarg[[1000..1909]])), null]]) TranslatableTableScan(table=[[fede [...]
+ </test-case>
+
+ <test-case sql="SELECT i.*, o.* FROM t_order o JOIN t_order_item i ON o.user_id = i.user_id AND o.order_id = i.order_id ORDER BY item_id">
+ <assertion expected-result="EnumerableSort(sort0=[$0], dir0=[ASC]) EnumerableCalc(expr#0..11=[{inputs}], item_id=[$t6], order_id=[$t7], user_id=[$t8], product_id=[$t9], quantity=[$t10], creation_date=[$t11], order_id0=[$t0], user_id0=[$t1], status=[$t2], merchant_id=[$t3], remark=[$t4], creation_date0=[$t5]) EnumerableHashJoin(condition=[AND(=($1, $8), =($0, $7))], joinType=[inner]) TranslatableTableScan(table=[[federate_jdbc, t_order]], fields=[[0, 1, 2, 3, 4, 5]]) [...]
+ </test-case>
+
+ <test-case sql="SELECT i.* FROM t_order o JOIN t_order_item i ON o.user_id = i.user_id AND o.order_id = i.order_id WHERE o.user_id IN (10, 19) AND o.order_id BETWEEN 1000 AND 1909 ORDER BY i.item_id DESC LIMIT 2">
+ <assertion expected-result="EnumerableLimit(fetch=[2]) EnumerableSort(sort0=[$0], dir0=[DESC]) EnumerableCalc(expr#0..7=[{inputs}], item_id=[$t2], order_id=[$t3], user_id=[$t4], product_id=[$t5], quantity=[$t6], creation_date=[$t7]) EnumerableHashJoin(condition=[AND(=($1, $4), =($0, $3))], joinType=[inner]) TranslatableTableScan(table=[[federate_jdbc, t_order]], fields=[[0, 1]], filters=[[AND(SEARCH($1, Sarg[10, 19]), SEARCH($0, Sarg[[1000..1909]])), null]]) [...]
+ </test-case>
</test-cases>