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 <[email protected]>
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>
