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 2023/03/31 08:42:51 UTC
[shardingsphere] branch master updated: Modify select join statement e2e (#24907)
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 0a79d5ccb09 Modify select join statement e2e (#24907)
0a79d5ccb09 is described below
commit 0a79d5ccb096f4ccd3d445499677d861262a395c
Author: ZhangCheng <fl...@outlook.com>
AuthorDate: Fri Mar 31 16:42:37 2023 +0800
Modify select join statement e2e (#24907)
---
.../cases/dql/dql-integration-select-join.xml | 190 ++++++++++-----------
.../dql/dql-integration-select-projection.xml | 13 +-
2 files changed, 100 insertions(+), 103 deletions(-)
diff --git a/test/e2e/sql/src/test/resources/cases/dql/dql-integration-select-join.xml b/test/e2e/sql/src/test/resources/cases/dql/dql-integration-select-join.xml
index 04683135d8d..839bea1d485 100644
--- a/test/e2e/sql/src/test/resources/cases/dql/dql-integration-select-join.xml
+++ b/test/e2e/sql/src/test/resources/cases/dql/dql-integration-select-join.xml
@@ -17,35 +17,22 @@
-->
<integration-test-cases>
- <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 (?, ?) AND o.order_id BETWEEN ? AND ? ORDER BY i.item_id" scenario-types="db,tbl,dbtbl_with_readwrite_splitting,readwrite_splitting">
- <assertion parameters="10:int, 11:int, 1000:int, 1909:int" expected-data-source-name="read_dataset" />
- </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" scenario-types="db,tbl,dbtbl_with_readwrite_splitting,readwrite_splitting">
- <assertion expected-data-source-name="read_dataset" />
- </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 JOIN t_broadcast_table c ON o.status = c.status WHERE o.user_id IN (?, ?) AND o.order_id BETWEEN ? AND ? AND o.status = ? ORDER BY i.item_id" scenario-types="db,tbl,dbtbl_with_readwrite_splitting">
- <assertion parameters="10:int, 11:int, 1001:int, 1100:int, init:String" expected-data-source-name="read_dataset" />
- <assertion parameters="10:int, 11:int, 1009:int, 1108:int, none:String" expected-data-source-name="read_dataset" />
+ <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 = ?" db-types="MySQL" scenario-types="db,tbl,dbtbl_with_readwrite_splitting,readwrite_splitting">
+ <assertion parameters="1000:int" expected-data-source-name="read_dataset" />
</test-case>
- <test-case sql="SELECT length.item_id password FROM t_order_item length where length.item_id = ? " db-types="MySQL,H2,SQLServer,Oracle" scenario-types="db,tbl,dbtbl_with_readwrite_splitting,readwrite_splitting">
- <assertion parameters="100001:int" expected-data-source-name="read_dataset" />
+ <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 (?,?)" db-types="MySQL" scenario-types="db,tbl,dbtbl_with_readwrite_splitting,readwrite_splitting">
+ <assertion parameters="1000:int,1001:int" expected-data-source-name="read_dataset" />
</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 WHERE o.order_id = ?" db-types="MySQL" scenario-types="db,tbl,dbtbl_with_readwrite_splitting,readwrite_splitting">
+ <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 = ?" scenario-types="tbl,dbtbl_with_readwrite_splitting,readwrite_splitting">
<assertion parameters="1000:int" expected-data-source-name="read_dataset" />
</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 = ?" db-types="MySQL" scenario-types="db,tbl,dbtbl_with_readwrite_splitting,readwrite_splitting">
+ <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 WHERE o.order_id = ?" db-types="MySQL" scenario-types="db,tbl,dbtbl_with_readwrite_splitting,readwrite_splitting">
<assertion parameters="1000:int" expected-data-source-name="read_dataset" />
</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 (?,?)" db-types="MySQL" scenario-types="db,tbl,dbtbl_with_readwrite_splitting,readwrite_splitting">
- <assertion parameters="1000:int,1001:int" expected-data-source-name="read_dataset" />
- </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 = ? OR o.order_id = ?) AND o.user_id = ?" scenario-types="db,tbl,dbtbl_with_readwrite_splitting,readwrite_splitting">
<assertion parameters="1000:int, 1100:int, 11:int" expected-data-source-name="read_dataset" />
</test-case>
@@ -54,27 +41,35 @@
<assertion parameters="1000:int, 1100:int, 11:int, init:String" expected-data-source-name="read_dataset" />
</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 (?, ?) AND o.order_id BETWEEN ? AND ? ORDER BY i.item_id DESC OFFSET ?" db-types="PostgreSQL" scenario-types="db,tbl,dbtbl_with_readwrite_splitting,readwrite_splitting">
- <assertion parameters="10:int, 19:int, 1000:int, 1909:int, 6:int" expected-data-source-name="read_dataset" />
+ <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" scenario-types="db,tbl,dbtbl_with_readwrite_splitting,readwrite_splitting">
+ <assertion expected-data-source-name="read_dataset" />
</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" db-types="H2,MySQL" scenario-types="db,tbl,dbtbl_with_readwrite_splitting,readwrite_splitting">
<assertion expected-data-source-name="read_dataset" />
</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 (?, ?) AND o.order_id BETWEEN ? AND ? ORDER BY i.item_id" scenario-types="db,tbl,dbtbl_with_readwrite_splitting,readwrite_splitting">
+ <assertion parameters="10:int, 11:int, 1000:int, 1909:int" expected-data-source-name="read_dataset" />
+ </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 (?, ?) AND o.order_id BETWEEN ? AND ? ORDER BY i.item_id DESC LIMIT ?" db-types="MySQL,H2,PostgreSQL" scenario-types="db,tbl,dbtbl_with_readwrite_splitting,readwrite_splitting">
<assertion parameters="10:int, 19:int, 1000:int, 1909:int, 2:int" expected-data-source-name="read_dataset" />
</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 (?, ?) AND o.`order_id` BETWEEN ? AND ? ORDER BY i.item_id DESC LIMIT ?, ?" db-types="MySQL" scenario-types="db,tbl,dbtbl_with_readwrite_splitting,readwrite_splitting">
+ <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 (?, ?) AND o.order_id BETWEEN ? AND ? ORDER BY i.item_id DESC OFFSET ?" db-types="PostgreSQL" scenario-types="db,tbl,dbtbl_with_readwrite_splitting,readwrite_splitting">
+ <assertion parameters="10:int, 19:int, 1000:int, 1909:int, 6:int" expected-data-source-name="read_dataset" />
+ </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 (?, ?) AND o.order_id BETWEEN ? AND ? ORDER BY i.item_id DESC OFFSET ? LIMIT ?" db-types="PostgreSQL" scenario-types="db,tbl,dbtbl_with_readwrite_splitting,readwrite_splitting">
<assertion parameters="10:int, 19:int, 1000:int, 1909:int, 2:int, 2:int" expected-data-source-name="read_dataset" />
</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 (?, ?) AND o.`order_id` BETWEEN ? AND ? ORDER BY i.item_id DESC LIMIT ? OFFSET ?" db-types="MySQL" scenario-types="db,tbl,dbtbl_with_readwrite_splitting,readwrite_splitting">
+ <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 (?, ?) AND o.`order_id` BETWEEN ? AND ? ORDER BY i.item_id DESC LIMIT ?, ?" db-types="MySQL" scenario-types="db,tbl,dbtbl_with_readwrite_splitting,readwrite_splitting">
<assertion parameters="10:int, 19:int, 1000:int, 1909:int, 2:int, 2:int" expected-data-source-name="read_dataset" />
</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 (?, ?) AND o.order_id BETWEEN ? AND ? ORDER BY i.item_id DESC OFFSET ? LIMIT ?" db-types="PostgreSQL" scenario-types="db,tbl,dbtbl_with_readwrite_splitting,readwrite_splitting">
+ <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 (?, ?) AND o.`order_id` BETWEEN ? AND ? ORDER BY i.item_id DESC LIMIT ? OFFSET ?" db-types="MySQL" scenario-types="db,tbl,dbtbl_with_readwrite_splitting,readwrite_splitting">
<assertion parameters="10:int, 19:int, 1000:int, 1909:int, 2:int, 2:int" expected-data-source-name="read_dataset" />
</test-case>
@@ -86,56 +81,41 @@
<assertion parameters="10:int, 19:int, 1000:int, 1909:int, 1:int, 10:int" expected-data-source-name="read_dataset" />
</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 = ?" db-types="MySQL,H2,PostgreSQL" scenario-types="tbl,dbtbl_with_readwrite_splitting,readwrite_splitting">
- <assertion parameters="1000:int" expected-data-source-name="read_dataset" />
- </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 = ?" scenario-types="tbl,dbtbl_with_readwrite_splitting,readwrite_splitting">
- <assertion parameters="1000:int" expected-data-source-name="read_dataset" />
+ <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 JOIN t_broadcast_table c ON o.status = c.status WHERE o.user_id IN (?, ?) AND o.order_id BETWEEN ? AND ? AND o.status = ? ORDER BY i.item_id" scenario-types="db,tbl,dbtbl_with_readwrite_splitting">
+ <assertion parameters="10:int, 11:int, 1001:int, 1100:int, init:String" expected-data-source-name="read_dataset" />
+ <assertion parameters="10:int, 11:int, 1009:int, 1108:int, none:String" expected-data-source-name="read_dataset" />
</test-case>
<test-case sql="SELECT i.* FROM t_order o JOIN t_order_item i USING(order_id) WHERE o.order_id = ?" db-types="MySQL,PostgreSQL" scenario-types="tbl,dbtbl_with_readwrite_splitting,readwrite_splitting">
<assertion parameters="1000:int" expected-data-source-name="read_dataset" />
</test-case>
- <test-case sql="SELECT * FROM t_order o FULL JOIN t_order_item i ON o.order_id = i.order_id WHERE o.user_id = ? OR i.user_id = ? ORDER BY o.order_id, 7" db-types="PostgreSQL,openGauss" scenario-types="db">
- <assertion parameters="10:int, 10:int" expected-data-source-name="read_dataset" />
- </test-case>
-
- <test-case sql="SELECT * FROM t_order o FULL JOIN t_order_item i USING(order_id) WHERE o.user_id = ? OR i.user_id = ? ORDER BY o.order_id, 7" db-types="PostgreSQL,openGauss" scenario-types="db">
- <assertion parameters="10:int, 10:int" expected-data-source-name="read_dataset" />
- </test-case>
-
- <test-case sql="SELECT * FROM t_order o NATURAL FULL JOIN t_order_item i WHERE o.user_id = ? OR i.user_id = ? ORDER BY o.order_id, 7" db-types="PostgreSQL,openGauss" scenario-types="db">
- <assertion parameters="10:int, 10:int" expected-data-source-name="read_dataset" />
- </test-case>
-
- <test-case sql="SELECT * FROM t_order o FULL JOIN t_merchant m ON o.merchant_id = m.merchant_id where o.user_id = ? OR m.country_id = 1 ORDER BY o.order_id, 7" db-types="PostgreSQL,openGauss" scenario-types="db">
+ <test-case sql="SELECT * FROM t_order o NATURAL JOIN t_merchant m WHERE o.user_id = ? ORDER BY o.order_id" db-types="MySQL,PostgreSQL,openGauss" scenario-types="db">
<assertion parameters="10:int" expected-data-source-name="read_dataset" />
</test-case>
- <test-case sql="SELECT * FROM t_order o FULL JOIN t_merchant m USING(merchant_id) where o.user_id = ? OR m.country_id = 1 ORDER BY o.order_id, 7" db-types="PostgreSQL,openGauss" scenario-types="db">
+ <test-case sql="SELECT * FROM t_order o NATURAL JOIN t_order_item i WHERE o.user_id = ? ORDER BY o.order_id, 7" db-types="MySQL,PostgreSQL,openGauss" scenario-types="db">
<assertion parameters="10:int" expected-data-source-name="read_dataset" />
</test-case>
- <test-case sql="SELECT * FROM t_order o NATURAL FULL JOIN t_merchant m where o.user_id = ? OR m.country_id = 1 ORDER BY o.order_id, 7" db-types="PostgreSQL,openGauss" scenario-types="db">
+ <test-case sql="SELECT * FROM t_product p NATURAL JOIN t_product_detail d WHERE p.product_id > ? ORDER BY p.product_id DESC" db-types="MySQL,PostgreSQL,openGauss" scenario-types="db">
<assertion parameters="10:int" expected-data-source-name="read_dataset" />
</test-case>
- <test-case sql="SELECT * FROM t_product p FULL JOIN t_product_detail d ON d.product_id = p.product_id WHERE d.detail_id = ? OR p.category_id = 10 ORDER BY d.product_id, 7" db-types="PostgreSQL,openGauss" scenario-types="db">
- <assertion parameters="10:int" expected-data-source-name="read_dataset" />
+ <test-case sql="SELECT * FROM t_single_table s INNER JOIN t_user o ON s.id = o.user_id" db-types="MySQL,Oracle,SQLServer" scenario-types="encrypt,dbtbl_with_readwrite_splitting_and_encrypt,sharding_and_encrypt,encrypt_and_readwrite_splitting">
+ <assertion expected-data-source-name="read_dataset" />
</test-case>
- <test-case sql="SELECT * FROM t_product p FULL JOIN t_product_detail d USING(product_id) WHERE d.detail_id = ? OR p.category_id = 10 ORDER BY d.product_id, 7" db-types="PostgreSQL,openGauss" scenario-types="db">
- <assertion parameters="10:int" expected-data-source-name="read_dataset" />
+ <test-case sql="SELECT * FROM t_single_table s INNER JOIN t_order o ON s.id = o.order_id" db-types="MySQL,Oracle,SQLServer" scenario-types="db,tbl,dbtbl_with_readwrite_splitting">
+ <assertion expected-data-source-name="read_dataset" />
</test-case>
- <test-case sql="SELECT * FROM t_product p NATURAL FULL JOIN t_product_detail d WHERE d.detail_id = ? OR p.category_id = 10 ORDER BY d.product_id, 7" db-types="PostgreSQL,openGauss" scenario-types="db">
- <assertion parameters="10:int" expected-data-source-name="read_dataset" />
+ <test-case sql="SELECT * FROM t_order o INNER JOIN t_user i ON o.user_id = i.user_id WHERE user_name LIKE '张%'" db-types="MySQL,PostgreSQL,openGauss" scenario-types="db">
+ <assertion expected-data-source-name="read_dataset" />
</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" db-types="MySQL,H2" scenario-types="db,tbl,dbtbl_with_readwrite_splitting,readwrite_splitting">
- <assertion expected-data-source-name="read_dataset" />
+ <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 = ?" db-types="MySQL,H2,PostgreSQL" scenario-types="tbl,dbtbl_with_readwrite_splitting,readwrite_splitting">
+ <assertion parameters="1000:int" expected-data-source-name="read_dataset" />
</test-case>
<!-- TODO add dbtbl_with_readwrite_splitting_and_encrypt,sharding_and_encrypt,encrypt_and_readwrite_splitting scenario when use standard t_user table in issue#21286 -->
@@ -143,143 +123,157 @@
<assertion expected-data-source-name="read_dataset" />
</test-case>
- <test-case sql="SELECT * FROM t_single_table s INNER JOIN t_order o ON s.id = o.order_id" db-types="MySQL,Oracle,SQLServer" scenario-types="db,tbl,dbtbl_with_readwrite_splitting">
+ <test-case sql="SELECT * FROM t_merchant m1 INNER JOIN t_merchant m2 ON m1.merchant_id = m2.merchant_id WHERE m2.business_code LIKE '%18'" db-types="MySQL,PostgreSQL,openGauss" scenario-types="db,encrypt" scenario-comments="Test single table's LIKE operator percentage wildcard in select join statement when use sharding feature.|Test encrypt table's LIKE operator percentage wildcard in select join statement when use encrypt feature.">
<assertion expected-data-source-name="read_dataset" />
</test-case>
- <test-case sql="SELECT * FROM t_single_table s INNER JOIN t_user o ON s.id = o.user_id" db-types="MySQL,Oracle,SQLServer" scenario-types="encrypt,dbtbl_with_readwrite_splitting_and_encrypt,sharding_and_encrypt,encrypt_and_readwrite_splitting">
+ <test-case sql="SELECT * FROM t_merchant m1 INNER JOIN t_merchant m2 ON m1.merchant_id = m2.merchant_id WHERE m2.business_code LIKE '_1000018'" db-types="MySQL,PostgreSQL,openGauss" scenario-types="db,encrypt" scenario-comments="Test single table's LIKE operator underscore wildcard in select join statement when use sharding feature.|Test encrypt table's LIKE operator underscore wildcard in select join statement when use encrypt feature.">
<assertion expected-data-source-name="read_dataset" />
</test-case>
+ <test-case sql="SELECT u1.* FROM t_user u1 INNER JOIN t_user u2 ON u1.user_id = u2.user_id ORDER BY user_id" db-types="MySQL,PostgreSQL,openGauss" scenario-types="mask,mask_encrypt,mask_sharding,mask_encrypt_sharding">
+ <assertion expected-data-source-name="expected_dataset" />
+ </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" db-types="MySQL,PostgreSQL,openGauss" scenario-types="db">
<assertion parameters="10:int" expected-data-source-name="read_dataset" />
</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" db-types="MySQL,PostgreSQL,openGauss" scenario-types="db">
+ <test-case sql="SELECT * FROM t_order o INNER JOIN t_merchant m ON o.merchant_id = m.merchant_id WHERE o.user_id = ? ORDER BY o.order_id" db-types="MySQL,PostgreSQL,openGauss" scenario-types="db">
<assertion parameters="10:int" expected-data-source-name="read_dataset" />
</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" db-types="MySQL,PostgreSQL,openGauss" scenario-types="db">
+ <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 > ? ORDER BY p.product_id DESC" db-types="MySQL,PostgreSQL,openGauss" scenario-types="db">
<assertion parameters="10:int" expected-data-source-name="read_dataset" />
</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 = ? ORDER BY o.order_id, 7" db-types="MySQL,PostgreSQL,openGauss" scenario-types="db">
+ <test-case sql="SELECT * FROM t_order o INNER JOIN t_merchant m USING(merchant_id) WHERE o.user_id = ? ORDER BY o.order_id" db-types="MySQL,PostgreSQL,openGauss" scenario-types="db">
<assertion parameters="10:int" expected-data-source-name="read_dataset" />
</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 = ? ORDER BY i.item_id, 7" db-types="MySQL,PostgreSQL,openGauss" scenario-types="db">
+ <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" db-types="MySQL,PostgreSQL,openGauss" scenario-types="db">
<assertion parameters="10:int" expected-data-source-name="read_dataset" />
</test-case>
- <test-case sql="SELECT * FROM t_order o INNER JOIN t_merchant m ON o.merchant_id = m.merchant_id WHERE o.user_id = ? ORDER BY o.order_id" db-types="MySQL,PostgreSQL,openGauss" scenario-types="db">
+ <test-case sql="SELECT * FROM t_product p INNER JOIN t_product_detail d USING(product_id) WHERE p.product_id > ? ORDER BY p.product_id DESC" db-types="MySQL,PostgreSQL,openGauss" scenario-types="db">
<assertion parameters="10:int" expected-data-source-name="read_dataset" />
</test-case>
- <test-case sql="SELECT * FROM t_order o LEFT JOIN t_order_item i USING(order_id) WHERE o.user_id = ? ORDER BY o.order_id, 7" db-types="MySQL,PostgreSQL,openGauss" scenario-types="db">
- <assertion parameters="10:int" expected-data-source-name="read_dataset" />
+ <test-case sql="SELECT * FROM t_order o FULL JOIN t_order_item i ON o.order_id = i.order_id WHERE o.user_id = ? OR i.user_id = ? ORDER BY o.order_id, 7" db-types="PostgreSQL,openGauss" scenario-types="db">
+ <assertion parameters="10:int, 10:int" expected-data-source-name="read_dataset" />
</test-case>
- <!-- TODO support MySQL using statement when calcite support right join using -->
- <test-case sql="SELECT * FROM t_order o RIGHT JOIN t_order_item i USING(order_id) WHERE i.user_id = ? ORDER BY i.item_id, 7" db-types="PostgreSQL,openGauss" scenario-types="db">
+ <test-case sql="SELECT * FROM t_order o FULL JOIN t_merchant m ON o.merchant_id = m.merchant_id where o.user_id = ? OR m.country_id = 1 ORDER BY o.order_id, 7" db-types="PostgreSQL,openGauss" scenario-types="db">
<assertion parameters="10:int" expected-data-source-name="read_dataset" />
</test-case>
- <test-case sql="SELECT * FROM t_order o INNER JOIN t_merchant m USING(merchant_id) WHERE o.user_id = ? ORDER BY o.order_id" db-types="MySQL,PostgreSQL,openGauss" scenario-types="db">
+ <test-case sql="SELECT * FROM t_product p FULL JOIN t_product_detail d ON d.product_id = p.product_id WHERE d.detail_id = ? OR p.category_id = 10 ORDER BY d.product_id, 7" db-types="PostgreSQL,openGauss" scenario-types="db">
<assertion parameters="10:int" expected-data-source-name="read_dataset" />
</test-case>
- <test-case sql="SELECT * FROM t_order o NATURAL LEFT JOIN t_order_item i WHERE o.user_id = ? ORDER BY o.order_id, 7" db-types="MySQL,PostgreSQL,openGauss" scenario-types="db">
+ <test-case sql="SELECT * FROM t_order o FULL JOIN t_order_item i USING(order_id) WHERE o.user_id = ? OR i.user_id = ? ORDER BY o.order_id, 7" db-types="PostgreSQL,openGauss" scenario-types="db">
+ <assertion parameters="10:int, 10:int" expected-data-source-name="read_dataset" />
+ </test-case>
+
+ <test-case sql="SELECT * FROM t_order o FULL JOIN t_merchant m USING(merchant_id) where o.user_id = ? OR m.country_id = 1 ORDER BY o.order_id, 7" db-types="PostgreSQL,openGauss" scenario-types="db">
<assertion parameters="10:int" expected-data-source-name="read_dataset" />
</test-case>
- <!-- TODO support MySQL natural right join statement when calcite support natural right join -->
- <test-case sql="SELECT * FROM t_order o NATURAL RIGHT JOIN t_order_item i WHERE i.user_id = ? ORDER BY i.item_id, 7" db-types="PostgreSQL,openGauss" scenario-types="db">
+ <test-case sql="SELECT * FROM t_product p FULL JOIN t_product_detail d USING(product_id) WHERE d.detail_id = ? OR p.category_id = 10 ORDER BY d.product_id, 7" db-types="PostgreSQL,openGauss" scenario-types="db">
<assertion parameters="10:int" expected-data-source-name="read_dataset" />
</test-case>
- <test-case sql="SELECT * FROM t_order o NATURAL JOIN t_merchant m WHERE o.user_id = ? ORDER BY o.order_id" db-types="MySQL,PostgreSQL,openGauss" scenario-types="db">
+ <test-case sql="SELECT * FROM t_order o NATURAL FULL JOIN t_order_item i WHERE o.user_id = ? OR i.user_id = ? ORDER BY o.order_id, 7" db-types="PostgreSQL,openGauss" scenario-types="db">
+ <assertion parameters="10:int, 10:int" expected-data-source-name="read_dataset" />
+ </test-case>
+
+ <test-case sql="SELECT * FROM t_order o NATURAL FULL JOIN t_merchant m where o.user_id = ? OR m.country_id = 1 ORDER BY o.order_id, 7" db-types="PostgreSQL,openGauss" scenario-types="db">
<assertion parameters="10:int" expected-data-source-name="read_dataset" />
</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 = ? ORDER BY o.order_id, 7" db-types="MySQL,PostgreSQL,openGauss" scenario-types="db">
+ <test-case sql="SELECT * FROM t_product p NATURAL FULL JOIN t_product_detail d WHERE d.detail_id = ? OR p.category_id = 10 ORDER BY d.product_id, 7" db-types="PostgreSQL,openGauss" scenario-types="db">
<assertion parameters="10:int" expected-data-source-name="read_dataset" />
</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" db-types="MySQL,PostgreSQL,openGauss" scenario-types="db">
+ <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" db-types="MySQL,H2" scenario-types="db,tbl,dbtbl_with_readwrite_splitting,readwrite_splitting">
<assertion expected-data-source-name="read_dataset" />
</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 > ? ORDER BY p.product_id DESC" db-types="MySQL,PostgreSQL,openGauss" scenario-types="db">
+ <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 = ? ORDER BY o.order_id, 7" db-types="MySQL,PostgreSQL,openGauss" scenario-types="db">
<assertion parameters="10:int" expected-data-source-name="read_dataset" />
</test-case>
- <test-case sql="SELECT * FROM t_order o LEFT JOIN t_merchant m USING(merchant_id) WHERE o.user_id = ? ORDER BY o.order_id, 7" db-types="MySQL,PostgreSQL,openGauss" scenario-types="db">
+ <test-case sql="SELECT * FROM t_order o LEFT JOIN t_merchant m ON o.merchant_id = m.merchant_id WHERE o.user_id = ? ORDER BY o.order_id, 7" db-types="MySQL,PostgreSQL,openGauss" scenario-types="db">
<assertion parameters="10:int" expected-data-source-name="read_dataset" />
</test-case>
- <!-- TODO support MySQL using statement when calcite support right join using -->
- <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" db-types="PostgreSQL,openGauss" scenario-types="db">
- <assertion expected-data-source-name="read_dataset" />
+ <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 = ? ORDER BY p.product_id, 7" db-types="MySQL,PostgreSQL,openGauss" scenario-types="db">
+ <assertion parameters="10:int" expected-data-source-name="read_dataset" />
</test-case>
- <test-case sql="SELECT * FROM t_product p INNER JOIN t_product_detail d USING(product_id) WHERE p.product_id > ? ORDER BY p.product_id DESC" db-types="MySQL,PostgreSQL,openGauss" scenario-types="db">
+ <test-case sql="SELECT * FROM t_order o LEFT JOIN t_order_item i USING(order_id) WHERE o.user_id = ? ORDER BY o.order_id, 7" db-types="MySQL,PostgreSQL,openGauss" scenario-types="db">
<assertion parameters="10:int" expected-data-source-name="read_dataset" />
</test-case>
- <test-case sql="SELECT * FROM t_order o NATURAL LEFT JOIN t_merchant m WHERE o.user_id = ? ORDER BY o.order_id, 7" db-types="MySQL,PostgreSQL,openGauss" scenario-types="db">
+ <test-case sql="SELECT * FROM t_order o LEFT JOIN t_merchant m USING(merchant_id) WHERE o.user_id = ? ORDER BY o.order_id, 7" db-types="MySQL,PostgreSQL,openGauss" scenario-types="db">
<assertion parameters="10:int" expected-data-source-name="read_dataset" />
</test-case>
- <!-- TODO support MySQL natural right join statement when calcite support natural right join -->
- <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" db-types="PostgreSQL,openGauss" scenario-types="db">
- <assertion expected-data-source-name="read_dataset" />
+ <test-case sql="SELECT * FROM t_product p LEFT JOIN t_product_detail d USING(product_id) WHERE p.category_id = ? ORDER BY p.product_id, 7" db-types="MySQL,PostgreSQL,openGauss" scenario-types="db">
+ <assertion parameters="10:int" expected-data-source-name="read_dataset" />
</test-case>
- <test-case sql="SELECT * FROM t_product p NATURAL JOIN t_product_detail d WHERE p.product_id > ? ORDER BY p.product_id DESC" db-types="MySQL,PostgreSQL,openGauss" scenario-types="db">
+ <test-case sql="SELECT * FROM t_order o NATURAL LEFT JOIN t_merchant m WHERE o.user_id = ? ORDER BY o.order_id, 7" db-types="MySQL,PostgreSQL,openGauss" scenario-types="db">
<assertion parameters="10:int" expected-data-source-name="read_dataset" />
</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 = ? ORDER BY p.product_id, 7" db-types="MySQL,PostgreSQL,openGauss" scenario-types="db">
+ <test-case sql="SELECT * FROM t_order o NATURAL LEFT JOIN t_order_item i WHERE o.user_id = ? ORDER BY o.order_id, 7" db-types="MySQL,PostgreSQL,openGauss" scenario-types="db">
<assertion parameters="10:int" expected-data-source-name="read_dataset" />
</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 = ? ORDER BY d.product_id, 7" db-types="MySQL,PostgreSQL,openGauss" scenario-types="db">
+ <test-case sql="SELECT * FROM t_product p NATURAL LEFT JOIN t_product_detail d WHERE p.category_id = ? ORDER BY p.product_id, 7" db-types="MySQL,PostgreSQL,openGauss" scenario-types="db">
<assertion parameters="10:int" expected-data-source-name="read_dataset" />
</test-case>
- <test-case sql="SELECT * FROM t_product p LEFT JOIN t_product_detail d USING(product_id) WHERE p.category_id = ? ORDER BY p.product_id, 7" db-types="MySQL,PostgreSQL,openGauss" scenario-types="db">
+ <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 = ? ORDER BY i.item_id, 7" db-types="MySQL,PostgreSQL,openGauss" scenario-types="db">
<assertion parameters="10:int" expected-data-source-name="read_dataset" />
</test-case>
- <!-- TODO support MySQL using statement when calcite support right join using -->
- <test-case sql="SELECT * FROM t_product p RIGHT JOIN t_product_detail d USING(product_id) WHERE d.detail_id = ? ORDER BY d.product_id, 7" db-types="PostgreSQL,openGauss" scenario-types="db">
+ <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 = ? ORDER BY d.product_id, 7" db-types="MySQL,PostgreSQL,openGauss" scenario-types="db">
<assertion parameters="10:int" expected-data-source-name="read_dataset" />
</test-case>
- <test-case sql="SELECT * FROM t_product p NATURAL LEFT JOIN t_product_detail d WHERE p.category_id = ? ORDER BY p.product_id, 7" db-types="MySQL,PostgreSQL,openGauss" scenario-types="db">
+ <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" db-types="MySQL,PostgreSQL,openGauss" scenario-types="db">
+ <assertion expected-data-source-name="read_dataset" />
+ </test-case>
+
+ <!-- TODO support MySQL using statement when calcite support right join using -->
+ <test-case sql="SELECT * FROM t_order o RIGHT JOIN t_order_item i USING(order_id) WHERE i.user_id = ? ORDER BY i.item_id, 7" db-types="PostgreSQL,openGauss" scenario-types="db">
<assertion parameters="10:int" expected-data-source-name="read_dataset" />
</test-case>
- <!-- TODO support MySQL natural right join statement when calcite support natural right join -->
- <test-case sql="SELECT * FROM t_product p NATURAL RIGHT JOIN t_product_detail d WHERE d.detail_id = ? ORDER BY d.product_id, 7" db-types="PostgreSQL,openGauss" scenario-types="db">
+ <!-- TODO support MySQL using statement when calcite support right join using -->
+ <test-case sql="SELECT * FROM t_product p RIGHT JOIN t_product_detail d USING(product_id) WHERE d.detail_id = ? ORDER BY d.product_id, 7" db-types="PostgreSQL,openGauss" scenario-types="db">
<assertion parameters="10:int" expected-data-source-name="read_dataset" />
</test-case>
- <test-case sql="SELECT * FROM t_merchant m1 INNER JOIN t_merchant m2 ON m1.merchant_id = m2.merchant_id WHERE m2.business_code LIKE '%18'" db-types="MySQL,PostgreSQL,openGauss" scenario-types="db,encrypt"
- scenario-comments="Test single table's LIKE operator percentage wildcard in select join statement when use sharding feature.|Test encrypt table's LIKE operator percentage wildcard in select join statement when use encrypt feature.">
+ <!-- TODO support MySQL using statement when calcite support right join using -->
+ <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" db-types="PostgreSQL,openGauss" scenario-types="db">
<assertion expected-data-source-name="read_dataset" />
</test-case>
- <test-case sql="SELECT * FROM t_merchant m1 INNER JOIN t_merchant m2 ON m1.merchant_id = m2.merchant_id WHERE m2.business_code LIKE '_1000018'" db-types="MySQL,PostgreSQL,openGauss" scenario-types="db,encrypt"
- scenario-comments="Test single table's LIKE operator underscore wildcard in select join statement when use sharding feature.|Test encrypt table's LIKE operator underscore wildcard in select join statement when use encrypt feature.">
- <assertion expected-data-source-name="read_dataset" />
+ <!-- TODO support MySQL natural right join statement when calcite support natural right join -->
+ <test-case sql="SELECT * FROM t_order o NATURAL RIGHT JOIN t_order_item i WHERE i.user_id = ? ORDER BY i.item_id, 7" db-types="PostgreSQL,openGauss" scenario-types="db">
+ <assertion parameters="10:int" expected-data-source-name="read_dataset" />
</test-case>
- <test-case sql="SELECT u1.* FROM t_user u1 INNER JOIN t_user u2 ON u1.user_id = u2.user_id ORDER BY user_id" db-types="MySQL,PostgreSQL,openGauss" scenario-types="mask,mask_encrypt,mask_sharding,mask_encrypt_sharding">
- <assertion expected-data-source-name="expected_dataset" />
+ <!-- TODO support MySQL natural right join statement when calcite support natural right join -->
+ <test-case sql="SELECT * FROM t_product p NATURAL RIGHT JOIN t_product_detail d WHERE d.detail_id = ? ORDER BY d.product_id, 7" db-types="PostgreSQL,openGauss" scenario-types="db">
+ <assertion parameters="10:int" expected-data-source-name="read_dataset" />
</test-case>
- <test-case sql="SELECT * FROM t_order o INNER JOIN t_user i ON o.user_id = i.user_id WHERE user_name LIKE '张%'" db-types="MySQL,PostgreSQL,openGauss" scenario-types="db">
+ <!-- TODO support MySQL natural right join statement when calcite support natural right join -->
+ <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" db-types="PostgreSQL,openGauss" scenario-types="db">
<assertion expected-data-source-name="read_dataset" />
</test-case>
</integration-test-cases>
diff --git a/test/e2e/sql/src/test/resources/cases/dql/dql-integration-select-projection.xml b/test/e2e/sql/src/test/resources/cases/dql/dql-integration-select-projection.xml
index cfa7b35e75f..5f564f21b67 100644
--- a/test/e2e/sql/src/test/resources/cases/dql/dql-integration-select-projection.xml
+++ b/test/e2e/sql/src/test/resources/cases/dql/dql-integration-select-projection.xml
@@ -24,7 +24,7 @@
<test-case sql="SELECT t_order.* FROM t_order WHERE user_id = ? AND order_id = ?" db-types="MySQL" scenario-types="db,tbl,dbtbl_with_readwrite_splitting,readwrite_splitting">
<assertion parameters="10:int, 1000:int" expected-data-source-name="read_dataset" />
</test-case>
-
+
<test-case sql="SELECT t_order.* FROM t_order t_order WHERE user_id = ? AND order_id = ?" db-types="MySQL" scenario-types="db,tbl,dbtbl_with_readwrite_splitting,readwrite_splitting">
<assertion parameters="10:int, 1000:int" expected-data-source-name="read_dataset" />
</test-case>
@@ -32,11 +32,15 @@
<test-case sql="SELECT t.* FROM t_order t WHERE user_id = ? AND order_id = ?" db-types="MySQL" scenario-types="db,tbl,dbtbl_with_readwrite_splitting,readwrite_splitting">
<assertion parameters="10:int, 1000:int" expected-data-source-name="read_dataset" />
</test-case>
-
+
<test-case sql="SELECT order_id, user_id, status FROM t_order WHERE user_id = ? AND order_id = ?" db-types="MySQL" scenario-types="db,tbl,dbtbl_with_readwrite_splitting,readwrite_splitting">
<assertion parameters="10:int, 1000:int" expected-data-source-name="read_dataset" />
</test-case>
-
+
+ <test-case sql="SELECT length.item_id password FROM t_order_item length where length.item_id = ?" db-types="MySQL,H2,SQLServer,Oracle" scenario-types="db,tbl,dbtbl_with_readwrite_splitting,readwrite_splitting">
+ <assertion expected-data-source-name="read_dataset" parameters="100001:int" />
+ </test-case>
+
<test-case sql="SELECT USER(), DATABASE()" db-types="MySQL" scenario-types="db,tbl,dbtbl_with_readwrite_splitting,readwrite_splitting">
</test-case>
@@ -64,8 +68,7 @@
<assertion expected-data-source-name="read_dataset" />
</test-case>
- <test-case sql="SELECT item_id, (SELECT order_id FROM t_order WHERE user_id = ? AND order_id = ?) AS order_id FROM t_order_item i WHERE i.user_id = ? AND i.order_id = ?;
-" db-types="MySQL" scenario-types="db">
+ <test-case sql="SELECT item_id, (SELECT order_id FROM t_order WHERE user_id = ? AND order_id = ?) AS order_id FROM t_order_item i WHERE i.user_id = ? AND i.order_id = ?" db-types="MySQL" scenario-types="db">
<assertion parameters="10:int, 1000:long, 10:int, 1000:long" expected-data-source-name="read_dataset" />
</test-case>
</integration-test-cases>