You are viewing a plain text version of this content. The canonical link for it is here.
Posted to notifications@shardingsphere.apache.org by zh...@apache.org on 2022/12/03 02:18:45 UTC
[shardingsphere] branch master updated: Add more test cases in the federation optimization test (#22601)
This is an automated email from the ASF dual-hosted git repository.
zhangliang 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 53ad7838eea Add more test cases in the federation optimization test (#22601)
53ad7838eea is described below
commit 53ad7838eeae4ef62d6d2d35e6e8c05852673811
Author: boyjoy1127 <10...@users.noreply.github.com>
AuthorDate: Sat Dec 3 10:18:39 2022 +0800
Add more test cases in the federation optimization test (#22601)
* test: add test case for federation optimization test.
* test: add more test case in federation optimization tests files.
Co-authored-by: boyjoy1127 <bo...@126.com>
---
.../resources/cases/federation-query-sql-cases.xml | 64 ++++++++++++++++++++++
1 file changed, 64 insertions(+)
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 92039547195..f0504f7944d 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
@@ -128,4 +128,68 @@
<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-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, 2">
+ <assertion expected-result="EnumerableLimit(offset=[2], 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]])), n [...]
+ </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 OFFSET 2">
+ <assertion expected-result="EnumerableLimit(offset=[2], 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]])), n [...]
+ </test-case>
+
+ <test-case sql="SELECT COUNT(i.user_id) 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 GROUP BY i.item_id ORDER BY i.item_id DESC LIMIT 1, 10">
+ <assertion expected-result="EnumerableLimit(offset=[1], fetch=[10]) EnumerableSort(sort0=[$1], dir0=[DESC]) EnumerableCalc(expr#0..1=[{inputs}], EXPR$0=[$t1], item_id=[$t0]) EnumerableAggregate(group=[{0}], EXPR$0=[COUNT($1)]) EnumerableCalc(expr#0..4=[{inputs}], item_id=[$t2], user_id0=[$t4]) EnumerableHashJoin(condition=[AND(=($1, $4), =($0, $3))], joinType=[inner]) TranslatableTableScan(table=[[federate_jdbc, t_order]], fields=[[0, 1]], filt [...]
+ </test-case>
+
+ <test-case sql="SELECT i.user_id 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 GROUP BY i.user_id,i.item_id ORDER BY i.item_id DESC LIMIT 1, 10">
+ <assertion expected-result="EnumerableLimit(offset=[1], fetch=[10]) EnumerableSort(sort0=[$1], dir0=[DESC]) EnumerableAggregate(group=[{0, 1}]) EnumerableCalc(expr#0..4=[{inputs}], user_id=[$t4], item_id=[$t2]) 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]]) Translata [...]
+ </test-case>
+
+ <test-case sql="SELECT i.* FROM t_order o INNER JOIN t_order_item i ON o.order_id = i.order_id WHERE o.order_id = 1000">
+ <assertion expected-result="EnumerableCalc(expr#0..6=[{inputs}], item_id=[$t1], order_id=[$t2], user_id=[$t3], product_id=[$t4], quantity=[$t5], creation_date=[$t6]) EnumerableHashJoin(condition=[=($0, $2)], joinType=[inner]) TranslatableTableScan(table=[[federate_jdbc, t_order]], fields=[[0]], filters=[[=(CAST($0):BIGINT, 1000)]]) TranslatableTableScan(table=[[federate_jdbc, t_order_item]], fields=[[0, 1, 2, 3, 4, 5]], filters=[[=(CAST($1):BIGINT, 1000), null, null, null, [...]
+ </test-case>
+
+ <test-case sql="SELECT t_order_item.* FROM t_order JOIN t_order_item ON t_order.order_id = t_order_item.order_id WHERE t_order.order_id = 1000">
+ <assertion expected-result="EnumerableCalc(expr#0..6=[{inputs}], item_id=[$t1], order_id=[$t2], user_id=[$t3], product_id=[$t4], quantity=[$t5], creation_date=[$t6]) EnumerableHashJoin(condition=[=($0, $2)], joinType=[inner]) TranslatableTableScan(table=[[federate_jdbc, t_order]], fields=[[0]], filters=[[=(CAST($0):BIGINT, 1000)]]) TranslatableTableScan(table=[[federate_jdbc, t_order_item]], fields=[[0, 1, 2, 3, 4, 5]], filters=[[=(CAST($1):BIGINT, 1000), null, null, null, [...]
+ </test-case>
+
+ <test-case sql="SELECT i.* FROM t_order o JOIN t_order_item i USING(order_id) WHERE o.order_id = 1000">
+ <assertion expected-result="EnumerableCalc(expr#0..6=[{inputs}], item_id=[$t1], order_id=[$t2], user_id=[$t3], product_id=[$t4], quantity=[$t5], creation_date=[$t6]) EnumerableHashJoin(condition=[=($0, $2)], joinType=[inner]) TranslatableTableScan(table=[[federate_jdbc, t_order]], fields=[[0]], filters=[[=(CAST($0):BIGINT, 1000)]]) TranslatableTableScan(table=[[federate_jdbc, t_order_item]], fields=[[0, 1, 2, 3, 4, 5]], filters=[[=(CAST($1):BIGINT, 1000), null, null, null, [...]
+ </test-case>
+
+ <test-case sql="SELECT DISTINCT t_order.*, t_order_item.order_id FROM t_order, t_order_item WHERE t_order.order_id = t_order_item.order_id ORDER BY t_order.order_id">
+ <assertion expected-result="EnumerableSort(sort0=[$0], dir0=[ASC]) EnumerableAggregate(group=[{0, 1, 2, 3, 4, 5, 6}]) EnumerableHashJoin(condition=[=($0, $6)], joinType=[inner]) TranslatableTableScan(table=[[federate_jdbc, t_order]], fields=[[0, 1, 2, 3, 4, 5]]) TranslatableTableScan(table=[[federate_jdbc, t_order_item]], fields=[[1]])" />
+ </test-case>
+
+ <test-case sql="SELECT * FROM t_order o LEFT JOIN t_order_item m ON o.order_id = m.order_id AND o.user_id = m.user_id order by o.order_id, m.item_id">
+ <assertion expected-result="EnumerableSort(sort0=[$0], sort1=[$6], dir0=[ASC], dir1=[ASC]) EnumerableHashJoin(condition=[AND(=($0, $7), =($1, $8))], joinType=[left]) TranslatableTableScan(table=[[federate_jdbc, t_order]], fields=[[0, 1, 2, 3, 4, 5]]) TranslatableTableScan(table=[[federate_jdbc, t_order_item]], fields=[[0, 1, 2, 3, 4, 5]])" />
+ </test-case>
+
+ <test-case sql="SELECT * FROM t_order o INNER JOIN t_order_item m ON o.order_id = m.order_id WHERE m.order_id IN (0, 11)">
+ <assertion expected-result="EnumerableHashJoin(condition=[=($0, $7)], joinType=[inner]) TranslatableTableScan(table=[[federate_jdbc, t_order]], fields=[[0, 1, 2, 3, 4, 5]], filters=[[SEARCH($0, Sarg[0L:JavaType(long), 11L:JavaType(long)]:JavaType(long)), null, null, null, null, null]]) TranslatableTableScan(table=[[federate_jdbc, t_order_item]], fields=[[0, 1, 2, 3, 4, 5]], filters=[[SEARCH($1, Sarg[0L:JavaType(long), 11L:JavaType(long)]:JavaType(long)), null, null, null, null, [...]
+ </test-case>
+
+ <test-case sql="SELECT * FROM t_single_table s INNER JOIN t_order o ON s.id = o.order_id">
+ <assertion expected-result="EnumerableCalc(expr#0..9=[{inputs}], proj#0..8=[{exprs}]) EnumerableHashJoin(condition=[=($1, $9)], joinType=[inner]) TranslatableTableScan(table=[[federate_jdbc, t_single_table]], fields=[[0, 1, 2]]) EnumerableCalc(expr#0..5=[{inputs}], expr#6=[CAST($t0):JavaType(class java.lang.Integer)], proj#0..6=[{exprs}]) TranslatableTableScan(table=[[federate_jdbc, t_order]], fields=[[0, 1, 2, 3, 4, 5]])" />
+ </test-case>
+
+ <test-case sql="SELECT * FROM t_single_table s INNER JOIN t_user_info o ON s.id = o.user_id">
+ <assertion expected-result="EnumerableCalc(expr#0..6=[{inputs}], proj#0..2=[{exprs}], user_id=[$t4], information=[$t5]) EnumerableHashJoin(condition=[=($3, $6)], joinType=[inner]) EnumerableCalc(expr#0..2=[{inputs}], expr#3=[CAST($t1):INTEGER], proj#0..3=[{exprs}]) TranslatableTableScan(table=[[federate_jdbc, t_single_table]], fields=[[0, 1, 2]]) EnumerableCalc(expr#0..1=[{inputs}], expr#2=[CAST($t0):INTEGER], proj#0..2=[{exprs}]) TranslatableTableScan(table=[[fe [...]
+ </test-case>
+
+ <test-case sql="SELECT o.*, i.* FROM t_order_federate o, t_order_item i WHERE o.order_id = 1000 AND i.item_id = 100000">
+ <assertion expected-result="EnumerableNestedLoopJoin(condition=[true], joinType=[inner]) TranslatableTableScan(table=[[federate_jdbc, t_order_federate]], fields=[[0, 1, 2]], filters=[[=(CAST($0):INTEGER, 1000), null, null]]) TranslatableTableScan(table=[[federate_jdbc, t_order_item]], fields=[[0, 1, 2, 3, 4, 5]], filters=[[=(CAST($0):BIGINT, 100000), null, null, null, null, null]])" />
+ </test-case>
+
+ <test-case sql="SELECT o.*, i.* FROM t_order_federate o, t_order_item i WHERE o.order_id = 1000 AND i.item_id = 100000 LIMIT 3">
+ <assertion expected-result="EnumerableLimit(fetch=[3]) EnumerableNestedLoopJoin(condition=[true], joinType=[inner]) TranslatableTableScan(table=[[federate_jdbc, t_order_federate]], fields=[[0, 1, 2]], filters=[[=(CAST($0):INTEGER, 1000), null, null]]) TranslatableTableScan(table=[[federate_jdbc, t_order_item]], fields=[[0, 1, 2, 3, 4, 5]], filters=[[=(CAST($0):BIGINT, 100000), null, null, null, null, null]])" />
+ </test-case>
+
+ <test-case sql="SELECT GROUP_CONCAT(i.item_id SEPARATOR ';') AS item_ids FROM t_order_federate o INNER JOIN t_order_item_federate_sharding i ON o.order_id = i.item_id WHERE i.order_id >= 10000">
+ <assertion expected-result="EnumerableAggregate(group=[{}], item_ids=[LISTAGG($0, $1)]) EnumerableCalc(expr#0..2=[{inputs}], expr#3=[';'], $f0=[$t2], $f1=[$t3]) EnumerableHashJoin(condition=[=($0, $1)], joinType=[inner]) EnumerableCalc(expr#0..2=[{inputs}], expr#3=[CAST($t0):INTEGER], order_id0=[$t3]) TranslatableTableScan(table=[[federate_jdbc, t_order_federate]], fields=[[0, 1, 2]]) EnumerableCalc(expr#0..4=[{inputs}], expr#5=[CAST($t0):INTEGER], expr#6=[CA [...]
+ </test-case>
+
+ <test-case sql="select t_order_federate.*, t_order_item_federate_sharding.* from t_order_federate,t_order_item_federate_sharding where t_order_federate.order_id = t_order_item_federate_sharding.item_id">
+ <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-cases>