You are viewing a plain text version of this content. The canonical link for it is here.
Posted to notifications@shardingsphere.apache.org by zh...@apache.org on 2023/02/14 05:51:50 UTC
[shardingsphere] branch master updated: 1、splitting dql-integration-test-cases.xml file (#24137)
This is an automated email from the ASF dual-hosted git repository.
zhaojinchao 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 392072ab0de 1、splitting dql-integration-test-cases.xml file (#24137)
392072ab0de is described below
commit 392072ab0de5dbf8400a6653834818918d003fbd
Author: gxxiong <xi...@foxmail.com>
AuthorDate: Tue Feb 14 13:51:40 2023 +0800
1、splitting dql-integration-test-cases.xml file (#24137)
2、splitting tcl-integration-test-cases.xml file
---
.../test/e2e/cases/SQLCommandType.java | 6 +-
.../cases/dql/dql-integration-select-aggregate.xml | 136 ++
.../cases/dql/dql-integration-select-combine.xml | 235 ++++
.../dql/dql-integration-select-expression.xml | 119 ++
.../cases/dql/dql-integration-select-group-by.xml | 55 +
.../cases/dql/dql-integration-select-join.xml | 285 ++++
.../dql-integration-select-lock.xml} | 6 +-
.../cases/dql/dql-integration-select-or.xml | 45 +
.../cases/dql/dql-integration-select-order-by.xml | 211 +++
...gration-select-pagination-group-by-order-by.xml | 101 ++
.../dql/dql-integration-select-pagination.xml | 41 +
.../dql-integration-select-special-function.xml | 43 +
.../cases/dql/dql-integration-select-sub-query.xml | 86 ++
.../resources/cases/dql/dql-integration-select.xml | 239 ++++
.../cases/dql/dql-integration-test-cases.xml | 1390 --------------------
...xml => tcl-integration-prepare-transaction.xml} | 2 +
16 files changed, 1606 insertions(+), 1394 deletions(-)
diff --git a/test/e2e/suite/src/test/java/org/apache/shardingsphere/test/e2e/cases/SQLCommandType.java b/test/e2e/suite/src/test/java/org/apache/shardingsphere/test/e2e/cases/SQLCommandType.java
index 8d16e72cd9a..3bedd36330f 100644
--- a/test/e2e/suite/src/test/java/org/apache/shardingsphere/test/e2e/cases/SQLCommandType.java
+++ b/test/e2e/suite/src/test/java/org/apache/shardingsphere/test/e2e/cases/SQLCommandType.java
@@ -46,7 +46,7 @@ public enum SQLCommandType {
*
* <p>Such as {@code SELECT}.</p>
*/
- DQL(SelectStatement.class, "dql-integration-test-cases", false, Arrays.asList("jdbc", "proxy")),
+ DQL(SelectStatement.class, "dql-integration", false, Arrays.asList("jdbc", "proxy")),
/**
* Data Manipulation Language.
@@ -67,7 +67,7 @@ public enum SQLCommandType {
*
* <p>Such as {@code SET}, {@code COMMIT}, {@code ROLLBACK}, {@code SAVEPOIINT}, {@code BEGIN}.</p>
*/
- TCL(TCLStatement.class, "tcl-integration-test-cases", true, Arrays.asList("jdbc", "proxy")),
+ TCL(TCLStatement.class, "tcl-integration", true, Arrays.asList("jdbc", "proxy")),
/**
* Database administrator Language.
@@ -92,7 +92,7 @@ public enum SQLCommandType {
/**
* Resource & Rule Query Language.
*/
- RQL(RQLStatement.class, "rql-integration-test-cases", true, Collections.singletonList("proxy"));
+ RQL(RQLStatement.class, "rql-integration", true, Collections.singletonList("proxy"));
private final Class<? extends SQLStatement> sqlStatementClass;
diff --git a/test/e2e/suite/src/test/resources/cases/dql/dql-integration-select-aggregate.xml b/test/e2e/suite/src/test/resources/cases/dql/dql-integration-select-aggregate.xml
new file mode 100644
index 00000000000..17e433fce14
--- /dev/null
+++ b/test/e2e/suite/src/test/resources/cases/dql/dql-integration-select-aggregate.xml
@@ -0,0 +1,136 @@
+<?xml version="1.0" encoding="UTF-8"?>
+<!--
+ ~ Licensed to the Apache Software Foundation (ASF) under one or more
+ ~ contributor license agreements. See the NOTICE file distributed with
+ ~ this work for additional information regarding copyright ownership.
+ ~ The ASF licenses this file to You under the Apache License, Version 2.0
+ ~ (the "License"); you may not use this file except in compliance with
+ ~ the License. You may obtain a copy of the License at
+ ~
+ ~ http://www.apache.org/licenses/LICENSE-2.0
+ ~
+ ~ Unless required by applicable law or agreed to in writing, software
+ ~ distributed under the License is distributed on an "AS IS" BASIS,
+ ~ WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ ~ See the License for the specific language governing permissions and
+ ~ limitations under the License.
+ -->
+
+<integration-test-cases>
+ <test-case sql="SELECT count(0) as orders_count FROM t_order o WHERE o.status LIKE CONCAT('%%', ?, '%%') AND o.user_id IN (?, ?) AND o.order_id BETWEEN ? AND ?" scenario-types="db,tbl,dbtbl_with_readwrite_splitting,readwrite_splitting">
+ <assertion parameters="init:String, 10:int, 11:int, 1000:int, 2901:int" expected-data-source-name="read_dataset" />
+ </test-case>
+
+ <test-case sql="SELECT count(0) as orders_count FROM t_order o WHERE o.status ~~ CONCAT('%%', ?, '%%') AND o.user_id IN (?, ?) AND o.order_id BETWEEN ? AND ?" db-types="PostgreSQL" scenario-types="db,tbl,dbtbl_with_readwrite_splitting,readwrite_splitting">
+ <assertion parameters="init:String, 10:int, 11:int, 1000:int, 2901:int" expected-data-source-name="read_dataset" />
+ </test-case>
+
+ <test-case sql="SELECT SUM(user_id) AS user_id_sum FROM t_order" scenario-types="db,tbl,dbtbl_with_readwrite_splitting,readwrite_splitting">
+ <assertion expected-data-source-name="read_dataset" />
+ </test-case>
+
+ <test-case sql="SELECT COUNT(*) AS orders_count FROM t_order" scenario-types="db,tbl,dbtbl_with_readwrite_splitting,readwrite_splitting">
+ <assertion expected-data-source-name="read_dataset" />
+ </test-case>
+
+ <test-case sql="SELECT COUNT(*) AS orders_count FROM t_order WHERE order_id > 1-1" scenario-types="db,tbl,dbtbl_with_readwrite_splitting,readwrite_splitting">
+ <assertion expected-data-source-name="read_dataset" />
+ </test-case>
+
+ <test-case sql="SELECT COUNT(*) AS orders_count FROM t_order WHERE order_id > 1 - 1" scenario-types="db,tbl,dbtbl_with_readwrite_splitting,readwrite_splitting">
+ <assertion expected-data-source-name="read_dataset" />
+ </test-case>
+
+ <test-case sql="SELECT MAX(user_id) AS max_user_id FROM t_order" scenario-types="db,tbl,dbtbl_with_readwrite_splitting,readwrite_splitting">
+ <assertion expected-data-source-name="read_dataset" />
+ </test-case>
+
+ <test-case sql="SELECT MIN(user_id) AS min_user_id FROM t_order" scenario-types="db,tbl,dbtbl_with_readwrite_splitting,readwrite_splitting">
+ <assertion expected-data-source-name="read_dataset" />
+ </test-case>
+
+ <!-- FIXME #15593 Expected: is "19", but: was "19.5000" in db scenario -->
+ <test-case sql="SELECT AVG(user_id) AS user_id_avg FROM t_order" scenario-types="db,tbl,dbtbl_with_readwrite_splitting,readwrite_splitting">
+ <assertion expected-data-source-name="read_dataset" />
+ </test-case>
+
+ <test-case sql="SELECT COUNT(*) AS items_count FROM t_order o, t_order_item i WHERE o.user_id = i.user_id AND o.order_id = i.order_id AND o.user_id IN (?, ?) AND o.order_id BETWEEN ? AND ?" 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 COUNT(*) AS items_count FROM t_order o JOIN t_order_item i ON o.user_id = i.user_id AND o.order_id = i.order_id WHERE o.user_id IN (?, ?) AND o.order_id BETWEEN ? AND ?" 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 COUNT(`order_id`) AS orders_count FROM t_order" db-types="MySQL" scenario-types="db,tbl,dbtbl_with_readwrite_splitting,readwrite_splitting">
+ <assertion expected-data-source-name="read_dataset" />
+ </test-case>
+
+ <test-case sql="SELECT SUM(order_id) AS orders_sum, user_id FROM t_order GROUP BY user_id ORDER BY user_id" scenario-types="db,tbl,dbtbl_with_readwrite_splitting,readwrite_splitting">
+ <assertion expected-data-source-name="read_dataset" />
+ </test-case>
+
+ <test-case sql="SELECT COUNT(order_id) AS orders_count, user_id FROM t_order GROUP BY user_id ORDER BY user_id" scenario-types="db,tbl,dbtbl_with_readwrite_splitting,readwrite_splitting">
+ <assertion expected-data-source-name="read_dataset" />
+ </test-case>
+
+ <test-case sql="SELECT MAX(order_id) AS max_order_id, user_id FROM t_order GROUP BY user_id ORDER BY user_id" scenario-types="db,tbl,dbtbl_with_readwrite_splitting,readwrite_splitting">
+ <assertion expected-data-source-name="read_dataset" />
+ </test-case>
+
+ <test-case sql="SELECT MIN(order_id) AS min_order_id, user_id FROM t_order GROUP BY user_id ORDER BY user_id" scenario-types="db,tbl,dbtbl_with_readwrite_splitting,readwrite_splitting">
+ <assertion expected-data-source-name="read_dataset" />
+ </test-case>
+
+ <test-case sql="SELECT AVG(order_id) AS orders_avg, user_id FROM t_order GROUP BY user_id ORDER BY user_id" scenario-types="db,tbl,dbtbl_with_readwrite_splitting,readwrite_splitting">
+ <assertion expected-data-source-name="read_dataset" />
+ </test-case>
+
+ <test-case sql="SELECT SUM(order_id) AS orders_sum, user_id FROM t_order GROUP BY user_id ORDER BY orders_sum DESC" scenario-types="db,tbl,dbtbl_with_readwrite_splitting,readwrite_splitting">
+ <assertion expected-data-source-name="read_dataset" />
+ </test-case>
+
+ <test-case sql="SELECT count(*) as items_count FROM t_order o JOIN t_order_item i ON o.user_id = i.user_id AND o.order_id = i.order_id WHERE o.user_id IN (?, ?) AND o.order_id BETWEEN ? AND ? GROUP BY o.user_id" scenario-types="db,tbl,dbtbl_with_readwrite_splitting,readwrite_splitting">
+ <assertion parameters="10:int, 11:int, 1000:int, 1109:int" expected-data-source-name="read_dataset" />
+ </test-case>
+
+ <test-case sql="SELECT sum(if(status=0, 1, 0)) func_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="12:int, 1000:int" expected-data-source-name="read_dataset" />
+ </test-case>
+
+ <test-case sql="SELECT (SELECT MAX(user_id) FROM t_order_federate_sharding) max_user_id, order_id_sharding, status FROM t_order_federate_sharding WHERE order_id_sharding > ?" db-types="MySQL,PostgreSQL" scenario-types="tbl">
+ <assertion parameters="1100:int" />
+ </test-case>
+
+ <test-case sql="SELECT user_id, SUM(order_id_sharding) FROM t_order_federate_sharding GROUP BY user_id HAVING SUM(order_id_sharding) > ? ORDER BY user_id" db-types="MySQL,PostgreSQL" scenario-types="tbl">
+ <assertion parameters="1000:int" />
+ </test-case>
+
+ <test-case sql="SELECT COUNT(1) FROM t_order WHERE order_id < ?" db-types="PostgreSQL" scenario-types="db,tbl">
+ <assertion parameters="2000:int" />
+ </test-case>
+
+ <test-case sql="SELECT SUM(CRC32(`order_id`)) FROM t_order WHERE order_id = ?" db-types="MySQL" scenario-types="db,tbl">
+ <assertion parameters="1000:int" />
+ </test-case>
+
+ <test-case sql="SELECT AVG(order_id_sharding) AS order_id_sharding_avg FROM (SELECT order_id_sharding, user_id FROM t_order_federate_sharding WHERE order_id_sharding = 1010) AS TEMP" scenario-types="db,dbtbl_with_readwrite_splitting_and_encrypt,sharding_and_encrypt,encrypt_and_readwrite_splitting" db-types="MySQL">
+ <assertion expected-data-source-name="read_dataset" />
+ </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 = ? GROUP BY i.product_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 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 = ? GROUP BY m.merchant_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 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 = ? GROUP BY p.product_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 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) > ? ORDER BY max_price" db-types="MySQL,PostgreSQL,openGauss" scenario-types="db">
+ <assertion parameters="10000:int" expected-data-source-name="read_dataset" />
+ </test-case>
+</integration-test-cases>
diff --git a/test/e2e/suite/src/test/resources/cases/dql/dql-integration-select-combine.xml b/test/e2e/suite/src/test/resources/cases/dql/dql-integration-select-combine.xml
new file mode 100644
index 00000000000..c61fa8f34e3
--- /dev/null
+++ b/test/e2e/suite/src/test/resources/cases/dql/dql-integration-select-combine.xml
@@ -0,0 +1,235 @@
+<?xml version="1.0" encoding="UTF-8"?>
+<!--
+ ~ Licensed to the Apache Software Foundation (ASF) under one or more
+ ~ contributor license agreements. See the NOTICE file distributed with
+ ~ this work for additional information regarding copyright ownership.
+ ~ The ASF licenses this file to You under the Apache License, Version 2.0
+ ~ (the "License"); you may not use this file except in compliance with
+ ~ the License. You may obtain a copy of the License at
+ ~
+ ~ http://www.apache.org/licenses/LICENSE-2.0
+ ~
+ ~ Unless required by applicable law or agreed to in writing, software
+ ~ distributed under the License is distributed on an "AS IS" BASIS,
+ ~ WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ ~ See the License for the specific language governing permissions and
+ ~ limitations under the License.
+ -->
+
+<integration-test-cases>
+ <test-case sql="SELECT order_id_sharding AS order_id, user_id, status FROM t_order_federate_sharding WHERE order_id_sharding = ? UNION ALL SELECT order_id, user_id, status FROM t_order_item_federate_sharding WHERE user_id = ?" scenario-types="db,dbtbl_with_readwrite_splitting,dbtbl_with_readwrite_splitting_and_encrypt,sharding_and_encrypt,encrypt_and_readwrite_splitting,tbl">
+ <assertion parameters="1010:int, 10:int" expected-data-source-name="read_dataset" />
+ </test-case>
+
+ <test-case sql="SELECT * FROM t_order UNION ALL SELECT * FROM t_order ORDER BY order_id LIMIT 5, 5" db-types="MySQL,openGauss" scenario-types="db">
+ <assertion expected-data-source-name="read_dataset" />
+ </test-case>
+
+ <test-case sql="SELECT * FROM t_order WHERE order_id > ? UNION SELECT * FROM t_order WHERE order_id > ? ORDER BY order_id LIMIT 5, 5" db-types="MySQL,openGauss" scenario-types="db">
+ <assertion parameters="2000:long, 1500:long" expected-data-source-name="read_dataset" />
+ </test-case>
+
+ <test-case sql="SELECT o.order_id, o.user_id FROM t_order o WHERE o.order_id > ? UNION ALL SELECT i.order_id, i.user_id FROM t_order_item i WHERE i.order_id > ? ORDER BY order_id LIMIT 5, 5" db-types="MySQL,openGauss" scenario-types="db">
+ <assertion parameters="2500:long, 2500:long" expected-data-source-name="read_dataset" />
+ </test-case>
+
+ <test-case sql="SELECT o.order_id, o.user_id FROM t_order o WHERE o.order_id > ? UNION SELECT i.order_id, i.user_id FROM t_order_item i WHERE i.order_id > ? ORDER BY order_id" db-types="MySQL,PostgreSQL,openGauss" scenario-types="db">
+ <assertion parameters="2500:long, 2500:long" expected-data-source-name="read_dataset" />
+ </test-case>
+
+ <test-case sql="SELECT o.user_id FROM t_order o WHERE o.order_id > ? UNION ALL SELECT u.user_id FROM t_user u ORDER BY user_id LIMIT 5, 5" db-types="MySQL,openGauss" scenario-types="db">
+ <assertion parameters="2500:long" expected-data-source-name="read_dataset" />
+ </test-case>
+
+ <test-case sql="SELECT o.user_id FROM t_order o WHERE o.order_id > ? UNION ALL SELECT u.user_id FROM t_user u ORDER BY user_id FETCH NEXT 3 ROW ONLY" db-types="PostgreSQL,openGauss" scenario-types="db"
+ scenario-comments="Test select union all fetch statement when use sharding feature and federation executor engine.">
+ <assertion parameters="2500:long" expected-data-source-name="read_dataset" />
+ </test-case>
+
+ <test-case sql="SELECT o.user_id FROM t_order o WHERE o.order_id > ? UNION SELECT u.user_id FROM t_user u ORDER BY user_id" db-types="MySQL,PostgreSQL,openGauss" scenario-types="db">
+ <assertion parameters="2500:long" expected-data-source-name="read_dataset" />
+ </test-case>
+
+ <test-case sql="SELECT * FROM t_order WHERE order_id = ? UNION SELECT * FROM t_order INTERSECT SELECT * FROM t_order WHERE order_id = ? ORDER BY order_id" db-types="PostgreSQL,openGauss" scenario-types="db">
+ <assertion parameters="2500:long,2900:long" expected-data-source-name="read_dataset" />
+ </test-case>
+
+ <test-case sql="(SELECT * FROM t_order WHERE order_id = ? UNION SELECT * FROM t_order) INTERSECT SELECT * FROM t_order WHERE order_id = ? ORDER BY order_id" db-types="PostgreSQL,openGauss" scenario-types="db">
+ <assertion parameters="2500:long,2900:long" expected-data-source-name="read_dataset" />
+ </test-case>
+
+ <test-case sql="SELECT * FROM t_order WHERE order_id = ? UNION SELECT * FROM t_order EXCEPT SELECT * FROM t_order WHERE order_id = ? ORDER BY order_id" db-types="PostgreSQL,openGauss" scenario-types="db">
+ <assertion parameters="2500:long,2900:long" expected-data-source-name="read_dataset" />
+ </test-case>
+
+ <test-case sql="SELECT * FROM t_order WHERE order_id = ? UNION (SELECT * FROM t_order EXCEPT SELECT * FROM t_order WHERE order_id = ?) ORDER BY order_id" db-types="PostgreSQL,openGauss" scenario-types="db">
+ <assertion parameters="2500:long,2900:long" expected-data-source-name="read_dataset" />
+ </test-case>
+
+ <test-case sql="SELECT * FROM t_order WHERE order_id = ? UNION SELECT * FROM t_order MINUS SELECT * FROM t_order WHERE order_id = ? ORDER BY order_id" db-types="openGauss" scenario-types="db">
+ <assertion parameters="2500:long,2900:long" expected-data-source-name="read_dataset" />
+ </test-case>
+
+ <test-case sql="SELECT * FROM t_order WHERE order_id = ? UNION (SELECT * FROM t_order MINUS SELECT * FROM t_order WHERE order_id = ?) ORDER BY order_id" db-types="openGauss" scenario-types="db">
+ <assertion parameters="2500:long,2900:long" expected-data-source-name="read_dataset" />
+ </test-case>
+
+ <test-case sql="SELECT * FROM t_order WHERE order_id = ? UNION SELECT * FROM t_order INTERSECT SELECT * FROM t_order WHERE order_id = ? EXCEPT SELECT * FROM t_order WHERE order_id = ? ORDER BY order_id" db-types="PostgreSQL,openGauss" scenario-types="db">
+ <assertion parameters="2500:long,2900:long,1000:long" expected-data-source-name="read_dataset" />
+ </test-case>
+
+ <test-case sql="(SELECT * FROM t_order WHERE order_id = ? UNION SELECT * FROM t_order) INTERSECT (SELECT * FROM t_order WHERE order_id = ? EXCEPT SELECT * FROM t_order WHERE order_id = ?) ORDER BY order_id" db-types="PostgreSQL,openGauss" scenario-types="db">
+ <assertion parameters="2500:long,2900:long,1000:long" expected-data-source-name="read_dataset" />
+ </test-case>
+
+ <test-case sql="SELECT * FROM t_order WHERE order_id = ? UNION SELECT * FROM t_order INTERSECT SELECT * FROM t_order WHERE order_id = ? MINUS SELECT * FROM t_order WHERE order_id = ? ORDER BY order_id" db-types="openGauss" scenario-types="db">
+ <assertion parameters="2500:long,2900:long,1000:long" expected-data-source-name="read_dataset" />
+ </test-case>
+
+ <test-case sql="(SELECT * FROM t_order WHERE order_id = ? UNION SELECT * FROM t_order) INTERSECT (SELECT * FROM t_order WHERE order_id = ? MINUS SELECT * FROM t_order WHERE order_id = ?) ORDER BY order_id" db-types="openGauss" scenario-types="db">
+ <assertion parameters="2500:long,2900:long,1000:long" expected-data-source-name="read_dataset" />
+ </test-case>
+
+ <test-case sql="SELECT * FROM t_order EXCEPT SELECT * FROM t_order WHERE order_id = ? INTERSECT SELECT * FROM t_order WHERE order_id = ? ORDER BY order_id" db-types="PostgreSQL,openGauss" scenario-types="db">
+ <assertion parameters="2500:long,2900:long" expected-data-source-name="read_dataset" />
+ </test-case>
+
+ <test-case sql="(SELECT * FROM t_order EXCEPT SELECT * FROM t_order WHERE order_id = ?) INTERSECT SELECT * FROM t_order WHERE order_id = ? ORDER BY order_id" db-types="PostgreSQL,openGauss" scenario-types="db">
+ <assertion parameters="2500:long,2900:long" expected-data-source-name="read_dataset" />
+ </test-case>
+
+ <test-case sql="SELECT * FROM t_order MINUS SELECT * FROM t_order WHERE order_id = ? INTERSECT SELECT * FROM t_order WHERE order_id = ? ORDER BY order_id" db-types="openGauss" scenario-types="db">
+ <assertion parameters="2500:long,2900:long" expected-data-source-name="read_dataset" />
+ </test-case>
+
+ <test-case sql="(SELECT * FROM t_order MINUS SELECT * FROM t_order WHERE order_id = ?) INTERSECT SELECT * FROM t_order WHERE order_id = ? ORDER BY order_id" db-types="openGauss" scenario-types="db">
+ <assertion parameters="2500:long,2900:long" expected-data-source-name="read_dataset" />
+ </test-case>
+
+ <test-case sql="SELECT * FROM t_order EXCEPT SELECT * FROM t_order WHERE order_id = ? UNION SELECT * FROM t_order WHERE order_id = ? ORDER BY order_id" db-types="PostgreSQL,openGauss" scenario-types="db">
+ <assertion parameters="2500:long,2900:long" expected-data-source-name="read_dataset" />
+ </test-case>
+
+ <test-case sql="SELECT * FROM t_order EXCEPT (SELECT * FROM t_order WHERE order_id = ? UNION SELECT * FROM t_order WHERE order_id = ?) ORDER BY order_id" db-types="PostgreSQL,openGauss" scenario-types="db">
+ <assertion parameters="2500:long,2900:long" expected-data-source-name="read_dataset" />
+ </test-case>
+
+ <test-case sql="SELECT * FROM t_order MINUS SELECT * FROM t_order WHERE order_id = ? UNION SELECT * FROM t_order WHERE order_id = ? ORDER BY order_id" db-types="openGauss" scenario-types="db">
+ <assertion parameters="2500:long,2900:long" expected-data-source-name="read_dataset" />
+ </test-case>
+
+ <test-case sql="SELECT * FROM t_order MINUS (SELECT * FROM t_order WHERE order_id = ? UNION SELECT * FROM t_order WHERE order_id = ?) ORDER BY order_id" db-types="openGauss" scenario-types="db">
+ <assertion parameters="2500:long,2900:long" expected-data-source-name="read_dataset" />
+ </test-case>
+
+ <test-case sql="SELECT * FROM t_order EXCEPT SELECT * FROM t_order INTERSECT SELECT * FROM t_order WHERE order_id = ? UNION SELECT * FROM t_order WHERE order_id = ? ORDER BY order_id" db-types="PostgreSQL,openGauss" scenario-types="db">
+ <assertion parameters="2500:long,2900:long" expected-data-source-name="read_dataset" />
+ </test-case>
+
+ <test-case sql="(SELECT * FROM t_order EXCEPT SELECT * FROM t_order) INTERSECT (SELECT * FROM t_order WHERE order_id = ? UNION SELECT * FROM t_order WHERE order_id = ?) ORDER BY order_id" db-types="PostgreSQL,openGauss" scenario-types="db">
+ <assertion parameters="2500:long,2900:long" expected-data-source-name="read_dataset" />
+ </test-case>
+
+ <test-case sql="SELECT * FROM t_order MINUS SELECT * FROM t_order INTERSECT SELECT * FROM t_order WHERE order_id = ? UNION SELECT * FROM t_order WHERE order_id = ? ORDER BY order_id" db-types="openGauss" scenario-types="db">
+ <assertion parameters="2500:long,2900:long" expected-data-source-name="read_dataset" />
+ </test-case>
+
+ <test-case sql="(SELECT * FROM t_order MINUS SELECT * FROM t_order) INTERSECT (SELECT * FROM t_order WHERE order_id = ? UNION SELECT * FROM t_order WHERE order_id = ?) ORDER BY order_id" db-types="openGauss" scenario-types="db">
+ <assertion parameters="2500:long,2900:long" expected-data-source-name="read_dataset" />
+ </test-case>
+
+ <!-- TODO support MySQL INTERSECT clause -->
+ <test-case sql="SELECT * FROM t_order INTERSECT ALL SELECT * FROM t_order ORDER BY order_id LIMIT 5, 5" db-types="openGauss" scenario-types="db">
+ <assertion expected-data-source-name="read_dataset" />
+ </test-case>
+
+ <test-case sql="SELECT * FROM t_order INTERSECT ALL SELECT * FROM t_order ORDER BY order_id FETCH NEXT 3 ROW ONLY" db-types="PostgreSQL,openGauss" scenario-types="db"
+ scenario-comments="Test select intersect all fetch statement when use sharding feature and federation executor engine.">
+ <assertion expected-data-source-name="read_dataset" />
+ </test-case>
+
+ <test-case sql="SELECT * FROM t_order WHERE order_id > ? INTERSECT SELECT * FROM t_order WHERE order_id > ? ORDER BY order_id" db-types="PostgreSQL,openGauss" scenario-types="db">
+ <assertion parameters="2000:long, 1500:long" expected-data-source-name="read_dataset" />
+ </test-case>
+
+ <test-case sql="SELECT o.order_id, o.user_id FROM t_order o WHERE o.order_id > ? INTERSECT ALL SELECT i.order_id, i.user_id FROM t_order_item i WHERE i.order_id > ? ORDER BY order_id" db-types="PostgreSQL,openGauss" scenario-types="db">
+ <assertion parameters="2500:long, 2500:long" expected-data-source-name="read_dataset" />
+ </test-case>
+
+ <test-case sql="SELECT o.order_id, o.user_id FROM t_order o WHERE o.order_id > ? INTERSECT SELECT i.order_id, i.user_id FROM t_order_item i WHERE i.order_id > ? ORDER BY order_id" db-types="PostgreSQL,openGauss" scenario-types="db">
+ <assertion parameters="2500:long, 2500:long" expected-data-source-name="read_dataset" />
+ </test-case>
+
+ <test-case sql="SELECT o.user_id FROM t_order o WHERE o.order_id > ? INTERSECT ALL SELECT u.user_id FROM t_user u ORDER BY user_id" db-types="PostgreSQL,openGauss" scenario-types="db">
+ <assertion parameters="2500:long" expected-data-source-name="read_dataset" />
+ </test-case>
+
+ <test-case sql="SELECT o.user_id FROM t_order o WHERE o.order_id > ? INTERSECT SELECT u.user_id FROM t_user u ORDER BY user_id" db-types="PostgreSQL,openGauss" scenario-types="db">
+ <assertion parameters="2500:long" expected-data-source-name="read_dataset" />
+ </test-case>
+
+ <test-case sql="select o.order_id_sharding, i.order_id from t_order_federate_sharding o, t_order_item_federate_sharding i where o.order_id_sharding = i.item_id and i.order_id > ?" scenario-types="db,tbl,dbtbl_with_readwrite_splitting,dbtbl_with_readwrite_splitting_and_encrypt,sharding_and_encrypt,encrypt_and_readwrite_splitting">
+ <assertion parameters="10000:int" expected-data-source-name="read_dataset" />
+ </test-case>
+
+ <test-case sql="select o.order_id_sharding, i.order_id from t_order_federate_sharding o, t_order_item_federate_sharding i where o.order_id_sharding = i.item_id and i.order_id > ?" scenario-types="db,tbl,dbtbl_with_readwrite_splitting,dbtbl_with_readwrite_splitting_and_encrypt,sharding_and_encrypt,encrypt_and_readwrite_splitting">
+ <assertion parameters="10000:int" expected-data-source-name="read_dataset" />
+ </test-case>
+
+ <test-case sql="select t_user_encrypt_federate_sharding.user_id, t_user_encrypt_federate_sharding.pwd, t_user_info.information from t_user_encrypt_federate_sharding, t_user_info where t_user_encrypt_federate_sharding.user_id = t_user_info.user_id and t_user_encrypt_federate_sharding.user_id > ? " scenario-types="dbtbl_with_readwrite_splitting_and_encrypt,sharding_and_encrypt,encrypt_and_readwrite_splitting">
+ <assertion parameters="1:int" expected-data-source-name="read_dataset" />
+ </test-case>
+
+ <test-case sql="select t_user_encrypt_federate.user_id, t_user_encrypt_federate.pwd, t_user_info.information from t_user_encrypt_federate, t_user_info where t_user_encrypt_federate.user_id = t_user_info.user_id and t_user_encrypt_federate.user_id > ? " scenario-types="dbtbl_with_readwrite_splitting_and_encrypt,sharding_and_encrypt,encrypt_and_readwrite_splitting">
+ <assertion parameters="1:int" expected-data-source-name="read_dataset" />
+ </test-case>
+
+ <!-- TODO support MySQL EXCEPT clause -->
+ <test-case sql="SELECT * FROM t_order EXCEPT ALL SELECT * FROM t_order ORDER BY order_id" db-types="PostgreSQL,openGauss" scenario-types="db">
+ <assertion expected-data-source-name="read_dataset" />
+ </test-case>
+
+ <test-case sql="SELECT * FROM t_order WHERE order_id > ? EXCEPT SELECT * FROM t_order WHERE order_id > ? ORDER BY order_id" db-types="PostgreSQL,openGauss" scenario-types="db">
+ <assertion parameters="2000:long, 1500:long" expected-data-source-name="read_dataset" />
+ </test-case>
+
+ <test-case sql="SELECT o.order_id, o.user_id FROM t_order o WHERE o.order_id > ? EXCEPT ALL SELECT i.order_id, i.user_id FROM t_order_item i WHERE i.order_id > ? ORDER BY order_id" db-types="PostgreSQL,openGauss" scenario-types="db">
+ <assertion parameters="2500:long, 2500:long" expected-data-source-name="read_dataset" />
+ </test-case>
+
+ <test-case sql="SELECT o.order_id, o.user_id FROM t_order o WHERE o.order_id > ? EXCEPT SELECT i.order_id, i.user_id FROM t_order_item i WHERE i.order_id > ? ORDER BY order_id" db-types="PostgreSQL,openGauss" scenario-types="db">
+ <assertion parameters="2500:long, 2500:long" expected-data-source-name="read_dataset" />
+ </test-case>
+
+ <test-case sql="SELECT o.user_id FROM t_order o WHERE o.order_id > ? EXCEPT ALL SELECT u.user_id FROM t_user u ORDER BY user_id" db-types="PostgreSQL,openGauss" scenario-types="db">
+ <assertion parameters="2500:long" expected-data-source-name="read_dataset" />
+ </test-case>
+
+ <test-case sql="SELECT o.user_id FROM t_order o WHERE o.order_id > ? EXCEPT SELECT u.user_id FROM t_user u ORDER BY user_id" db-types="PostgreSQL,openGauss" scenario-types="db">
+ <assertion parameters="2500:long" expected-data-source-name="read_dataset" />
+ </test-case>
+
+ <test-case sql="SELECT * FROM t_order MINUS ALL SELECT * FROM t_order ORDER BY order_id" db-types="openGauss" scenario-types="db">
+ <assertion expected-data-source-name="read_dataset" />
+ </test-case>
+
+ <test-case sql="SELECT * FROM t_order WHERE order_id > ? MINUS SELECT * FROM t_order WHERE order_id > ? ORDER BY order_id" db-types="openGauss" scenario-types="db">
+ <assertion parameters="2000:long, 1500:long" expected-data-source-name="read_dataset" />
+ </test-case>
+
+ <test-case sql="SELECT o.order_id, o.user_id FROM t_order o WHERE o.order_id > ? MINUS ALL SELECT i.order_id, i.user_id FROM t_order_item i WHERE i.order_id > ? ORDER BY order_id" db-types="openGauss" scenario-types="db">
+ <assertion parameters="2500:long, 2500:long" expected-data-source-name="read_dataset" />
+ </test-case>
+
+ <test-case sql="SELECT o.order_id, o.user_id FROM t_order o WHERE o.order_id > ? MINUS SELECT i.order_id, i.user_id FROM t_order_item i WHERE i.order_id > ? ORDER BY order_id" db-types="openGauss" scenario-types="db">
+ <assertion parameters="2500:long, 2500:long" expected-data-source-name="read_dataset" />
+ </test-case>
+
+ <test-case sql="SELECT o.user_id FROM t_order o WHERE o.order_id > ? MINUS ALL SELECT u.user_id FROM t_user u ORDER BY user_id" db-types="openGauss" scenario-types="db">
+ <assertion parameters="2500:long" expected-data-source-name="read_dataset" />
+ </test-case>
+
+ <test-case sql="SELECT o.user_id FROM t_order o WHERE o.order_id > ? MINUS SELECT u.user_id FROM t_user u ORDER BY user_id" db-types="openGauss" scenario-types="db">
+ <assertion parameters="2500:long" expected-data-source-name="read_dataset" />
+ </test-case>
+</integration-test-cases>
diff --git a/test/e2e/suite/src/test/resources/cases/dql/dql-integration-select-expression.xml b/test/e2e/suite/src/test/resources/cases/dql/dql-integration-select-expression.xml
new file mode 100644
index 00000000000..e77d9918a80
--- /dev/null
+++ b/test/e2e/suite/src/test/resources/cases/dql/dql-integration-select-expression.xml
@@ -0,0 +1,119 @@
+<?xml version="1.0" encoding="UTF-8"?>
+<!--
+ ~ Licensed to the Apache Software Foundation (ASF) under one or more
+ ~ contributor license agreements. See the NOTICE file distributed with
+ ~ this work for additional information regarding copyright ownership.
+ ~ The ASF licenses this file to You under the Apache License, Version 2.0
+ ~ (the "License"); you may not use this file except in compliance with
+ ~ the License. You may obtain a copy of the License at
+ ~
+ ~ http://www.apache.org/licenses/LICENSE-2.0
+ ~
+ ~ Unless required by applicable law or agreed to in writing, software
+ ~ distributed under the License is distributed on an "AS IS" BASIS,
+ ~ WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ ~ See the License for the specific language governing permissions and
+ ~ limitations under the License.
+ -->
+
+<integration-test-cases>
+ <test-case sql="SELECT * FROM t_order o WHERE o.status REGEXP ? AND o.order_id IN (?, ?)" db-types="MySQL" scenario-types="db,tbl,dbtbl_with_readwrite_splitting,readwrite_splitting">
+ <assertion parameters="init:String, 1000:int, 1001:int" expected-data-source-name="read_dataset" />
+ </test-case>
+
+ <test-case sql="SELECT DATE(i.creation_date) AS creation_date FROM `t_order_item` AS i ORDER BY DATE(i.creation_date) DESC" db-types="MySQL" scenario-types="db,tbl,dbtbl_with_readwrite_splitting,readwrite_splitting">
+ <assertion expected-data-source-name="read_dataset" />
+ </test-case>
+
+ <test-case sql="SELECT o.order_id + 1 * 2 as exp FROM t_order AS o ORDER BY o.order_id" scenario-types="db,tbl,dbtbl_with_readwrite_splitting,readwrite_splitting">
+ <assertion expected-data-source-name="read_dataset" />
+ </test-case>
+
+ <test-case sql="select t.*,o.item_id as item_id,(case when t.status = 'init' then '已启用' when t.status = 'failed' then '已停用' end) as stateName
+ from t_order t left join t_order_item as o on o.order_id =t.order_id where t.order_id=1000 limit 1" db-types="MySQL,H2" scenario-types="tbl,readwrite_splitting">
+ <assertion expected-data-source-name="read_dataset" />
+ </test-case>
+
+ <!--<test-case sql="SELECT date_format(creation_date, '%%y-%%m-%%d') as creation_date, count(*) as c_number FROM `t_order_item` WHERE order_id in (?, ?) GROUP BY date_format(creation_date, '%%y-%%m-%%d')" db-types="MySQL" scenario-types="db,tbl,dbtbl_with_readwrite_splitting,readwrite_splitting">-->
+ <!--<assertion sharding-rule-type="dbtbl_with_readwrite_splitting" parameters="1000:int, 1100:int" expected-data-source-name="read_dataset" />-->
+ <!--</test-case>-->
+
+ <!-- // TODO -->
+ <!-- <test-case sql="SELECT DISTINCT item_id FROM t_order_item" scenario-types="db,tbl,dbtbl_with_readwrite_splitting,readwrite_splitting">-->
+ <!-- <assertion expected-data-source-name="read_dataset" />-->
+ <!-- </test-case>-->
+
+ <!-- <test-case sql="SELECT DISTINCT order_id, user_id, status FROM t_order" scenario-types="db,tbl,dbtbl_with_readwrite_splitting,readwrite_splitting">-->
+ <!-- <assertion expected-data-source-name="read_dataset" />-->
+ <!-- </test-case>-->
+
+ <!-- // TODO -->
+ <!--<test-case sql="SELECT DISTINCT t_order.order_id FROM t_order order by t_order.order_id" scenario-types="db,tbl,dbtbl_with_readwrite_splitting,readwrite_splitting">-->
+ <!--<assertion expected-data-source-name="read_dataset" />-->
+ <!--</test-case>-->
+
+ <!--<test-case sql="SELECT DISTINCT t_order.*, t_order_item.order_id FROM t_order, t_order_item WHERE t_order.order_id = t_order_item.order_id" scenario-types="db,tbl,dbtbl_with_readwrite_splitting,readwrite_splitting">-->
+ <!--<assertion expected-data-source-name="read_dataset" />-->
+ <!--</test-case>-->
+
+ <test-case sql="SELECT DISTINCT item_id FROM t_order_item ORDER BY 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 DISTINCT t_order.order_id FROM t_order ORDER BY order_id" scenario-types="db,tbl,dbtbl_with_readwrite_splitting,readwrite_splitting">
+ <assertion expected-data-source-name="read_dataset" />
+ </test-case>
+
+ <test-case sql="SELECT DISTINCT order_id, user_id, status FROM t_order ORDER BY order_id" scenario-types="db,tbl,dbtbl_with_readwrite_splitting,readwrite_splitting">
+ <assertion expected-data-source-name="read_dataset" />
+ </test-case>
+
+ <test-case sql="SELECT DISTINCT * FROM t_order WHERE order_id > 1100 ORDER BY order_id" scenario-types="db,tbl,dbtbl_with_readwrite_splitting,readwrite_splitting">
+ <assertion expected-data-source-name="read_dataset" />
+ </test-case>
+
+ <test-case sql="SELECT DISTINCT t_order.*, t_order_item.order_id FROM t_order, t_order_item WHERE t_order.order_id = t_order_item.order_id ORDER BY t_order.order_id" scenario-types="db,tbl,dbtbl_with_readwrite_splitting,readwrite_splitting">
+ <assertion expected-data-source-name="read_dataset" />
+ </test-case>
+
+ <test-case sql="SELECT SUM(DISTINCT order_id) s FROM t_order WHERE order_id < 1100" scenario-types="db,tbl,dbtbl_with_readwrite_splitting,readwrite_splitting">
+ <assertion expected-data-source-name="read_dataset" />
+ </test-case>
+
+ <test-case sql="SELECT COUNT(DISTINCT order_id) c FROM t_order WHERE order_id < 1100" scenario-types="db,tbl,dbtbl_with_readwrite_splitting,readwrite_splitting">
+ <assertion expected-data-source-name="read_dataset" />
+ </test-case>
+
+ <!-- // TODO -->
+ <!-- <test-case sql="SELECT AVG(DISTINCT order_id) FROM t_order WHERE order_id < 1100" db-types="MySQL" scenario-types="db,tbl,dbtbl_with_readwrite_splitting,readwrite_splitting">-->
+ <!-- <assertion expected-data-source-name="read_dataset" />-->
+ <!-- </test-case>-->
+
+ <test-case sql="SELECT DISTINCT(item_id) FROM t_order_item ORDER BY 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 COUNT(DISTINCT order_id), SUM(DISTINCT order_id) FROM t_order WHERE order_id < 1100" db-types="MySQL" scenario-types="db,tbl,dbtbl_with_readwrite_splitting,readwrite_splitting">
+ <assertion expected-data-source-name="read_dataset" />
+ </test-case>
+
+ <test-case sql="SELECT COUNT(DISTINCT user_id + order_id) c FROM t_order WHERE order_id < 1100" scenario-types="db,tbl,dbtbl_with_readwrite_splitting,readwrite_splitting">
+ <assertion expected-data-source-name="read_dataset" />
+ </test-case>
+
+ <test-case sql="SELECT SUM(DISTINCT user_id), SUM(order_id_sharding) FROM t_order_federate_sharding WHERE order_id_sharding > ?" db-types="MySQL,PostgreSQL" scenario-types="tbl">
+ <assertion parameters="1000:int" />
+ </test-case>
+
+ <test-case sql="SELECT * FROM t_order_item_join_view WHERE order_id > ?" db-types="MySQL,PostgreSQL,openGauss" scenario-types="db">
+ <assertion parameters="1000:long" expected-data-source-name="read_dataset" />
+ </test-case>
+
+ <test-case sql="SELECT n.nspname as "Schema", c.relname as "Name", CASE c.relkind WHEN 'r' THEN 'table' WHEN 'v' THEN 'view' WHEN 'm' THEN 'materialized view' WHEN 'i' THEN 'index' WHEN 'S' THEN 'sequence' WHEN 's' THEN 'special' WHEN 'f' THEN 'foreign table' WHEN 'p' THEN 'partitioned table' WHEN 'I' THEN 'partitioned index' END as "Type", pg_catalog.pg_get_userbyid(c.relowner) as "Owner" FROM pg_catalog.pg_class c LEFT JOIN pg_catalog.pg_name [...]
+ <assertion expected-data-file="select_sys_data_for_pg.xml" />
+ </test-case>
+
+ <test-case sql="SELECT n.nspname as "Schema", c.relname as "Name", CASE c.relkind WHEN 'r' THEN 'table' WHEN 'v' THEN 'view' WHEN 'i' THEN 'index' WHEN 'I' THEN 'global partition index' WHEN 'S' THEN 'sequence' WHEN 'L' THEN 'large sequence' WHEN 'f' THEN 'foreign table' WHEN 'm' THEN 'materialized view' WHEN 'e' THEN 'stream' WHEN 'o' THEN 'contview' END as "Type", pg_catalog.pg_get_userbyid(c.relowner) as "Owner", c.reloptions as "Stora [...]
+ <assertion expected-data-file="select_sys_data_for_og.xml" />
+ </test-case>
+</integration-test-cases>
diff --git a/test/e2e/suite/src/test/resources/cases/dql/dql-integration-select-group-by.xml b/test/e2e/suite/src/test/resources/cases/dql/dql-integration-select-group-by.xml
new file mode 100644
index 00000000000..d0baa27d399
--- /dev/null
+++ b/test/e2e/suite/src/test/resources/cases/dql/dql-integration-select-group-by.xml
@@ -0,0 +1,55 @@
+<?xml version="1.0" encoding="UTF-8"?>
+<!--
+ ~ Licensed to the Apache Software Foundation (ASF) under one or more
+ ~ contributor license agreements. See the NOTICE file distributed with
+ ~ this work for additional information regarding copyright ownership.
+ ~ The ASF licenses this file to You under the Apache License, Version 2.0
+ ~ (the "License"); you may not use this file except in compliance with
+ ~ the License. You may obtain a copy of the License at
+ ~
+ ~ http://www.apache.org/licenses/LICENSE-2.0
+ ~
+ ~ Unless required by applicable law or agreed to in writing, software
+ ~ distributed under the License is distributed on an "AS IS" BASIS,
+ ~ WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ ~ See the License for the specific language governing permissions and
+ ~ limitations under the License.
+ -->
+
+<integration-test-cases>
+ <test-case sql="SELECT user_id FROM t_order GROUP BY user_id ORDER BY user_id LIMIT ?" db-types="H2,MySQL,PostgreSQL" scenario-types="db,tbl,dbtbl_with_readwrite_splitting,readwrite_splitting">
+ <assertion parameters="1:int" expected-data-source-name="read_dataset" />
+ </test-case>
+
+ <test-case sql="SELECT user_id, SUM(order_id) AS orders_sum FROM t_order GROUP BY user_id ORDER BY SUM(order_id) LIMIT ?" db-types="H2,MySQL,PostgreSQL" scenario-types="db,tbl,dbtbl_with_readwrite_splitting,readwrite_splitting">
+ <assertion parameters="1:int" expected-data-source-name="read_dataset" />
+ </test-case>
+
+ <test-case sql="SELECT o.user_id uid FROM t_order o GROUP BY o.user_id ORDER BY o.user_id" db-types="H2,MySQL,SQLServer,PostgreSQL" scenario-types="db,tbl,dbtbl_with_readwrite_splitting,readwrite_splitting">
+ <assertion expected-data-source-name="read_dataset" />
+ </test-case>
+
+ <test-case sql="SELECT SUM(order_id) AS orders_sum, user_id as `key` FROM t_order GROUP BY `key`" db-types="MySQL" scenario-types="db,tbl,dbtbl_with_readwrite_splitting,readwrite_splitting">
+ <assertion expected-data-source-name="read_dataset" />
+ </test-case>
+
+ <test-case sql="SELECT order_id, COUNT(DISTINCT order_id) c FROM t_order WHERE order_id < 1100 GROUP BY order_id ORDER BY order_id" scenario-types="db,tbl,dbtbl_with_readwrite_splitting,readwrite_splitting">
+ <assertion expected-data-source-name="read_dataset" />
+ </test-case>
+
+ <test-case sql="SELECT COUNT(DISTINCT order_id) c, order_id FROM t_order GROUP BY order_id ORDER BY order_id" scenario-types="db,tbl,dbtbl_with_readwrite_splitting,readwrite_splitting">
+ <assertion expected-data-source-name="read_dataset" />
+ </test-case>
+
+ <test-case sql="SELECT COUNT(order_id) AS orders_count, user_id FROM t_order GROUP BY 2 ORDER BY 2" db-types="MySQL,Oracle,SQLServer,PostgreSQL" scenario-types="db,tbl,dbtbl_with_readwrite_splitting,readwrite_splitting" />
+
+ <test-case sql="SELECT country_id, COUNT(1) FROM t_merchant WHERE business_code LIKE '%18' GROUP BY country_id" db-types="MySQL,PostgreSQL,openGauss" scenario-types="db,encrypt"
+ scenario-comments="Test single table's LIKE operator percentage wildcard in select group by statement when use sharding feature.|Test encrypt table's LIKE operator percentage wildcard in select group by statement when use encrypt feature.">
+ <assertion expected-data-source-name="read_dataset" />
+ </test-case>
+
+ <test-case sql="SELECT country_id, COUNT(1) FROM t_merchant WHERE business_code LIKE '_1000018' GROUP BY country_id" db-types="MySQL,PostgreSQL,openGauss" scenario-types="db,encrypt"
+ scenario-comments="Test single table's LIKE operator underscore wildcard in select group by statement when use sharding feature.|Test encrypt table's LIKE operator underscore wildcard in select group by statement when use encrypt feature.">
+ <assertion expected-data-source-name="read_dataset" />
+ </test-case>
+</integration-test-cases>
diff --git a/test/e2e/suite/src/test/resources/cases/dql/dql-integration-select-join.xml b/test/e2e/suite/src/test/resources/cases/dql/dql-integration-select-join.xml
new file mode 100644
index 00000000000..04683135d8d
--- /dev/null
+++ b/test/e2e/suite/src/test/resources/cases/dql/dql-integration-select-join.xml
@@ -0,0 +1,285 @@
+<?xml version="1.0" encoding="UTF-8"?>
+<!--
+ ~ Licensed to the Apache Software Foundation (ASF) under one or more
+ ~ contributor license agreements. See the NOTICE file distributed with
+ ~ this work for additional information regarding copyright ownership.
+ ~ The ASF licenses this file to You under the Apache License, Version 2.0
+ ~ (the "License"); you may not use this file except in compliance with
+ ~ the License. You may obtain a copy of the License at
+ ~
+ ~ http://www.apache.org/licenses/LICENSE-2.0
+ ~
+ ~ Unless required by applicable law or agreed to in writing, software
+ ~ distributed under the License is distributed on an "AS IS" BASIS,
+ ~ WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ ~ See the License for the specific language governing permissions and
+ ~ limitations under the License.
+ -->
+
+<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>
+
+ <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>
+
+ <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 = ?" 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>
+
+ <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.order_id = ? OR o.order_id = ?) AND o.user_id = ? AND o.status = ?" scenario-types="db,tbl,dbtbl_with_readwrite_splitting">
+ <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>
+
+ <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 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">
+ <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">
+ <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">
+ <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.user_id FROM t_order o JOIN t_order_item i ON o.user_id = i.user_id AND o.order_id = i.order_id WHERE o.user_id IN (?, ?) AND o.order_id BETWEEN ? AND ? GROUP BY i.item_id ORDER BY i.item_id DESC LIMIT ?, ?" db-types="MySQL,H2" scenario-types="db,tbl,dbtbl_with_readwrite_splitting,readwrite_splitting">
+ <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.user_id FROM t_order o JOIN t_order_item i ON o.user_id = i.user_id AND o.order_id = i.order_id WHERE o.user_id IN (?, ?) AND o.order_id BETWEEN ? AND ? GROUP BY i.user_id 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, 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>
+
+ <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">
+ <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">
+ <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">
+ <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>
+
+ <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_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 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>
+
+ <!-- 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 -->
+ <test-case sql="SELECT * FROM t_user u INNER JOIN t_user_item m ON u.user_id = m.user_id WHERE u.user_id IN (10, 11)" scenario-types="encrypt">
+ <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">
+ <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">
+ <assertion expected-data-source-name="read_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">
+ <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">
+ <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">
+ <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">
+ <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">
+ <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>
+
+ <!-- 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>
+
+ <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 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>
+
+ <!-- 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 * 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 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>
+
+ <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>
+
+ <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_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 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_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 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>
+
+ <!-- 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>
+
+ <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 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 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 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>
+
+ <!-- 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_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>
+
+ <!-- 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_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_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_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>
+</integration-test-cases>
diff --git a/test/e2e/suite/src/test/resources/cases/tcl/tcl-integration-test-cases.xml b/test/e2e/suite/src/test/resources/cases/dql/dql-integration-select-lock.xml
similarity index 73%
copy from test/e2e/suite/src/test/resources/cases/tcl/tcl-integration-test-cases.xml
copy to test/e2e/suite/src/test/resources/cases/dql/dql-integration-select-lock.xml
index 652341a871f..49bbd133807 100644
--- a/test/e2e/suite/src/test/resources/cases/tcl/tcl-integration-test-cases.xml
+++ b/test/e2e/suite/src/test/resources/cases/dql/dql-integration-select-lock.xml
@@ -1,3 +1,4 @@
+<?xml version="1.0" encoding="UTF-8"?>
<!--
~ Licensed to the Apache Software Foundation (ASF) under one or more
~ contributor license agreements. See the NOTICE file distributed with
@@ -14,6 +15,9 @@
~ See the License for the specific language governing permissions and
~ limitations under the License.
-->
+
<integration-test-cases>
- <test-case sql="PREPARE TRANSACTION 'foo1" db-types="PostgreSQL" />
+ <test-case sql="SELECT * FROM t_order WHERE user_id = ? FOR UPDATE" lock-clause="true" scenario-types="db,tbl,dbtbl_with_readwrite_splitting,readwrite_splitting">
+ <assertion parameters="10:int" expected-data-source-name="write_dataset" />
+ </test-case>
</integration-test-cases>
diff --git a/test/e2e/suite/src/test/resources/cases/dql/dql-integration-select-or.xml b/test/e2e/suite/src/test/resources/cases/dql/dql-integration-select-or.xml
new file mode 100644
index 00000000000..26e5702a46c
--- /dev/null
+++ b/test/e2e/suite/src/test/resources/cases/dql/dql-integration-select-or.xml
@@ -0,0 +1,45 @@
+<?xml version="1.0" encoding="UTF-8"?>
+<!--
+ ~ Licensed to the Apache Software Foundation (ASF) under one or more
+ ~ contributor license agreements. See the NOTICE file distributed with
+ ~ this work for additional information regarding copyright ownership.
+ ~ The ASF licenses this file to You under the Apache License, Version 2.0
+ ~ (the "License"); you may not use this file except in compliance with
+ ~ the License. You may obtain a copy of the License at
+ ~
+ ~ http://www.apache.org/licenses/LICENSE-2.0
+ ~
+ ~ Unless required by applicable law or agreed to in writing, software
+ ~ distributed under the License is distributed on an "AS IS" BASIS,
+ ~ WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ ~ See the License for the specific language governing permissions and
+ ~ limitations under the License.
+ -->
+
+<integration-test-cases>
+ <test-case sql="SELECT * FROM t_order WHERE order_id = ? OR order_id = ?" 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 * FROM t_order WHERE order_id = ? OR user_id = ? ORDER BY order_id" scenario-types="db,tbl,dbtbl_with_readwrite_splitting,readwrite_splitting">
+ <assertion parameters="1000:int, 11:int" expected-data-source-name="read_dataset" />
+ </test-case>
+
+ <!-- TODO FIXED ME PostgreSQL default order by collate -->
+ <test-case sql="SELECT * FROM t_order WHERE order_id = ? OR status = ? ORDER BY order_id" db-types="H2,MySQL,Oracle,SQLServer" scenario-types="db,tbl,dbtbl_with_readwrite_splitting,readwrite_splitting">
+ <assertion parameters="1000:int, init:String" expected-data-source-name="read_dataset" />
+ </test-case>
+
+ <test-case sql="SELECT * FROM t_order WHERE (order_id = ? OR status = ?) AND user_id = ?" scenario-types="db,tbl,dbtbl_with_readwrite_splitting,readwrite_splitting">
+ <assertion parameters="1000:int, init:String, 11:int" expected-data-source-name="read_dataset" />
+ </test-case>
+
+ <test-case sql="SELECT * FROM t_order WHERE ((status = ? AND (order_id = ? OR (order_id = ?)) AND (user_id = ? OR (user_id = ?)))) ORDER BY order_id" scenario-types="db,tbl,dbtbl_with_readwrite_splitting,readwrite_splitting">
+ <assertion parameters="init:String, 1000:int, 1100:int, 10:int, 11: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 -->
+ <test-case sql="SELECT * FROM t_user WHERE password IN ('222222', '333333') OR user_id = 10" scenario-types="encrypt">
+ <assertion expected-data-source-name="read_dataset" />
+ </test-case>
+</integration-test-cases>
diff --git a/test/e2e/suite/src/test/resources/cases/dql/dql-integration-select-order-by.xml b/test/e2e/suite/src/test/resources/cases/dql/dql-integration-select-order-by.xml
new file mode 100644
index 00000000000..8bb35feb76e
--- /dev/null
+++ b/test/e2e/suite/src/test/resources/cases/dql/dql-integration-select-order-by.xml
@@ -0,0 +1,211 @@
+<?xml version="1.0" encoding="UTF-8"?>
+<!--
+ ~ Licensed to the Apache Software Foundation (ASF) under one or more
+ ~ contributor license agreements. See the NOTICE file distributed with
+ ~ this work for additional information regarding copyright ownership.
+ ~ The ASF licenses this file to You under the Apache License, Version 2.0
+ ~ (the "License"); you may not use this file except in compliance with
+ ~ the License. You may obtain a copy of the License at
+ ~
+ ~ http://www.apache.org/licenses/LICENSE-2.0
+ ~
+ ~ Unless required by applicable law or agreed to in writing, software
+ ~ distributed under the License is distributed on an "AS IS" BASIS,
+ ~ WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ ~ See the License for the specific language governing permissions and
+ ~ limitations under the License.
+ -->
+
+<integration-test-cases>
+ <test-case sql="SELECT * FROM t_order_item WHERE item_id <> ? ORDER BY item_id" scenario-types="db,tbl,dbtbl_with_readwrite_splitting,readwrite_splitting">
+ <assertion parameters="100001:int" expected-data-source-name="read_dataset" />
+ </test-case>
+
+ <test-case sql="SELECT * FROM t_order_item WHERE item_id != ? ORDER BY item_id" scenario-types="db,tbl,dbtbl_with_readwrite_splitting,readwrite_splitting">
+ <assertion parameters="100001:int" expected-data-source-name="read_dataset" />
+ </test-case>
+
+ <test-case sql="SELECT * FROM t_order_item WHERE item_id IS NOT NULL AND item_id NOT IN (?, ?) ORDER BY item_id" scenario-types="db,tbl,dbtbl_with_readwrite_splitting,readwrite_splitting">
+ <assertion parameters="100000:int, 100001:int" expected-data-source-name="read_dataset" />
+ </test-case>
+
+ <test-case sql="SELECT * FROM t_order_item WHERE item_id IS NOT NULL AND item_id NOT BETWEEN ? AND ? ORDER BY item_id" scenario-types="db,tbl,dbtbl_with_readwrite_splitting,readwrite_splitting">
+ <assertion parameters="100000:int, 100001:int" expected-data-source-name="read_dataset" />
+ </test-case>
+
+ <test-case sql="SELECT * FROM t_order WHERE order_id IN (?, ?) AND order_id IN (?, ?) ORDER BY order_id" scenario-types="db,tbl,dbtbl_with_readwrite_splitting,readwrite_splitting">
+ <assertion parameters="1000:int, 1001:int, 1001:int, 1100:int" expected-data-source-name="read_dataset" />
+ </test-case>
+
+ <test-case sql="SELECT * FROM t_order WHERE user_id IN (?, ?, ?) AND order_id IN (?, ?) ORDER BY user_id, order_id" scenario-types="db,tbl,dbtbl_with_readwrite_splitting,readwrite_splitting">
+ <assertion parameters="10:int, 12:int, 15:int, 1000:int, 1101:int" expected-data-source-name="read_dataset" />
+ <assertion parameters="10:int, 12:int, 15:int, 1309:int, 1408:int" expected-data-source-name="read_dataset" />
+ </test-case>
+
+ <test-case sql="SELECT * FROM t_order WHERE user_id BETWEEN ? AND ? AND order_id BETWEEN ? AND ? ORDER BY user_id, order_id" scenario-types="db,tbl,dbtbl_with_readwrite_splitting,readwrite_splitting">
+ <assertion parameters="10:int, 12:int, 1009:int, 1108:int" expected-data-source-name="read_dataset" />
+ <assertion parameters="10:int, 12:int, 1309:int, 1408:int" expected-data-source-name="read_dataset" />
+ </test-case>
+
+ <test-case sql="SELECT * FROM t_order WHERE user_id >= ? AND user_id <= ? AND order_id >= ? AND order_id <= ? ORDER BY user_id, order_id" scenario-types="db,tbl,dbtbl_with_readwrite_splitting,readwrite_splitting">
+ <assertion parameters="10:int, 12:int, 1009:int, 1108:int" expected-data-source-name="read_dataset" />
+ <assertion parameters="10:int, 12:int, 1309:int, 1408:int" expected-data-source-name="read_dataset" />
+ </test-case>
+
+ <test-case sql="SELECT * FROM t_order o ORDER BY o.order_id, 2 DESC" 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, t_order_item i WHERE o.order_id = i.order_id AND o.status = 'init' ORDER BY o.order_id DESC, 1" scenario-types="db,tbl,dbtbl_with_readwrite_splitting,readwrite_splitting">
+ <assertion expected-data-source-name="read_dataset" />
+ </test-case>
+
+ <test-case sql="SELECT o.order_id as gen_order_id_ FROM t_order o ORDER BY o.order_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, t_order_item i WHERE o.order_id = i.order_id AND o.status = 'init' ORDER BY i.creation_date DESC, o.order_id DESC, i.item_id" scenario-types="db,tbl,dbtbl_with_readwrite_splitting,readwrite_splitting">
+ <assertion expected-data-source-name="read_dataset" />
+ </test-case>
+
+ <!-- // TODO add null data -->
+ <test-case sql="SELECT o.order_id as gen_order_id_ FROM t_order o ORDER BY o.order_id NULLS FIRST" db-types="Oracle" scenario-types="db,tbl,dbtbl_with_readwrite_splitting,readwrite_splitting">
+ <assertion expected-data-source-name="read_dataset" />
+ </test-case>
+
+ <!-- // TODO add null data -->
+ <test-case sql="SELECT o.order_id as gen_order_id_ FROM t_order o ORDER BY o.order_id ASC NULLS LAST" db-types="Oracle" scenario-types="db,tbl,dbtbl_with_readwrite_splitting,readwrite_splitting">
+ <assertion expected-data-source-name="read_dataset" />
+ </test-case>
+
+ <test-case sql="SELECT *, order_id, o.* FROM t_order o ORDER BY o.order_id" scenario-types="db,tbl,dbtbl_with_readwrite_splitting,readwrite_splitting">
+ <assertion expected-data-source-name="read_dataset" />
+ </test-case>
+
+ <test-case sql="SELECT o.* FROM t_order o ORDER BY o.order_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 o.* FROM t_order o ORDER BY o.order_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 * FROM t_order o ORDER BY order_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 t_order.* FROM t_order ORDER BY t_order.order_id" db-types="H2,MySQL" scenario-types="db,tbl,dbtbl_with_readwrite_splitting,readwrite_splitting">
+ <assertion expected-data-source-name="read_dataset" />
+ </test-case>
+
+ <!--TODO need to add later-->
+ <!--<test-case sql="SELECT * FROM t_order o ORDER BY ?" db-types="H2,MySQL" scenario-types="db,tbl,dbtbl_with_readwrite_splitting,readwrite_splitting">-->
+ <!--<assertion parameters="order_id:String" expected-data-source-name="read_dataset" />-->
+ <!--</test-case>-->
+
+ <test-case sql="SELECT * FROM t_order ORDER BY order_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 * FROM t_order WHERE ROWNUM <= ? ORDER BY order_id" db-types="Oracle" scenario-types="db,tbl,dbtbl_with_readwrite_splitting,readwrite_splitting">
+ <assertion parameters="20:int" expected-data-source-name="read_dataset" />
+ </test-case>
+
+ <test-case sql="SELECT * FROM t_order ORDER BY order_id OFFSET 0 ROW FETCH NEXT ? ROWS ONLY" db-types="SQLServer" scenario-types="db,tbl,dbtbl_with_readwrite_splitting,readwrite_splitting">
+ <assertion parameters="20:int" expected-data-source-name="read_dataset" />
+ </test-case>
+
+ <test-case sql="SELECT * FROM "t_order_item" WHERE "item_id" != ? ORDER BY "item_id"" db-types="PostgreSQL" scenario-types="db,tbl,dbtbl_with_readwrite_splitting,readwrite_splitting">
+ <assertion parameters="100001:int" expected-data-source-name="read_dataset" />
+ </test-case>
+
+ <test-case sql="select t_user_encrypt_federate_sharding.user_id, t_user_encrypt_federate_sharding.pwd, t_user_info.information from t_user_encrypt_federate_sharding, t_user_info where t_user_encrypt_federate_sharding.user_id = t_user_info.user_id order by t_user_encrypt_federate_sharding.user_id" scenario-types="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 t_order_federate.* from t_order_federate, t_order_item_federate_sharding where t_order_federate.order_id = t_order_item_federate_sharding.item_id ORDER BY t_order_item_federate_sharding.user_id" scenario-types="db,dbtbl_with_readwrite_splitting,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 o.item_id, o.order_id, o.creation_date, s.id FROM t_order_item o INNER JOIN t_single_table s ON o.order_id = s.id ORDER BY o.item_id " scenario-types="db,tbl" />
+
+ <test-case sql="SELECT * FROM t_order_subquery_view ORDER BY order_id" db-types="MySQL,PostgreSQL,openGauss" scenario-types="db">
+ <assertion expected-data-source-name="read_dataset" />
+ </test-case>
+
+ <test-case sql="SELECT * FROM t_order_union_view ORDER BY order_id" db-types="MySQL,PostgreSQL,openGauss" scenario-types="db">
+ <assertion expected-data-source-name="read_dataset" />
+ </test-case>
+
+ <test-case sql="SELECT * FROM t_order ORDER BY merchant_id ASC, order_id ASC" db-types="MySQL,PostgreSQL,openGauss,Oracle,SQLServer" scenario-types="db">
+ <assertion expected-data-source-name="read_dataset" />
+ </test-case>
+
+ <test-case sql="SELECT * FROM t_order ORDER BY merchant_id DESC, order_id DESC" db-types="MySQL,PostgreSQL,openGauss,Oracle,SQLServer" scenario-types="db">
+ <assertion expected-data-source-name="read_dataset" />
+ </test-case>
+
+ <test-case sql="SELECT * FROM t_order ORDER BY merchant_id ASC, order_id ASC NULLS FIRST" db-types="PostgreSQL,openGauss,Oracle" scenario-types="db">
+ <assertion expected-data-source-name="read_dataset" />
+ </test-case>
+
+ <test-case sql="SELECT * FROM t_order ORDER BY merchant_id ASC, order_id ASC NULLS LAST" db-types="PostgreSQL,openGauss,Oracle" scenario-types="db">
+ <assertion expected-data-source-name="read_dataset" />
+ </test-case>
+
+ <test-case sql="SELECT * FROM t_order ORDER BY merchant_id DESC, order_id DESC NULLS FIRST" db-types="PostgreSQL,openGauss,Oracle" scenario-types="db">
+ <assertion expected-data-source-name="read_dataset" />
+ </test-case>
+
+ <test-case sql="SELECT * FROM t_order ORDER BY merchant_id DESC, order_id DESC NULLS LAST" db-types="PostgreSQL,openGauss,Oracle" scenario-types="db">
+ <assertion expected-data-source-name="read_dataset" />
+ </test-case>
+
+ <test-case sql="SELECT merchant_id, COUNT(1) AS count FROM t_order GROUP BY merchant_id ORDER BY merchant_id ASC" db-types="MySQL,PostgreSQL,openGauss,Oracle,SQLServer" scenario-types="db">
+ <assertion expected-data-source-name="read_dataset" />
+ </test-case>
+
+ <test-case sql="SELECT merchant_id, COUNT(1) AS count FROM t_order GROUP BY merchant_id ORDER BY merchant_id DESC" db-types="MySQL,PostgreSQL,openGauss,Oracle,SQLServer" scenario-types="db">
+ <assertion expected-data-source-name="read_dataset" />
+ </test-case>
+
+ <test-case sql="SELECT merchant_id, COUNT(1) AS count FROM t_order GROUP BY merchant_id ORDER BY order_id ASC" db-types="MySQL" scenario-types="db">
+ <assertion expected-data-source-name="read_dataset" />
+ </test-case>
+
+ <test-case sql="SELECT merchant_id, COUNT(1) AS count FROM t_order GROUP BY merchant_id ORDER BY order_id DESC" db-types="MySQL" scenario-types="db">
+ <assertion expected-data-source-name="read_dataset" />
+ </test-case>
+
+ <test-case sql="SELECT merchant_id, COUNT(1) AS count FROM t_order GROUP BY merchant_id ORDER BY merchant_id ASC NULLS FIRST" db-types="PostgreSQL,openGauss,Oracle" scenario-types="db">
+ <assertion expected-data-source-name="read_dataset" />
+ </test-case>
+
+ <test-case sql="SELECT merchant_id, COUNT(1) AS count FROM t_order GROUP BY merchant_id ORDER BY merchant_id ASC NULLS LAST" db-types="PostgreSQL,openGauss,Oracle" scenario-types="db">
+ <assertion expected-data-source-name="read_dataset" />
+ </test-case>
+
+ <test-case sql="SELECT merchant_id, COUNT(1) AS count FROM t_order GROUP BY merchant_id ORDER BY merchant_id DESC NULLS FIRST" db-types="PostgreSQL,openGauss,Oracle" scenario-types="db">
+ <assertion expected-data-source-name="read_dataset" />
+ </test-case>
+
+ <test-case sql="SELECT merchant_id, COUNT(1) AS count FROM t_order GROUP BY merchant_id ORDER BY merchant_id DESC NULLS LAST" db-types="PostgreSQL,openGauss,Oracle" scenario-types="db">
+ <assertion expected-data-source-name="read_dataset" />
+ </test-case>
+
+ <test-case sql="SELECT * FROM t_order o INNER JOIN t_order_item i ON o.order_id = i.order_id ORDER BY o.order_id ASC NULLS FIRST, i.item_id DESC" db-types="PostgreSQL,openGauss,Oracle" scenario-types="db">
+ <assertion expected-data-source-name="read_dataset" />
+ </test-case>
+
+ <test-case sql="SELECT * FROM t_order o INNER JOIN t_order_item i ON o.order_id = i.order_id ORDER BY o.order_id ASC NULLS LAST, i.item_id DESC" db-types="PostgreSQL,openGauss,Oracle" scenario-types="db">
+ <assertion expected-data-source-name="read_dataset" />
+ </test-case>
+
+ <test-case sql="SELECT * FROM t_user 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 shardingsphere.sharding_table_statistics order by id;" db-types="MySQL,PostgreSQL,openGauss" scenario-types="db">
+ <assertion expected-data-file="select_sharding_table_statistics.xml" />
+ </test-case>
+</integration-test-cases>
diff --git a/test/e2e/suite/src/test/resources/cases/dql/dql-integration-select-pagination-group-by-order-by.xml b/test/e2e/suite/src/test/resources/cases/dql/dql-integration-select-pagination-group-by-order-by.xml
new file mode 100644
index 00000000000..8638107a18f
--- /dev/null
+++ b/test/e2e/suite/src/test/resources/cases/dql/dql-integration-select-pagination-group-by-order-by.xml
@@ -0,0 +1,101 @@
+<?xml version="1.0" encoding="UTF-8"?>
+<!--
+ ~ Licensed to the Apache Software Foundation (ASF) under one or more
+ ~ contributor license agreements. See the NOTICE file distributed with
+ ~ this work for additional information regarding copyright ownership.
+ ~ The ASF licenses this file to You under the Apache License, Version 2.0
+ ~ (the "License"); you may not use this file except in compliance with
+ ~ the License. You may obtain a copy of the License at
+ ~
+ ~ http://www.apache.org/licenses/LICENSE-2.0
+ ~
+ ~ Unless required by applicable law or agreed to in writing, software
+ ~ distributed under the License is distributed on an "AS IS" BASIS,
+ ~ WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ ~ See the License for the specific language governing permissions and
+ ~ limitations under the License.
+ -->
+
+<integration-test-cases>
+ <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" db-types="MySQL,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 LIMIT 5, 2" db-types="MySQL,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 LIMIT 5, 2" db-types="MySQL,openGauss" scenario-types="db">
+ <assertion parameters="10:int" expected-data-source-name="read_dataset" />
+ </test-case>
+
+ <!-- FIXME: In MySQL, JOIN, CROSS JOIN, and INNER JOIN are syntactic equivalents -->
+ <test-case sql="SELECT * FROM t_order o CROSS JOIN t_order_item i WHERE o.user_id = ? ORDER BY o.order_id, 7 LIMIT 10, 10" db-types="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 LIMIT 5, 2" db-types="MySQL,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 LIMIT 5, 2" db-types="MySQL,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 LIMIT 5, 2" db-types="MySQL,openGauss" scenario-types="db">
+ <assertion parameters="10:int" expected-data-source-name="read_dataset" />
+ </test-case>
+
+ <test-case sql="SELECT * FROM t_order o CROSS JOIN t_merchant m WHERE o.user_id = ? ORDER BY o.order_id, 7 LIMIT 10, 10" db-types="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 ON p.product_id = d.product_id WHERE p.product_id > ? ORDER BY p.product_id DESC LIMIT 2, 5" db-types="MySQL,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 LIMIT 2, 5" db-types="MySQL,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 LIMIT 2, 5" db-types="MySQL,openGauss" scenario-types="db">
+ <assertion parameters="10:int" expected-data-source-name="read_dataset" />
+ </test-case>
+
+ <test-case sql="SELECT * FROM t_product p CROSS JOIN t_product_detail d WHERE p.product_id = ? ORDER BY d.product_id, 7 LIMIT 10, 10" db-types="openGauss" scenario-types="db">
+ <assertion parameters="10:int" expected-data-source-name="read_dataset" />
+ </test-case>
+
+ <test-case sql="SELECT * FROM t_order_subquery_view ORDER BY order_id LIMIT 5, 2" db-types="MySQL,openGauss" scenario-types="db">
+ <assertion expected-data-source-name="read_dataset" />
+ </test-case>
+
+ <test-case sql="SELECT * FROM t_order_union_view ORDER BY order_id LIMIT 5, 2" db-types="MySQL,openGauss" scenario-types="db">
+ <assertion 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 FETCH NEXT 3 ROW ONLY" db-types="PostgreSQL,openGauss" scenario-types="db"
+ scenario-comments="Test select inner join fetch statement when use sharding feature and federation executor engine.">
+ <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 FETCH NEXT 3 ROW ONLY" db-types="openGauss" scenario-types="db"
+ scenario-comments="Test select natural join fetch statement when use sharding feature and federation executor engine.">
+ <assertion parameters="10:int" expected-data-source-name="read_dataset" />
+ </test-case>
+
+ <test-case sql="SELECT * FROM t_product p CROSS JOIN t_product_detail d WHERE p.product_id = ? ORDER BY d.product_id, 7 FETCH NEXT 3 ROW ONLY" db-types="PostgreSQL,openGauss" scenario-types="db"
+ scenario-comments="Test select cross join fetch statement when use sharding feature and federation executor engine.">
+ <assertion parameters="10:int" expected-data-source-name="read_dataset" />
+ </test-case>
+
+ <test-case sql="SELECT * FROM t_order_subquery_view ORDER BY order_id FETCH NEXT 3 ROW ONLY" db-types="PostgreSQL,openGauss" scenario-types="db"
+ scenario-comments="Test select ... from subquery view fetch statement when use sharding feature and federation executor engine.">
+ <assertion expected-data-source-name="read_dataset" />
+ </test-case>
+
+ <test-case sql="SELECT * FROM t_order_union_view ORDER BY order_id FETCH NEXT 3 ROW ONLY" db-types="PostgreSQL,openGauss" scenario-types="db"
+ scenario-comments="Test select ... from union view fetch statement when use sharding feature and federation executor engine.">
+ <assertion expected-data-source-name="read_dataset" />
+ </test-case>
+</integration-test-cases>
diff --git a/test/e2e/suite/src/test/resources/cases/dql/dql-integration-select-pagination.xml b/test/e2e/suite/src/test/resources/cases/dql/dql-integration-select-pagination.xml
new file mode 100644
index 00000000000..100fb771e7c
--- /dev/null
+++ b/test/e2e/suite/src/test/resources/cases/dql/dql-integration-select-pagination.xml
@@ -0,0 +1,41 @@
+<?xml version="1.0" encoding="UTF-8"?>
+<!--
+ ~ Licensed to the Apache Software Foundation (ASF) under one or more
+ ~ contributor license agreements. See the NOTICE file distributed with
+ ~ this work for additional information regarding copyright ownership.
+ ~ The ASF licenses this file to You under the Apache License, Version 2.0
+ ~ (the "License"); you may not use this file except in compliance with
+ ~ the License. You may obtain a copy of the License at
+ ~
+ ~ http://www.apache.org/licenses/LICENSE-2.0
+ ~
+ ~ Unless required by applicable law or agreed to in writing, software
+ ~ distributed under the License is distributed on an "AS IS" BASIS,
+ ~ WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ ~ See the License for the specific language governing permissions and
+ ~ limitations under the License.
+ -->
+
+<integration-test-cases>
+ <test-case sql="SELECT * FROM t_order_item_join_view LIMIT 5, 2" db-types="MySQL,openGauss" scenario-types="db">
+ <assertion expected-data-source-name="read_dataset" />
+ </test-case>
+
+ <test-case sql="SELECT * FROM t_order_aggregation_view LIMIT 5, 2" db-types="MySQL,openGauss" scenario-types="db">
+ <assertion expected-data-source-name="read_dataset" />
+ </test-case>
+
+ <test-case sql="SELECT * FROM t_order_item_join_view FETCH NEXT 3 ROW ONLY" db-types="PostgreSQL,openGauss" scenario-types="db"
+ scenario-comments="Test select ... from join view fetch statement when use sharding feature and federation executor engine.">
+ <assertion expected-data-source-name="read_dataset" />
+ </test-case>
+
+ <test-case sql="SELECT * FROM t_order_aggregation_view FETCH NEXT 3 ROW ONLY" db-types="PostgreSQL,openGauss" scenario-types="db"
+ scenario-comments="Test select ... from aggregation view fetch statement when use sharding feature and federation executor engine.">
+ <assertion expected-data-source-name="read_dataset" />
+ </test-case>
+
+ <test-case sql="SELECT o.*, i.* FROM t_order_federate o, t_order_item_federate i WHERE o.order_id = ? AND i.item_id = ? LIMIT ?" scenario-types="db,dbtbl_with_readwrite_splitting,dbtbl_with_readwrite_splitting_and_encrypt,sharding_and_encrypt,encrypt_and_readwrite_splitting">
+ <assertion parameters="1000:int, 100000:int, 3:int" expected-data-source-name="read_dataset" />
+ </test-case>
+</integration-test-cases>
diff --git a/test/e2e/suite/src/test/resources/cases/dql/dql-integration-select-special-function.xml b/test/e2e/suite/src/test/resources/cases/dql/dql-integration-select-special-function.xml
new file mode 100644
index 00000000000..384df90933e
--- /dev/null
+++ b/test/e2e/suite/src/test/resources/cases/dql/dql-integration-select-special-function.xml
@@ -0,0 +1,43 @@
+<?xml version="1.0" encoding="UTF-8"?>
+<!--
+ ~ Licensed to the Apache Software Foundation (ASF) under one or more
+ ~ contributor license agreements. See the NOTICE file distributed with
+ ~ this work for additional information regarding copyright ownership.
+ ~ The ASF licenses this file to You under the Apache License, Version 2.0
+ ~ (the "License"); you may not use this file except in compliance with
+ ~ the License. You may obtain a copy of the License at
+ ~
+ ~ http://www.apache.org/licenses/LICENSE-2.0
+ ~
+ ~ Unless required by applicable law or agreed to in writing, software
+ ~ distributed under the License is distributed on an "AS IS" BASIS,
+ ~ WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ ~ See the License for the specific language governing permissions and
+ ~ limitations under the License.
+ -->
+
+<integration-test-cases>
+ <test-case sql="SELECT GROUP_CONCAT(i.item_id SEPARATOR ';') AS item_ids FROM t_order_federate o INNER JOIN t_order_item_federate_sharding i ON o.order_id = i.item_id WHERE i.order_id >= ?" db-types="MySQL" scenario-types="dbtbl_with_readwrite_splitting,dbtbl_with_readwrite_splitting_and_encrypt,sharding_and_encrypt,encrypt_and_readwrite_splitting">
+ <assertion parameters="10000:int" expected-data-source-name="read_dataset" />
+ </test-case>
+
+ <!-- TODO Fix https://github.com/apache/shardingsphere/issues/23499 -->
+ <!-- <test-case sql="SELECT ?::money" db-types="PostgreSQL,openGauss" scenario-types="passthrough"-->
+ <!-- scenario-comments="Test ShardingSphere-Proxy compatibility for PostgreSQL/openGauss money type: type cast to money">-->
+ <!-- <assertion parameters="-92233720368547758.08:String" expected-data-source-name="expected_dataset" />-->
+ <!-- <assertion parameters="92233720368547758.07:String" expected-data-source-name="expected_dataset" />-->
+ <!-- </test-case>-->
+
+ <test-case sql="SELECT ?::money::varchar" db-types="PostgreSQL,openGauss" scenario-types="passthrough"
+ scenario-comments="Test ShardingSphere-Proxy compatibility for PostgreSQL/openGauss money type: type cast money to varchar">
+ <assertion parameters="-92233720368547758.08:String" expected-data-source-name="expected_dataset" />
+ <assertion parameters="-$92233720368547758.08:String" expected-data-source-name="expected_dataset" />
+ <assertion parameters="92233720368547758.07:String" expected-data-source-name="expected_dataset" />
+ <assertion parameters="$92233720368547758.07:String" expected-data-source-name="expected_dataset" />
+ </test-case>
+
+ <test-case sql="INSERT INTO t_with_generated_id (val) values (?) RETURNING *, id, val aliased_val, t_with_generated_id" db-types="PostgreSQL,openGauss" scenario-types="passthrough"
+ scenario-comments="Test ShardingSphere-Proxy compatibility for PostgreSQL/openGauss insert returning clause">
+ <assertion parameters="foo:String" expected-data-source-name="expected_dataset" />
+ </test-case>
+</integration-test-cases>
diff --git a/test/e2e/suite/src/test/resources/cases/dql/dql-integration-select-sub-query.xml b/test/e2e/suite/src/test/resources/cases/dql/dql-integration-select-sub-query.xml
new file mode 100644
index 00000000000..eb3b7e6a721
--- /dev/null
+++ b/test/e2e/suite/src/test/resources/cases/dql/dql-integration-select-sub-query.xml
@@ -0,0 +1,86 @@
+<?xml version="1.0" encoding="UTF-8"?>
+<!--
+ ~ Licensed to the Apache Software Foundation (ASF) under one or more
+ ~ contributor license agreements. See the NOTICE file distributed with
+ ~ this work for additional information regarding copyright ownership.
+ ~ The ASF licenses this file to You under the Apache License, Version 2.0
+ ~ (the "License"); you may not use this file except in compliance with
+ ~ the License. You may obtain a copy of the License at
+ ~
+ ~ http://www.apache.org/licenses/LICENSE-2.0
+ ~
+ ~ Unless required by applicable law or agreed to in writing, software
+ ~ distributed under the License is distributed on an "AS IS" BASIS,
+ ~ WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ ~ See the License for the specific language governing permissions and
+ ~ limitations under the License.
+ -->
+
+<integration-test-cases>
+ <test-case sql="SELECT * FROM (SELECT TOP (?) row_number() OVER (ORDER BY i.item_id DESC) AS rownum_, i.item_id, o.order_id as order_id, o.status as status, o.user_id as user_id FROM t_order o JOIN t_order_item i ON o.user_id = i.user_id AND o.order_id = i.order_id WHERE o.user_id IN (?, ?) AND o.order_id BETWEEN ? AND ?) AS row_" db-types="SQLServer" scenario-types="db,tbl,dbtbl_with_readwrite_splitting,readwrite_splitting">
+ <assertion parameters="2:int, 10:int, 19:int, 1000:int, 1909:int" expected-data-source-name="read_dataset" />
+ </test-case>
+
+ <test-case sql="SELECT * FROM (SELECT row_.*, rownum rownum_ FROM (SELECT order0_.order_id as order_id, order0_.status as status, order0_.user_id as user_id FROM t_order order0_ JOIN t_order_item i ON order0_.user_id = i.user_id AND order0_.order_id = i.order_id WHERE order0_.user_id IN (?, ?) AND order0_.order_id BETWEEN ? AND ? ORDER BY i.item_id DESC) row_ WHERE rownum <= ?)" db-types="Oracle" 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 * FROM (SELECT TOP (?) row_number() OVER (ORDER BY i.item_id DESC) AS rownum_, i.item_id, o.order_id as order_id, o.status as status, o.user_id as user_id FROM t_order o JOIN t_order_item i ON o.user_id = i.user_id AND o.order_id = i.order_id WHERE o.user_id IN (?, ?) AND o.order_id BETWEEN ? AND ?) AS row_ WHERE row_.rownum_ > ?" db-types="SQLServer" scenario-types="db,tbl,dbtbl_with_readwrite_splitting,readwrite_splitting">
+ <assertion parameters="4:int, 10:int, 19:int, 1000:int, 1909:int, 2:int" expected-data-source-name="read_dataset" />
+ </test-case>
+
+ <test-case sql="SELECT * FROM (SELECT TOP (?) row_number() OVER (ORDER BY i.item_id DESC) AS rownum_, i.item_id, o.order_id as order_id, o.status as status, o.user_id as user_id FROM t_order o JOIN t_order_item i ON o.user_id = i.user_id AND o.order_id = i.order_id WHERE o.user_id IN (?, ?) AND o.order_id BETWEEN ? AND ?) AS row_ WHERE row_.rownum_ >= ?" db-types="SQLServer" scenario-types="db,tbl,dbtbl_with_readwrite_splitting,readwrite_splitting">
+ <assertion parameters="4:int, 10:int, 19:int, 1000:int, 1909:int, 3:int" expected-data-source-name="read_dataset" />
+ </test-case>
+
+ <test-case sql="SELECT * FROM (SELECT row_.*, rownum rownum_ FROM (SELECT order0_.order_id as order_id, order0_.status as status, order0_.user_id as user_id FROM t_order order0_ JOIN t_order_item i ON order0_.user_id = i.user_id AND order0_.order_id = i.order_id WHERE order0_.user_id IN (?, ?) AND order0_.order_id BETWEEN ? AND ? ORDER BY i.item_id DESC) row_ WHERE rownum <= ?) t WHERE t.rownum_ > ?" db-types="Oracle" scenario-types="db,tbl,dbtbl_with_readwrite_splitting,readwr [...]
+ <assertion parameters="10:int, 19:int, 1000:int, 1909:int, 4:int, 2:int" expected-data-source-name="read_dataset" />
+ </test-case>
+
+ <test-case sql="SELECT * FROM (SELECT row_.*, rownum rownum_ FROM (SELECT order0_.order_id as order_id, order0_.status as status, order0_.user_id as user_id FROM t_order order0_ JOIN t_order_item i ON order0_.user_id = i.user_id AND order0_.order_id = i.order_id WHERE order0_.user_id IN (?, ?) AND order0_.order_id BETWEEN ? AND ? ORDER BY i.item_id DESC) row_ WHERE rownum <= ?) t WHERE t.rownum_ >= ?" db-types="Oracle" scenario-types="db,tbl,dbtbl_with_readwrite_splitting,readw [...]
+ <assertion parameters="10:int, 19:int, 1000:int, 1909:int, 4:int, 3:int" expected-data-source-name="read_dataset" />
+ </test-case>
+
+ <test-case sql="SELECT * FROM (SELECT order_id_sharding, user_id FROM t_order_federate_sharding WHERE order_id_sharding = 1010) AS TEMP" scenario-types="db,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 o.order_id, o.user_id, o.status, o.merchant_id, (SELECT t.merchant_name FROM t_merchant t WHERE t.merchant_id = o.merchant_id) AS merchant_name FROM t_order o WHERE o.order_id = ?" db-types="MySQL,PostgreSQL,openGauss" scenario-types="db">
+ <assertion parameters="1000:long" expected-data-source-name="read_dataset" />
+ </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 > ? ORDER BY item_id" db-types="MySQL,PostgreSQL,openGauss" scenario-types="db">
+ <assertion parameters="1200:long" expected-data-source-name="read_dataset" />
+ </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 = ?) ORDER BY 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 (SELECT count(*) as count, id from t_single_table group by id) as temp_table" db-types="MySQL" scenario-types="db">
+ <assertion expected-data-source-name="read_dataset" />
+ </test-case>
+
+ <test-case sql="SELECT * FROM t_merchant WHERE merchant_id IN (SELECT merchant_id FROM t_merchant WHERE business_code LIKE '%18')" db-types="MySQL,PostgreSQL,openGauss" scenario-types="db,encrypt"
+ scenario-comments="Test single table's LIKE operator percentage wildcard in subquery select statement when use sharding feature.|Test encrypt table's LIKE operator percentage wildcard in subquery select statement when use encrypt feature.">
+ <assertion expected-data-source-name="read_dataset" />
+ </test-case>
+
+ <test-case sql="SELECT * FROM t_merchant WHERE merchant_id IN (SELECT merchant_id FROM t_merchant WHERE business_code LIKE '_1000018')" db-types="MySQL,PostgreSQL,openGauss" scenario-types="db,encrypt"
+ scenario-comments="Test single table's LIKE operator underscore wildcard in subquery select statement when use sharding feature.|Test encrypt table's LIKE operator underscore wildcard in subquery select statement when use encrypt feature.">
+ <assertion expected-data-source-name="read_dataset" />
+ </test-case>
+
+ <test-case sql="SELECT * FROM (SELECT m1.* FROM t_merchant m1 INNER JOIN t_merchant m2 ON m1.merchant_id = m2.merchant_id) temp" db-types="MySQL,PostgreSQL,openGauss" scenario-types="encrypt"
+ scenario-comments="Test encrypt shorthand expansion for subquery with select join statement when use encrypt feature.">
+ <assertion expected-data-source-name="read_dataset" />
+ </test-case>
+
+ <test-case sql="SELECT * FROM (SELECT * FROM t_user) temp 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 (SELECT u1.* FROM t_user u1 INNER JOIN t_user u2 ON u1.user_id = u2.user_id) temp 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>
+</integration-test-cases>
diff --git a/test/e2e/suite/src/test/resources/cases/dql/dql-integration-select.xml b/test/e2e/suite/src/test/resources/cases/dql/dql-integration-select.xml
new file mode 100644
index 00000000000..610ed5cb0ac
--- /dev/null
+++ b/test/e2e/suite/src/test/resources/cases/dql/dql-integration-select.xml
@@ -0,0 +1,239 @@
+<?xml version="1.0" encoding="UTF-8"?>
+<!--
+ ~ Licensed to the Apache Software Foundation (ASF) under one or more
+ ~ contributor license agreements. See the NOTICE file distributed with
+ ~ this work for additional information regarding copyright ownership.
+ ~ The ASF licenses this file to You under the Apache License, Version 2.0
+ ~ (the "License"); you may not use this file except in compliance with
+ ~ the License. You may obtain a copy of the License at
+ ~
+ ~ http://www.apache.org/licenses/LICENSE-2.0
+ ~
+ ~ Unless required by applicable law or agreed to in writing, software
+ ~ distributed under the License is distributed on an "AS IS" BASIS,
+ ~ WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ ~ See the License for the specific language governing permissions and
+ ~ limitations under the License.
+ -->
+
+<integration-test-cases>
+ <test-case sql="SELECT 1 as a" />
+
+ <test-case sql="SELECT t_order.* FROM t_order t_order WHERE user_id = ? AND order_id = ?" scenario-types="db,tbl,dbtbl_with_readwrite_splitting,readwrite_splitting">
+ <assertion parameters="10:int, 1000:int" expected-data-source-name="read_dataset" />
+ <assertion parameters="12:int, 1000:int" />
+ </test-case>
+
+ <test-case sql="SELECT t_order.order_id,t_order.user_id,status FROM t_order t_order WHERE t_order.user_id = ? AND order_id = ?" db-types="MySQL,H2" scenario-types="db,tbl,dbtbl_with_readwrite_splitting,readwrite_splitting">
+ <assertion parameters="10:int, 1000:int" expected-data-source-name="read_dataset" />
+ <assertion parameters="12:int, 1000:int" expected-data-source-name="read_dataset" />
+ </test-case>
+
+ <test-case sql="SELECT * FROM t_order WHERE user_id = ? AND order_id = ?" scenario-types="db,tbl,dbtbl_with_readwrite_splitting,readwrite_splitting">
+ <assertion parameters="10:int, 1000:int" expected-data-source-name="read_dataset" />
+ <assertion parameters="12:int, 1000:int" expected-data-source-name="read_dataset" />
+ </test-case>
+
+ <test-case sql="SELECT * FROM t_order WHERE order_id = ? AND order_id = ?" 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 * /* this is 
 block comment */ FROM /* this is another 
 block comment */ t_order where status='1'" db-types="MySQL" scenario-types="db,tbl,dbtbl_with_readwrite_splitting,readwrite_splitting">
+ <assertion expected-data-source-name="read_dataset" />
+ </test-case>
+
+ <test-case sql="SELECT * -- this is an line comment 
 FROM -- this is another line comment 
 t_order where status='1'" db-types="MySQL" 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_order where status='\''" db-types="MySQL" 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_order where status="\""" db-types="MySQL" scenario-types="db,tbl,dbtbl_with_readwrite_splitting,readwrite_splitting">
+ <assertion expected-data-source-name="read_dataset" />
+ </test-case>
+
+ <test-case sql="SELECT status as 'status' FROM t_order" db-types="MySQL" scenario-types="db,tbl,dbtbl_with_readwrite_splitting,readwrite_splitting">
+ <assertion expected-data-source-name="read_dataset" />
+ </test-case>
+
+ <test-case sql="SELECT INTERVAL(status,1,5) func_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="12:int, 1000:int" expected-data-source-name="read_dataset" />
+ </test-case>
+
+ <test-case sql="SELECT * FROM t_broadcast_table" scenario-types="db,tbl,dbtbl_with_readwrite_splitting">
+ <assertion 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 -->
+ <test-case sql="SELECT * FROM t_user WHERE user_id <= 15 ORDER BY user_id" scenario-types="encrypt">
+ <assertion 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 -->
+ <test-case sql="SELECT * FROM t_user WHERE password = '111111'" scenario-types="encrypt">
+ <assertion 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 -->
+ <test-case sql="SELECT * FROM t_user WHERE password IN ('222222', '333333')" scenario-types="encrypt">
+ <assertion 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 -->
+ <test-case sql="SELECT * FROM t_user WHERE password IN ('222222', '333333') AND user_id = 12" scenario-types="encrypt">
+ <assertion expected-data-source-name="read_dataset" />
+ </test-case>
+
+ <test-case sql="SELECT * FROM t_single_table" db-types="MySQL,Oracle,SQLServer" scenario-types="db,tbl,dbtbl_with_readwrite_splitting,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 o.*, i.* FROM t_order_federate o, t_order_item_federate i WHERE o.order_id = ? AND i.item_id = ?" scenario-types="db,dbtbl_with_readwrite_splitting,dbtbl_with_readwrite_splitting_and_encrypt,sharding_and_encrypt,encrypt_and_readwrite_splitting">
+ <assertion parameters="1000:int, 100000:int" expected-data-source-name="read_dataset" />
+ </test-case>
+
+ <test-case sql="SELECT user_id, CONCAT('SUM:', total, '.') AS content FROM (SELECT user_id, SUM(order_id_sharding) AS total FROM t_order_federate_sharding GROUP BY user_id HAVING SUM(order_id_sharding) > ?) AS temp ORDER BY temp.user_id" db-types="MySQL,PostgreSQL" scenario-types="tbl">
+ <assertion parameters="1000:int" />
+ </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" scenario-types="dbtbl_with_readwrite_splitting,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 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.order_id = ?" scenario-types="dbtbl_with_readwrite_splitting,dbtbl_with_readwrite_splitting_and_encrypt,sharding_and_encrypt,encrypt_and_readwrite_splitting">
+ <assertion parameters="10001:int" expected-data-source-name="read_dataset" />
+ </test-case>
+
+ <test-case sql="select o.order_id_sharding, i.order_id from t_order_federate_sharding o, t_order_item_federate_sharding i where o.order_id_sharding = i.item_id" scenario-types="db,tbl,dbtbl_with_readwrite_splitting,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 t_user_encrypt_federate.user_id, t_user_encrypt_federate.pwd, t_user_info.information from t_user_encrypt_federate, t_user_info where t_user_encrypt_federate.user_id = t_user_info.user_id " scenario-types="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 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' " scenario-types="db,dbtbl_with_readwrite_splitting,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 o.*, i.* from t_order_federate o, t_order_item_federate_sharding i where o.order_id = i.item_id " scenario-types="db,dbtbl_with_readwrite_splitting,dbtbl_with_readwrite_splitting_and_encrypt,sharding_and_encrypt,encrypt_and_readwrite_splitting">
+ <assertion expected-data-source-name="read_dataset" />
+ </test-case>
+
+ <!-- TODO complete test type -->
+ <test-case sql="SELECT order_id, user_id, order_name, type_char, type_boolean, type_smallint, type_enum, type_decimal, type_date, type_time, type_timestamp FROM t_shadow WHERE user_id = ?" db-types="MySQL,PostgreSQL" scenario-types="shadow">
+ <assertion parameters="1:int" expected-data-source-name="prod_dataset" />
+ <assertion parameters="0:int" expected-data-source-name="shadow_dataset" />
+ </test-case>
+
+ <test-case sql="SELECT order_id, user_id, order_name, type_char, type_boolean, type_smallint, type_enum, type_decimal, type_date, type_time, type_timestamp FROM t_shadow WHERE user_id = ?" db-types="MySQL,PostgreSQL" scenario-types="encrypt_shadow">
+ <assertion parameters="1:int" expected-data-source-name="prod_dataset" />
+ <assertion parameters="0:int" expected-data-source-name="encrypt_shadow_dataset" />
+ </test-case>
+
+ <test-case sql="SELECT order_id, user_id, order_name, type_char, type_boolean, type_smallint, type_enum, type_decimal, type_date, type_time, type_timestamp FROM t_shadow WHERE user_id = ?" db-types="MySQL,PostgreSQL" scenario-types="readwrite_splitting_and_shadow">
+ <assertion parameters="1:int" expected-data-source-name="read_dataset" />
+ </test-case>
+
+ <test-case sql="SELECT order_id, user_id, order_name, type_char, type_boolean, type_smallint, type_enum, type_decimal, type_date, type_time, type_timestamp FROM t_shadow WHERE user_id = ?" db-types="PostgreSQL" scenario-types="sharding_and_shadow,sharding_encrypt_shadow">
+ <assertion parameters="1:int" expected-data-source-name="prod_dataset" />
+ <assertion parameters="0:int" expected-data-source-name="shadow_dataset" />
+ </test-case>
+
+ <!-- TODO FIX ME Expected: is "true" but was "1"-->
+ <!-- <test-case sql="SELECT order_id, user_id, order_name, type_char, type_boolean, type_smallint, type_enum, type_decimal, type_date, type_time, type_timestamp FROM t_shadow WHERE user_id = ?" db-types="MySQL" scenario-types="sharding_and_shadow">-->
+ <!-- <assertion parameters="1:int" expected-data-source-name="prod_dataset" />-->
+ <!-- <assertion parameters="0:int" expected-data-source-name="shadow_dataset" />-->
+ <!-- </test-case>-->
+
+ <test-case sql="SELECT * FROM t_order_item_join_view" db-types="MySQL,PostgreSQL,openGauss" scenario-types="db">
+ <assertion expected-data-source-name="read_dataset" />
+ </test-case>
+
+ <test-case sql="SELECT * FROM t_order_subquery_view WHERE order_id = ?" db-types="MySQL,PostgreSQL,openGauss" scenario-types="db">
+ <assertion parameters="1201:long" expected-data-source-name="read_dataset" />
+ </test-case>
+
+ <test-case sql="SELECT * FROM t_order_aggregation_view" db-types="MySQL,PostgreSQL,openGauss" scenario-types="db">
+ <assertion expected-data-source-name="read_dataset" />
+ </test-case>
+
+ <test-case sql="SELECT * FROM t_order_aggregation_view WHERE max_price = ?" db-types="MySQL,PostgreSQL,openGauss" scenario-types="db">
+ <assertion parameters="8200:int" expected-data-source-name="read_dataset" />
+ </test-case>
+
+ <test-case sql="SELECT * FROM t_order_union_view WHERE order_id = ?" db-types="MySQL,PostgreSQL,openGauss" scenario-types="db">
+ <assertion parameters="2500:long" expected-data-source-name="read_dataset" />
+ </test-case>
+
+ <test-case sql="SELECT * FROM t_merchant WHERE business_code LIKE '%18'" db-types="MySQL,PostgreSQL,openGauss" scenario-types="db,encrypt"
+ scenario-comments="Test single table's LIKE operator percentage wildcard in simple select statement when use sharding feature.|Test encrypt table's LIKE operator percentage wildcard in simple select statement when use encrypt feature.">
+ <assertion expected-data-source-name="read_dataset" />
+ </test-case>
+
+ <test-case sql="SELECT * FROM t_merchant WHERE business_code like '%18'" db-types="MySQL,PostgreSQL,openGauss" scenario-types="db,encrypt"
+ scenario-comments="Test single table's lower case like operator percentage wildcard in simple select statement when use sharding feature.|Test encrypt table's lower case like operator percentage wildcard in simple select statement when use encrypt feature.">
+ <assertion expected-data-source-name="read_dataset" />
+ </test-case>
+
+ <test-case sql="SELECT * FROM t_merchant WHERE business_code LIKE '_1000018'" db-types="MySQL,PostgreSQL,openGauss" scenario-types="db,encrypt"
+ scenario-comments="Test single table's LIKE operator underscore wildcard in simple select statement when use sharding feature.|Test encrypt table's LIKE operator underscore wildcard in simple select statement when use encrypt feature.">
+ <assertion expected-data-source-name="read_dataset" />
+ </test-case>
+
+ <test-case sql="SELECT * FROM (SELECT * FROM t_merchant) temp" db-types="MySQL,PostgreSQL,openGauss" scenario-types="encrypt"
+ scenario-comments="Test encrypt shorthand expansion for subquery with simple select statement when use encrypt feature.">
+ <assertion expected-data-source-name="read_dataset" />
+ </test-case>
+
+ <test-case sql="SELECT * FROM t_data_type_integer_unsigned" db-types="MySQL" scenario-types="passthrough" scenario-comments="Test ShardingSphere-Proxy MySQL compatibility for unsigned integer data types">
+ <assertion expected-data-source-name="expected_dataset" />
+ </test-case>
+
+ <test-case sql="SELECT * FROM t_data_type_money" db-types="PostgreSQL,openGauss" scenario-types="passthrough"
+ scenario-comments="Test ShardingSphere-Proxy compatibility for PostgreSQL/openGauss money type">
+ <assertion expected-data-source-name="expected_dataset" />
+ </test-case>
+
+ <test-case sql="SELECT val = ?::money FROM t_data_type_money WHERE id = ?" db-types="PostgreSQL,openGauss" scenario-types="passthrough"
+ scenario-comments="Test ShardingSphere-Proxy compatibility for PostgreSQL/openGauss money type: equal operator">
+ <assertion parameters="$123.00:String, 1001:int" expected-data-source-name="expected_dataset" />
+ <assertion parameters="$123.01:String, 1001:int" expected-data-source-name="expected_dataset" />
+ </test-case>
+
+ <test-case sql="SELECT val != ?::money FROM t_data_type_money WHERE id = ?" db-types="PostgreSQL,openGauss" scenario-types="passthrough"
+ scenario-comments="Test ShardingSphere-Proxy compatibility for PostgreSQL/openGauss money type: not equal operator">
+ <assertion parameters="$124.00:String, 1001:int" expected-data-source-name="expected_dataset" />
+ <assertion parameters="$123.00:String, 1001:int" expected-data-source-name="expected_dataset" />
+ </test-case>
+
+ <test-case sql="SELECT val <= ?::money FROM t_data_type_money WHERE id = ?" db-types="PostgreSQL,openGauss" scenario-types="passthrough"
+ scenario-comments="Test ShardingSphere-Proxy compatibility for PostgreSQL/openGauss money type: less than or equal operator">
+ <assertion parameters="$123.00:String, 1001:int" expected-data-source-name="expected_dataset" />
+ <assertion parameters="$122.99:String, 1001:int" expected-data-source-name="expected_dataset" />
+ </test-case>
+
+ <test-case sql="SELECT val >= ?::money FROM t_data_type_money WHERE id = ?" db-types="PostgreSQL,openGauss" scenario-types="passthrough"
+ scenario-comments="Test ShardingSphere-Proxy compatibility for PostgreSQL/openGauss money type: greater than or equal operator">
+ <assertion parameters="$123.00:String, 1001:int" expected-data-source-name="expected_dataset" />
+ <assertion parameters="$123.01:String, 1001:int" expected-data-source-name="expected_dataset" />
+ </test-case>
+
+ <test-case sql="SELECT val < ?::money FROM t_data_type_money WHERE id = ?" db-types="PostgreSQL,openGauss" scenario-types="passthrough"
+ scenario-comments="Test ShardingSphere-Proxy compatibility for PostgreSQL/openGauss money type: less than operator">
+ <assertion parameters="$122.00:String, 1001:int" expected-data-source-name="expected_dataset" />
+ <assertion parameters="$124.00:String, 1001:int" expected-data-source-name="expected_dataset" />
+ </test-case>
+
+ <test-case sql="SELECT val > ?::money FROM t_data_type_money WHERE id = ?" db-types="PostgreSQL,openGauss" scenario-types="passthrough"
+ scenario-comments="Test ShardingSphere-Proxy compatibility for PostgreSQL/openGauss money type: greater than operator">
+ <assertion parameters="$122.00:String, 1001:int" expected-data-source-name="expected_dataset" />
+ <assertion parameters="$124.00:String, 1001:int" expected-data-source-name="expected_dataset" />
+ </test-case>
+
+ <test-case sql="select * from shardingsphere.cluster_information;" db-types="MySQL,PostgreSQL,openGauss" scenario-types="db">
+ <assertion expected-data-file="select_cluster_information.xml" />
+ </test-case>
+</integration-test-cases>
diff --git a/test/e2e/suite/src/test/resources/cases/dql/dql-integration-test-cases.xml b/test/e2e/suite/src/test/resources/cases/dql/dql-integration-test-cases.xml
deleted file mode 100644
index 2ddaa0dd036..00000000000
--- a/test/e2e/suite/src/test/resources/cases/dql/dql-integration-test-cases.xml
+++ /dev/null
@@ -1,1390 +0,0 @@
-<!--
- ~ Licensed to the Apache Software Foundation (ASF) under one or more
- ~ contributor license agreements. See the NOTICE file distributed with
- ~ this work for additional information regarding copyright ownership.
- ~ The ASF licenses this file to You under the Apache License, Version 2.0
- ~ (the "License"); you may not use this file except in compliance with
- ~ the License. You may obtain a copy of the License at
- ~
- ~ http://www.apache.org/licenses/LICENSE-2.0
- ~
- ~ Unless required by applicable law or agreed to in writing, software
- ~ distributed under the License is distributed on an "AS IS" BASIS,
- ~ WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
- ~ See the License for the specific language governing permissions and
- ~ limitations under the License.
- -->
-
-<integration-test-cases>
- <test-case sql="SELECT 1 as a" />
-
- <test-case sql="SELECT t_order.* FROM t_order t_order WHERE user_id = ? AND order_id = ?" scenario-types="db,tbl,dbtbl_with_readwrite_splitting,readwrite_splitting">
- <assertion parameters="10:int, 1000:int" expected-data-source-name="read_dataset" />
- <assertion parameters="12:int, 1000:int" />
- </test-case>
-
- <test-case sql="SELECT t_order.order_id,t_order.user_id,status FROM t_order t_order WHERE t_order.user_id = ? AND order_id = ?" db-types="MySQL,H2" scenario-types="db,tbl,dbtbl_with_readwrite_splitting,readwrite_splitting">
- <assertion parameters="10:int, 1000:int" expected-data-source-name="read_dataset" />
- <assertion parameters="12:int, 1000:int" expected-data-source-name="read_dataset" />
- </test-case>
-
- <test-case sql="SELECT * FROM t_order_item WHERE item_id <> ? ORDER BY item_id" scenario-types="db,tbl,dbtbl_with_readwrite_splitting,readwrite_splitting">
- <assertion parameters="100001:int" expected-data-source-name="read_dataset" />
- </test-case>
-
- <test-case sql="SELECT * FROM t_order_item WHERE item_id != ? ORDER BY item_id" scenario-types="db,tbl,dbtbl_with_readwrite_splitting,readwrite_splitting">
- <assertion parameters="100001:int" expected-data-source-name="read_dataset" />
- </test-case>
-
- <test-case sql="SELECT * FROM t_order_item WHERE item_id IS NOT NULL AND item_id NOT IN (?, ?) ORDER BY item_id" scenario-types="db,tbl,dbtbl_with_readwrite_splitting,readwrite_splitting">
- <assertion parameters="100000:int, 100001:int" expected-data-source-name="read_dataset" />
- </test-case>
-
- <test-case sql="SELECT * FROM t_order_item WHERE item_id IS NOT NULL AND item_id NOT BETWEEN ? AND ? ORDER BY item_id" scenario-types="db,tbl,dbtbl_with_readwrite_splitting,readwrite_splitting">
- <assertion parameters="100000:int, 100001:int" expected-data-source-name="read_dataset" />
- </test-case>
-
- <test-case sql="SELECT * FROM t_order WHERE user_id = ? AND order_id = ?" scenario-types="db,tbl,dbtbl_with_readwrite_splitting,readwrite_splitting">
- <assertion parameters="10:int, 1000:int" expected-data-source-name="read_dataset" />
- <assertion parameters="12:int, 1000:int" expected-data-source-name="read_dataset" />
- </test-case>
-
- <test-case sql="SELECT * FROM t_order WHERE order_id = ? AND order_id = ?" 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 * FROM t_order WHERE order_id IN (?, ?) AND order_id IN (?, ?) ORDER BY order_id" scenario-types="db,tbl,dbtbl_with_readwrite_splitting,readwrite_splitting">
- <assertion parameters="1000:int, 1001:int, 1001:int, 1100:int" expected-data-source-name="read_dataset" />
- </test-case>
-
- <test-case sql="SELECT * FROM t_order WHERE user_id IN (?, ?, ?) AND order_id IN (?, ?) ORDER BY user_id, order_id" scenario-types="db,tbl,dbtbl_with_readwrite_splitting,readwrite_splitting">
- <assertion parameters="10:int, 12:int, 15:int, 1000:int, 1101:int" expected-data-source-name="read_dataset" />
- <assertion parameters="10:int, 12:int, 15:int, 1309:int, 1408:int" expected-data-source-name="read_dataset" />
- </test-case>
-
- <test-case sql="SELECT * FROM t_order WHERE user_id BETWEEN ? AND ? AND order_id BETWEEN ? AND ? ORDER BY user_id, order_id" scenario-types="db,tbl,dbtbl_with_readwrite_splitting,readwrite_splitting">
- <assertion parameters="10:int, 12:int, 1009:int, 1108:int" expected-data-source-name="read_dataset" />
- <assertion parameters="10:int, 12:int, 1309:int, 1408:int" expected-data-source-name="read_dataset" />
- </test-case>
-
- <test-case sql="SELECT * FROM t_order WHERE user_id >= ? AND user_id <= ? AND order_id >= ? AND order_id <= ? ORDER BY user_id, order_id" scenario-types="db,tbl,dbtbl_with_readwrite_splitting,readwrite_splitting">
- <assertion parameters="10:int, 12:int, 1009:int, 1108:int" expected-data-source-name="read_dataset" />
- <assertion parameters="10:int, 12:int, 1309:int, 1408:int" expected-data-source-name="read_dataset" />
- </test-case>
-
- <test-case sql="SELECT count(0) as orders_count FROM t_order o WHERE o.status LIKE CONCAT('%%', ?, '%%') AND o.user_id IN (?, ?) AND o.order_id BETWEEN ? AND ?" scenario-types="db,tbl,dbtbl_with_readwrite_splitting,readwrite_splitting">
- <assertion parameters="init:String, 10:int, 11:int, 1000:int, 2901:int" expected-data-source-name="read_dataset" />
- </test-case>
-
- <test-case sql="SELECT count(0) as orders_count FROM t_order o WHERE o.status ~~ CONCAT('%%', ?, '%%') AND o.user_id IN (?, ?) AND o.order_id BETWEEN ? AND ?" db-types="PostgreSQL" scenario-types="db,tbl,dbtbl_with_readwrite_splitting,readwrite_splitting">
- <assertion parameters="init:String, 10:int, 11:int, 1000:int, 2901: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" 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>
-
- <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>
-
- <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 = ?" 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 SUM(user_id) AS user_id_sum FROM t_order" scenario-types="db,tbl,dbtbl_with_readwrite_splitting,readwrite_splitting">
- <assertion expected-data-source-name="read_dataset" />
- </test-case>
-
- <test-case sql="SELECT COUNT(*) AS orders_count FROM t_order" scenario-types="db,tbl,dbtbl_with_readwrite_splitting,readwrite_splitting">
- <assertion expected-data-source-name="read_dataset" />
- </test-case>
-
- <test-case sql="SELECT COUNT(*) AS orders_count FROM t_order WHERE order_id > 1-1" scenario-types="db,tbl,dbtbl_with_readwrite_splitting,readwrite_splitting">
- <assertion expected-data-source-name="read_dataset" />
- </test-case>
-
- <test-case sql="SELECT COUNT(*) AS orders_count FROM t_order WHERE order_id > 1 - 1" scenario-types="db,tbl,dbtbl_with_readwrite_splitting,readwrite_splitting">
- <assertion expected-data-source-name="read_dataset" />
- </test-case>
-
- <test-case sql="SELECT MAX(user_id) AS max_user_id FROM t_order" scenario-types="db,tbl,dbtbl_with_readwrite_splitting,readwrite_splitting">
- <assertion expected-data-source-name="read_dataset" />
- </test-case>
-
- <test-case sql="SELECT MIN(user_id) AS min_user_id FROM t_order" scenario-types="db,tbl,dbtbl_with_readwrite_splitting,readwrite_splitting">
- <assertion expected-data-source-name="read_dataset" />
- </test-case>
-
- <!-- FIXME #15593 Expected: is "19", but: was "19.5000" in db scenario -->
- <test-case sql="SELECT AVG(user_id) AS user_id_avg FROM t_order" scenario-types="db,tbl,dbtbl_with_readwrite_splitting,readwrite_splitting">
- <assertion expected-data-source-name="read_dataset" />
- </test-case>
-
- <test-case sql="SELECT COUNT(*) AS items_count FROM t_order o, t_order_item i WHERE o.user_id = i.user_id AND o.order_id = i.order_id AND o.user_id IN (?, ?) AND o.order_id BETWEEN ? AND ?" 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 COUNT(*) AS items_count FROM t_order o JOIN t_order_item i ON o.user_id = i.user_id AND o.order_id = i.order_id WHERE o.user_id IN (?, ?) AND o.order_id BETWEEN ? AND ?" 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 COUNT(`order_id`) AS orders_count FROM t_order" db-types="MySQL" scenario-types="db,tbl,dbtbl_with_readwrite_splitting,readwrite_splitting">
- <assertion expected-data-source-name="read_dataset" />
- </test-case>
-
- <test-case sql="SELECT * /* this is 
 block comment */ FROM /* this is another 
 block comment */ t_order where status='1'" db-types="MySQL" scenario-types="db,tbl,dbtbl_with_readwrite_splitting,readwrite_splitting">
- <assertion expected-data-source-name="read_dataset" />
- </test-case>
-
- <test-case sql="SELECT * -- this is an line comment 
 FROM -- this is another line comment 
 t_order where status='1'" db-types="MySQL" scenario-types="db,tbl,dbtbl_with_readwrite_splitting,readwrite_splitting">
- <assertion expected-data-source-name="read_dataset" />
- </test-case>
-
- <test-case sql="SELECT o.order_id + 1 * 2 as exp FROM t_order AS o ORDER BY o.order_id" scenario-types="db,tbl,dbtbl_with_readwrite_splitting,readwrite_splitting">
- <assertion expected-data-source-name="read_dataset" />
- </test-case>
-
- <test-case sql="SELECT DATE(i.creation_date) AS creation_date FROM `t_order_item` AS i ORDER BY DATE(i.creation_date) DESC" db-types="MySQL" 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_order o WHERE o.status REGEXP ? AND o.order_id IN (?, ?)" db-types="MySQL" scenario-types="db,tbl,dbtbl_with_readwrite_splitting,readwrite_splitting">
- <assertion parameters="init:String, 1000:int, 1001:int" expected-data-source-name="read_dataset" />
- </test-case>
-
- <test-case sql="SELECT SUM(order_id) AS orders_sum, user_id FROM t_order GROUP BY user_id ORDER BY user_id" scenario-types="db,tbl,dbtbl_with_readwrite_splitting,readwrite_splitting">
- <assertion expected-data-source-name="read_dataset" />
- </test-case>
-
- <test-case sql="SELECT COUNT(order_id) AS orders_count, user_id FROM t_order GROUP BY user_id ORDER BY user_id" scenario-types="db,tbl,dbtbl_with_readwrite_splitting,readwrite_splitting">
- <assertion expected-data-source-name="read_dataset" />
- </test-case>
-
- <test-case sql="SELECT MAX(order_id) AS max_order_id, user_id FROM t_order GROUP BY user_id ORDER BY user_id" scenario-types="db,tbl,dbtbl_with_readwrite_splitting,readwrite_splitting">
- <assertion expected-data-source-name="read_dataset" />
- </test-case>
-
- <test-case sql="SELECT MIN(order_id) AS min_order_id, user_id FROM t_order GROUP BY user_id ORDER BY user_id" scenario-types="db,tbl,dbtbl_with_readwrite_splitting,readwrite_splitting">
- <assertion expected-data-source-name="read_dataset" />
- </test-case>
-
- <test-case sql="SELECT AVG(order_id) AS orders_avg, user_id FROM t_order GROUP BY user_id ORDER BY user_id" scenario-types="db,tbl,dbtbl_with_readwrite_splitting,readwrite_splitting">
- <assertion expected-data-source-name="read_dataset" />
- </test-case>
-
- <test-case sql="SELECT SUM(order_id) AS orders_sum, user_id FROM t_order GROUP BY user_id ORDER BY orders_sum DESC" scenario-types="db,tbl,dbtbl_with_readwrite_splitting,readwrite_splitting">
- <assertion expected-data-source-name="read_dataset" />
- </test-case>
-
- <test-case sql="SELECT count(*) as items_count FROM t_order o JOIN t_order_item i ON o.user_id = i.user_id AND o.order_id = i.order_id WHERE o.user_id IN (?, ?) AND o.order_id BETWEEN ? AND ? GROUP BY o.user_id" scenario-types="db,tbl,dbtbl_with_readwrite_splitting,readwrite_splitting">
- <assertion parameters="10:int, 11:int, 1000:int, 1109:int" expected-data-source-name="read_dataset" />
- </test-case>
-
- <test-case sql="SELECT user_id FROM t_order GROUP BY user_id ORDER BY user_id LIMIT ?" db-types="H2,MySQL,PostgreSQL" scenario-types="db,tbl,dbtbl_with_readwrite_splitting,readwrite_splitting">
- <assertion parameters="1:int" expected-data-source-name="read_dataset" />
- </test-case>
-
- <test-case sql="SELECT user_id, SUM(order_id) AS orders_sum FROM t_order GROUP BY user_id ORDER BY SUM(order_id) LIMIT ?" db-types="H2,MySQL,PostgreSQL" scenario-types="db,tbl,dbtbl_with_readwrite_splitting,readwrite_splitting">
- <assertion parameters="1:int" expected-data-source-name="read_dataset" />
- </test-case>
-
- <test-case sql="SELECT o.user_id uid FROM t_order o GROUP BY o.user_id ORDER BY o.user_id" db-types="H2,MySQL,SQLServer,PostgreSQL" scenario-types="db,tbl,dbtbl_with_readwrite_splitting,readwrite_splitting">
- <assertion expected-data-source-name="read_dataset" />
- </test-case>
-
- <test-case sql="SELECT SUM(order_id) AS orders_sum, user_id as `key` FROM t_order GROUP BY `key`" db-types="MySQL" scenario-types="db,tbl,dbtbl_with_readwrite_splitting,readwrite_splitting">
- <assertion expected-data-source-name="read_dataset" />
- </test-case>
-
- <test-case sql="SELECT COUNT(order_id) AS orders_count, user_id FROM t_order GROUP BY 2 ORDER BY 2" db-types="MySQL,Oracle,SQLServer,PostgreSQL" scenario-types="db,tbl,dbtbl_with_readwrite_splitting,readwrite_splitting" />
-
- <!--<test-case sql="SELECT date_format(creation_date, '%%y-%%m-%%d') as creation_date, count(*) as c_number FROM `t_order_item` WHERE order_id in (?, ?) GROUP BY date_format(creation_date, '%%y-%%m-%%d')" db-types="MySQL" scenario-types="db,tbl,dbtbl_with_readwrite_splitting,readwrite_splitting">-->
- <!--<assertion sharding-rule-type="dbtbl_with_readwrite_splitting" parameters="1000:int, 1100:int" expected-data-source-name="read_dataset" />-->
- <!--</test-case>-->
-
- <test-case sql="SELECT * FROM t_order WHERE order_id = ? OR order_id = ?" 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 * FROM t_order WHERE order_id = ? OR user_id = ? ORDER BY order_id" scenario-types="db,tbl,dbtbl_with_readwrite_splitting,readwrite_splitting">
- <assertion parameters="1000:int, 11:int" expected-data-source-name="read_dataset" />
- </test-case>
-
- <!-- TODO FIXED ME PostgreSQL default order by collate -->
- <test-case sql="SELECT * FROM t_order WHERE order_id = ? OR status = ? ORDER BY order_id" db-types="H2,MySQL,Oracle,SQLServer" scenario-types="db,tbl,dbtbl_with_readwrite_splitting,readwrite_splitting">
- <assertion parameters="1000:int, init:String" expected-data-source-name="read_dataset" />
- </test-case>
-
- <test-case sql="SELECT * FROM t_order WHERE (order_id = ? OR status = ?) AND user_id = ?" scenario-types="db,tbl,dbtbl_with_readwrite_splitting,readwrite_splitting">
- <assertion parameters="1000:int, init:String, 11:int" expected-data-source-name="read_dataset" />
- </test-case>
-
- <test-case sql="SELECT * FROM t_order WHERE ((status = ? AND (order_id = ? OR (order_id = ?)) AND (user_id = ? OR (user_id = ?)))) ORDER BY order_id" scenario-types="db,tbl,dbtbl_with_readwrite_splitting,readwrite_splitting">
- <assertion parameters="init:String, 1000:int, 1100:int, 10:int, 11: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>
-
- <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.order_id = ? OR o.order_id = ?) AND o.user_id = ? AND o.status = ?" scenario-types="db,tbl,dbtbl_with_readwrite_splitting">
- <assertion parameters="1000:int, 1100:int, 11:int, init:String" expected-data-source-name="read_dataset" />
- </test-case>
-
- <test-case sql="SELECT * FROM t_order o ORDER BY o.order_id, 2 DESC" 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, t_order_item i WHERE o.order_id = i.order_id AND o.status = 'init' ORDER BY o.order_id DESC, 1" scenario-types="db,tbl,dbtbl_with_readwrite_splitting,readwrite_splitting">
- <assertion expected-data-source-name="read_dataset" />
- </test-case>
-
- <test-case sql="SELECT o.order_id as gen_order_id_ FROM t_order o ORDER BY o.order_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, t_order_item i WHERE o.order_id = i.order_id AND o.status = 'init' ORDER BY i.creation_date DESC, o.order_id DESC, i.item_id" scenario-types="db,tbl,dbtbl_with_readwrite_splitting,readwrite_splitting">
- <assertion expected-data-source-name="read_dataset" />
- </test-case>
-
- <!-- // TODO add null data -->
- <test-case sql="SELECT o.order_id as gen_order_id_ FROM t_order o ORDER BY o.order_id NULLS FIRST" db-types="Oracle" scenario-types="db,tbl,dbtbl_with_readwrite_splitting,readwrite_splitting">
- <assertion expected-data-source-name="read_dataset" />
- </test-case>
-
- <!-- // TODO add null data -->
- <test-case sql="SELECT o.order_id as gen_order_id_ FROM t_order o ORDER BY o.order_id ASC NULLS LAST" db-types="Oracle" scenario-types="db,tbl,dbtbl_with_readwrite_splitting,readwrite_splitting">
- <assertion expected-data-source-name="read_dataset" />
- </test-case>
-
- <test-case sql="SELECT *, order_id, o.* FROM t_order o ORDER BY o.order_id" scenario-types="db,tbl,dbtbl_with_readwrite_splitting,readwrite_splitting">
- <assertion expected-data-source-name="read_dataset" />
- </test-case>
-
- <test-case sql="SELECT o.* FROM t_order o ORDER BY o.order_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 o.* FROM t_order o ORDER BY o.order_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 * FROM t_order o ORDER BY order_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 t_order.* FROM t_order ORDER BY t_order.order_id" db-types="H2,MySQL" scenario-types="db,tbl,dbtbl_with_readwrite_splitting,readwrite_splitting">
- <assertion expected-data-source-name="read_dataset" />
- </test-case>
-
- <!--TODO need to add later-->
- <!--<test-case sql="SELECT * FROM t_order o ORDER BY ?" db-types="H2,MySQL" scenario-types="db,tbl,dbtbl_with_readwrite_splitting,readwrite_splitting">-->
- <!--<assertion parameters="order_id:String" expected-data-source-name="read_dataset" />-->
- <!--</test-case>-->
-
- <test-case sql="SELECT * FROM t_order ORDER BY order_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 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.*, 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 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 * FROM (SELECT TOP (?) row_number() OVER (ORDER BY i.item_id DESC) AS rownum_, i.item_id, o.order_id as order_id, o.status as status, o.user_id as user_id FROM t_order o JOIN t_order_item i ON o.user_id = i.user_id AND o.order_id = i.order_id WHERE o.user_id IN (?, ?) AND o.order_id BETWEEN ? AND ?) AS row_" db-types="SQLServer" scenario-types="db,tbl,dbtbl_with_readwrite_splitting,readwrite_splitting">
- <assertion parameters="2:int, 10:int, 19:int, 1000:int, 1909:int" expected-data-source-name="read_dataset" />
- </test-case>
-
- <test-case sql="SELECT * FROM (SELECT row_.*, rownum rownum_ FROM (SELECT order0_.order_id as order_id, order0_.status as status, order0_.user_id as user_id FROM t_order order0_ JOIN t_order_item i ON order0_.user_id = i.user_id AND order0_.order_id = i.order_id WHERE order0_.user_id IN (?, ?) AND order0_.order_id BETWEEN ? AND ? ORDER BY i.item_id DESC) row_ WHERE rownum <= ?)" db-types="Oracle" 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">
- <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">
- <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">
- <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 * FROM (SELECT TOP (?) row_number() OVER (ORDER BY i.item_id DESC) AS rownum_, i.item_id, o.order_id as order_id, o.status as status, o.user_id as user_id FROM t_order o JOIN t_order_item i ON o.user_id = i.user_id AND o.order_id = i.order_id WHERE o.user_id IN (?, ?) AND o.order_id BETWEEN ? AND ?) AS row_ WHERE row_.rownum_ > ?" db-types="SQLServer" scenario-types="db,tbl,dbtbl_with_readwrite_splitting,readwrite_splitting">
- <assertion parameters="4:int, 10:int, 19:int, 1000:int, 1909:int, 2:int" expected-data-source-name="read_dataset" />
- </test-case>
-
- <test-case sql="SELECT * FROM (SELECT TOP (?) row_number() OVER (ORDER BY i.item_id DESC) AS rownum_, i.item_id, o.order_id as order_id, o.status as status, o.user_id as user_id FROM t_order o JOIN t_order_item i ON o.user_id = i.user_id AND o.order_id = i.order_id WHERE o.user_id IN (?, ?) AND o.order_id BETWEEN ? AND ?) AS row_ WHERE row_.rownum_ >= ?" db-types="SQLServer" scenario-types="db,tbl,dbtbl_with_readwrite_splitting,readwrite_splitting">
- <assertion parameters="4:int, 10:int, 19:int, 1000:int, 1909:int, 3:int" expected-data-source-name="read_dataset" />
- </test-case>
-
- <test-case sql="SELECT * FROM (SELECT row_.*, rownum rownum_ FROM (SELECT order0_.order_id as order_id, order0_.status as status, order0_.user_id as user_id FROM t_order order0_ JOIN t_order_item i ON order0_.user_id = i.user_id AND order0_.order_id = i.order_id WHERE order0_.user_id IN (?, ?) AND order0_.order_id BETWEEN ? AND ? ORDER BY i.item_id DESC) row_ WHERE rownum <= ?) t WHERE t.rownum_ > ?" db-types="Oracle" scenario-types="db,tbl,dbtbl_with_readwrite_splitting,readwr [...]
- <assertion parameters="10:int, 19:int, 1000:int, 1909:int, 4:int, 2:int" expected-data-source-name="read_dataset" />
- </test-case>
-
- <test-case sql="SELECT * FROM (SELECT row_.*, rownum rownum_ FROM (SELECT order0_.order_id as order_id, order0_.status as status, order0_.user_id as user_id FROM t_order order0_ JOIN t_order_item i ON order0_.user_id = i.user_id AND order0_.order_id = i.order_id WHERE order0_.user_id IN (?, ?) AND order0_.order_id BETWEEN ? AND ? ORDER BY i.item_id DESC) row_ WHERE rownum <= ?) t WHERE t.rownum_ >= ?" db-types="Oracle" scenario-types="db,tbl,dbtbl_with_readwrite_splitting,readw [...]
- <assertion parameters="10:int, 19:int, 1000:int, 1909:int, 4:int, 3:int" expected-data-source-name="read_dataset" />
- </test-case>
-
- <test-case sql="SELECT * FROM t_order WHERE ROWNUM <= ? ORDER BY order_id" db-types="Oracle" scenario-types="db,tbl,dbtbl_with_readwrite_splitting,readwrite_splitting">
- <assertion parameters="20:int" expected-data-source-name="read_dataset" />
- </test-case>
-
- <test-case sql="SELECT * FROM t_order ORDER BY order_id OFFSET 0 ROW FETCH NEXT ? ROWS ONLY" db-types="SQLServer" scenario-types="db,tbl,dbtbl_with_readwrite_splitting,readwrite_splitting">
- <assertion parameters="20:int" expected-data-source-name="read_dataset" />
- </test-case>
-
- <test-case sql="SELECT i.user_id FROM t_order o JOIN t_order_item i ON o.user_id = i.user_id AND o.order_id = i.order_id WHERE o.user_id IN (?, ?) AND o.order_id BETWEEN ? AND ? GROUP BY i.item_id ORDER BY i.item_id DESC LIMIT ?, ?" db-types="MySQL,H2" scenario-types="db,tbl,dbtbl_with_readwrite_splitting,readwrite_splitting">
- <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.user_id FROM t_order o JOIN t_order_item i ON o.user_id = i.user_id AND o.order_id = i.order_id WHERE o.user_id IN (?, ?) AND o.order_id BETWEEN ? AND ? GROUP BY i.user_id 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, 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>
-
- <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_item" WHERE "item_id" != ? ORDER BY "item_id"" db-types="PostgreSQL" scenario-types="db,tbl,dbtbl_with_readwrite_splitting,readwrite_splitting">
- <assertion parameters="100001:int" expected-data-source-name="read_dataset" />
- </test-case>
-
- <test-case sql="SELECT * FROM t_order where status='\''" db-types="MySQL" 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_order where status="\""" db-types="MySQL" scenario-types="db,tbl,dbtbl_with_readwrite_splitting,readwrite_splitting">
- <assertion expected-data-source-name="read_dataset" />
- </test-case>
-
- <test-case sql="SELECT status as 'status' FROM t_order" db-types="MySQL" scenario-types="db,tbl,dbtbl_with_readwrite_splitting,readwrite_splitting">
- <assertion expected-data-source-name="read_dataset" />
- </test-case>
-
- <!-- // TODO -->
-<!-- <test-case sql="SELECT DISTINCT item_id FROM t_order_item" scenario-types="db,tbl,dbtbl_with_readwrite_splitting,readwrite_splitting">-->
-<!-- <assertion expected-data-source-name="read_dataset" />-->
-<!-- </test-case>-->
-
-<!-- <test-case sql="SELECT DISTINCT order_id, user_id, status FROM t_order" scenario-types="db,tbl,dbtbl_with_readwrite_splitting,readwrite_splitting">-->
-<!-- <assertion expected-data-source-name="read_dataset" />-->
-<!-- </test-case>-->
-
- <!-- // TODO -->
- <!--<test-case sql="SELECT DISTINCT t_order.order_id FROM t_order order by t_order.order_id" scenario-types="db,tbl,dbtbl_with_readwrite_splitting,readwrite_splitting">-->
- <!--<assertion expected-data-source-name="read_dataset" />-->
- <!--</test-case>-->
-
- <!--<test-case sql="SELECT DISTINCT t_order.*, t_order_item.order_id FROM t_order, t_order_item WHERE t_order.order_id = t_order_item.order_id" scenario-types="db,tbl,dbtbl_with_readwrite_splitting,readwrite_splitting">-->
- <!--<assertion expected-data-source-name="read_dataset" />-->
- <!--</test-case>-->
-
- <test-case sql="SELECT DISTINCT item_id FROM t_order_item ORDER BY 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 DISTINCT t_order.order_id FROM t_order ORDER BY order_id" scenario-types="db,tbl,dbtbl_with_readwrite_splitting,readwrite_splitting">
- <assertion expected-data-source-name="read_dataset" />
- </test-case>
-
- <test-case sql="SELECT DISTINCT order_id, user_id, status FROM t_order ORDER BY order_id" scenario-types="db,tbl,dbtbl_with_readwrite_splitting,readwrite_splitting">
- <assertion expected-data-source-name="read_dataset" />
- </test-case>
-
- <test-case sql="SELECT DISTINCT * FROM t_order WHERE order_id > 1100 ORDER BY order_id" scenario-types="db,tbl,dbtbl_with_readwrite_splitting,readwrite_splitting">
- <assertion expected-data-source-name="read_dataset" />
- </test-case>
-
- <test-case sql="SELECT DISTINCT t_order.*, t_order_item.order_id FROM t_order, t_order_item WHERE t_order.order_id = t_order_item.order_id ORDER BY t_order.order_id" scenario-types="db,tbl,dbtbl_with_readwrite_splitting,readwrite_splitting">
- <assertion expected-data-source-name="read_dataset" />
- </test-case>
-
- <test-case sql="SELECT SUM(DISTINCT order_id) s FROM t_order WHERE order_id < 1100" scenario-types="db,tbl,dbtbl_with_readwrite_splitting,readwrite_splitting">
- <assertion expected-data-source-name="read_dataset" />
- </test-case>
-
- <test-case sql="SELECT COUNT(DISTINCT order_id) c FROM t_order WHERE order_id < 1100" scenario-types="db,tbl,dbtbl_with_readwrite_splitting,readwrite_splitting">
- <assertion expected-data-source-name="read_dataset" />
- </test-case>
-
- <!-- // TODO -->
-<!-- <test-case sql="SELECT AVG(DISTINCT order_id) FROM t_order WHERE order_id < 1100" db-types="MySQL" scenario-types="db,tbl,dbtbl_with_readwrite_splitting,readwrite_splitting">-->
-<!-- <assertion expected-data-source-name="read_dataset" />-->
-<!-- </test-case>-->
-
- <test-case sql="SELECT DISTINCT(item_id) FROM t_order_item ORDER BY 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 COUNT(DISTINCT order_id), SUM(DISTINCT order_id) FROM t_order WHERE order_id < 1100" db-types="MySQL" scenario-types="db,tbl,dbtbl_with_readwrite_splitting,readwrite_splitting">
- <assertion expected-data-source-name="read_dataset" />
- </test-case>
-
- <test-case sql="SELECT COUNT(DISTINCT user_id + order_id) c FROM t_order WHERE order_id < 1100" scenario-types="db,tbl,dbtbl_with_readwrite_splitting,readwrite_splitting">
- <assertion expected-data-source-name="read_dataset" />
- </test-case>
-
- <test-case sql="SELECT order_id, COUNT(DISTINCT order_id) c FROM t_order WHERE order_id < 1100 GROUP BY order_id ORDER BY order_id" scenario-types="db,tbl,dbtbl_with_readwrite_splitting,readwrite_splitting">
- <assertion expected-data-source-name="read_dataset" />
- </test-case>
-
- <test-case sql="SELECT COUNT(DISTINCT order_id) c, order_id FROM t_order GROUP BY order_id ORDER BY order_id" scenario-types="db,tbl,dbtbl_with_readwrite_splitting,readwrite_splitting">
- <assertion expected-data-source-name="read_dataset" />
- </test-case>
-
- <test-case sql="SELECT sum(if(status=0, 1, 0)) func_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="12:int, 1000:int" expected-data-source-name="read_dataset" />
- </test-case>
-
- <test-case sql="SELECT INTERVAL(status,1,5) func_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="12:int, 1000:int" expected-data-source-name="read_dataset" />
- </test-case>
-
- <test-case sql="SELECT * FROM t_order WHERE user_id = ? FOR UPDATE" lock-clause="true" scenario-types="db,tbl,dbtbl_with_readwrite_splitting,readwrite_splitting">
- <assertion parameters="10:int" expected-data-source-name="write_dataset" />
- </test-case>
-
- <test-case sql="select t.*,o.item_id as item_id,(case when t.status = 'init' then '已启用' when t.status = 'failed' then '已停用' end) as stateName
- from t_order t left join t_order_item as o on o.order_id =t.order_id where t.order_id=1000 limit 1" db-types="MySQL,H2" scenario-types="tbl,readwrite_splitting">
- <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>
-
- <test-case sql="SELECT * FROM t_broadcast_table" scenario-types="db,tbl,dbtbl_with_readwrite_splitting">
- <assertion 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 -->
- <test-case sql="SELECT * FROM t_user WHERE user_id <= 15 ORDER BY user_id" scenario-types="encrypt">
- <assertion 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 -->
- <test-case sql="SELECT * FROM t_user WHERE password = '111111'" scenario-types="encrypt">
- <assertion 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 -->
- <test-case sql="SELECT * FROM t_user WHERE password IN ('222222', '333333')" scenario-types="encrypt">
- <assertion 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 -->
- <test-case sql="SELECT * FROM t_user WHERE password IN ('222222', '333333') AND user_id = 12" scenario-types="encrypt">
- <assertion 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 -->
- <test-case sql="SELECT * FROM t_user WHERE password IN ('222222', '333333') OR user_id = 10" scenario-types="encrypt">
- <assertion 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 -->
- <test-case sql="SELECT * FROM t_user u INNER JOIN t_user_item m ON u.user_id = m.user_id WHERE u.user_id IN (10, 11)" scenario-types="encrypt">
- <assertion expected-data-source-name="read_dataset" />
- </test-case>
-
- <test-case sql="SELECT * FROM t_single_table" db-types="MySQL,Oracle,SQLServer" scenario-types="db,tbl,dbtbl_with_readwrite_splitting,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_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_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 o.*, i.* FROM t_order_federate o, t_order_item_federate i WHERE o.order_id = ? AND i.item_id = ?" scenario-types="db,dbtbl_with_readwrite_splitting,dbtbl_with_readwrite_splitting_and_encrypt,sharding_and_encrypt,encrypt_and_readwrite_splitting">
- <assertion parameters="1000:int, 100000:int" expected-data-source-name="read_dataset" />
- </test-case>
-
- <test-case sql="SELECT o.*, i.* FROM t_order_federate o, t_order_item_federate i WHERE o.order_id = ? AND i.item_id = ? LIMIT ?" scenario-types="db,dbtbl_with_readwrite_splitting,dbtbl_with_readwrite_splitting_and_encrypt,sharding_and_encrypt,encrypt_and_readwrite_splitting">
- <assertion parameters="1000:int, 100000:int, 3:int" expected-data-source-name="read_dataset" />
- </test-case>
-
- <test-case sql="SELECT user_id, CONCAT('SUM:', total, '.') AS content FROM (SELECT user_id, SUM(order_id_sharding) AS total FROM t_order_federate_sharding GROUP BY user_id HAVING SUM(order_id_sharding) > ?) AS temp ORDER BY temp.user_id" db-types="MySQL,PostgreSQL" scenario-types="tbl">
- <assertion parameters="1000:int" />
- </test-case>
-
- <test-case sql="SELECT GROUP_CONCAT(i.item_id SEPARATOR ';') AS item_ids FROM t_order_federate o INNER JOIN t_order_item_federate_sharding i ON o.order_id = i.item_id WHERE i.order_id >= ?" db-types="MySQL" scenario-types="dbtbl_with_readwrite_splitting,dbtbl_with_readwrite_splitting_and_encrypt,sharding_and_encrypt,encrypt_and_readwrite_splitting">
- <assertion parameters="10000:int" expected-data-source-name="read_dataset" />
- </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" scenario-types="dbtbl_with_readwrite_splitting,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 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.order_id = ?" scenario-types="dbtbl_with_readwrite_splitting,dbtbl_with_readwrite_splitting_and_encrypt,sharding_and_encrypt,encrypt_and_readwrite_splitting">
- <assertion parameters="10001:int" expected-data-source-name="read_dataset" />
- </test-case>
-
- <test-case sql="SELECT SUM(DISTINCT user_id), SUM(order_id_sharding) FROM t_order_federate_sharding WHERE order_id_sharding > ?" db-types="MySQL,PostgreSQL" scenario-types="tbl">
- <assertion parameters="1000:int" />
- </test-case>
-
- <test-case sql="SELECT (SELECT MAX(user_id) FROM t_order_federate_sharding) max_user_id, order_id_sharding, status FROM t_order_federate_sharding WHERE order_id_sharding > ?" db-types="MySQL,PostgreSQL" scenario-types="tbl">
- <assertion parameters="1100:int" />
- </test-case>
-
- <test-case sql="SELECT user_id, SUM(order_id_sharding) FROM t_order_federate_sharding GROUP BY user_id HAVING SUM(order_id_sharding) > ? ORDER BY user_id" db-types="MySQL,PostgreSQL" scenario-types="tbl">
- <assertion parameters="1000:int" />
- </test-case>
-
- <test-case sql="SELECT COUNT(1) FROM t_order WHERE order_id < ?" db-types="PostgreSQL" scenario-types="db,tbl">
- <assertion parameters="2000:int" />
- </test-case>
-
- <test-case sql="SELECT SUM(CRC32(`order_id`)) FROM t_order WHERE order_id = ?" db-types="MySQL" scenario-types="db,tbl">
- <assertion parameters="1000:int" />
- </test-case>
-
- <test-case sql="select o.order_id_sharding, i.order_id from t_order_federate_sharding o, t_order_item_federate_sharding i where o.order_id_sharding = i.item_id" scenario-types="db,tbl,dbtbl_with_readwrite_splitting,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 o.order_id_sharding, i.order_id from t_order_federate_sharding o, t_order_item_federate_sharding i where o.order_id_sharding = i.item_id and i.order_id > ?" scenario-types="db,tbl,dbtbl_with_readwrite_splitting,dbtbl_with_readwrite_splitting_and_encrypt,sharding_and_encrypt,encrypt_and_readwrite_splitting">
- <assertion parameters="10000:int" expected-data-source-name="read_dataset" />
- </test-case>
-
- <test-case sql="select o.order_id_sharding, i.order_id from t_order_federate_sharding o, t_order_item_federate_sharding i where o.order_id_sharding = i.item_id and i.order_id > ?" scenario-types="db,tbl,dbtbl_with_readwrite_splitting,dbtbl_with_readwrite_splitting_and_encrypt,sharding_and_encrypt,encrypt_and_readwrite_splitting">
- <assertion parameters="10000:int" expected-data-source-name="read_dataset" />
- </test-case>
-
- <test-case sql="select t_user_encrypt_federate_sharding.user_id, t_user_encrypt_federate_sharding.pwd, t_user_info.information from t_user_encrypt_federate_sharding, t_user_info where t_user_encrypt_federate_sharding.user_id = t_user_info.user_id order by t_user_encrypt_federate_sharding.user_id" scenario-types="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 t_user_encrypt_federate_sharding.user_id, t_user_encrypt_federate_sharding.pwd, t_user_info.information from t_user_encrypt_federate_sharding, t_user_info where t_user_encrypt_federate_sharding.user_id = t_user_info.user_id and t_user_encrypt_federate_sharding.user_id > ? " scenario-types="dbtbl_with_readwrite_splitting_and_encrypt,sharding_and_encrypt,encrypt_and_readwrite_splitting">
- <assertion parameters="1:int" expected-data-source-name="read_dataset" />
- </test-case>
-
- <test-case sql="select t_order_federate.* from t_order_federate, t_order_item_federate_sharding where t_order_federate.order_id = t_order_item_federate_sharding.item_id ORDER BY t_order_item_federate_sharding.user_id" scenario-types="db,dbtbl_with_readwrite_splitting,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 t_user_encrypt_federate.user_id, t_user_encrypt_federate.pwd, t_user_info.information from t_user_encrypt_federate, t_user_info where t_user_encrypt_federate.user_id = t_user_info.user_id " scenario-types="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 t_user_encrypt_federate.user_id, t_user_encrypt_federate.pwd, t_user_info.information from t_user_encrypt_federate, t_user_info where t_user_encrypt_federate.user_id = t_user_info.user_id and t_user_encrypt_federate.user_id > ? " scenario-types="dbtbl_with_readwrite_splitting_and_encrypt,sharding_and_encrypt,encrypt_and_readwrite_splitting">
- <assertion parameters="1:int" expected-data-source-name="read_dataset" />
- </test-case>
-
- <test-case sql="SELECT o.item_id, o.order_id, o.creation_date, s.id FROM t_order_item o INNER JOIN t_single_table s ON o.order_id = s.id ORDER BY o.item_id " scenario-types="db,tbl" />
-
- <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' " scenario-types="db,dbtbl_with_readwrite_splitting,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 o.*, i.* from t_order_federate o, t_order_item_federate_sharding i where o.order_id = i.item_id " scenario-types="db,dbtbl_with_readwrite_splitting,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 (SELECT order_id_sharding, user_id FROM t_order_federate_sharding WHERE order_id_sharding = 1010) AS TEMP" scenario-types="db,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 AVG(order_id_sharding) AS order_id_sharding_avg FROM (SELECT order_id_sharding, user_id FROM t_order_federate_sharding WHERE order_id_sharding = 1010) AS TEMP" scenario-types="db,dbtbl_with_readwrite_splitting_and_encrypt,sharding_and_encrypt,encrypt_and_readwrite_splitting" db-types="MySQL">
- <assertion expected-data-source-name="read_dataset" />
- </test-case>
-
- <test-case sql="SELECT order_id_sharding AS order_id, user_id, status FROM t_order_federate_sharding WHERE order_id_sharding = ? UNION ALL SELECT order_id, user_id, status FROM t_order_item_federate_sharding WHERE user_id = ?" scenario-types="db,dbtbl_with_readwrite_splitting,dbtbl_with_readwrite_splitting_and_encrypt,sharding_and_encrypt,encrypt_and_readwrite_splitting,tbl">
- <assertion parameters="1010:int, 10:int" expected-data-source-name="read_dataset" />
- </test-case>
-
- <!-- TODO complete test type -->
- <test-case sql="SELECT order_id, user_id, order_name, type_char, type_boolean, type_smallint, type_enum, type_decimal, type_date, type_time, type_timestamp FROM t_shadow WHERE user_id = ?" db-types="MySQL,PostgreSQL" scenario-types="shadow">
- <assertion parameters="1:int" expected-data-source-name="prod_dataset" />
- <assertion parameters="0:int" expected-data-source-name="shadow_dataset" />
- </test-case>
-
- <test-case sql="SELECT order_id, user_id, order_name, type_char, type_boolean, type_smallint, type_enum, type_decimal, type_date, type_time, type_timestamp FROM t_shadow WHERE user_id = ?" db-types="MySQL,PostgreSQL" scenario-types="encrypt_shadow">
- <assertion parameters="1:int" expected-data-source-name="prod_dataset" />
- <assertion parameters="0:int" expected-data-source-name="encrypt_shadow_dataset" />
- </test-case>
-
- <test-case sql="SELECT order_id, user_id, order_name, type_char, type_boolean, type_smallint, type_enum, type_decimal, type_date, type_time, type_timestamp FROM t_shadow WHERE user_id = ?" db-types="MySQL,PostgreSQL" scenario-types="readwrite_splitting_and_shadow">
- <assertion parameters="1:int" expected-data-source-name="read_dataset" />
- </test-case>
-
- <test-case sql="SELECT order_id, user_id, order_name, type_char, type_boolean, type_smallint, type_enum, type_decimal, type_date, type_time, type_timestamp FROM t_shadow WHERE user_id = ?" db-types="PostgreSQL" scenario-types="sharding_and_shadow,sharding_encrypt_shadow">
- <assertion parameters="1:int" expected-data-source-name="prod_dataset" />
- <assertion parameters="0:int" expected-data-source-name="shadow_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 ON o.order_id = i.order_id WHERE o.user_id = ? ORDER BY o.order_id, 7 LIMIT 5, 2" db-types="MySQL,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">
- <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 LIMIT 5, 2" db-types="MySQL,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">
- <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 LIMIT 5, 2" db-types="MySQL,openGauss" scenario-types="db">
- <assertion parameters="10:int" expected-data-source-name="read_dataset" />
- </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 = ? GROUP BY i.product_id" db-types="MySQL,PostgreSQL,openGauss" scenario-types="db">
- <assertion parameters="10:int" expected-data-source-name="read_dataset" />
- </test-case>
-
- <!-- FIXME: In MySQL, JOIN, CROSS JOIN, and INNER JOIN are syntactic equivalents -->
- <test-case sql="SELECT * FROM t_order o CROSS JOIN t_order_item i WHERE o.user_id = ? ORDER BY o.order_id, 7 LIMIT 10, 10" db-types="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">
- <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">
- <assertion parameters="10: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 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 INNER JOIN t_merchant m ON o.merchant_id = m.merchant_id WHERE o.user_id = ? ORDER BY o.order_id LIMIT 5, 2" db-types="MySQL,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>
-
- <!-- 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>
-
- <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 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 INNER JOIN t_merchant m USING(merchant_id) WHERE o.user_id = ? ORDER BY o.order_id LIMIT 5, 2" db-types="MySQL,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">
- <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">
- <assertion parameters="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 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 NATURAL JOIN t_merchant m WHERE o.user_id = ? ORDER BY o.order_id LIMIT 5, 2" db-types="MySQL,openGauss" scenario-types="db">
- <assertion parameters="10:int" expected-data-source-name="read_dataset" />
- </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 = ? GROUP BY m.merchant_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 CROSS JOIN t_merchant m WHERE o.user_id = ? ORDER BY o.order_id, 7 LIMIT 10, 10" db-types="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">
- <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">
- <assertion 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">
- <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 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_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 LIMIT 2, 5" db-types="MySQL,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">
- <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>
-
- <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>
-
- <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_product p INNER JOIN t_product_detail d USING(product_id) WHERE p.product_id > ? ORDER BY p.product_id DESC LIMIT 2, 5" db-types="MySQL,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 FETCH NEXT 3 ROW ONLY" db-types="PostgreSQL,openGauss" scenario-types="db"
- scenario-comments="Test select inner join fetch statement when use sharding feature and federation executor engine.">
- <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">
- <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>
-
- <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_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 NATURAL JOIN t_product_detail d WHERE p.product_id > ? ORDER BY p.product_id DESC LIMIT 2, 5" db-types="MySQL,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 FETCH NEXT 3 ROW ONLY" db-types="openGauss" scenario-types="db"
- scenario-comments="Test select natural join fetch statement when use sharding feature and federation executor engine.">
- <assertion parameters="10:int" expected-data-source-name="read_dataset" />
- </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 = ? GROUP BY p.product_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_product p CROSS JOIN t_product_detail d WHERE p.product_id = ? ORDER BY d.product_id, 7 LIMIT 10, 10" db-types="openGauss" scenario-types="db">
- <assertion parameters="10:int" expected-data-source-name="read_dataset" />
- </test-case>
-
- <test-case sql="SELECT * FROM t_product p CROSS JOIN t_product_detail d WHERE p.product_id = ? ORDER BY d.product_id, 7 FETCH NEXT 3 ROW ONLY" db-types="PostgreSQL,openGauss" scenario-types="db"
- scenario-comments="Test select cross join fetch statement when use sharding feature and federation executor engine.">
- <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">
- <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">
- <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>
-
- <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>
-
- <!-- 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_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_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>
-
- <!-- 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_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 o.order_id, o.user_id, o.status, o.merchant_id, (SELECT t.merchant_name FROM t_merchant t WHERE t.merchant_id = o.merchant_id) AS merchant_name FROM t_order o WHERE o.order_id = ?" db-types="MySQL,PostgreSQL,openGauss" scenario-types="db">
- <assertion parameters="1000:long" expected-data-source-name="read_dataset" />
- </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 > ? ORDER BY item_id" db-types="MySQL,PostgreSQL,openGauss" scenario-types="db">
- <assertion parameters="1200:long" expected-data-source-name="read_dataset" />
- </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 = ?) ORDER BY 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 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) > ? ORDER BY max_price" db-types="MySQL,PostgreSQL,openGauss" scenario-types="db">
- <assertion parameters="10000:int" expected-data-source-name="read_dataset" />
- </test-case>
-
- <test-case sql="SELECT * FROM t_order UNION ALL SELECT * FROM t_order ORDER BY order_id LIMIT 5, 5" db-types="MySQL,openGauss" scenario-types="db">
- <assertion expected-data-source-name="read_dataset" />
- </test-case>
-
- <test-case sql="SELECT * FROM t_order WHERE order_id > ? UNION SELECT * FROM t_order WHERE order_id > ? ORDER BY order_id LIMIT 5, 5" db-types="MySQL,openGauss" scenario-types="db">
- <assertion parameters="2000:long, 1500:long" expected-data-source-name="read_dataset" />
- </test-case>
-
- <test-case sql="SELECT o.order_id, o.user_id FROM t_order o WHERE o.order_id > ? UNION ALL SELECT i.order_id, i.user_id FROM t_order_item i WHERE i.order_id > ? ORDER BY order_id LIMIT 5, 5" db-types="MySQL,openGauss" scenario-types="db">
- <assertion parameters="2500:long, 2500:long" expected-data-source-name="read_dataset" />
- </test-case>
-
- <test-case sql="SELECT o.order_id, o.user_id FROM t_order o WHERE o.order_id > ? UNION SELECT i.order_id, i.user_id FROM t_order_item i WHERE i.order_id > ? ORDER BY order_id" db-types="MySQL,PostgreSQL,openGauss" scenario-types="db">
- <assertion parameters="2500:long, 2500:long" expected-data-source-name="read_dataset" />
- </test-case>
-
- <test-case sql="SELECT o.user_id FROM t_order o WHERE o.order_id > ? UNION ALL SELECT u.user_id FROM t_user u ORDER BY user_id LIMIT 5, 5" db-types="MySQL,openGauss" scenario-types="db">
- <assertion parameters="2500:long" expected-data-source-name="read_dataset" />
- </test-case>
-
- <test-case sql="SELECT o.user_id FROM t_order o WHERE o.order_id > ? UNION ALL SELECT u.user_id FROM t_user u ORDER BY user_id FETCH NEXT 3 ROW ONLY" db-types="PostgreSQL,openGauss" scenario-types="db"
- scenario-comments="Test select union all fetch statement when use sharding feature and federation executor engine.">
- <assertion parameters="2500:long" expected-data-source-name="read_dataset" />
- </test-case>
-
- <test-case sql="SELECT o.user_id FROM t_order o WHERE o.order_id > ? UNION SELECT u.user_id FROM t_user u ORDER BY user_id" db-types="MySQL,PostgreSQL,openGauss" scenario-types="db">
- <assertion parameters="2500:long" expected-data-source-name="read_dataset" />
- </test-case>
-
- <!-- TODO support MySQL INTERSECT clause -->
- <test-case sql="SELECT * FROM t_order INTERSECT ALL SELECT * FROM t_order ORDER BY order_id LIMIT 5, 5" db-types="openGauss" scenario-types="db">
- <assertion expected-data-source-name="read_dataset" />
- </test-case>
-
- <test-case sql="SELECT * FROM t_order INTERSECT ALL SELECT * FROM t_order ORDER BY order_id FETCH NEXT 3 ROW ONLY" db-types="PostgreSQL,openGauss" scenario-types="db"
- scenario-comments="Test select intersect all fetch statement when use sharding feature and federation executor engine.">
- <assertion expected-data-source-name="read_dataset" />
- </test-case>
-
- <test-case sql="SELECT * FROM t_order WHERE order_id > ? INTERSECT SELECT * FROM t_order WHERE order_id > ? ORDER BY order_id" db-types="PostgreSQL,openGauss" scenario-types="db">
- <assertion parameters="2000:long, 1500:long" expected-data-source-name="read_dataset" />
- </test-case>
-
- <test-case sql="SELECT o.order_id, o.user_id FROM t_order o WHERE o.order_id > ? INTERSECT ALL SELECT i.order_id, i.user_id FROM t_order_item i WHERE i.order_id > ? ORDER BY order_id" db-types="PostgreSQL,openGauss" scenario-types="db">
- <assertion parameters="2500:long, 2500:long" expected-data-source-name="read_dataset" />
- </test-case>
-
- <test-case sql="SELECT o.order_id, o.user_id FROM t_order o WHERE o.order_id > ? INTERSECT SELECT i.order_id, i.user_id FROM t_order_item i WHERE i.order_id > ? ORDER BY order_id" db-types="PostgreSQL,openGauss" scenario-types="db">
- <assertion parameters="2500:long, 2500:long" expected-data-source-name="read_dataset" />
- </test-case>
-
- <test-case sql="SELECT o.user_id FROM t_order o WHERE o.order_id > ? INTERSECT ALL SELECT u.user_id FROM t_user u ORDER BY user_id" db-types="PostgreSQL,openGauss" scenario-types="db">
- <assertion parameters="2500:long" expected-data-source-name="read_dataset" />
- </test-case>
-
- <test-case sql="SELECT o.user_id FROM t_order o WHERE o.order_id > ? INTERSECT SELECT u.user_id FROM t_user u ORDER BY user_id" db-types="PostgreSQL,openGauss" scenario-types="db">
- <assertion parameters="2500:long" expected-data-source-name="read_dataset" />
- </test-case>
-
- <!-- TODO support MySQL EXCEPT clause -->
- <test-case sql="SELECT * FROM t_order EXCEPT ALL SELECT * FROM t_order ORDER BY order_id" db-types="PostgreSQL,openGauss" scenario-types="db">
- <assertion expected-data-source-name="read_dataset" />
- </test-case>
-
- <test-case sql="SELECT * FROM t_order WHERE order_id > ? EXCEPT SELECT * FROM t_order WHERE order_id > ? ORDER BY order_id" db-types="PostgreSQL,openGauss" scenario-types="db">
- <assertion parameters="2000:long, 1500:long" expected-data-source-name="read_dataset" />
- </test-case>
-
- <test-case sql="SELECT o.order_id, o.user_id FROM t_order o WHERE o.order_id > ? EXCEPT ALL SELECT i.order_id, i.user_id FROM t_order_item i WHERE i.order_id > ? ORDER BY order_id" db-types="PostgreSQL,openGauss" scenario-types="db">
- <assertion parameters="2500:long, 2500:long" expected-data-source-name="read_dataset" />
- </test-case>
-
- <test-case sql="SELECT o.order_id, o.user_id FROM t_order o WHERE o.order_id > ? EXCEPT SELECT i.order_id, i.user_id FROM t_order_item i WHERE i.order_id > ? ORDER BY order_id" db-types="PostgreSQL,openGauss" scenario-types="db">
- <assertion parameters="2500:long, 2500:long" expected-data-source-name="read_dataset" />
- </test-case>
-
- <test-case sql="SELECT o.user_id FROM t_order o WHERE o.order_id > ? EXCEPT ALL SELECT u.user_id FROM t_user u ORDER BY user_id" db-types="PostgreSQL,openGauss" scenario-types="db">
- <assertion parameters="2500:long" expected-data-source-name="read_dataset" />
- </test-case>
-
- <test-case sql="SELECT o.user_id FROM t_order o WHERE o.order_id > ? EXCEPT SELECT u.user_id FROM t_user u ORDER BY user_id" db-types="PostgreSQL,openGauss" scenario-types="db">
- <assertion parameters="2500:long" expected-data-source-name="read_dataset" />
- </test-case>
-
- <test-case sql="SELECT * FROM t_order MINUS ALL SELECT * FROM t_order ORDER BY order_id" db-types="openGauss" scenario-types="db">
- <assertion expected-data-source-name="read_dataset" />
- </test-case>
-
- <test-case sql="SELECT * FROM t_order WHERE order_id > ? MINUS SELECT * FROM t_order WHERE order_id > ? ORDER BY order_id" db-types="openGauss" scenario-types="db">
- <assertion parameters="2000:long, 1500:long" expected-data-source-name="read_dataset" />
- </test-case>
-
- <test-case sql="SELECT o.order_id, o.user_id FROM t_order o WHERE o.order_id > ? MINUS ALL SELECT i.order_id, i.user_id FROM t_order_item i WHERE i.order_id > ? ORDER BY order_id" db-types="openGauss" scenario-types="db">
- <assertion parameters="2500:long, 2500:long" expected-data-source-name="read_dataset" />
- </test-case>
-
- <test-case sql="SELECT o.order_id, o.user_id FROM t_order o WHERE o.order_id > ? MINUS SELECT i.order_id, i.user_id FROM t_order_item i WHERE i.order_id > ? ORDER BY order_id" db-types="openGauss" scenario-types="db">
- <assertion parameters="2500:long, 2500:long" expected-data-source-name="read_dataset" />
- </test-case>
-
- <test-case sql="SELECT o.user_id FROM t_order o WHERE o.order_id > ? MINUS ALL SELECT u.user_id FROM t_user u ORDER BY user_id" db-types="openGauss" scenario-types="db">
- <assertion parameters="2500:long" expected-data-source-name="read_dataset" />
- </test-case>
-
- <test-case sql="SELECT o.user_id FROM t_order o WHERE o.order_id > ? MINUS SELECT u.user_id FROM t_user u ORDER BY user_id" db-types="openGauss" scenario-types="db">
- <assertion parameters="2500:long" expected-data-source-name="read_dataset" />
- </test-case>
-
- <test-case sql="SELECT * FROM t_order WHERE order_id = ? UNION SELECT * FROM t_order INTERSECT SELECT * FROM t_order WHERE order_id = ? ORDER BY order_id" db-types="PostgreSQL,openGauss" scenario-types="db">
- <assertion parameters="2500:long,2900:long" expected-data-source-name="read_dataset" />
- </test-case>
-
- <test-case sql="(SELECT * FROM t_order WHERE order_id = ? UNION SELECT * FROM t_order) INTERSECT SELECT * FROM t_order WHERE order_id = ? ORDER BY order_id" db-types="PostgreSQL,openGauss" scenario-types="db">
- <assertion parameters="2500:long,2900:long" expected-data-source-name="read_dataset" />
- </test-case>
-
- <test-case sql="SELECT * FROM t_order WHERE order_id = ? UNION SELECT * FROM t_order EXCEPT SELECT * FROM t_order WHERE order_id = ? ORDER BY order_id" db-types="PostgreSQL,openGauss" scenario-types="db">
- <assertion parameters="2500:long,2900:long" expected-data-source-name="read_dataset" />
- </test-case>
-
- <test-case sql="SELECT * FROM t_order WHERE order_id = ? UNION (SELECT * FROM t_order EXCEPT SELECT * FROM t_order WHERE order_id = ?) ORDER BY order_id" db-types="PostgreSQL,openGauss" scenario-types="db">
- <assertion parameters="2500:long,2900:long" expected-data-source-name="read_dataset" />
- </test-case>
-
- <test-case sql="SELECT * FROM t_order WHERE order_id = ? UNION SELECT * FROM t_order MINUS SELECT * FROM t_order WHERE order_id = ? ORDER BY order_id" db-types="openGauss" scenario-types="db">
- <assertion parameters="2500:long,2900:long" expected-data-source-name="read_dataset" />
- </test-case>
-
- <test-case sql="SELECT * FROM t_order WHERE order_id = ? UNION (SELECT * FROM t_order MINUS SELECT * FROM t_order WHERE order_id = ?) ORDER BY order_id" db-types="openGauss" scenario-types="db">
- <assertion parameters="2500:long,2900:long" expected-data-source-name="read_dataset" />
- </test-case>
-
- <test-case sql="SELECT * FROM t_order WHERE order_id = ? UNION SELECT * FROM t_order INTERSECT SELECT * FROM t_order WHERE order_id = ? EXCEPT SELECT * FROM t_order WHERE order_id = ? ORDER BY order_id" db-types="PostgreSQL,openGauss" scenario-types="db">
- <assertion parameters="2500:long,2900:long,1000:long" expected-data-source-name="read_dataset" />
- </test-case>
-
- <test-case sql="(SELECT * FROM t_order WHERE order_id = ? UNION SELECT * FROM t_order) INTERSECT (SELECT * FROM t_order WHERE order_id = ? EXCEPT SELECT * FROM t_order WHERE order_id = ?) ORDER BY order_id" db-types="PostgreSQL,openGauss" scenario-types="db">
- <assertion parameters="2500:long,2900:long,1000:long" expected-data-source-name="read_dataset" />
- </test-case>
-
- <test-case sql="SELECT * FROM t_order WHERE order_id = ? UNION SELECT * FROM t_order INTERSECT SELECT * FROM t_order WHERE order_id = ? MINUS SELECT * FROM t_order WHERE order_id = ? ORDER BY order_id" db-types="openGauss" scenario-types="db">
- <assertion parameters="2500:long,2900:long,1000:long" expected-data-source-name="read_dataset" />
- </test-case>
-
- <test-case sql="(SELECT * FROM t_order WHERE order_id = ? UNION SELECT * FROM t_order) INTERSECT (SELECT * FROM t_order WHERE order_id = ? MINUS SELECT * FROM t_order WHERE order_id = ?) ORDER BY order_id" db-types="openGauss" scenario-types="db">
- <assertion parameters="2500:long,2900:long,1000:long" expected-data-source-name="read_dataset" />
- </test-case>
-
- <test-case sql="SELECT * FROM t_order EXCEPT SELECT * FROM t_order WHERE order_id = ? INTERSECT SELECT * FROM t_order WHERE order_id = ? ORDER BY order_id" db-types="PostgreSQL,openGauss" scenario-types="db">
- <assertion parameters="2500:long,2900:long" expected-data-source-name="read_dataset" />
- </test-case>
-
- <test-case sql="(SELECT * FROM t_order EXCEPT SELECT * FROM t_order WHERE order_id = ?) INTERSECT SELECT * FROM t_order WHERE order_id = ? ORDER BY order_id" db-types="PostgreSQL,openGauss" scenario-types="db">
- <assertion parameters="2500:long,2900:long" expected-data-source-name="read_dataset" />
- </test-case>
-
- <test-case sql="SELECT * FROM t_order MINUS SELECT * FROM t_order WHERE order_id = ? INTERSECT SELECT * FROM t_order WHERE order_id = ? ORDER BY order_id" db-types="openGauss" scenario-types="db">
- <assertion parameters="2500:long,2900:long" expected-data-source-name="read_dataset" />
- </test-case>
-
- <test-case sql="(SELECT * FROM t_order MINUS SELECT * FROM t_order WHERE order_id = ?) INTERSECT SELECT * FROM t_order WHERE order_id = ? ORDER BY order_id" db-types="openGauss" scenario-types="db">
- <assertion parameters="2500:long,2900:long" expected-data-source-name="read_dataset" />
- </test-case>
-
- <test-case sql="SELECT * FROM t_order EXCEPT SELECT * FROM t_order WHERE order_id = ? UNION SELECT * FROM t_order WHERE order_id = ? ORDER BY order_id" db-types="PostgreSQL,openGauss" scenario-types="db">
- <assertion parameters="2500:long,2900:long" expected-data-source-name="read_dataset" />
- </test-case>
-
- <test-case sql="SELECT * FROM t_order EXCEPT (SELECT * FROM t_order WHERE order_id = ? UNION SELECT * FROM t_order WHERE order_id = ?) ORDER BY order_id" db-types="PostgreSQL,openGauss" scenario-types="db">
- <assertion parameters="2500:long,2900:long" expected-data-source-name="read_dataset" />
- </test-case>
-
- <test-case sql="SELECT * FROM t_order MINUS SELECT * FROM t_order WHERE order_id = ? UNION SELECT * FROM t_order WHERE order_id = ? ORDER BY order_id" db-types="openGauss" scenario-types="db">
- <assertion parameters="2500:long,2900:long" expected-data-source-name="read_dataset" />
- </test-case>
-
- <test-case sql="SELECT * FROM t_order MINUS (SELECT * FROM t_order WHERE order_id = ? UNION SELECT * FROM t_order WHERE order_id = ?) ORDER BY order_id" db-types="openGauss" scenario-types="db">
- <assertion parameters="2500:long,2900:long" expected-data-source-name="read_dataset" />
- </test-case>
-
- <test-case sql="SELECT * FROM t_order EXCEPT SELECT * FROM t_order INTERSECT SELECT * FROM t_order WHERE order_id = ? UNION SELECT * FROM t_order WHERE order_id = ? ORDER BY order_id" db-types="PostgreSQL,openGauss" scenario-types="db">
- <assertion parameters="2500:long,2900:long" expected-data-source-name="read_dataset" />
- </test-case>
-
- <test-case sql="(SELECT * FROM t_order EXCEPT SELECT * FROM t_order) INTERSECT (SELECT * FROM t_order WHERE order_id = ? UNION SELECT * FROM t_order WHERE order_id = ?) ORDER BY order_id" db-types="PostgreSQL,openGauss" scenario-types="db">
- <assertion parameters="2500:long,2900:long" expected-data-source-name="read_dataset" />
- </test-case>
-
- <test-case sql="SELECT * FROM t_order MINUS SELECT * FROM t_order INTERSECT SELECT * FROM t_order WHERE order_id = ? UNION SELECT * FROM t_order WHERE order_id = ? ORDER BY order_id" db-types="openGauss" scenario-types="db">
- <assertion parameters="2500:long,2900:long" expected-data-source-name="read_dataset" />
- </test-case>
-
- <test-case sql="(SELECT * FROM t_order MINUS SELECT * FROM t_order) INTERSECT (SELECT * FROM t_order WHERE order_id = ? UNION SELECT * FROM t_order WHERE order_id = ?) ORDER BY order_id" db-types="openGauss" scenario-types="db">
- <assertion parameters="2500:long,2900:long" expected-data-source-name="read_dataset" />
- </test-case>
-
-<!-- TODO FIX ME Expected: is "true" but was "1"-->
-<!-- <test-case sql="SELECT order_id, user_id, order_name, type_char, type_boolean, type_smallint, type_enum, type_decimal, type_date, type_time, type_timestamp FROM t_shadow WHERE user_id = ?" db-types="MySQL" scenario-types="sharding_and_shadow">-->
-<!-- <assertion parameters="1:int" expected-data-source-name="prod_dataset" />-->
-<!-- <assertion parameters="0:int" expected-data-source-name="shadow_dataset" />-->
-<!-- </test-case>-->
-
- <test-case sql="SELECT * FROM t_order_item_join_view" db-types="MySQL,PostgreSQL,openGauss" scenario-types="db">
- <assertion expected-data-source-name="read_dataset" />
- </test-case>
-
- <test-case sql="SELECT * FROM t_order_item_join_view WHERE order_id > ?" db-types="MySQL,PostgreSQL,openGauss" scenario-types="db">
- <assertion parameters="1000:long" expected-data-source-name="read_dataset" />
- </test-case>
-
- <test-case sql="SELECT * FROM t_order_item_join_view LIMIT 5, 2" db-types="MySQL,openGauss" scenario-types="db">
- <assertion expected-data-source-name="read_dataset" />
- </test-case>
-
- <test-case sql="SELECT * FROM t_order_item_join_view FETCH NEXT 3 ROW ONLY" db-types="PostgreSQL,openGauss" scenario-types="db"
- scenario-comments="Test select ... from join view fetch statement when use sharding feature and federation executor engine.">
- <assertion expected-data-source-name="read_dataset" />
- </test-case>
-
- <test-case sql="SELECT * FROM t_order_subquery_view ORDER BY order_id" db-types="MySQL,PostgreSQL,openGauss" scenario-types="db">
- <assertion expected-data-source-name="read_dataset" />
- </test-case>
-
- <test-case sql="SELECT * FROM t_order_subquery_view WHERE order_id = ?" db-types="MySQL,PostgreSQL,openGauss" scenario-types="db">
- <assertion parameters="1201:long" expected-data-source-name="read_dataset" />
- </test-case>
-
- <test-case sql="SELECT * FROM t_order_subquery_view ORDER BY order_id LIMIT 5, 2" db-types="MySQL,openGauss" scenario-types="db">
- <assertion expected-data-source-name="read_dataset" />
- </test-case>
-
- <test-case sql="SELECT * FROM t_order_subquery_view ORDER BY order_id FETCH NEXT 3 ROW ONLY" db-types="PostgreSQL,openGauss" scenario-types="db"
- scenario-comments="Test select ... from subquery view fetch statement when use sharding feature and federation executor engine.">
- <assertion expected-data-source-name="read_dataset" />
- </test-case>
-
- <test-case sql="SELECT * FROM t_order_aggregation_view" db-types="MySQL,PostgreSQL,openGauss" scenario-types="db">
- <assertion expected-data-source-name="read_dataset" />
- </test-case>
-
- <test-case sql="SELECT * FROM t_order_aggregation_view WHERE max_price = ?" db-types="MySQL,PostgreSQL,openGauss" scenario-types="db">
- <assertion parameters="8200:int" expected-data-source-name="read_dataset" />
- </test-case>
-
- <test-case sql="SELECT * FROM t_order_aggregation_view LIMIT 5, 2" db-types="MySQL,openGauss" scenario-types="db">
- <assertion expected-data-source-name="read_dataset" />
- </test-case>
-
- <test-case sql="SELECT * FROM t_order_aggregation_view FETCH NEXT 3 ROW ONLY" db-types="PostgreSQL,openGauss" scenario-types="db"
- scenario-comments="Test select ... from aggregation view fetch statement when use sharding feature and federation executor engine.">
- <assertion expected-data-source-name="read_dataset" />
- </test-case>
-
- <test-case sql="SELECT * FROM t_order_union_view ORDER BY order_id" db-types="MySQL,PostgreSQL,openGauss" scenario-types="db">
- <assertion expected-data-source-name="read_dataset" />
- </test-case>
-
- <test-case sql="SELECT * FROM t_order_union_view WHERE order_id = ?" db-types="MySQL,PostgreSQL,openGauss" scenario-types="db">
- <assertion parameters="2500:long" expected-data-source-name="read_dataset" />
- </test-case>
-
- <test-case sql="SELECT * FROM t_order_union_view ORDER BY order_id LIMIT 5, 2" db-types="MySQL,openGauss" scenario-types="db">
- <assertion expected-data-source-name="read_dataset" />
- </test-case>
-
- <test-case sql="SELECT * FROM t_order_union_view ORDER BY order_id FETCH NEXT 3 ROW ONLY" db-types="PostgreSQL,openGauss" scenario-types="db"
- scenario-comments="Test select ... from union view fetch statement when use sharding feature and federation executor engine.">
- <assertion expected-data-source-name="read_dataset" />
- </test-case>
-
- <test-case sql="SELECT * FROM t_order ORDER BY merchant_id ASC, order_id ASC" db-types="MySQL,PostgreSQL,openGauss,Oracle,SQLServer" scenario-types="db">
- <assertion expected-data-source-name="read_dataset" />
- </test-case>
-
- <test-case sql="SELECT * FROM t_order ORDER BY merchant_id DESC, order_id DESC" db-types="MySQL,PostgreSQL,openGauss,Oracle,SQLServer" scenario-types="db">
- <assertion expected-data-source-name="read_dataset" />
- </test-case>
-
- <test-case sql="SELECT * FROM t_order ORDER BY merchant_id ASC, order_id ASC NULLS FIRST" db-types="PostgreSQL,openGauss,Oracle" scenario-types="db">
- <assertion expected-data-source-name="read_dataset" />
- </test-case>
-
- <test-case sql="SELECT * FROM t_order ORDER BY merchant_id ASC, order_id ASC NULLS LAST" db-types="PostgreSQL,openGauss,Oracle" scenario-types="db">
- <assertion expected-data-source-name="read_dataset" />
- </test-case>
-
- <test-case sql="SELECT * FROM t_order ORDER BY merchant_id DESC, order_id DESC NULLS FIRST" db-types="PostgreSQL,openGauss,Oracle" scenario-types="db">
- <assertion expected-data-source-name="read_dataset" />
- </test-case>
-
- <test-case sql="SELECT * FROM t_order ORDER BY merchant_id DESC, order_id DESC NULLS LAST" db-types="PostgreSQL,openGauss,Oracle" scenario-types="db">
- <assertion expected-data-source-name="read_dataset" />
- </test-case>
-
- <test-case sql="SELECT merchant_id, COUNT(1) AS count FROM t_order GROUP BY merchant_id ORDER BY merchant_id ASC" db-types="MySQL,PostgreSQL,openGauss,Oracle,SQLServer" scenario-types="db">
- <assertion expected-data-source-name="read_dataset" />
- </test-case>
-
- <test-case sql="SELECT merchant_id, COUNT(1) AS count FROM t_order GROUP BY merchant_id ORDER BY merchant_id DESC" db-types="MySQL,PostgreSQL,openGauss,Oracle,SQLServer" scenario-types="db">
- <assertion expected-data-source-name="read_dataset" />
- </test-case>
-
- <test-case sql="SELECT merchant_id, COUNT(1) AS count FROM t_order GROUP BY merchant_id ORDER BY order_id ASC" db-types="MySQL" scenario-types="db">
- <assertion expected-data-source-name="read_dataset" />
- </test-case>
-
- <test-case sql="SELECT merchant_id, COUNT(1) AS count FROM t_order GROUP BY merchant_id ORDER BY order_id DESC" db-types="MySQL" scenario-types="db">
- <assertion expected-data-source-name="read_dataset" />
- </test-case>
-
- <test-case sql="SELECT merchant_id, COUNT(1) AS count FROM t_order GROUP BY merchant_id ORDER BY merchant_id ASC NULLS FIRST" db-types="PostgreSQL,openGauss,Oracle" scenario-types="db">
- <assertion expected-data-source-name="read_dataset" />
- </test-case>
-
- <test-case sql="SELECT merchant_id, COUNT(1) AS count FROM t_order GROUP BY merchant_id ORDER BY merchant_id ASC NULLS LAST" db-types="PostgreSQL,openGauss,Oracle" scenario-types="db">
- <assertion expected-data-source-name="read_dataset" />
- </test-case>
-
- <test-case sql="SELECT merchant_id, COUNT(1) AS count FROM t_order GROUP BY merchant_id ORDER BY merchant_id DESC NULLS FIRST" db-types="PostgreSQL,openGauss,Oracle" scenario-types="db">
- <assertion expected-data-source-name="read_dataset" />
- </test-case>
-
- <test-case sql="SELECT merchant_id, COUNT(1) AS count FROM t_order GROUP BY merchant_id ORDER BY merchant_id DESC NULLS LAST" db-types="PostgreSQL,openGauss,Oracle" scenario-types="db">
- <assertion expected-data-source-name="read_dataset" />
- </test-case>
-
- <test-case sql="SELECT * FROM t_order o INNER JOIN t_order_item i ON o.order_id = i.order_id ORDER BY o.order_id ASC NULLS FIRST, i.item_id DESC" db-types="PostgreSQL,openGauss,Oracle" scenario-types="db">
- <assertion expected-data-source-name="read_dataset" />
- </test-case>
-
- <test-case sql="SELECT * FROM t_order o INNER JOIN t_order_item i ON o.order_id = i.order_id ORDER BY o.order_id ASC NULLS LAST, i.item_id DESC" db-types="PostgreSQL,openGauss,Oracle" scenario-types="db">
- <assertion 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">
- <assertion expected-data-source-name="read_dataset" />
- </test-case>
-
- <test-case sql="SELECT * FROM (SELECT count(*) as count, id from t_single_table group by id) as temp_table" db-types="MySQL" scenario-types="db">
- <assertion expected-data-source-name="read_dataset" />
- </test-case>
-
- <test-case sql="SELECT * FROM t_merchant WHERE business_code LIKE '%18'" db-types="MySQL,PostgreSQL,openGauss" scenario-types="db,encrypt"
- scenario-comments="Test single table's LIKE operator percentage wildcard in simple select statement when use sharding feature.|Test encrypt table's LIKE operator percentage wildcard in simple select statement when use encrypt feature.">
- <assertion expected-data-source-name="read_dataset" />
- </test-case>
-
- <test-case sql="SELECT * FROM t_merchant WHERE business_code like '%18'" db-types="MySQL,PostgreSQL,openGauss" scenario-types="db,encrypt"
- scenario-comments="Test single table's lower case like operator percentage wildcard in simple select statement when use sharding feature.|Test encrypt table's lower case like operator percentage wildcard in simple select statement when use encrypt feature.">
- <assertion expected-data-source-name="read_dataset" />
- </test-case>
-
- <test-case sql="SELECT * FROM t_merchant WHERE business_code LIKE '_1000018'" db-types="MySQL,PostgreSQL,openGauss" scenario-types="db,encrypt"
- scenario-comments="Test single table's LIKE operator underscore wildcard in simple select statement when use sharding feature.|Test encrypt table's LIKE operator underscore wildcard in simple select statement when use encrypt feature.">
- <assertion expected-data-source-name="read_dataset" />
- </test-case>
-
- <test-case sql="SELECT * FROM t_merchant WHERE merchant_id IN (SELECT merchant_id FROM t_merchant WHERE business_code LIKE '%18')" db-types="MySQL,PostgreSQL,openGauss" scenario-types="db,encrypt"
- scenario-comments="Test single table's LIKE operator percentage wildcard in subquery select statement when use sharding feature.|Test encrypt table's LIKE operator percentage wildcard in subquery select statement when use encrypt feature.">
- <assertion expected-data-source-name="read_dataset" />
- </test-case>
-
- <test-case sql="SELECT * FROM t_merchant WHERE merchant_id IN (SELECT merchant_id FROM t_merchant WHERE business_code LIKE '_1000018')" db-types="MySQL,PostgreSQL,openGauss" scenario-types="db,encrypt"
- scenario-comments="Test single table's LIKE operator underscore wildcard in subquery select statement when use sharding feature.|Test encrypt table's LIKE operator underscore wildcard in subquery select statement when use encrypt feature.">
- <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 '%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_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 country_id, COUNT(1) FROM t_merchant WHERE business_code LIKE '%18' GROUP BY country_id" db-types="MySQL,PostgreSQL,openGauss" scenario-types="db,encrypt"
- scenario-comments="Test single table's LIKE operator percentage wildcard in select group by statement when use sharding feature.|Test encrypt table's LIKE operator percentage wildcard in select group by statement when use encrypt feature.">
- <assertion expected-data-source-name="read_dataset" />
- </test-case>
-
- <test-case sql="SELECT country_id, COUNT(1) FROM t_merchant WHERE business_code LIKE '_1000018' GROUP BY country_id" db-types="MySQL,PostgreSQL,openGauss" scenario-types="db,encrypt"
- scenario-comments="Test single table's LIKE operator underscore wildcard in select group by statement when use sharding feature.|Test encrypt table's LIKE operator underscore wildcard in select group by statement when use encrypt feature.">
- <assertion expected-data-source-name="read_dataset" />
- </test-case>
-
- <test-case sql="SELECT * FROM (SELECT * FROM t_merchant) temp" db-types="MySQL,PostgreSQL,openGauss" scenario-types="encrypt"
- scenario-comments="Test encrypt shorthand expansion for subquery with simple select statement when use encrypt feature.">
- <assertion expected-data-source-name="read_dataset" />
- </test-case>
-
- <test-case sql="SELECT * FROM (SELECT m1.* FROM t_merchant m1 INNER JOIN t_merchant m2 ON m1.merchant_id = m2.merchant_id) temp" db-types="MySQL,PostgreSQL,openGauss" scenario-types="encrypt"
- scenario-comments="Test encrypt shorthand expansion for subquery with select join statement when use encrypt feature.">
- <assertion expected-data-source-name="read_dataset" />
- </test-case>
-
- <test-case sql="SELECT * FROM t_data_type_integer_unsigned" db-types="MySQL" scenario-types="passthrough" scenario-comments="Test ShardingSphere-Proxy MySQL compatibility for unsigned integer data types">
- <assertion expected-data-source-name="expected_dataset" />
- </test-case>
-
- <test-case sql="INSERT INTO t_with_generated_id (val) values (?) RETURNING *, id, val aliased_val, t_with_generated_id" db-types="PostgreSQL,openGauss" scenario-types="passthrough"
- scenario-comments="Test ShardingSphere-Proxy compatibility for PostgreSQL/openGauss insert returning clause">
- <assertion parameters="foo:String" expected-data-source-name="expected_dataset" />
- </test-case>
-
- <test-case sql="SELECT * FROM t_user 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 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 (SELECT * FROM t_user) temp 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 (SELECT u1.* FROM t_user u1 INNER JOIN t_user u2 ON u1.user_id = u2.user_id) temp 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_data_type_money" db-types="PostgreSQL,openGauss" scenario-types="passthrough"
- scenario-comments="Test ShardingSphere-Proxy compatibility for PostgreSQL/openGauss money type">
- <assertion expected-data-source-name="expected_dataset" />
- </test-case>
-
- <test-case sql="SELECT val = ?::money FROM t_data_type_money WHERE id = ?" db-types="PostgreSQL,openGauss" scenario-types="passthrough"
- scenario-comments="Test ShardingSphere-Proxy compatibility for PostgreSQL/openGauss money type: equal operator">
- <assertion parameters="$123.00:String, 1001:int" expected-data-source-name="expected_dataset" />
- <assertion parameters="$123.01:String, 1001:int" expected-data-source-name="expected_dataset" />
- </test-case>
-
- <test-case sql="SELECT val != ?::money FROM t_data_type_money WHERE id = ?" db-types="PostgreSQL,openGauss" scenario-types="passthrough"
- scenario-comments="Test ShardingSphere-Proxy compatibility for PostgreSQL/openGauss money type: not equal operator">
- <assertion parameters="$124.00:String, 1001:int" expected-data-source-name="expected_dataset" />
- <assertion parameters="$123.00:String, 1001:int" expected-data-source-name="expected_dataset" />
- </test-case>
-
- <test-case sql="SELECT val <= ?::money FROM t_data_type_money WHERE id = ?" db-types="PostgreSQL,openGauss" scenario-types="passthrough"
- scenario-comments="Test ShardingSphere-Proxy compatibility for PostgreSQL/openGauss money type: less than or equal operator">
- <assertion parameters="$123.00:String, 1001:int" expected-data-source-name="expected_dataset" />
- <assertion parameters="$122.99:String, 1001:int" expected-data-source-name="expected_dataset" />
- </test-case>
-
- <test-case sql="SELECT val >= ?::money FROM t_data_type_money WHERE id = ?" db-types="PostgreSQL,openGauss" scenario-types="passthrough"
- scenario-comments="Test ShardingSphere-Proxy compatibility for PostgreSQL/openGauss money type: greater than or equal operator">
- <assertion parameters="$123.00:String, 1001:int" expected-data-source-name="expected_dataset" />
- <assertion parameters="$123.01:String, 1001:int" expected-data-source-name="expected_dataset" />
- </test-case>
-
- <test-case sql="SELECT val < ?::money FROM t_data_type_money WHERE id = ?" db-types="PostgreSQL,openGauss" scenario-types="passthrough"
- scenario-comments="Test ShardingSphere-Proxy compatibility for PostgreSQL/openGauss money type: less than operator">
- <assertion parameters="$122.00:String, 1001:int" expected-data-source-name="expected_dataset" />
- <assertion parameters="$124.00:String, 1001:int" expected-data-source-name="expected_dataset" />
- </test-case>
-
- <test-case sql="SELECT val > ?::money FROM t_data_type_money WHERE id = ?" db-types="PostgreSQL,openGauss" scenario-types="passthrough"
- scenario-comments="Test ShardingSphere-Proxy compatibility for PostgreSQL/openGauss money type: greater than operator">
- <assertion parameters="$122.00:String, 1001:int" expected-data-source-name="expected_dataset" />
- <assertion parameters="$124.00:String, 1001:int" expected-data-source-name="expected_dataset" />
- </test-case>
-
- <!-- TODO Fix https://github.com/apache/shardingsphere/issues/23499 -->
- <!-- <test-case sql="SELECT ?::money" db-types="PostgreSQL,openGauss" scenario-types="passthrough"-->
- <!-- scenario-comments="Test ShardingSphere-Proxy compatibility for PostgreSQL/openGauss money type: type cast to money">-->
- <!-- <assertion parameters="-92233720368547758.08:String" expected-data-source-name="expected_dataset" />-->
- <!-- <assertion parameters="92233720368547758.07:String" expected-data-source-name="expected_dataset" />-->
- <!-- </test-case>-->
-
- <test-case sql="SELECT ?::money::varchar" db-types="PostgreSQL,openGauss" scenario-types="passthrough"
- scenario-comments="Test ShardingSphere-Proxy compatibility for PostgreSQL/openGauss money type: type cast money to varchar">
- <assertion parameters="-92233720368547758.08:String" expected-data-source-name="expected_dataset" />
- <assertion parameters="-$92233720368547758.08:String" expected-data-source-name="expected_dataset" />
- <assertion parameters="92233720368547758.07:String" expected-data-source-name="expected_dataset" />
- <assertion parameters="$92233720368547758.07:String" expected-data-source-name="expected_dataset" />
- </test-case>
-
- <test-case sql="SELECT n.nspname as "Schema", c.relname as "Name", CASE c.relkind WHEN 'r' THEN 'table' WHEN 'v' THEN 'view' WHEN 'm' THEN 'materialized view' WHEN 'i' THEN 'index' WHEN 'S' THEN 'sequence' WHEN 's' THEN 'special' WHEN 'f' THEN 'foreign table' WHEN 'p' THEN 'partitioned table' WHEN 'I' THEN 'partitioned index' END as "Type", pg_catalog.pg_get_userbyid(c.relowner) as "Owner" FROM pg_catalog.pg_class c LEFT JOIN pg_catalog.pg_name [...]
- <assertion expected-data-file="select_sys_data_for_pg.xml" />
- </test-case>
-
- <test-case sql="SELECT n.nspname as "Schema", c.relname as "Name", CASE c.relkind WHEN 'r' THEN 'table' WHEN 'v' THEN 'view' WHEN 'i' THEN 'index' WHEN 'I' THEN 'global partition index' WHEN 'S' THEN 'sequence' WHEN 'L' THEN 'large sequence' WHEN 'f' THEN 'foreign table' WHEN 'm' THEN 'materialized view' WHEN 'e' THEN 'stream' WHEN 'o' THEN 'contview' END as "Type", pg_catalog.pg_get_userbyid(c.relowner) as "Owner", c.reloptions as "Stora [...]
- <assertion expected-data-file="select_sys_data_for_og.xml" />
- </test-case>
-
- <test-case sql="select * from shardingsphere.sharding_table_statistics order by id;" db-types="MySQL,PostgreSQL,openGauss" scenario-types="db">
- <assertion expected-data-file="select_sharding_table_statistics.xml" />
- </test-case>
-
- <test-case sql="select * from shardingsphere.cluster_information;" db-types="MySQL,PostgreSQL,openGauss" scenario-types="db">
- <assertion expected-data-file="select_cluster_information.xml" />
- </test-case>
-</integration-test-cases>
diff --git a/test/e2e/suite/src/test/resources/cases/tcl/tcl-integration-test-cases.xml b/test/e2e/suite/src/test/resources/cases/tcl/tcl-integration-prepare-transaction.xml
similarity index 95%
rename from test/e2e/suite/src/test/resources/cases/tcl/tcl-integration-test-cases.xml
rename to test/e2e/suite/src/test/resources/cases/tcl/tcl-integration-prepare-transaction.xml
index 652341a871f..354b700be39 100644
--- a/test/e2e/suite/src/test/resources/cases/tcl/tcl-integration-test-cases.xml
+++ b/test/e2e/suite/src/test/resources/cases/tcl/tcl-integration-prepare-transaction.xml
@@ -1,3 +1,4 @@
+<?xml version="1.0" encoding="UTF-8"?>
<!--
~ Licensed to the Apache Software Foundation (ASF) under one or more
~ contributor license agreements. See the NOTICE file distributed with
@@ -14,6 +15,7 @@
~ See the License for the specific language governing permissions and
~ limitations under the License.
-->
+
<integration-test-cases>
<test-case sql="PREPARE TRANSACTION 'foo1" db-types="PostgreSQL" />
</integration-test-cases>