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 <[email protected]>
AuthorDate: Thu Dec 1 08:30:44 2022 +0800
test: add test case for federation optimization test. (#22545)
Co-authored-by: boyjoy1127 <[email protected]>
---
.../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>
