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 b2a01c519e0 19937 federation optimization test 20221205 (#22693)
b2a01c519e0 is described below
commit b2a01c519e084d269b6f23f1ddf9a68aace7a21e
Author: boyjoy1127 <[email protected]>
AuthorDate: Thu Dec 8 16:50:46 2022 +0800
19937 federation optimization test 20221205 (#22693)
* test: add test case for federation optimization test.
* test: add more test case in federation optimization tests files.
* test: add more test cases in federation optimization unit test files.
Co-authored-by: boyjoy1127 <[email protected]>
---
.../optimizer/it/SQLOptimizeEngineIT.java | 34 ++++
.../resources/cases/federation-query-sql-cases.xml | 220 +++++++++++++++++++++
2 files changed, 254 insertions(+)
diff --git
a/kernel/sql-federation/optimizer/src/test/java/org/apache/shardingsphere/sqlfederation/optimizer/it/SQLOptimizeEngineIT.java
b/kernel/sql-federation/optimizer/src/test/java/org/apache/shardingsphere/sqlfederation/optimizer/it/SQLOptimizeEngineIT.java
index 17333a0257a..845548d062f 100644
---
a/kernel/sql-federation/optimizer/src/test/java/org/apache/shardingsphere/sqlfederation/optimizer/it/SQLOptimizeEngineIT.java
+++
b/kernel/sql-federation/optimizer/src/test/java/org/apache/shardingsphere/sqlfederation/optimizer/it/SQLOptimizeEngineIT.java
@@ -88,6 +88,9 @@ public final class SQLOptimizeEngineIT {
tables.put("t_single_table", createTSingleTableMetaData());
tables.put("t_order_federate_sharding",
createTOrderFederateShardingMetaData());
tables.put("t_order_item_federate_sharding",
createTOrderItemFederateShardingMetaData());
+ tables.put("t_merchant", createTMerchantMetaData());
+ tables.put("t_product", createTProductMetaData());
+ tables.put("t_product_detail", createTProductDetailMetaData());
optimizeEngine = new SQLOptimizeEngine(createSqlToRelConverter(new
ShardingSphereSchema(tables, Collections.emptyMap())),
SQLFederationPlannerUtil.createHepPlanner());
}
@@ -150,6 +153,37 @@ public final class SQLOptimizeEngineIT {
Collections.emptyList(), Collections.emptyList());
}
+ private ShardingSphereTable createTMerchantMetaData() {
+ ShardingSphereColumn merchantIdColumn = new
ShardingSphereColumn("merchant_id", Types.INTEGER, true, false, false, true,
false);
+ ShardingSphereColumn countryIdColumn = new
ShardingSphereColumn("country_id", Types.SMALLINT, false, false, false, true,
false);
+ ShardingSphereColumn merchantNameColumn = new
ShardingSphereColumn("merchant_name", Types.VARCHAR, false, false, false, true,
false);
+ ShardingSphereColumn businessCodeColumn = new
ShardingSphereColumn("business_code", Types.VARCHAR, false, false, false, true,
false);
+ ShardingSphereColumn telephoneColumn = new
ShardingSphereColumn("telephone", Types.CHAR, false, false, false, true, false);
+ ShardingSphereColumn creationDateColumn = new
ShardingSphereColumn("creation_date", Types.DATE, false, false, false, true,
false);
+ return new ShardingSphereTable("t_merchant",
Arrays.asList(merchantIdColumn, countryIdColumn, merchantNameColumn,
businessCodeColumn, telephoneColumn, creationDateColumn),
+ Collections.emptyList(), Collections.emptyList());
+ }
+
+ private ShardingSphereTable createTProductDetailMetaData() {
+ ShardingSphereColumn detailIdColumn = new
ShardingSphereColumn("detail_id", Types.INTEGER, true, false, false, true,
false);
+ ShardingSphereColumn productIdColumn = new
ShardingSphereColumn("product_id", Types.INTEGER, false, false, false, true,
false);
+ ShardingSphereColumn descriptionColumn = new
ShardingSphereColumn("description", Types.VARCHAR, false, false, false, true,
false);
+ ShardingSphereColumn creationDateColumn = new
ShardingSphereColumn("creation_date", Types.DATE, false, false, false, true,
false);
+ return new ShardingSphereTable("t_product_detail",
Arrays.asList(detailIdColumn, productIdColumn, descriptionColumn,
creationDateColumn),
+ Collections.emptyList(), Collections.emptyList());
+ }
+
+ private ShardingSphereTable createTProductMetaData() {
+ ShardingSphereColumn productIdColumn = new
ShardingSphereColumn("product_id", Types.INTEGER, true, false, false, true,
false);
+ ShardingSphereColumn productNameColumn = new
ShardingSphereColumn("product_name", Types.VARCHAR, false, false, false, true,
false);
+ ShardingSphereColumn categoryIdColumn = new
ShardingSphereColumn("category_id", Types.INTEGER, false, false, false, true,
false);
+ ShardingSphereColumn priceColumn = new ShardingSphereColumn("price",
Types.DECIMAL, false, false, false, true, false);
+ ShardingSphereColumn statusColumn = new ShardingSphereColumn("status",
Types.VARCHAR, false, false, false, true, false);
+ ShardingSphereColumn creationDateColumn = new
ShardingSphereColumn("creation_date", Types.DATE, false, false, false, true,
false);
+ return new ShardingSphereTable("t_product",
Arrays.asList(productIdColumn, productNameColumn, categoryIdColumn,
priceColumn, statusColumn, creationDateColumn),
+ Collections.emptyList(), Collections.emptyList());
+ }
+
private SqlToRelConverter createSqlToRelConverter(final
ShardingSphereSchema schema) {
CalciteConnectionConfig connectionConfig = new
CalciteConnectionConfigImpl(new Properties());
RelDataTypeFactory relDataTypeFactory = new JavaTypeFactoryImpl();
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 f0504f7944d..cb3da3fbf9b 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
@@ -192,4 +192,224 @@
<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-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 AND
t_order_item_federate_sharding.remarks = 't_order_item_federate_sharding' ">
+ <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 o.*, i.* from t_order_federate o,
t_order_item_federate_sharding i where o.order_id = i.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-case sql="SELECT * FROM t_order o INNER JOIN t_order_item i ON
o.order_id = i.order_id WHERE o.user_id = ? ORDER BY o.order_id, 7">
+ <assertion expected-result="EnumerableSort(sort0=[$0], sort1=[$6],
dir0=[ASC], dir1=[ASC]) EnumerableHashJoin(condition=[=($0, $7)],
joinType=[inner]) TranslatableTableScan(table=[[federate_jdbc, t_order]],
fields=[[0, 1, 2, 3, 4, 5]], filters=[[=($1, ?0), null, null, null, null,
null]]) 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 i ON
o.order_id = i.order_id WHERE o.user_id = ? ORDER BY o.order_id, 7 LIMIT 5, 2">
+ <assertion expected-result="EnumerableLimit(offset=[5], fetch=[2])
EnumerableSort(sort0=[$0], sort1=[$6], dir0=[ASC], dir1=[ASC])
EnumerableHashJoin(condition=[=($0, $7)], joinType=[inner])
TranslatableTableScan(table=[[federate_jdbc, t_order]], fields=[[0, 1, 2, 3, 4,
5]], filters=[[=($1, ?0), null, null, null, null, null]])
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 i
USING(order_id) WHERE o.user_id = ? ORDER BY o.order_id, 7">
+ <assertion expected-result="EnumerableSort(sort0=[$11], sort1=[$6],
dir0=[ASC], dir1=[ASC]) EnumerableCalc(expr#0..11=[{inputs}],
expr#12=[COALESCE($t0, $t7)], order_id=[$t12], user_id=[$t1], status=[$t2],
merchant_id=[$t3], remark=[$t4], creation_date=[$t5], item_id=[$t6],
user_id0=[$t8], product_id=[$t9], quantity=[$t10], creation_date0=[$t11],
order_id0=[$t0]) EnumerableHashJoin(condition=[=($0, $7)], joinType=[inner])
TranslatableTableScan(table=[[federate_jdbc, t_or [...]
+ </test-case>
+
+ <test-case sql="SELECT * FROM t_order o INNER JOIN t_order_item i
USING(order_id) WHERE o.user_id = ? ORDER BY o.order_id, 7 LIMIT 5, 2">
+ <assertion expected-result="EnumerableLimit(offset=[5], fetch=[2])
EnumerableSort(sort0=[$11], sort1=[$6], dir0=[ASC], dir1=[ASC])
EnumerableCalc(expr#0..11=[{inputs}], expr#12=[COALESCE($t0, $t7)],
order_id=[$t12], user_id=[$t1], status=[$t2], merchant_id=[$t3], remark=[$t4],
creation_date=[$t5], item_id=[$t6], user_id0=[$t8], product_id=[$t9],
quantity=[$t10], creation_date0=[$t11], order_id0=[$t0])
EnumerableHashJoin(condition=[=($0, $7)], joinType=[inner]) Tra [...]
+ </test-case>
+
+ <test-case sql="SELECT * FROM t_order o NATURAL JOIN t_order_item i WHERE
o.user_id = ? ORDER BY o.order_id, 7">
+ <assertion expected-result="EnumerableSort(sort0=[$9], sort1=[$6],
dir0=[ASC], dir1=[ASC]) EnumerableCalc(expr#0..11=[{inputs}],
expr#12=[COALESCE($t0, $t7)], expr#13=[COALESCE($t1, $t8)],
expr#14=[COALESCE($t5, $t11)], order_id=[$t12], user_id=[$t13],
creation_date=[$t14], status=[$t2], merchant_id=[$t3], remark=[$t4],
item_id=[$t6], product_id=[$t9], quantity=[$t10], order_id0=[$t0])
EnumerableHashJoin(condition=[AND(=($0, $7), =($1, $8), =($5, $11))],
joinType=[inner]) [...]
+ </test-case>
+
+ <test-case sql="SELECT * FROM t_order o NATURAL JOIN t_order_item i WHERE
o.user_id = ? ORDER BY o.order_id, 7 LIMIT 5, 2">
+ <assertion expected-result="EnumerableLimit(offset=[5], fetch=[2])
EnumerableSort(sort0=[$9], sort1=[$6], dir0=[ASC], dir1=[ASC])
EnumerableCalc(expr#0..11=[{inputs}], expr#12=[COALESCE($t0, $t7)],
expr#13=[COALESCE($t1, $t8)], expr#14=[COALESCE($t5, $t11)], order_id=[$t12],
user_id=[$t13], creation_date=[$t14], status=[$t2], merchant_id=[$t3],
remark=[$t4], item_id=[$t6], product_id=[$t9], quantity=[$t10],
order_id0=[$t0]) EnumerableHashJoin(condition=[AND(=($0, $7), =( [...]
+ </test-case>
+
+ <test-case sql="SELECT MIN(o.order_id), MIN(o.merchant_id), i.product_id
FROM t_order o INNER JOIN t_order_item i ON o.order_id = i.order_id WHERE
o.user_id = 10 GROUP BY i.product_id">
+ <assertion expected-result="EnumerableCalc(expr#0..2=[{inputs}],
EXPR$0=[$t1], EXPR$1=[$t2], product_id=[$t0]) EnumerableAggregate(group=[{0}],
EXPR$0=[MIN($1)], EXPR$1=[MIN($2)]) EnumerableCalc(expr#0..3=[{inputs}],
product_id=[$t3], order_id=[$t0], merchant_id=[$t1])
EnumerableHashJoin(condition=[=($0, $2)], joinType=[inner])
TranslatableTableScan(table=[[federate_jdbc, t_order]], fields=[[0, 3]],
filters=[[=(CAST($1):INTEGER, 10), null]]) TranslatableTab [...]
+ </test-case>
+
+ <test-case sql="SELECT * FROM t_order o CROSS JOIN t_order_item i ON
o.order_id = i.order_id WHERE o.user_id = 7 ORDER BY o.order_id LIMIT 10, 10">
+ <assertion expected-result="EnumerableLimit(offset=[10], fetch=[10])
EnumerableSort(sort0=[$0], dir0=[ASC]) EnumerableHashJoin(condition=[=($0,
$7)], joinType=[inner]) TranslatableTableScan(table=[[federate_jdbc,
t_order]], fields=[[0, 1, 2, 3, 4, 5]], filters=[[=(CAST($1):INTEGER, 7), null,
null, null, null, null]]) TranslatableTableScan(table=[[federate_jdbc,
t_order_item]], fields=[[0, 1, 2, 3, 4, 5]])" />
+ </test-case>
+
+ <test-case sql="SELECT * FROM t_order o LEFT JOIN t_order_item i ON
o.order_id = i.order_id WHERE o.user_id = 10 ORDER BY o.order_id, 7">
+ <assertion expected-result="EnumerableSort(sort0=[$0], sort1=[$6],
dir0=[ASC], dir1=[ASC]) EnumerableHashJoin(condition=[=($0, $7)],
joinType=[left]) TranslatableTableScan(table=[[federate_jdbc, t_order]],
fields=[[0, 1, 2, 3, 4, 5]], filters=[[=(CAST($1):INTEGER, 10), null, null,
null, null, null]]) TranslatableTableScan(table=[[federate_jdbc,
t_order_item]], fields=[[0, 1, 2, 3, 4, 5]])" />
+ </test-case>
+
+ <test-case sql="SELECT * FROM t_order o RIGHT JOIN t_order_item i ON
o.order_id = i.order_id WHERE i.user_id = 10 ORDER BY i.item_id, 7">
+ <assertion expected-result="EnumerableSort(sort0=[$6], dir0=[ASC])
EnumerableHashJoin(condition=[=($0, $7)], joinType=[right])
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]], filters=[[=(CAST($2):INTEGER, 10), null, null,
null, null, null]])" />
+ </test-case>
+
+ <test-case sql="SELECT * FROM t_order o JOIN t_order_item i ON o.order_id
= i.order_id WHERE o.user_id = 10 OR i.user_id = 10 ORDER BY o.order_id">
+ <assertion expected-result="EnumerableSort(sort0=[$0], dir0=[ASC])
EnumerableCalc(expr#0..13=[{inputs}], proj#0..5=[{exprs}], item_id=[$t7],
order_id0=[$t8], user_id0=[$t9], product_id=[$t10], quantity=[$t11],
creation_date0=[$t12]) EnumerableHashJoin(condition=[AND(=($0, $8), OR($6,
$13))], joinType=[inner]) EnumerableCalc(expr#0..5=[{inputs}],
expr#6=[CAST($t1):INTEGER], expr#7=[10], expr#8=[=($t6, $t7)],
proj#0..5=[{exprs}], EXPR$0=[$t8]) TranslatableTableScan( [...]
+ </test-case>
+
+ <test-case sql="SELECT * FROM t_order o LEFT JOIN t_order_item i
USING(order_id) WHERE o.user_id = 10 ORDER BY o.order_id, 7">
+ <assertion expected-result="EnumerableSort(sort0=[$11], sort1=[$6],
dir0=[ASC], dir1=[ASC]) EnumerableCalc(expr#0..11=[{inputs}],
expr#12=[COALESCE($t0, $t7)], order_id=[$t12], user_id=[$t1], status=[$t2],
merchant_id=[$t3], remark=[$t4], creation_date=[$t5], item_id=[$t6],
user_id0=[$t8], product_id=[$t9], quantity=[$t10], creation_date0=[$t11],
order_id0=[$t0]) EnumerableHashJoin(condition=[=($0, $7)], joinType=[left])
TranslatableTableScan(table=[[federate_jdbc, t_ord [...]
+ </test-case>
+
+ <test-case sql="SELECT * FROM t_order o RIGHT JOIN t_order_item i
USING(order_id) WHERE i.user_id = 10 ORDER BY i.item_id, 7">
+ <assertion expected-result="EnumerableSort(sort0=[$6], dir0=[ASC])
EnumerableCalc(expr#0..11=[{inputs}], expr#12=[COALESCE($t0, $t7)],
order_id=[$t12], user_id=[$t1], status=[$t2], merchant_id=[$t3], remark=[$t4],
creation_date=[$t5], item_id=[$t6], user_id0=[$t8], product_id=[$t9],
quantity=[$t10], creation_date0=[$t11]) EnumerableHashJoin(condition=[=($0,
$7)], joinType=[right]) TranslatableTableScan(table=[[federate_jdbc,
t_order]], fields=[[0, 1, 2, 3, 4, 5]]) T [...]
+ </test-case>
+
+ <test-case sql="SELECT * FROM t_order o JOIN t_order_item i
USING(order_id) WHERE o.user_id = 10 OR i.user_id = 10 ORDER BY o.order_id, 7">
+ <assertion expected-result="EnumerableSort(sort0=[$11], sort1=[$6],
dir0=[ASC], dir1=[ASC]) EnumerableCalc(expr#0..13=[{inputs}],
expr#14=[COALESCE($t0, $t8)], order_id=[$t14], user_id=[$t1], status=[$t2],
merchant_id=[$t3], remark=[$t4], creation_date=[$t5], item_id=[$t7],
user_id0=[$t9], product_id=[$t10], quantity=[$t11], creation_date0=[$t12],
order_id0=[$t0]) EnumerableHashJoin(condition=[AND(=($0, $8), OR($6, $13))],
joinType=[inner]) EnumerableCalc(expr#0..5=[{inp [...]
+ </test-case>
+
+ <test-case sql="SELECT * FROM t_order o INNER JOIN t_merchant m
USING(merchant_id) WHERE o.user_id = 10 ORDER BY o.order_id">
+ <assertion expected-result="EnumerableSort(sort0=[$1], dir0=[ASC])
EnumerableCalc(expr#0..11=[{inputs}], expr#12=[COALESCE($t3, $t6)],
merchant_id=[$t12], order_id=[$t0], user_id=[$t1], status=[$t2], remark=[$t4],
creation_date=[$t5], country_id=[$t7], merchant_name=[$t8],
business_code=[$t9], telephone=[$t10], creation_date0=[$t11])
EnumerableHashJoin(condition=[=($3, $6)], joinType=[inner])
TranslatableTableScan(table=[[federate_jdbc, t_order]], fields=[[0, 1, 2, 3, 4,
[...]
+ </test-case>
+
+ <test-case sql="SELECT * FROM t_order o INNER JOIN t_merchant m
USING(merchant_id) WHERE o.user_id = 10 ORDER BY o.order_id LIMIT 5, 2">
+ <assertion expected-result="EnumerableLimit(offset=[5], fetch=[2])
EnumerableSort(sort0=[$1], dir0=[ASC]) EnumerableCalc(expr#0..11=[{inputs}],
expr#12=[COALESCE($t3, $t6)], merchant_id=[$t12], order_id=[$t0],
user_id=[$t1], status=[$t2], remark=[$t4], creation_date=[$t5],
country_id=[$t7], merchant_name=[$t8], business_code=[$t9], telephone=[$t10],
creation_date0=[$t11]) EnumerableHashJoin(condition=[=($3, $6)],
joinType=[inner]) TranslatableTableScan(table=[[fed [...]
+ </test-case>
+
+ <test-case sql="SELECT * FROM t_order o NATURAL LEFT JOIN t_order_item i
WHERE o.user_id = 10 ORDER BY o.order_id, 7">
+ <assertion expected-result="EnumerableSort(sort0=[$9], sort1=[$6],
dir0=[ASC], dir1=[ASC]) EnumerableCalc(expr#0..11=[{inputs}],
expr#12=[COALESCE($t0, $t7)], expr#13=[COALESCE($t1, $t8)],
expr#14=[COALESCE($t5, $t11)], order_id=[$t12], user_id=[$t13],
creation_date=[$t14], status=[$t2], merchant_id=[$t3], remark=[$t4],
item_id=[$t6], product_id=[$t9], quantity=[$t10], order_id0=[$t0])
EnumerableHashJoin(condition=[AND(=($0, $7), =($1, $8), =($5, $11))],
joinType=[left]) [...]
+ </test-case>
+
+ <test-case sql="SELECT * FROM t_order o NATURAL RIGHT JOIN t_order_item i
WHERE i.user_id = 10 ORDER BY i.item_id, 7">
+ <assertion expected-result="EnumerableSort(sort0=[$6], dir0=[ASC])
EnumerableCalc(expr#0..11=[{inputs}], expr#12=[COALESCE($t0, $t7)],
expr#13=[COALESCE($t1, $t8)], expr#14=[COALESCE($t5, $t11)], order_id=[$t12],
user_id=[$t13], creation_date=[$t14], status=[$t2], merchant_id=[$t3],
remark=[$t4], item_id=[$t6], product_id=[$t9], quantity=[$t10])
EnumerableHashJoin(condition=[AND(=($0, $7), =($1, $8), =($5, $11))],
joinType=[right]) TranslatableTableScan(table=[[federate_ [...]
+ </test-case>
+
+ <test-case sql="SELECT * FROM t_order o NATURAL JOIN t_order_item i WHERE
o.user_id = 10 OR i.user_id = 10 ORDER BY o.order_id, 7">
+ <assertion expected-result="EnumerableSort(sort0=[$9], sort1=[$6],
dir0=[ASC], dir1=[ASC]) EnumerableCalc(expr#0..13=[{inputs}],
expr#14=[COALESCE($t0, $t8)], expr#15=[COALESCE($t1, $t9)],
expr#16=[COALESCE($t5, $t12)], order_id=[$t14], user_id=[$t15],
creation_date=[$t16], status=[$t2], merchant_id=[$t3], remark=[$t4],
item_id=[$t7], product_id=[$t10], quantity=[$t11], order_id0=[$t0])
EnumerableHashJoin(condition=[AND(=($0, $8), =($1, $9), =($5, $12), OR($6,
$13))], joinTyp [...]
+ </test-case>
+
+ <test-case sql="SELECT * FROM t_order o NATURAL JOIN t_merchant m WHERE
o.user_id = 10 ORDER BY o.order_id">
+ <assertion expected-result="EnumerableSort(sort0=[$2], dir0=[ASC])
EnumerableCalc(expr#0..11=[{inputs}], expr#12=[COALESCE($t3, $t6)],
expr#13=[COALESCE($t5, $t11)], merchant_id=[$t12], creation_date=[$t13],
order_id=[$t0], user_id=[$t1], status=[$t2], remark=[$t4], country_id=[$t7],
merchant_name=[$t8], business_code=[$t9], telephone=[$t10])
EnumerableHashJoin(condition=[AND(=($3, $6), =($5, $11))], joinType=[inner])
TranslatableTableScan(table=[[federate_jdbc, t_order] [...]
+ </test-case>
+
+ <test-case sql="SELECT * FROM t_order o NATURAL JOIN t_merchant m WHERE
o.user_id = 10 ORDER BY o.order_id LIMIT 5, 2">
+ <assertion expected-result="EnumerableLimit(offset=[5], fetch=[2])
EnumerableSort(sort0=[$2], dir0=[ASC]) EnumerableCalc(expr#0..11=[{inputs}],
expr#12=[COALESCE($t3, $t6)], expr#13=[COALESCE($t5, $t11)],
merchant_id=[$t12], creation_date=[$t13], order_id=[$t0], user_id=[$t1],
status=[$t2], remark=[$t4], country_id=[$t7], merchant_name=[$t8],
business_code=[$t9], telephone=[$t10])
EnumerableHashJoin(condition=[AND(=($3, $6), =($5, $11))], joinType=[inner])
Transla [...]
+ </test-case>
+
+ <test-case sql="SELECT MIN(o.order_id), MIN(o.merchant_id),
MIN(m.merchant_name) FROM t_order o INNER JOIN t_merchant m ON o.merchant_id =
m.merchant_id WHERE o.user_id = 10 GROUP BY m.merchant_id">
+ <assertion expected-result="EnumerableCalc(expr#0..3=[{inputs}],
EXPR$0=[$t1], EXPR$1=[$t2], EXPR$2=[$t3]) EnumerableAggregate(group=[{0}],
EXPR$0=[MIN($1)], EXPR$1=[MIN($2)], EXPR$2=[MIN($3)])
EnumerableCalc(expr#0..3=[{inputs}], merchant_id0=[$t2], order_id=[$t0],
merchant_id=[$t1], merchant_name=[$t3])
EnumerableHashJoin(condition=[=($1, $2)], joinType=[inner])
TranslatableTableScan(table=[[federate_jdbc, t_order]], fields=[[0, 3]],
filters=[[=(CAST($1):INTEGER [...]
+ </test-case>
+
+ <test-case sql="SELECT * FROM t_order o CROSS JOIN t_merchant m ON
o.merchant_id = m.merchant_id WHERE o.user_id = 10 ORDER BY o.order_id, 7 LIMIT
10, 10">
+ <assertion expected-result="EnumerableLimit(offset=[10], fetch=[10])
EnumerableSort(sort0=[$0], sort1=[$6], dir0=[ASC], dir1=[ASC])
EnumerableHashJoin(condition=[=($3, $6)], joinType=[inner])
TranslatableTableScan(table=[[federate_jdbc, t_order]], fields=[[0, 1, 2, 3, 4,
5]], filters=[[=(CAST($1):INTEGER, 10), null, null, null, null, null]])
TranslatableTableScan(table=[[federate_jdbc, t_merchant]], fields=[[0, 1, 2, 3,
4, 5]])" />
+ </test-case>
+
+ <test-case sql="SELECT * FROM t_order o LEFT JOIN t_merchant m ON
o.merchant_id = m.merchant_id WHERE o.user_id = 10 ORDER BY o.order_id, 7">
+ <assertion expected-result="EnumerableSort(sort0=[$0], sort1=[$6],
dir0=[ASC], dir1=[ASC]) EnumerableHashJoin(condition=[=($3, $6)],
joinType=[left]) TranslatableTableScan(table=[[federate_jdbc, t_order]],
fields=[[0, 1, 2, 3, 4, 5]], filters=[[=(CAST($1):INTEGER, 10), null, null,
null, null, null]]) TranslatableTableScan(table=[[federate_jdbc,
t_merchant]], fields=[[0, 1, 2, 3, 4, 5]])" />
+ </test-case>
+
+ <test-case sql="SELECT * FROM t_order o RIGHT JOIN t_merchant m ON
o.merchant_id = m.merchant_id WHERE m.country_id = 1 ORDER BY o.order_id,
m.merchant_id, 7">
+ <assertion expected-result="EnumerableSort(sort0=[$0], sort1=[$6],
dir0=[ASC], dir1=[ASC]) EnumerableHashJoin(condition=[=($3, $6)],
joinType=[right]) TranslatableTableScan(table=[[federate_jdbc, t_order]],
fields=[[0, 1, 2, 3, 4, 5]]) TranslatableTableScan(table=[[federate_jdbc,
t_merchant]], fields=[[0, 1, 2, 3, 4, 5]], filters=[[=(CAST($1):INTEGER, 1),
null, null, null, null, null]])" />
+ </test-case>
+
+ <test-case sql="SELECT * FROM t_order o JOIN t_merchant m ON o.merchant_id
= m.merchant_id where o.user_id = 10 OR m.country_id = 1 ORDER BY o.order_id,
7">
+ <assertion expected-result="EnumerableSort(sort0=[$0], sort1=[$6],
dir0=[ASC], dir1=[ASC]) EnumerableCalc(expr#0..13=[{inputs}],
proj#0..5=[{exprs}], merchant_id0=[$t7], country_id=[$t8], merchant_name=[$t9],
business_code=[$t10], telephone=[$t11], creation_date0=[$t12])
EnumerableHashJoin(condition=[AND(=($3, $7), OR($6, $13))], joinType=[inner])
EnumerableCalc(expr#0..5=[{inputs}], expr#6=[CAST($t1):INTEGER],
expr#7=[10], expr#8=[=($t6, $t7)], proj#0..5=[{exprs}], EXPR [...]
+ </test-case>
+
+ <test-case sql="SELECT * FROM t_product p INNER JOIN t_product_detail d ON
p.product_id = d.product_id WHERE p.product_id > 10 ORDER BY p.product_id DESC">
+ <assertion expected-result="EnumerableSort(sort0=[$0], dir0=[DESC])
EnumerableHashJoin(condition=[=($0, $7)], joinType=[inner])
TranslatableTableScan(table=[[federate_jdbc, t_product]], fields=[[0, 1, 2, 3,
4, 5]], filters=[[>($0, 10), null, null, null, null, null]])
TranslatableTableScan(table=[[federate_jdbc, t_product_detail]], fields=[[0, 1,
2, 3]], filters=[[>($1, 10), null, null, null]])" />
+ </test-case>
+
+ <test-case sql="SELECT * FROM t_product p INNER JOIN t_product_detail d ON
p.product_id = d.product_id WHERE p.product_id > 10 ORDER BY p.product_id DESC
LIMIT 2, 5">
+ <assertion expected-result="EnumerableLimit(offset=[2], fetch=[5])
EnumerableSort(sort0=[$0], dir0=[DESC]) EnumerableHashJoin(condition=[=($0,
$7)], joinType=[inner]) TranslatableTableScan(table=[[federate_jdbc,
t_product]], fields=[[0, 1, 2, 3, 4, 5]], filters=[[>($0, 10), null, null,
null, null, null]]) TranslatableTableScan(table=[[federate_jdbc,
t_product_detail]], fields=[[0, 1, 2, 3]], filters=[[>($1, 10), null, null,
null]])" />
+ </test-case>
+
+ <test-case sql="SELECT * FROM t_order o LEFT JOIN t_merchant m
USING(merchant_id) WHERE o.user_id = 10 ORDER BY o.order_id, 7">
+ <assertion expected-result="EnumerableSort(sort0=[$1], sort1=[$6],
dir0=[ASC], dir1=[ASC]) EnumerableCalc(expr#0..11=[{inputs}],
expr#12=[COALESCE($t3, $t6)], merchant_id=[$t12], order_id=[$t0],
user_id=[$t1], status=[$t2], remark=[$t4], creation_date=[$t5],
country_id=[$t7], merchant_name=[$t8], business_code=[$t9], telephone=[$t10],
creation_date0=[$t11]) EnumerableHashJoin(condition=[=($3, $6)],
joinType=[left]) TranslatableTableScan(table=[[federate_jdbc, t_order]],
[...]
+ </test-case>
+
+ <test-case sql="SELECT * FROM t_order o RIGHT JOIN t_merchant m
USING(merchant_id) WHERE m.country_id = 1 ORDER BY o.order_id, m.merchant_id,
7">
+ <assertion expected-result="EnumerableSort(sort0=[$1], sort1=[$11],
sort2=[$6], dir0=[ASC], dir1=[ASC], dir2=[ASC])
EnumerableCalc(expr#0..11=[{inputs}], expr#12=[COALESCE($t3, $t6)],
merchant_id=[$t12], order_id=[$t0], user_id=[$t1], status=[$t2], remark=[$t4],
creation_date=[$t5], country_id=[$t7], merchant_name=[$t8],
business_code=[$t9], telephone=[$t10], creation_date0=[$t11],
merchant_id0=[$t6]) EnumerableHashJoin(condition=[=($3, $6)],
joinType=[right]) Translatab [...]
+ </test-case>
+
+ <test-case sql="SELECT * FROM t_order o JOIN t_merchant m
USING(merchant_id) where o.user_id = 10 OR m.country_id = 1 ORDER BY
o.order_id, 7">
+ <assertion expected-result="EnumerableSort(sort0=[$1], sort1=[$6],
dir0=[ASC], dir1=[ASC]) EnumerableCalc(expr#0..13=[{inputs}],
expr#14=[COALESCE($t3, $t7)], merchant_id=[$t14], order_id=[$t0],
user_id=[$t1], status=[$t2], remark=[$t4], creation_date=[$t5],
country_id=[$t8], merchant_name=[$t9], business_code=[$t10], telephone=[$t11],
creation_date0=[$t12]) EnumerableHashJoin(condition=[AND(=($3, $7), OR($6,
$13))], joinType=[inner]) EnumerableCalc(expr#0..5=[{inputs}], [...]
+ </test-case>
+
+ <test-case sql="SELECT * FROM t_product p INNER JOIN t_product_detail d
USING(product_id) WHERE p.product_id > 10 ORDER BY p.product_id DESC">
+ <assertion expected-result="EnumerableSort(sort0=[$9], dir0=[DESC])
EnumerableCalc(expr#0..9=[{inputs}], expr#10=[COALESCE($t0, $t7)],
product_id=[$t10], product_name=[$t1], category_id=[$t2], price=[$t3],
status=[$t4], creation_date=[$t5], detail_id=[$t6], description=[$t8],
creation_date0=[$t9], product_id0=[$t0]) EnumerableHashJoin(condition=[=($0,
$7)], joinType=[inner]) TranslatableTableScan(table=[[federate_jdbc,
t_product]], fields=[[0, 1, 2, 3, 4, 5]], filters=[[ [...]
+ </test-case>
+
+ <test-case sql="SELECT * FROM t_product p INNER JOIN t_product_detail d
USING(product_id) WHERE p.product_id > 10 ORDER BY p.product_id DESC LIMIT 2,
5">
+ <assertion expected-result="EnumerableLimit(offset=[2], fetch=[5])
EnumerableSort(sort0=[$9], dir0=[DESC]) EnumerableCalc(expr#0..9=[{inputs}],
expr#10=[COALESCE($t0, $t7)], product_id=[$t10], product_name=[$t1],
category_id=[$t2], price=[$t3], status=[$t4], creation_date=[$t5],
detail_id=[$t6], description=[$t8], creation_date0=[$t9], product_id0=[$t0])
EnumerableHashJoin(condition=[=($0, $7)], joinType=[inner])
TranslatableTableScan(table=[[federate_jdbc, t_prod [...]
+ </test-case>
+
+ <test-case sql="SELECT * FROM t_order o NATURAL LEFT JOIN t_merchant m
WHERE o.user_id = 10 ORDER BY o.order_id, 7">
+ <assertion expected-result="EnumerableSort(sort0=[$2], sort1=[$6],
dir0=[ASC], dir1=[ASC]) EnumerableCalc(expr#0..11=[{inputs}],
expr#12=[COALESCE($t3, $t6)], expr#13=[COALESCE($t5, $t11)],
merchant_id=[$t12], creation_date=[$t13], order_id=[$t0], user_id=[$t1],
status=[$t2], remark=[$t4], country_id=[$t7], merchant_name=[$t8],
business_code=[$t9], telephone=[$t10])
EnumerableHashJoin(condition=[AND(=($3, $6), =($5, $11))], joinType=[left])
TranslatableTableScan(table=[[ [...]
+ </test-case>
+
+ <test-case sql="SELECT * FROM t_order o NATURAL RIGHT JOIN t_merchant m
WHERE m.country_id = 1 ORDER BY o.order_id, m.merchant_id, 7">
+ <assertion expected-result="EnumerableSort(sort0=[$2], sort1=[$10],
sort2=[$6], dir0=[ASC], dir1=[ASC], dir2=[ASC])
EnumerableCalc(expr#0..11=[{inputs}], expr#12=[COALESCE($t3, $t6)],
expr#13=[COALESCE($t5, $t11)], merchant_id=[$t12], creation_date=[$t13],
order_id=[$t0], user_id=[$t1], status=[$t2], remark=[$t4], country_id=[$t7],
merchant_name=[$t8], business_code=[$t9], telephone=[$t10], merchant_id0=[$t6])
EnumerableHashJoin(condition=[AND(=($3, $6), =($5, $11))], joinTyp [...]
+ </test-case>
+
+ <test-case sql="SELECT * FROM t_order o NATURAL JOIN t_merchant m where
o.user_id = 10 OR m.country_id = 1 ORDER BY o.order_id, 7">
+ <assertion expected-result="EnumerableSort(sort0=[$2], sort1=[$6],
dir0=[ASC], dir1=[ASC]) EnumerableCalc(expr#0..13=[{inputs}],
expr#14=[COALESCE($t3, $t7)], expr#15=[COALESCE($t5, $t12)],
merchant_id=[$t14], creation_date=[$t15], order_id=[$t0], user_id=[$t1],
status=[$t2], remark=[$t4], country_id=[$t8], merchant_name=[$t9],
business_code=[$t10], telephone=[$t11])
EnumerableHashJoin(condition=[AND(=($3, $7), =($5, $12), OR($6, $13))],
joinType=[inner]) EnumerableCalc( [...]
+ </test-case>
+
+ <test-case sql="SELECT * FROM t_product p NATURAL JOIN t_product_detail d
WHERE p.product_id > 10 ORDER BY p.product_id DESC">
+ <assertion expected-result="EnumerableSort(sort0=[$8], dir0=[DESC])
EnumerableCalc(expr#0..9=[{inputs}], expr#10=[COALESCE($t0, $t7)],
expr#11=[COALESCE($t5, $t9)], product_id=[$t10], creation_date=[$t11],
product_name=[$t1], category_id=[$t2], price=[$t3], status=[$t4],
detail_id=[$t6], description=[$t8], product_id0=[$t0])
EnumerableHashJoin(condition=[AND(=($0, $7), =($5, $9))], joinType=[inner])
TranslatableTableScan(table=[[federate_jdbc, t_product]], fields=[[0, 1, [...]
+ </test-case>
+
+ <test-case sql="SELECT * FROM t_product p NATURAL JOIN t_product_detail d
WHERE p.product_id > 10 ORDER BY p.product_id DESC LIMIT 2, 5">
+ <assertion expected-result="EnumerableLimit(offset=[2], fetch=[5])
EnumerableSort(sort0=[$8], dir0=[DESC]) EnumerableCalc(expr#0..9=[{inputs}],
expr#10=[COALESCE($t0, $t7)], expr#11=[COALESCE($t5, $t9)], product_id=[$t10],
creation_date=[$t11], product_name=[$t1], category_id=[$t2], price=[$t3],
status=[$t4], detail_id=[$t6], description=[$t8], product_id0=[$t0])
EnumerableHashJoin(condition=[AND(=($0, $7), =($5, $9))], joinType=[inner])
TranslatableTableScan(tabl [...]
+ </test-case>
+
+ <test-case sql="SELECT MIN(d.detail_id), MIN(p.category_id), p.product_id
FROM t_product p INNER JOIN t_product_detail d ON p.product_id = d.product_id
WHERE p.product_id = 10 GROUP BY p.product_id">
+ <assertion expected-result="EnumerableCalc(expr#0..2=[{inputs}],
EXPR$0=[$t1], EXPR$1=[$t2], product_id=[$t0]) EnumerableAggregate(group=[{0}],
EXPR$0=[MIN($1)], EXPR$1=[MIN($2)]) EnumerableCalc(expr#0..3=[{inputs}],
product_id=[$t0], detail_id=[$t2], category_id=[$t1])
EnumerableHashJoin(condition=[=($0, $3)], joinType=[inner])
TranslatableTableScan(table=[[federate_jdbc, t_product]], fields=[[0, 2]],
filters=[[=(CAST($0):INTEGER, 10), null]]) Translatable [...]
+ </test-case>
+
+ <test-case sql="SELECT * FROM t_product p CROSS JOIN t_product_detail d ON
p.product_id = d.product_id WHERE p.product_id = 10 ORDER BY d.product_id, 7
LIMIT 10, 10">
+ <assertion expected-result="EnumerableLimit(offset=[10], fetch=[10])
EnumerableSort(sort0=[$7], sort1=[$6], dir0=[ASC], dir1=[ASC])
EnumerableHashJoin(condition=[=($0, $7)], joinType=[inner])
TranslatableTableScan(table=[[federate_jdbc, t_product]], fields=[[0, 1, 2, 3,
4, 5]], filters=[[=(CAST($0):INTEGER, 10), null, null, null, null, null]])
TranslatableTableScan(table=[[federate_jdbc, t_product_detail]], fields=[[0, 1,
2, 3]], filters=[[=(CAST($1):INTEGER, 10), n [...]
+ </test-case>
+
+ <test-case sql="SELECT * FROM t_product p LEFT JOIN t_product_detail d ON
d.product_id = p.product_id WHERE p.category_id = 10 ORDER BY p.product_id, 7">
+ <assertion expected-result="EnumerableSort(sort0=[$0], sort1=[$6],
dir0=[ASC], dir1=[ASC]) EnumerableHashJoin(condition=[=($0, $7)],
joinType=[left]) TranslatableTableScan(table=[[federate_jdbc, t_product]],
fields=[[0, 1, 2, 3, 4, 5]], filters=[[=(CAST($2):INTEGER, 10), null, null,
null, null, null]]) TranslatableTableScan(table=[[federate_jdbc,
t_product_detail]], fields=[[0, 1, 2, 3]])" />
+ </test-case>
+
+ <test-case sql="SELECT * FROM t_product p RIGHT JOIN t_product_detail d ON
d.product_id = p.product_id WHERE d.detail_id = 10 ORDER BY d.product_id, 7">
+ <assertion expected-result="EnumerableSort(sort0=[$7], sort1=[$6],
dir0=[ASC], dir1=[ASC]) EnumerableHashJoin(condition=[=($0, $7)],
joinType=[right]) TranslatableTableScan(table=[[federate_jdbc, t_product]],
fields=[[0, 1, 2, 3, 4, 5]]) TranslatableTableScan(table=[[federate_jdbc,
t_product_detail]], fields=[[0, 1, 2, 3]], filters=[[=(CAST($0):INTEGER, 10),
null, null, null]])" />
+ </test-case>
+
+ <test-case sql="SELECT * FROM t_product p JOIN t_product_detail d ON
d.product_id = p.product_id WHERE d.detail_id = 10 OR p.category_id = 10 ORDER
BY d.product_id, 7">
+ <assertion expected-result="EnumerableSort(sort0=[$7], sort1=[$6],
dir0=[ASC], dir1=[ASC]) EnumerableCalc(expr#0..11=[{inputs}],
proj#0..5=[{exprs}], detail_id=[$t7], product_id0=[$t8], description=[$t9],
creation_date0=[$t10]) EnumerableHashJoin(condition=[AND(=($0, $8), OR($11,
$6))], joinType=[inner]) EnumerableCalc(expr#0..5=[{inputs}],
expr#6=[CAST($t2):INTEGER], expr#7=[10], expr#8=[=($t6, $t7)],
proj#0..5=[{exprs}], EXPR$0=[$t8]) TranslatableTableScan(table [...]
+ </test-case>
+
+ <test-case sql="SELECT * FROM t_product p LEFT JOIN t_product_detail d
USING(product_id) WHERE p.category_id = 10 ORDER BY p.product_id, 7">
+ <assertion expected-result="EnumerableSort(sort0=[$9], sort1=[$6],
dir0=[ASC], dir1=[ASC]) EnumerableCalc(expr#0..9=[{inputs}],
expr#10=[COALESCE($t0, $t7)], product_id=[$t10], product_name=[$t1],
category_id=[$t2], price=[$t3], status=[$t4], creation_date=[$t5],
detail_id=[$t6], description=[$t8], creation_date0=[$t9], product_id0=[$t0])
EnumerableHashJoin(condition=[=($0, $7)], joinType=[left])
TranslatableTableScan(table=[[federate_jdbc, t_product]], fields=[[0, 1, 2,
[...]
+ </test-case>
+
+ <test-case sql="SELECT * FROM t_product p RIGHT JOIN t_product_detail d
USING(product_id) WHERE d.detail_id = 10 ORDER BY d.product_id, 7">
+ <assertion expected-result="EnumerableSort(sort0=[$9], sort1=[$6],
dir0=[ASC], dir1=[ASC]) EnumerableCalc(expr#0..9=[{inputs}],
expr#10=[COALESCE($t0, $t7)], product_id=[$t10], product_name=[$t1],
category_id=[$t2], price=[$t3], status=[$t4], creation_date=[$t5],
detail_id=[$t6], description=[$t8], creation_date0=[$t9], product_id0=[$t7])
EnumerableHashJoin(condition=[=($0, $7)], joinType=[right])
TranslatableTableScan(table=[[federate_jdbc, t_product]], fields=[[0, 1, 2 [...]
+ </test-case>
+
+ <test-case sql="SELECT * FROM t_product p JOIN t_product_detail d
USING(product_id) WHERE d.detail_id = 10 OR p.category_id = 10 ORDER BY
d.product_id, 7">
+ <assertion expected-result="EnumerableSort(sort0=[$9], sort1=[$6],
dir0=[ASC], dir1=[ASC]) EnumerableCalc(expr#0..11=[{inputs}],
expr#12=[COALESCE($t0, $t8)], product_id=[$t12], product_name=[$t1],
category_id=[$t2], price=[$t3], status=[$t4], creation_date=[$t5],
detail_id=[$t7], description=[$t9], creation_date0=[$t10], product_id0=[$t8])
EnumerableHashJoin(condition=[AND(=($0, $8), OR($11, $6))], joinType=[inner])
EnumerableCalc(expr#0..5=[{inputs}], expr#6=[CAST($t2) [...]
+ </test-case>
+
+ <test-case sql="SELECT * FROM t_product p NATURAL LEFT JOIN
t_product_detail d WHERE p.category_id = 10 ORDER BY p.product_id, 7">
+ <assertion expected-result="EnumerableSort(sort0=[$8], sort1=[$6],
dir0=[ASC], dir1=[ASC]) EnumerableCalc(expr#0..9=[{inputs}],
expr#10=[COALESCE($t0, $t7)], expr#11=[COALESCE($t5, $t9)], product_id=[$t10],
creation_date=[$t11], product_name=[$t1], category_id=[$t2], price=[$t3],
status=[$t4], detail_id=[$t6], description=[$t8], product_id0=[$t0])
EnumerableHashJoin(condition=[AND(=($0, $7), =($5, $9))], joinType=[left])
TranslatableTableScan(table=[[federate_jdbc, t_pro [...]
+ </test-case>
+
+ <test-case sql="SELECT * FROM t_product p NATURAL RIGHT JOIN
t_product_detail d WHERE d.detail_id = 10 ORDER BY d.product_id, 7">
+ <assertion expected-result="EnumerableSort(sort0=[$8], sort1=[$6],
dir0=[ASC], dir1=[ASC]) EnumerableCalc(expr#0..9=[{inputs}],
expr#10=[COALESCE($t0, $t7)], expr#11=[COALESCE($t5, $t9)], product_id=[$t10],
creation_date=[$t11], product_name=[$t1], category_id=[$t2], price=[$t3],
status=[$t4], detail_id=[$t6], description=[$t8], product_id0=[$t7])
EnumerableHashJoin(condition=[AND(=($0, $7), =($5, $9))], joinType=[right])
TranslatableTableScan(table=[[federate_jdbc, t_pr [...]
+ </test-case>
+
+ <test-case sql="SELECT * FROM t_product p NATURAL JOIN t_product_detail d
WHERE d.detail_id = 10 OR p.category_id = 10 ORDER BY d.product_id, 7">
+ <assertion expected-result="EnumerableSort(sort0=[$8], sort1=[$6],
dir0=[ASC], dir1=[ASC]) EnumerableCalc(expr#0..11=[{inputs}],
expr#12=[COALESCE($t0, $t8)], expr#13=[COALESCE($t5, $t10)], product_id=[$t12],
creation_date=[$t13], product_name=[$t1], category_id=[$t2], price=[$t3],
status=[$t4], detail_id=[$t7], description=[$t9], product_id0=[$t8])
EnumerableHashJoin(condition=[AND(=($0, $8), =($5, $10), OR($11, $6))],
joinType=[inner]) EnumerableCalc(expr#0..5=[{inputs [...]
+ </test-case>
+
+ <test-case sql="SELECT * FROM (SELECT o.* FROM t_order o WHERE o.user_id
IN (10, 11, 12)) AS t, t_order_item i WHERE t.order_id = i.order_id AND
t.order_id > 10 ORDER BY item_id">
+ <assertion expected-result="EnumerableSort(sort0=[$6], dir0=[ASC])
EnumerableHashJoin(condition=[=($0, $7)], joinType=[inner])
TranslatableTableScan(table=[[federate_jdbc, t_order]], fields=[[0, 1, 2, 3, 4,
5]], filters=[[AND(SEARCH($1, Sarg[10, 11, 12]), >($0, 10)), null, null, null,
null, null]]) TranslatableTableScan(table=[[federate_jdbc, t_order_item]],
fields=[[0, 1, 2, 3, 4, 5]], filters=[[>($1, 10), null, null, null, null,
null]])" />
+ </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 = 10) ORDER BY order_id">
+ <assertion expected-result="EnumerableSort(sort0=[$0], dir0=[ASC])
EnumerableCalc(expr#0..6=[{inputs}], proj#0..5=[{exprs}])
EnumerableHashJoin(condition=[=($0, $6)], joinType=[inner])
TranslatableTableScan(table=[[federate_jdbc, t_order]], fields=[[0, 1, 2, 3, 4,
5]]) EnumerableAggregate(group=[{0}])
EnumerableCalc(expr#0..2=[{inputs}], order_id=[$t0])
EnumerableHashJoin(condition=[=($1, $2)], joinType=[inner])
TranslatableTableScan(table [...]
+ </test-case>
+
+ <test-case sql="SELECT MAX(p.price) AS max_price, MIN(p.price) AS
min_price, SUM(p.price) AS sum_price, AVG(p.price) AS avg_price, COUNT(1) AS
count FROM t_order o INNER JOIN t_order_item i ON o.order_id = i.order_id INNER
JOIN t_product p ON i.product_id = p.product_id GROUP BY o.order_id HAVING
SUM(p.price) > 10 ORDER BY max_price">
+ <assertion expected-result="EnumerableSort(sort0=[$0], dir0=[ASC])
EnumerableCalc(expr#0..5=[{inputs}], expr#6=[0], expr#7=[=($t4, $t6)],
expr#8=[null:JavaType(class java.math.BigDecimal)], expr#9=[CASE($t7, $t8,
$t3)], expr#10=[/($t9, $t4)], expr#11=[CAST($t10):JavaType(class
java.math.BigDecimal)], expr#12=[10], expr#13=[>($t9, $t12)], max_price=[$t1],
min_price=[$t2], sum_price=[$t9], avg_price=[$t11], count=[$t5],
$condition=[$t13]) EnumerableAggregate(group=[{0}], max_pr [...]
+ </test-case>
+
+ <test-case sql="SELECT * FROM t_order UNION ALL SELECT * FROM t_order
ORDER BY order_id LIMIT 5, 5">
+ <assertion expected-result="EnumerableLimit(offset=[5], fetch=[5])
EnumerableMergeUnion(all=[true]) EnumerableLimit(fetch=[10])
EnumerableSort(sort0=[$0], dir0=[ASC])
TranslatableTableScan(table=[[federate_jdbc, t_order]], fields=[[0, 1, 2, 3, 4,
5]]) EnumerableLimit(fetch=[10]) EnumerableSort(sort0=[$0], dir0=[ASC])
TranslatableTableScan(table=[[federate_jdbc, t_order]], fields=[[0, 1,
2, 3, 4, 5]])" />
+ </test-case>
</test-cases>
