This is an automated email from the ASF dual-hosted git repository.
duanzhengqiang pushed a commit to branch master
in repository https://gitbox.apache.org/repos/asf/shardingsphere.git
The following commit(s) were added to refs/heads/master by this push:
new 8ce0862aaba Add select query of projection and subquery test cases
(#24900)
8ce0862aaba is described below
commit 8ce0862aabac4d2a9d123974ff9d7c7a74c9c51f
Author: ZhangCheng <[email protected]>
AuthorDate: Thu Mar 30 16:20:30 2023 +0800
Add select query of projection and subquery test cases (#24900)
* Add select query of projection and subquery test cases
* Fix
* Add more MySQL information_schema tables
* fix
* fix
---
.../dql/dql-integration-select-projection.xml | 71 ++++++++++++++++++++++
.../cases/dql/dql-integration-select-sub-query.xml | 36 +++++------
2 files changed, 89 insertions(+), 18 deletions(-)
diff --git
a/test/e2e/sql/src/test/resources/cases/dql/dql-integration-select-projection.xml
b/test/e2e/sql/src/test/resources/cases/dql/dql-integration-select-projection.xml
new file mode 100644
index 00000000000..cfa7b35e75f
--- /dev/null
+++
b/test/e2e/sql/src/test/resources/cases/dql/dql-integration-select-projection.xml
@@ -0,0 +1,71 @@
+<?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 user_id = ? AND order_id = ?"
db-types="MySQL"
scenario-types="db,tbl,dbtbl_with_readwrite_splitting,readwrite_splitting">
+ <assertion parameters="10:int, 1000:int"
expected-data-source-name="read_dataset" />
+ </test-case>
+
+ <test-case sql="SELECT t_order.* FROM t_order WHERE user_id = ? AND
order_id = ?" db-types="MySQL"
scenario-types="db,tbl,dbtbl_with_readwrite_splitting,readwrite_splitting">
+ <assertion parameters="10:int, 1000:int"
expected-data-source-name="read_dataset" />
+ </test-case>
+
+ <test-case sql="SELECT t_order.* FROM t_order t_order WHERE user_id = ?
AND order_id = ?" db-types="MySQL"
scenario-types="db,tbl,dbtbl_with_readwrite_splitting,readwrite_splitting">
+ <assertion parameters="10:int, 1000:int"
expected-data-source-name="read_dataset" />
+ </test-case>
+
+ <test-case sql="SELECT t.* FROM t_order t WHERE user_id = ? AND order_id =
?" db-types="MySQL"
scenario-types="db,tbl,dbtbl_with_readwrite_splitting,readwrite_splitting">
+ <assertion parameters="10:int, 1000:int"
expected-data-source-name="read_dataset" />
+ </test-case>
+
+ <test-case sql="SELECT order_id, user_id, status FROM t_order WHERE
user_id = ? AND order_id = ?" db-types="MySQL"
scenario-types="db,tbl,dbtbl_with_readwrite_splitting,readwrite_splitting">
+ <assertion parameters="10:int, 1000:int"
expected-data-source-name="read_dataset" />
+ </test-case>
+
+ <test-case sql="SELECT USER(), DATABASE()" db-types="MySQL"
scenario-types="db,tbl,dbtbl_with_readwrite_splitting,readwrite_splitting">
+ </test-case>
+
+ <test-case sql="SELECT 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(order_id) 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 order_id AS id FROM t_order WHERE user_id = ? AND
order_id = ?" db-types="MySQL"
scenario-types="db,tbl,dbtbl_with_readwrite_splitting,readwrite_splitting">
+ <assertion parameters="10:int, 1000:int"
expected-data-source-name="read_dataset" />
+ </test-case>
+
+ <test-case sql="SELECT order_id id FROM t_order WHERE user_id = ? AND
order_id = ?" db-types="MySQL"
scenario-types="db,tbl,dbtbl_with_readwrite_splitting,readwrite_splitting">
+ <assertion parameters="10:int, 1000:int"
expected-data-source-name="read_dataset" />
+ </test-case>
+
+ <test-case sql="SELECT 12 AS order_id, order_id FROM t_order WHERE user_id
= ? AND order_id = ?" db-types="MySQL"
scenario-types="db,tbl,dbtbl_with_readwrite_splitting,readwrite_splitting">
+ <assertion parameters="10:int, 1000:int"
expected-data-source-name="read_dataset" />
+ </test-case>
+
+ <test-case sql="SELECT 1 + 1 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 item_id, (SELECT order_id FROM t_order WHERE
user_id = ? AND order_id = ?) AS order_id FROM t_order_item i WHERE i.user_id
= ? AND i.order_id = ?;
+" db-types="MySQL" scenario-types="db">
+ <assertion parameters="10:int, 1000:long, 10:int, 1000:long"
expected-data-source-name="read_dataset" />
+ </test-case>
+</integration-test-cases>
diff --git
a/test/e2e/sql/src/test/resources/cases/dql/dql-integration-select-sub-query.xml
b/test/e2e/sql/src/test/resources/cases/dql/dql-integration-select-sub-query.xml
index eb3b7e6a721..14f569faa2b 100644
---
a/test/e2e/sql/src/test/resources/cases/dql/dql-integration-select-sub-query.xml
+++
b/test/e2e/sql/src/test/resources/cases/dql/dql-integration-select-sub-query.xml
@@ -17,6 +17,10 @@
-->
<integration-test-cases>
+ <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 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>
@@ -45,32 +49,14 @@
<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" />
@@ -83,4 +69,18 @@
<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_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 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>
</integration-test-cases>
