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 7dcb9a62ab9 Change db and tbl test cases to federation query scenarios
(#29691)
7dcb9a62ab9 is described below
commit 7dcb9a62ab967e009497e9faff4174368dc4571c
Author: niu niu <[email protected]>
AuthorDate: Fri Jan 12 18:21:23 2024 +0800
Change db and tbl test cases to federation query scenarios (#29691)
* Change db and tbl test cases to federation query scenarios
* Add action
* Change case
* Add excepted create view sql
* Remove db and tbl scenario
* Change tbl sqlFederationEnabled conf
* Remove db create view sql
* Change db sqlFederation conf
* Fix ci
---
.github/workflows/e2e-sql.yml | 2 +-
.../dql/dataset/db/select_sys_data_for_og.xml | 4 -
.../dql/dataset/db/select_sys_data_for_pg.xml | 4 -
.../cases/dql/dql-integration-select-aggregate.xml | 8 +-
.../cases/dql/dql-integration-select-combine.xml | 112 ++++++++++-----------
.../dql/dql-integration-select-expression.xml | 6 +-
.../cases/dql/dql-integration-select-join.xml | 81 +++++++--------
.../cases/dql/dql-integration-select-order-by.xml | 12 +--
...gration-select-pagination-group-by-order-by.xml | 42 ++++----
.../dql/dql-integration-select-pagination.xml | 8 +-
.../cases/dql/dql-integration-select-sub-query.xml | 10 +-
.../resources/cases/dql/dql-integration-select.xml | 14 +--
.../cases/rql/dataset/db/count_single_table.xml | 2 +-
.../cases/rql/dataset/db/show_single_tables.xml | 4 -
.../actual/init-sql/h2/actual-logic_db-init.sql | 26 -----
.../actual/init-sql/mysql/actual-logic_db-init.sql | 26 -----
.../init-sql/opengauss/actual-logic_db-init.sql | 26 -----
.../init-sql/postgresql/actual-logic_db-init.sql | 26 -----
.../data/expected/init-sql/h2/01-expected-init.sql | 8 --
.../expected/init-sql/mysql/01-expected-init.sql | 4 -
.../init-sql/opengauss/01-expected-init.sql | 8 --
.../init-sql/postgresql/01-expected-init.sql | 8 --
.../env/scenario/db/proxy/mode/cluster/server.yaml | 2 +-
.../scenario/db/proxy/mode/standalone/server.yaml | 2 +-
.../src/test/resources/env/scenario/db/rules.yaml | 2 +-
.../expected/init-sql/mysql/01-expected-init.sql | 4 +
.../init-sql/opengauss/01-expected-init.sql | 8 ++
.../init-sql/postgresql/01-expected-init.sql | 8 ++
.../scenario/tbl/proxy/mode/cluster/server.yaml | 2 +-
.../scenario/tbl/proxy/mode/standalone/server.yaml | 2 +-
.../src/test/resources/env/scenario/tbl/rules.yaml | 2 +-
31 files changed, 176 insertions(+), 297 deletions(-)
diff --git a/.github/workflows/e2e-sql.yml b/.github/workflows/e2e-sql.yml
index 1cf68951a92..9f54680a03e 100644
--- a/.github/workflows/e2e-sql.yml
+++ b/.github/workflows/e2e-sql.yml
@@ -110,7 +110,7 @@ jobs:
database: [ MySQL, PostgreSQL ]
# Fix me #25051
#scenario: [ dbtbl_with_readwrite_splitting,
dbtbl_with_readwrite_splitting_and_encrypt, sharding_and_encrypt,
encrypt_and_readwrite_splitting, encrypt_shadow,
readwrite_splitting_and_shadow, sharding_and_shadow, sharding_encrypt_shadow,
mask_encrypt, mask_sharding, mask_encrypt_sharding ]
- scenario: [ empty_rules, rdl_empty_rules, passthrough, db, tbl,
encrypt, readwrite_splitting, shadow, mask,
dbtbl_with_readwrite_splitting_and_encrypt, sharding_and_encrypt,
encrypt_and_readwrite_splitting, encrypt_shadow,
readwrite_splitting_and_shadow, sharding_and_shadow, sharding_encrypt_shadow,
mask_encrypt, mask_sharding, mask_encrypt_sharding ]
+ scenario: [ empty_rules, rdl_empty_rules, passthrough, db, tbl,
encrypt, readwrite_splitting, shadow, mask,
dbtbl_with_readwrite_splitting_and_encrypt, sharding_and_encrypt,
encrypt_and_readwrite_splitting, encrypt_shadow,
readwrite_splitting_and_shadow, sharding_and_shadow, sharding_encrypt_shadow,
mask_encrypt, mask_sharding, mask_encrypt_sharding, db_tbl_sql_federation ]
additional-options: [ '' ]
include:
- adapter: proxy
diff --git
a/test/e2e/sql/src/test/resources/cases/dql/dataset/db/select_sys_data_for_og.xml
b/test/e2e/sql/src/test/resources/cases/dql/dataset/db/select_sys_data_for_og.xml
index 0963a031c91..8e92673f015 100644
---
a/test/e2e/sql/src/test/resources/cases/dql/dataset/db/select_sys_data_for_og.xml
+++
b/test/e2e/sql/src/test/resources/cases/dql/dataset/db/select_sys_data_for_og.xml
@@ -27,11 +27,7 @@
<row values="public | t_country | table | mock user |" />
<row values="public | t_merchant | table | mock user |" />
<row values="public | t_order | table | mock user |" />
- <row values="public | t_order_aggregation_view | view | mock user |"
mayNotExist="true" />
<row values="public | t_order_item | table | mock user |" />
- <row values="public | t_order_item_join_view | view | mock user |"
mayNotExist="true" />
- <row values="public | t_order_subquery_view | view | mock user |"
mayNotExist="true" />
- <row values="public | t_order_union_view | view | mock user |"
mayNotExist="true" />
<row values="public | t_product | table | mock user |" />
<row values="public | t_product_category | table | mock user |" />
<row values="public | t_product_detail | table | mock user |" />
diff --git
a/test/e2e/sql/src/test/resources/cases/dql/dataset/db/select_sys_data_for_pg.xml
b/test/e2e/sql/src/test/resources/cases/dql/dataset/db/select_sys_data_for_pg.xml
index db2fc9b62e8..b3b27350c09 100644
---
a/test/e2e/sql/src/test/resources/cases/dql/dataset/db/select_sys_data_for_pg.xml
+++
b/test/e2e/sql/src/test/resources/cases/dql/dataset/db/select_sys_data_for_pg.xml
@@ -26,11 +26,7 @@
<row values="public | t_country | table | mock user" />
<row values="public | t_merchant | table | mock user" />
<row values="public | t_order | table | mock user" />
- <row values="public | t_order_aggregation_view | view | mock user"
mayNotExist="true" />
<row values="public | t_order_item | table | mock user" />
- <row values="public | t_order_item_join_view | view | mock user"
mayNotExist="true" />
- <row values="public | t_order_subquery_view | view | mock user"
mayNotExist="true" />
- <row values="public | t_order_union_view | view | mock user"
mayNotExist="true" />
<row values="public | t_product | table | mock user" />
<row values="public | t_product_category | table | mock user" />
<row values="public | t_product_detail | table | mock user" />
diff --git
a/test/e2e/sql/src/test/resources/cases/dql/dql-integration-select-aggregate.xml
b/test/e2e/sql/src/test/resources/cases/dql/dql-integration-select-aggregate.xml
index d772697bac9..8654d57d365 100644
---
a/test/e2e/sql/src/test/resources/cases/dql/dql-integration-select-aggregate.xml
+++
b/test/e2e/sql/src/test/resources/cases/dql/dql-integration-select-aggregate.xml
@@ -121,19 +121,19 @@
<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">
+ <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_tbl_sql_federation">
<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">
+ <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_tbl_sql_federation">
<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">
+ <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_tbl_sql_federation">
<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">
+ <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_tbl_sql_federation">
<assertion parameters="10000:int"
expected-data-source-name="read_dataset" />
</test-case>
diff --git
a/test/e2e/sql/src/test/resources/cases/dql/dql-integration-select-combine.xml
b/test/e2e/sql/src/test/resources/cases/dql/dql-integration-select-combine.xml
index d131429d67e..6a429070b6b 100644
---
a/test/e2e/sql/src/test/resources/cases/dql/dql-integration-select-combine.xml
+++
b/test/e2e/sql/src/test/resources/cases/dql/dql-integration-select-combine.xml
@@ -32,41 +32,41 @@
<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 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_tbl_sql_federation">
+ <assertion parameters="2500:int, 2500:int"
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 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_tbl_sql_federation">
+ <assertion parameters="2500:int, 2500:int"
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 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_tbl_sql_federation">
+ <assertion parameters="2500:int"
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"
+ <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_tbl_sql_federation"
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" />
+ <assertion parameters="2500:int"
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 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_tbl_sql_federation">
+ <assertion parameters="2500:int"
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 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_tbl_sql_federation">
+ <assertion parameters="2500:int,2900:int"
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 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_tbl_sql_federation">
+ <assertion parameters="2500:int,2900:int"
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 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_tbl_sql_federation">
+ <assertion parameters="2500:int,2900:int"
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 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_tbl_sql_federation">
+ <assertion parameters="2500:int,2900:int"
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">
@@ -77,12 +77,12 @@
<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 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_tbl_sql_federation">
+ <assertion parameters="2500:int,2900:int,1000:int"
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 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_tbl_sql_federation">
+ <assertion parameters="2500:int,2900:int,1000:int"
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">
@@ -93,12 +93,12 @@
<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 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_tbl_sql_federation">
+ <assertion parameters="2500:int,2900:int"
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 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_tbl_sql_federation">
+ <assertion parameters="2500:int,2900:int"
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">
@@ -109,12 +109,12 @@
<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 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_tbl_sql_federation">
+ <assertion parameters="2500:int,2900:int"
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 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_tbl_sql_federation">
+ <assertion parameters="2500:int,2900:int"
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">
@@ -125,12 +125,12 @@
<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 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_tbl_sql_federation">
+ <assertion parameters="2500:int,2900:int"
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 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_tbl_sql_federation">
+ <assertion parameters="2500:int,2900:int"
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">
@@ -146,29 +146,29 @@
<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"
+ <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_tbl_sql_federation"
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 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_tbl_sql_federation">
+ <assertion parameters="2000:int, 1500:int"
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 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_tbl_sql_federation">
+ <assertion parameters="2500:int, 2500:int"
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 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_tbl_sql_federation">
+ <assertion parameters="2500:int, 2500:int"
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 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_tbl_sql_federation">
+ <assertion parameters="2500:int"
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 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_tbl_sql_federation">
+ <assertion parameters="2500:int"
expected-data-source-name="read_dataset" />
</test-case>
<!-- TODO Replace with standard table structure -->
@@ -192,28 +192,28 @@
</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">
+ <test-case sql="SELECT * FROM t_order EXCEPT ALL SELECT * FROM t_order
ORDER BY order_id" db-types="PostgreSQL,openGauss"
scenario-types="db_tbl_sql_federation">
<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 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_tbl_sql_federation">
+ <assertion parameters="2000:int, 1500:int"
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 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_tbl_sql_federation">
+ <assertion parameters="2500:int, 2500:int"
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 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_tbl_sql_federation">
+ <assertion parameters="2500:int, 2500:int"
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 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_tbl_sql_federation">
+ <assertion parameters="2500:int"
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 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_tbl_sql_federation">
+ <assertion parameters="2500:int"
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">
diff --git
a/test/e2e/sql/src/test/resources/cases/dql/dql-integration-select-expression.xml
b/test/e2e/sql/src/test/resources/cases/dql/dql-integration-select-expression.xml
index f110f88bbb3..f0df5c20240 100644
---
a/test/e2e/sql/src/test/resources/cases/dql/dql-integration-select-expression.xml
+++
b/test/e2e/sql/src/test/resources/cases/dql/dql-integration-select-expression.xml
@@ -72,7 +72,7 @@
<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">
+ <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="dbtbl_with_readwrite_splitting,readwrite_splitting,db_tbl_sql_federation">
<assertion expected-data-source-name="read_dataset" />
</test-case>
@@ -107,8 +107,8 @@
<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 sql="SELECT * FROM t_order_item_join_view WHERE order_id > ?"
db-types="MySQL,PostgreSQL,openGauss" scenario-types="db_tbl_sql_federation">
+ <assertion parameters="1000:int"
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 [...]
diff --git
a/test/e2e/sql/src/test/resources/cases/dql/dql-integration-select-join.xml
b/test/e2e/sql/src/test/resources/cases/dql/dql-integration-select-join.xml
index 839bea1d485..5fa9912fb4e 100644
--- a/test/e2e/sql/src/test/resources/cases/dql/dql-integration-select-join.xml
+++ b/test/e2e/sql/src/test/resources/cases/dql/dql-integration-select-join.xml
@@ -33,7 +33,7 @@
<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 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">
+ <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="dbtbl_with_readwrite_splitting,readwrite_splitting,db_tbl_sql_federation">
<assertion parameters="1000:int, 1100:int, 11:int"
expected-data-source-name="read_dataset" />
</test-case>
@@ -90,15 +90,15 @@
<assertion parameters="1000:int"
expected-data-source-name="read_dataset" />
</test-case>
- <test-case sql="SELECT * FROM t_order o NATURAL JOIN t_merchant m WHERE
o.user_id = ? ORDER BY o.order_id" db-types="MySQL,PostgreSQL,openGauss"
scenario-types="db">
+ <test-case sql="SELECT * FROM t_order o NATURAL JOIN t_merchant m WHERE
o.user_id = ? ORDER BY o.order_id" db-types="MySQL,PostgreSQL,openGauss"
scenario-types="db_tbl_sql_federation">
<assertion parameters="10:int"
expected-data-source-name="read_dataset" />
</test-case>
- <test-case sql="SELECT * FROM t_order o NATURAL JOIN t_order_item i WHERE
o.user_id = ? ORDER BY o.order_id, 7" db-types="MySQL,PostgreSQL,openGauss"
scenario-types="db">
+ <test-case sql="SELECT * FROM t_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_tbl_sql_federation">
<assertion parameters="10:int"
expected-data-source-name="read_dataset" />
</test-case>
- <test-case sql="SELECT * FROM t_product p NATURAL JOIN t_product_detail d
WHERE p.product_id > ? ORDER BY p.product_id DESC"
db-types="MySQL,PostgreSQL,openGauss" scenario-types="db">
+ <test-case sql="SELECT * FROM t_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_tbl_sql_federation">
<assertion parameters="10:int"
expected-data-source-name="read_dataset" />
</test-case>
@@ -106,11 +106,12 @@
<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">
+ <!-- TODO Replace with standard table structure -->
+ <!--<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>-->
- <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">
+ <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_tbl_sql_federation">
<assertion expected-data-source-name="read_dataset" />
</test-case>
@@ -135,63 +136,63 @@
<assertion expected-data-source-name="expected_dataset" />
</test-case>
- <test-case sql="SELECT * FROM t_order o INNER JOIN t_order_item i ON
o.order_id = i.order_id WHERE o.user_id = ? ORDER BY o.order_id, 7"
db-types="MySQL,PostgreSQL,openGauss" scenario-types="db">
+ <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_tbl_sql_federation">
<assertion parameters="10:int"
expected-data-source-name="read_dataset" />
</test-case>
- <test-case sql="SELECT * FROM t_order o INNER JOIN t_merchant m ON
o.merchant_id = m.merchant_id WHERE o.user_id = ? ORDER BY o.order_id"
db-types="MySQL,PostgreSQL,openGauss" scenario-types="db">
+ <test-case sql="SELECT * FROM t_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_tbl_sql_federation">
<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">
+ <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_tbl_sql_federation">
<assertion parameters="10:int"
expected-data-source-name="read_dataset" />
</test-case>
- <test-case sql="SELECT * FROM t_order o INNER JOIN t_merchant m
USING(merchant_id) WHERE o.user_id = ? ORDER BY o.order_id"
db-types="MySQL,PostgreSQL,openGauss" scenario-types="db">
+ <test-case sql="SELECT * FROM t_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_tbl_sql_federation">
<assertion parameters="10:int"
expected-data-source-name="read_dataset" />
</test-case>
- <test-case sql="SELECT * FROM t_order o INNER JOIN t_order_item i
USING(order_id) WHERE o.user_id = ? ORDER BY o.order_id, 7"
db-types="MySQL,PostgreSQL,openGauss" scenario-types="db">
+ <test-case sql="SELECT * FROM t_order o INNER JOIN t_order_item i
USING(order_id) WHERE o.user_id = ? ORDER BY o.order_id, 7"
db-types="MySQL,PostgreSQL,openGauss" scenario-types="db_tbl_sql_federation">
<assertion parameters="10:int"
expected-data-source-name="read_dataset" />
</test-case>
- <test-case sql="SELECT * FROM t_product p INNER JOIN t_product_detail d
USING(product_id) WHERE p.product_id > ? ORDER BY p.product_id DESC"
db-types="MySQL,PostgreSQL,openGauss" scenario-types="db">
+ <test-case sql="SELECT * FROM t_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_tbl_sql_federation">
<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">
+ <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_tbl_sql_federation">
<assertion parameters="10:int, 10:int"
expected-data-source-name="read_dataset" />
</test-case>
- <test-case sql="SELECT * FROM t_order o FULL JOIN t_merchant m ON
o.merchant_id = m.merchant_id where o.user_id = ? OR m.country_id = 1 ORDER BY
o.order_id, 7" db-types="PostgreSQL,openGauss" scenario-types="db">
+ <test-case sql="SELECT * FROM t_order o 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_tbl_sql_federation">
<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">
+ <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_tbl_sql_federation">
<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">
+ <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_tbl_sql_federation">
<assertion parameters="10:int, 10:int"
expected-data-source-name="read_dataset" />
</test-case>
- <test-case sql="SELECT * FROM t_order o FULL JOIN t_merchant m
USING(merchant_id) where o.user_id = ? OR m.country_id = 1 ORDER BY o.order_id,
7" db-types="PostgreSQL,openGauss" scenario-types="db">
+ <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_tbl_sql_federation">
<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">
+ <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_tbl_sql_federation">
<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">
+ <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_tbl_sql_federation">
<assertion parameters="10:int, 10:int"
expected-data-source-name="read_dataset" />
</test-case>
- <test-case sql="SELECT * FROM t_order o NATURAL FULL JOIN t_merchant m
where o.user_id = ? OR m.country_id = 1 ORDER BY o.order_id, 7"
db-types="PostgreSQL,openGauss" scenario-types="db">
+ <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_tbl_sql_federation">
<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">
+ <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_tbl_sql_federation">
<assertion parameters="10:int"
expected-data-source-name="read_dataset" />
</test-case>
@@ -199,81 +200,81 @@
<assertion expected-data-source-name="read_dataset" />
</test-case>
- <test-case sql="SELECT * FROM t_order o LEFT JOIN t_order_item i ON
o.order_id = i.order_id WHERE o.user_id = ? ORDER BY o.order_id, 7"
db-types="MySQL,PostgreSQL,openGauss" scenario-types="db">
+ <test-case sql="SELECT * FROM t_order o 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_tbl_sql_federation">
<assertion parameters="10:int"
expected-data-source-name="read_dataset" />
</test-case>
- <test-case sql="SELECT * FROM t_order o LEFT JOIN t_merchant m ON
o.merchant_id = m.merchant_id WHERE o.user_id = ? ORDER BY o.order_id, 7"
db-types="MySQL,PostgreSQL,openGauss" scenario-types="db">
+ <test-case sql="SELECT * FROM t_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_tbl_sql_federation">
<assertion parameters="10:int"
expected-data-source-name="read_dataset" />
</test-case>
- <test-case sql="SELECT * FROM t_product p LEFT JOIN t_product_detail d ON
d.product_id = p.product_id WHERE p.category_id = ? ORDER BY p.product_id, 7"
db-types="MySQL,PostgreSQL,openGauss" scenario-types="db">
+ <test-case sql="SELECT * FROM t_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_tbl_sql_federation">
<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">
+ <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_tbl_sql_federation">
<assertion parameters="10:int"
expected-data-source-name="read_dataset" />
</test-case>
- <test-case sql="SELECT * FROM t_order o LEFT JOIN t_merchant m
USING(merchant_id) WHERE o.user_id = ? ORDER BY o.order_id, 7"
db-types="MySQL,PostgreSQL,openGauss" scenario-types="db">
+ <test-case sql="SELECT * FROM t_order o LEFT JOIN t_merchant m
USING(merchant_id) WHERE o.user_id = ? ORDER BY o.order_id, 7"
db-types="MySQL,PostgreSQL,openGauss" scenario-types="db_tbl_sql_federation">
<assertion parameters="10:int"
expected-data-source-name="read_dataset" />
</test-case>
- <test-case sql="SELECT * FROM t_product p LEFT JOIN t_product_detail d
USING(product_id) WHERE p.category_id = ? ORDER BY p.product_id, 7"
db-types="MySQL,PostgreSQL,openGauss" scenario-types="db">
+ <test-case sql="SELECT * FROM t_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_tbl_sql_federation">
<assertion parameters="10:int"
expected-data-source-name="read_dataset" />
</test-case>
- <test-case sql="SELECT * FROM t_order o NATURAL LEFT JOIN t_merchant m
WHERE o.user_id = ? ORDER BY o.order_id, 7"
db-types="MySQL,PostgreSQL,openGauss" scenario-types="db">
+ <test-case sql="SELECT * FROM t_order o NATURAL LEFT JOIN t_merchant m
WHERE o.user_id = ? ORDER BY o.order_id, 7"
db-types="MySQL,PostgreSQL,openGauss" scenario-types="db_tbl_sql_federation">
<assertion parameters="10:int"
expected-data-source-name="read_dataset" />
</test-case>
- <test-case sql="SELECT * FROM t_order o NATURAL LEFT JOIN t_order_item i
WHERE o.user_id = ? ORDER BY o.order_id, 7"
db-types="MySQL,PostgreSQL,openGauss" scenario-types="db">
+ <test-case sql="SELECT * FROM t_order o 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_tbl_sql_federation">
<assertion parameters="10:int"
expected-data-source-name="read_dataset" />
</test-case>
- <test-case sql="SELECT * FROM t_product p NATURAL LEFT JOIN
t_product_detail d WHERE p.category_id = ? ORDER BY p.product_id, 7"
db-types="MySQL,PostgreSQL,openGauss" scenario-types="db">
+ <test-case sql="SELECT * FROM t_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_tbl_sql_federation">
<assertion parameters="10:int"
expected-data-source-name="read_dataset" />
</test-case>
- <test-case sql="SELECT * FROM t_order o RIGHT JOIN t_order_item i ON
o.order_id = i.order_id WHERE i.user_id = ? ORDER BY i.item_id, 7"
db-types="MySQL,PostgreSQL,openGauss" scenario-types="db">
+ <test-case sql="SELECT * FROM t_order o 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_tbl_sql_federation">
<assertion parameters="10:int"
expected-data-source-name="read_dataset" />
</test-case>
- <test-case sql="SELECT * FROM t_product p RIGHT JOIN t_product_detail d ON
d.product_id = p.product_id WHERE d.detail_id = ? ORDER BY d.product_id, 7"
db-types="MySQL,PostgreSQL,openGauss" scenario-types="db">
+ <test-case sql="SELECT * FROM t_product p 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_tbl_sql_federation">
<assertion parameters="10:int"
expected-data-source-name="read_dataset" />
</test-case>
- <test-case sql="SELECT * FROM t_order o RIGHT JOIN t_merchant m ON
o.merchant_id = m.merchant_id WHERE m.country_id = 1 ORDER BY o.order_id,
m.merchant_id, 7" db-types="MySQL,PostgreSQL,openGauss" scenario-types="db">
+ <test-case sql="SELECT * FROM t_order o 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_tbl_sql_federation">
<assertion expected-data-source-name="read_dataset" />
</test-case>
<!-- TODO support MySQL using statement when calcite support right join
using -->
- <test-case sql="SELECT * FROM t_order o RIGHT JOIN t_order_item i
USING(order_id) WHERE i.user_id = ? ORDER BY i.item_id, 7"
db-types="PostgreSQL,openGauss" scenario-types="db">
+ <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_tbl_sql_federation">
<assertion parameters="10:int"
expected-data-source-name="read_dataset" />
</test-case>
<!-- TODO support MySQL using statement when calcite support right join
using -->
- <test-case sql="SELECT * FROM t_product p RIGHT JOIN t_product_detail d
USING(product_id) WHERE d.detail_id = ? ORDER BY d.product_id, 7"
db-types="PostgreSQL,openGauss" scenario-types="db">
+ <test-case sql="SELECT * FROM t_product p RIGHT JOIN t_product_detail d
USING(product_id) WHERE d.detail_id = ? ORDER BY d.product_id, 7"
db-types="PostgreSQL,openGauss" scenario-types="db_tbl_sql_federation">
<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">
+ <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_tbl_sql_federation">
<assertion expected-data-source-name="read_dataset" />
</test-case>
<!-- TODO support MySQL natural right join statement when calcite support
natural right join -->
- <test-case sql="SELECT * FROM t_order o NATURAL RIGHT JOIN t_order_item i
WHERE i.user_id = ? ORDER BY i.item_id, 7" db-types="PostgreSQL,openGauss"
scenario-types="db">
+ <test-case sql="SELECT * FROM t_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_tbl_sql_federation">
<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">
+ <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_tbl_sql_federation">
<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">
+ <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_tbl_sql_federation">
<assertion expected-data-source-name="read_dataset" />
</test-case>
</integration-test-cases>
diff --git
a/test/e2e/sql/src/test/resources/cases/dql/dql-integration-select-order-by.xml
b/test/e2e/sql/src/test/resources/cases/dql/dql-integration-select-order-by.xml
index 50160fc009c..709fd068fd0 100644
---
a/test/e2e/sql/src/test/resources/cases/dql/dql-integration-select-order-by.xml
+++
b/test/e2e/sql/src/test/resources/cases/dql/dql-integration-select-order-by.xml
@@ -56,7 +56,7 @@
<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">
+ <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="dbtbl_with_readwrite_splitting,readwrite_splitting,db_tbl_sql_federation">
<assertion expected-data-source-name="read_dataset" />
</test-case>
@@ -64,7 +64,7 @@
<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">
+ <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="dbtbl_with_readwrite_splitting,readwrite_splitting,db_tbl_sql_federation">
<assertion expected-data-source-name="read_dataset" />
</test-case>
@@ -131,11 +131,11 @@
<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">
+ <test-case sql="SELECT * FROM t_order_subquery_view ORDER BY order_id"
db-types="MySQL,PostgreSQL,openGauss" scenario-types="db_tbl_sql_federation">
<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">
+ <test-case sql="SELECT * FROM t_order_union_view ORDER BY order_id"
db-types="MySQL,PostgreSQL,openGauss" scenario-types="db_tbl_sql_federation">
<assertion expected-data-source-name="read_dataset" />
</test-case>
@@ -195,11 +195,11 @@
<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">
+ <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_tbl_sql_federation">
<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">
+ <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_tbl_sql_federation">
<assertion expected-data-source-name="read_dataset" />
</test-case>
diff --git
a/test/e2e/sql/src/test/resources/cases/dql/dql-integration-select-pagination-group-by-order-by.xml
b/test/e2e/sql/src/test/resources/cases/dql/dql-integration-select-pagination-group-by-order-by.xml
index 4c4e7412a84..3dfef92ebdc 100644
---
a/test/e2e/sql/src/test/resources/cases/dql/dql-integration-select-pagination-group-by-order-by.xml
+++
b/test/e2e/sql/src/test/resources/cases/dql/dql-integration-select-pagination-group-by-order-by.xml
@@ -17,63 +17,65 @@
-->
<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">
+ <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_tbl_sql_federation">
<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">
+ <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_tbl_sql_federation">
<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">
+ <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_tbl_sql_federation">
<assertion parameters="10:int"
expected-data-source-name="read_dataset" />
</test-case>
- <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="MySQL,openGauss"
scenario-types="db">
+ <!-- TODO Connection acquisition timeout -->
+ <!-- <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="MySQL,openGauss" scenario-types="db_tbl_sql_federation">
<assertion parameters="10:int"
expected-data-source-name="read_dataset" />
- </test-case>
+ </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">
+ <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_tbl_sql_federation">
<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">
+ <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_tbl_sql_federation">
<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">
+ <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_tbl_sql_federation">
<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="MySQL,openGauss"
scenario-types="db">
+ <!-- TODO Connection acquisition timeout -->
+ <!--<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="MySQL,openGauss"
scenario-types="db_tbl_sql_federation">
<assertion parameters="10:int"
expected-data-source-name="read_dataset" />
- </test-case>
+ </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">
+ <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_tbl_sql_federation">
<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">
+ <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_tbl_sql_federation">
<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">
+ <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_tbl_sql_federation">
<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="MySQL,openGauss" scenario-types="db">
+ <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="MySQL,openGauss" scenario-types="db_tbl_sql_federation">
<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">
+ <test-case sql="SELECT * FROM t_order_subquery_view ORDER BY order_id
LIMIT 5, 2" db-types="MySQL,openGauss" scenario-types="db_tbl_sql_federation">
<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">
+ <test-case sql="SELECT * FROM t_order_union_view ORDER BY order_id LIMIT
5, 2" db-types="MySQL,openGauss" scenario-types="db_tbl_sql_federation">
<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"
+ <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_tbl_sql_federation"
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>
@@ -83,17 +85,17 @@
<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"
+ <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_tbl_sql_federation"
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"
+ <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_tbl_sql_federation"
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"
+ <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_tbl_sql_federation"
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>
diff --git
a/test/e2e/sql/src/test/resources/cases/dql/dql-integration-select-pagination.xml
b/test/e2e/sql/src/test/resources/cases/dql/dql-integration-select-pagination.xml
index 789f4e43c24..13436f360b6 100644
---
a/test/e2e/sql/src/test/resources/cases/dql/dql-integration-select-pagination.xml
+++
b/test/e2e/sql/src/test/resources/cases/dql/dql-integration-select-pagination.xml
@@ -17,20 +17,20 @@
-->
<integration-test-cases>
- <test-case sql="SELECT * FROM t_order_item_join_view LIMIT 5, 2"
db-types="MySQL,openGauss" scenario-types="db">
+ <test-case sql="SELECT * FROM t_order_item_join_view LIMIT 5, 2"
db-types="MySQL,openGauss" scenario-types="db_tbl_sql_federation">
<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">
+ <test-case sql="SELECT * FROM t_order_aggregation_view LIMIT 5, 2"
db-types="MySQL,openGauss" scenario-types="db_tbl_sql_federation">
<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"
+ <test-case sql="SELECT * FROM t_order_item_join_view FETCH NEXT 3 ROW
ONLY" db-types="PostgreSQL,openGauss" scenario-types="db_tbl_sql_federation"
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"
+ <test-case sql="SELECT * FROM t_order_aggregation_view FETCH NEXT 3 ROW
ONLY" db-types="PostgreSQL,openGauss" scenario-types="db_tbl_sql_federation"
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>
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 9749b6297bb..35f84918662 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,8 +17,8 @@
-->
<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 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_tbl_sql_federation">
+ <assertion parameters="1000: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">
@@ -50,15 +50,15 @@
<assertion 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 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_tbl_sql_federation">
+ <assertion parameters="1200: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_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">
+ <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_tbl_sql_federation">
<assertion parameters="10:int"
expected-data-source-name="read_dataset" />
</test-case>
diff --git
a/test/e2e/sql/src/test/resources/cases/dql/dql-integration-select.xml
b/test/e2e/sql/src/test/resources/cases/dql/dql-integration-select.xml
index b37cc0bacc6..ecc2745bc0f 100644
--- a/test/e2e/sql/src/test/resources/cases/dql/dql-integration-select.xml
+++ b/test/e2e/sql/src/test/resources/cases/dql/dql-integration-select.xml
@@ -162,24 +162,24 @@
<!-- <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">
+ <test-case sql="SELECT * FROM t_order_item_join_view"
db-types="MySQL,PostgreSQL,openGauss" scenario-types="db_tbl_sql_federation">
<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 sql="SELECT * FROM t_order_subquery_view WHERE order_id = ?"
db-types="MySQL,PostgreSQL,openGauss" scenario-types="db_tbl_sql_federation">
+ <assertion parameters="1201:int"
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">
+ <test-case sql="SELECT * FROM t_order_aggregation_view"
db-types="MySQL,PostgreSQL,openGauss" scenario-types="db_tbl_sql_federation">
<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">
+ <test-case sql="SELECT * FROM t_order_aggregation_view WHERE max_price =
?" db-types="MySQL,PostgreSQL,openGauss" scenario-types="db_tbl_sql_federation">
<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 sql="SELECT * FROM t_order_union_view WHERE order_id = ?"
db-types="MySQL,PostgreSQL,openGauss" scenario-types="db_tbl_sql_federation">
+ <assertion parameters="2500:int"
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"
diff --git
a/test/e2e/sql/src/test/resources/cases/rql/dataset/db/count_single_table.xml
b/test/e2e/sql/src/test/resources/cases/rql/dataset/db/count_single_table.xml
index 022bd5e0efa..d621d4bbc34 100644
---
a/test/e2e/sql/src/test/resources/cases/rql/dataset/db/count_single_table.xml
+++
b/test/e2e/sql/src/test/resources/cases/rql/dataset/db/count_single_table.xml
@@ -20,5 +20,5 @@
<column name="database" />
<column name="count" />
</metadata>
- <row values="db| 9" />
+ <row values="db| 5" />
</dataset>
diff --git
a/test/e2e/sql/src/test/resources/cases/rql/dataset/db/show_single_tables.xml
b/test/e2e/sql/src/test/resources/cases/rql/dataset/db/show_single_tables.xml
index 859c2552a9a..5b690bf1a42 100644
---
a/test/e2e/sql/src/test/resources/cases/rql/dataset/db/show_single_tables.xml
+++
b/test/e2e/sql/src/test/resources/cases/rql/dataset/db/show_single_tables.xml
@@ -21,10 +21,6 @@
<column name="storage_unit_name" />
</metadata>
<row values="t_merchant| ds_1" />
- <row values="t_order_aggregation_view| ds_0" />
- <row values="t_order_item_join_view| ds_0" />
- <row values="t_order_subquery_view| ds_0" />
- <row values="t_order_union_view| ds_0" />
<row values="t_product| ds_0" />
<row values="t_product_detail| ds_1" />
<row values="t_single_table| ds_0" />
diff --git
a/test/e2e/sql/src/test/resources/env/scenario/db/data/actual/init-sql/h2/actual-logic_db-init.sql
b/test/e2e/sql/src/test/resources/env/scenario/db/data/actual/init-sql/h2/actual-logic_db-init.sql
deleted file mode 100644
index 9e6322e78f9..00000000000
---
a/test/e2e/sql/src/test/resources/env/scenario/db/data/actual/init-sql/h2/actual-logic_db-init.sql
+++ /dev/null
@@ -1,26 +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.
---
-
-DROP VIEW IF EXISTS t_order_item_join_view CASCADE;
-DROP VIEW IF EXISTS t_order_subquery_view CASCADE;
-DROP VIEW IF EXISTS t_order_aggregation_view CASCADE;
-DROP VIEW IF EXISTS t_order_union_view CASCADE;
-
-CREATE VIEW t_order_item_join_view AS SELECT o.order_id, o.user_id, i.item_id
FROM t_order o INNER JOIN t_order_item i ON o.order_id = i.order_id ORDER BY
o.order_id, i.item_id;
-CREATE VIEW t_order_subquery_view AS SELECT * FROM t_order o WHERE o.order_id
IN (SELECT i.order_id FROM t_order_item i INNER JOIN t_product p ON
i.product_id = p.product_id WHERE p.product_id = 10);
-CREATE VIEW t_order_aggregation_view AS 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) > 10000 ORDER BY max_price;
-CREATE VIEW t_order_union_view AS SELECT * FROM t_order WHERE order_id > 2000
UNION SELECT * FROM t_order WHERE order_id > 1500;
diff --git
a/test/e2e/sql/src/test/resources/env/scenario/db/data/actual/init-sql/mysql/actual-logic_db-init.sql
b/test/e2e/sql/src/test/resources/env/scenario/db/data/actual/init-sql/mysql/actual-logic_db-init.sql
deleted file mode 100644
index e64ee300da9..00000000000
---
a/test/e2e/sql/src/test/resources/env/scenario/db/data/actual/init-sql/mysql/actual-logic_db-init.sql
+++ /dev/null
@@ -1,26 +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.
---
-
-DROP VIEW IF EXISTS t_order_item_join_view;
-DROP VIEW IF EXISTS t_order_subquery_view;
-DROP VIEW IF EXISTS t_order_aggregation_view;
-DROP VIEW IF EXISTS t_order_union_view;
-
-CREATE VIEW t_order_item_join_view AS SELECT o.order_id, o.user_id, i.item_id
FROM t_order o INNER JOIN t_order_item i ON o.order_id = i.order_id ORDER BY
o.order_id, i.item_id;
-CREATE VIEW t_order_subquery_view AS SELECT * FROM t_order o WHERE o.order_id
IN (SELECT i.order_id FROM t_order_item i INNER JOIN t_product p ON
i.product_id = p.product_id WHERE p.product_id = 10);
-CREATE VIEW t_order_aggregation_view AS 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) > 10000 ORDER BY max_price;
-CREATE VIEW t_order_union_view AS SELECT * FROM t_order WHERE order_id > 2000
UNION SELECT * FROM t_order WHERE order_id > 1500;
diff --git
a/test/e2e/sql/src/test/resources/env/scenario/db/data/actual/init-sql/opengauss/actual-logic_db-init.sql
b/test/e2e/sql/src/test/resources/env/scenario/db/data/actual/init-sql/opengauss/actual-logic_db-init.sql
deleted file mode 100644
index e64ee300da9..00000000000
---
a/test/e2e/sql/src/test/resources/env/scenario/db/data/actual/init-sql/opengauss/actual-logic_db-init.sql
+++ /dev/null
@@ -1,26 +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.
---
-
-DROP VIEW IF EXISTS t_order_item_join_view;
-DROP VIEW IF EXISTS t_order_subquery_view;
-DROP VIEW IF EXISTS t_order_aggregation_view;
-DROP VIEW IF EXISTS t_order_union_view;
-
-CREATE VIEW t_order_item_join_view AS SELECT o.order_id, o.user_id, i.item_id
FROM t_order o INNER JOIN t_order_item i ON o.order_id = i.order_id ORDER BY
o.order_id, i.item_id;
-CREATE VIEW t_order_subquery_view AS SELECT * FROM t_order o WHERE o.order_id
IN (SELECT i.order_id FROM t_order_item i INNER JOIN t_product p ON
i.product_id = p.product_id WHERE p.product_id = 10);
-CREATE VIEW t_order_aggregation_view AS 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) > 10000 ORDER BY max_price;
-CREATE VIEW t_order_union_view AS SELECT * FROM t_order WHERE order_id > 2000
UNION SELECT * FROM t_order WHERE order_id > 1500;
diff --git
a/test/e2e/sql/src/test/resources/env/scenario/db/data/actual/init-sql/postgresql/actual-logic_db-init.sql
b/test/e2e/sql/src/test/resources/env/scenario/db/data/actual/init-sql/postgresql/actual-logic_db-init.sql
deleted file mode 100644
index e64ee300da9..00000000000
---
a/test/e2e/sql/src/test/resources/env/scenario/db/data/actual/init-sql/postgresql/actual-logic_db-init.sql
+++ /dev/null
@@ -1,26 +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.
---
-
-DROP VIEW IF EXISTS t_order_item_join_view;
-DROP VIEW IF EXISTS t_order_subquery_view;
-DROP VIEW IF EXISTS t_order_aggregation_view;
-DROP VIEW IF EXISTS t_order_union_view;
-
-CREATE VIEW t_order_item_join_view AS SELECT o.order_id, o.user_id, i.item_id
FROM t_order o INNER JOIN t_order_item i ON o.order_id = i.order_id ORDER BY
o.order_id, i.item_id;
-CREATE VIEW t_order_subquery_view AS SELECT * FROM t_order o WHERE o.order_id
IN (SELECT i.order_id FROM t_order_item i INNER JOIN t_product p ON
i.product_id = p.product_id WHERE p.product_id = 10);
-CREATE VIEW t_order_aggregation_view AS 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) > 10000 ORDER BY max_price;
-CREATE VIEW t_order_union_view AS SELECT * FROM t_order WHERE order_id > 2000
UNION SELECT * FROM t_order WHERE order_id > 1500;
diff --git
a/test/e2e/sql/src/test/resources/env/scenario/db/data/expected/init-sql/h2/01-expected-init.sql
b/test/e2e/sql/src/test/resources/env/scenario/db/data/expected/init-sql/h2/01-expected-init.sql
index eea1183eb66..bdf453f4f3d 100644
---
a/test/e2e/sql/src/test/resources/env/scenario/db/data/expected/init-sql/h2/01-expected-init.sql
+++
b/test/e2e/sql/src/test/resources/env/scenario/db/data/expected/init-sql/h2/01-expected-init.sql
@@ -23,10 +23,6 @@ DROP TABLE IF EXISTS t_product CASCADE;
DROP TABLE IF EXISTS t_product_detail CASCADE;
DROP TABLE IF EXISTS t_product_category CASCADE;
DROP TABLE IF EXISTS t_country CASCADE;
-DROP VIEW IF EXISTS t_order_item_join_view CASCADE;
-DROP VIEW IF EXISTS t_order_subquery_view CASCADE;
-DROP VIEW IF EXISTS t_order_aggregation_view CASCADE;
-DROP VIEW IF EXISTS t_order_union_view CASCADE;
-- TODO replace these tables with standard tables
DROP TABLE IF EXISTS t_single_table CASCADE;
DROP TABLE IF EXISTS t_broadcast_table CASCADE;
@@ -39,10 +35,6 @@ CREATE TABLE t_product (product_id INT PRIMARY KEY,
product_name VARCHAR(50) NOT
CREATE TABLE t_product_detail (detail_id INT PRIMARY KEY, product_id INT NOT
NULL, description VARCHAR(50) NOT NULL, creation_date DATE NOT NULL);
CREATE TABLE t_product_category (category_id INT PRIMARY KEY, category_name
VARCHAR(50) NOT NULL, parent_id INT NOT NULL, level TINYINT NOT NULL,
creation_date DATE NOT NULL);
CREATE TABLE t_country (country_id SMALLINT PRIMARY KEY, country_name
VARCHAR(50), continent_name VARCHAR(50), creation_date DATE NOT NULL);
-CREATE VIEW t_order_item_join_view AS SELECT o.order_id, o.user_id, i.item_id
FROM t_order o INNER JOIN t_order_item i ON o.order_id = i.order_id ORDER BY
o.order_id, i.item_id;
-CREATE VIEW t_order_subquery_view AS SELECT * FROM t_order o WHERE o.order_id
IN (SELECT i.order_id FROM t_order_item i INNER JOIN t_product p ON
i.product_id = p.product_id WHERE p.product_id = 10);
-CREATE VIEW t_order_aggregation_view AS 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) > 10000 ORDER BY max_price;
-CREATE VIEW t_order_union_view AS SELECT * FROM t_order WHERE order_id > 2000
UNION SELECT * FROM t_order WHERE order_id > 1500;
-- TODO replace these tables with standard tables
CREATE TABLE t_single_table (single_id INT NOT NULL, id INT NOT NULL, status
VARCHAR(45) NULL, PRIMARY KEY (single_id));
CREATE TABLE t_broadcast_table (id INT NOT NULL, status VARCHAR(45) NULL,
PRIMARY KEY (id));
diff --git
a/test/e2e/sql/src/test/resources/env/scenario/db/data/expected/init-sql/mysql/01-expected-init.sql
b/test/e2e/sql/src/test/resources/env/scenario/db/data/expected/init-sql/mysql/01-expected-init.sql
index 4781aa1f2c9..fccd5b6ef6f 100644
---
a/test/e2e/sql/src/test/resources/env/scenario/db/data/expected/init-sql/mysql/01-expected-init.sql
+++
b/test/e2e/sql/src/test/resources/env/scenario/db/data/expected/init-sql/mysql/01-expected-init.sql
@@ -29,10 +29,6 @@ CREATE TABLE expected_dataset.t_product (product_id INT
PRIMARY KEY, product_nam
CREATE TABLE expected_dataset.t_product_detail (detail_id INT PRIMARY KEY,
product_id INT NOT NULL, description VARCHAR(50) NOT NULL, creation_date DATE
NOT NULL);
CREATE TABLE expected_dataset.t_product_category (category_id INT PRIMARY KEY,
category_name VARCHAR(50) NOT NULL, parent_id INT NOT NULL, level TINYINT NOT
NULL, creation_date DATE NOT NULL);
CREATE TABLE expected_dataset.t_country (country_id SMALLINT PRIMARY KEY,
country_name VARCHAR(50), continent_name VARCHAR(50), creation_date DATE NOT
NULL);
-CREATE VIEW expected_dataset.t_order_item_join_view AS SELECT o.order_id,
o.user_id, i.item_id FROM expected_dataset.t_order o INNER JOIN
expected_dataset.t_order_item i ON o.order_id = i.order_id ORDER BY o.order_id,
i.item_id;
-CREATE VIEW expected_dataset.t_order_subquery_view AS SELECT * FROM
expected_dataset.t_order o WHERE o.order_id IN (SELECT i.order_id FROM
expected_dataset.t_order_item i INNER JOIN expected_dataset.t_product p ON
i.product_id = p.product_id WHERE p.product_id = 10);
-CREATE VIEW expected_dataset.t_order_aggregation_view AS 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 expected_dataset.t_order o
INNER JOIN expected_dataset.t_order_item i ON o.order_id = i.order_id INNER
JOIN expected_dataset.t_product p ON i.product_id = p.product_id GROUP BY
o.order_id HAVING SUM(p.price) > 10000 ORDER BY max_price;
-CREATE VIEW expected_dataset.t_order_union_view AS SELECT * FROM
expected_dataset.t_order WHERE order_id > 2000 UNION SELECT * FROM
expected_dataset.t_order WHERE order_id > 1500;
-- TODO replace these tables with standard tables
CREATE TABLE expected_dataset.t_single_table (single_id INT NOT NULL
AUTO_INCREMENT, id INT NOT NULL, status VARCHAR(45) NULL, PRIMARY KEY
(single_id), UNIQUE KEY (id, status));
CREATE TABLE expected_dataset.t_broadcast_table (id INT NOT NULL, status
VARCHAR(45) NULL, PRIMARY KEY (id));
diff --git
a/test/e2e/sql/src/test/resources/env/scenario/db/data/expected/init-sql/opengauss/01-expected-init.sql
b/test/e2e/sql/src/test/resources/env/scenario/db/data/expected/init-sql/opengauss/01-expected-init.sql
index 744baa3b0cf..a459d0085d5 100644
---
a/test/e2e/sql/src/test/resources/env/scenario/db/data/expected/init-sql/opengauss/01-expected-init.sql
+++
b/test/e2e/sql/src/test/resources/env/scenario/db/data/expected/init-sql/opengauss/01-expected-init.sql
@@ -30,10 +30,6 @@ DROP TABLE IF EXISTS t_product;
DROP TABLE IF EXISTS t_product_detail;
DROP TABLE IF EXISTS t_product_category;
DROP TABLE IF EXISTS t_country;
-DROP VIEW IF EXISTS t_order_item_join_view;
-DROP VIEW IF EXISTS t_order_subquery_view;
-DROP VIEW IF EXISTS t_order_aggregation_view;
-DROP VIEW IF EXISTS t_order_union_view;
-- TODO replace these tables with standard tables
DROP TABLE IF EXISTS t_single_table;
DROP TABLE IF EXISTS t_broadcast_table;
@@ -46,10 +42,6 @@ CREATE TABLE t_product (product_id INT PRIMARY KEY,
product_name VARCHAR(50) NOT
CREATE TABLE t_product_detail (detail_id INT PRIMARY KEY, product_id INT NOT
NULL, description VARCHAR(50) NOT NULL, creation_date DATE NOT NULL);
CREATE TABLE t_product_category (category_id INT PRIMARY KEY, category_name
VARCHAR(50) NOT NULL, parent_id INT NOT NULL, level TINYINT NOT NULL,
creation_date DATE NOT NULL);
CREATE TABLE t_country (country_id SMALLINT PRIMARY KEY, country_name
VARCHAR(50), continent_name VARCHAR(50), creation_date DATE NOT NULL);
-CREATE VIEW t_order_item_join_view AS SELECT o.order_id, o.user_id, i.item_id
FROM t_order o INNER JOIN t_order_item i ON o.order_id = i.order_id ORDER BY
o.order_id, i.item_id;
-CREATE VIEW t_order_subquery_view AS SELECT * FROM t_order o WHERE o.order_id
IN (SELECT i.order_id FROM t_order_item i INNER JOIN t_product p ON
i.product_id = p.product_id WHERE p.product_id = 10);
-CREATE VIEW t_order_aggregation_view AS 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) > 10000 ORDER BY max_price;
-CREATE VIEW t_order_union_view AS SELECT * FROM t_order WHERE order_id > 2000
UNION SELECT * FROM t_order WHERE order_id > 1500;
-- TODO replace these tables with standard tables
CREATE TABLE t_single_table (single_id INT NOT NULL, id INT NOT NULL, status
VARCHAR(45) NULL, PRIMARY KEY (single_id));
CREATE TABLE t_broadcast_table (id INT NOT NULL, status VARCHAR(45) NULL,
PRIMARY KEY (id));
diff --git
a/test/e2e/sql/src/test/resources/env/scenario/db/data/expected/init-sql/postgresql/01-expected-init.sql
b/test/e2e/sql/src/test/resources/env/scenario/db/data/expected/init-sql/postgresql/01-expected-init.sql
index e77ce509287..c2432c9655e 100644
---
a/test/e2e/sql/src/test/resources/env/scenario/db/data/expected/init-sql/postgresql/01-expected-init.sql
+++
b/test/e2e/sql/src/test/resources/env/scenario/db/data/expected/init-sql/postgresql/01-expected-init.sql
@@ -30,10 +30,6 @@ DROP TABLE IF EXISTS t_product;
DROP TABLE IF EXISTS t_product_detail;
DROP TABLE IF EXISTS t_product_category;
DROP TABLE IF EXISTS t_country;
-DROP VIEW IF EXISTS t_order_item_join_view;
-DROP VIEW IF EXISTS t_order_subquery_view;
-DROP VIEW IF EXISTS t_order_aggregation_view;
-DROP VIEW IF EXISTS t_order_union_view;
-- TODO replace these tables with standard tables
DROP TABLE IF EXISTS t_single_table;
DROP TABLE IF EXISTS t_broadcast_table;
@@ -46,10 +42,6 @@ CREATE TABLE t_product (product_id INT PRIMARY KEY,
product_name VARCHAR(50) NOT
CREATE TABLE t_product_detail (detail_id INT PRIMARY KEY, product_id INT NOT
NULL, description VARCHAR(50) NOT NULL, creation_date DATE NOT NULL);
CREATE TABLE t_product_category (category_id INT PRIMARY KEY, category_name
VARCHAR(50) NOT NULL, parent_id INT NOT NULL, level SMALLINT NOT NULL,
creation_date DATE NOT NULL);
CREATE TABLE t_country (country_id SMALLINT PRIMARY KEY, country_name
VARCHAR(50), continent_name VARCHAR(50), creation_date DATE NOT NULL);
-CREATE VIEW t_order_item_join_view AS SELECT o.order_id, o.user_id, i.item_id
FROM t_order o INNER JOIN t_order_item i ON o.order_id = i.order_id ORDER BY
o.order_id, i.item_id;
-CREATE VIEW t_order_subquery_view AS SELECT * FROM t_order o WHERE o.order_id
IN (SELECT i.order_id FROM t_order_item i INNER JOIN t_product p ON
i.product_id = p.product_id WHERE p.product_id = 10);
-CREATE VIEW t_order_aggregation_view AS 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) > 10000 ORDER BY max_price;
-CREATE VIEW t_order_union_view AS SELECT * FROM t_order WHERE order_id > 2000
UNION SELECT * FROM t_order WHERE order_id > 1500;
-- TODO replace these tables with standard tables
CREATE TABLE t_single_table (single_id INT NOT NULL, id INT NOT NULL, status
VARCHAR(45) NULL, PRIMARY KEY (single_id));
CREATE TABLE t_broadcast_table (id INT NOT NULL, status VARCHAR(45) NULL,
PRIMARY KEY (id));
diff --git
a/test/e2e/sql/src/test/resources/env/scenario/db/proxy/mode/cluster/server.yaml
b/test/e2e/sql/src/test/resources/env/scenario/db/proxy/mode/cluster/server.yaml
index d2e91bb212a..7d2f3119890 100644
---
a/test/e2e/sql/src/test/resources/env/scenario/db/proxy/mode/cluster/server.yaml
+++
b/test/e2e/sql/src/test/resources/env/scenario/db/proxy/mode/cluster/server.yaml
@@ -43,7 +43,7 @@ sqlParser:
maximumSize: 1024
sqlFederation:
- sqlFederationEnabled: true
+ sqlFederationEnabled: false
executionPlanCache:
initialCapacity: 2000
maximumSize: 65535
diff --git
a/test/e2e/sql/src/test/resources/env/scenario/db/proxy/mode/standalone/server.yaml
b/test/e2e/sql/src/test/resources/env/scenario/db/proxy/mode/standalone/server.yaml
index aef1f5d0d8e..6cbc30c11ce 100644
---
a/test/e2e/sql/src/test/resources/env/scenario/db/proxy/mode/standalone/server.yaml
+++
b/test/e2e/sql/src/test/resources/env/scenario/db/proxy/mode/standalone/server.yaml
@@ -31,7 +31,7 @@ sqlParser:
maximumSize: 1024
sqlFederation:
- sqlFederationEnabled: true
+ sqlFederationEnabled: false
executionPlanCache:
initialCapacity: 2000
maximumSize: 65535
diff --git a/test/e2e/sql/src/test/resources/env/scenario/db/rules.yaml
b/test/e2e/sql/src/test/resources/env/scenario/db/rules.yaml
index d190dd23049..675adc9f0b0 100644
--- a/test/e2e/sql/src/test/resources/env/scenario/db/rules.yaml
+++ b/test/e2e/sql/src/test/resources/env/scenario/db/rules.yaml
@@ -70,7 +70,7 @@ rules:
- t_product_category
sqlFederation:
- sqlFederationEnabled: true
+ sqlFederationEnabled: false
executionPlanCache:
initialCapacity: 2000
maximumSize: 65535
diff --git
a/test/e2e/sql/src/test/resources/env/scenario/db_tbl_sql_federation/data/expected/init-sql/mysql/01-expected-init.sql
b/test/e2e/sql/src/test/resources/env/scenario/db_tbl_sql_federation/data/expected/init-sql/mysql/01-expected-init.sql
index 86b473d4705..6066d47f74a 100644
---
a/test/e2e/sql/src/test/resources/env/scenario/db_tbl_sql_federation/data/expected/init-sql/mysql/01-expected-init.sql
+++
b/test/e2e/sql/src/test/resources/env/scenario/db_tbl_sql_federation/data/expected/init-sql/mysql/01-expected-init.sql
@@ -29,5 +29,9 @@ CREATE TABLE sql_federation.t_product (product_id INT PRIMARY
KEY, product_name
CREATE TABLE sql_federation.t_product_detail (detail_id INT PRIMARY KEY,
product_id INT NOT NULL, description VARCHAR(50) NOT NULL, creation_date DATE
NOT NULL);
CREATE TABLE sql_federation.t_product_category( category_id INT PRIMARY KEY,
category_name VARCHAR(50) NOT NULL, parent_id INT NOT NULL, level INT NOT NULL,
creation_date DATE NOT NULL);
CREATE TABLE sql_federation.t_country (country_id INT PRIMARY KEY,
country_name VARCHAR(50), continent_name VARCHAR(50), creation_date DATE NOT
NULL);
+CREATE VIEW sql_federation.t_order_item_join_view AS SELECT o.order_id,
o.user_id, i.item_id FROM sql_federation.t_order o INNER JOIN
sql_federation.t_order_item i ON o.order_id = i.order_id ORDER BY o.order_id,
i.item_id;
+CREATE VIEW sql_federation.t_order_subquery_view AS SELECT * FROM
sql_federation.t_order o WHERE o.order_id IN (SELECT i.order_id FROM
sql_federation.t_order_item i INNER JOIN sql_federation.t_product p ON
i.product_id = p.product_id WHERE p.product_id = 10);
+CREATE VIEW sql_federation.t_order_aggregation_view AS 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 sql_federation.t_order o INNER JOIN
sql_federation.t_order_item i ON o.order_id = i.order_id INNER JOIN
sql_federation.t_product p ON i.product_id = p.product_id GROUP BY o.order_id
HAVING SUM(p.price) > 10000 ORDER BY max_price;
+CREATE VIEW sql_federation.t_order_union_view AS SELECT * FROM
sql_federation.t_order WHERE order_id > 2000 UNION SELECT * FROM
sql_federation.t_order WHERE order_id > 1500;
CREATE INDEX order_index_t_order ON sql_federation.t_order (order_id);
diff --git
a/test/e2e/sql/src/test/resources/env/scenario/db_tbl_sql_federation/data/expected/init-sql/opengauss/01-expected-init.sql
b/test/e2e/sql/src/test/resources/env/scenario/db_tbl_sql_federation/data/expected/init-sql/opengauss/01-expected-init.sql
index 2ea877feb32..28b4578921a 100644
---
a/test/e2e/sql/src/test/resources/env/scenario/db_tbl_sql_federation/data/expected/init-sql/opengauss/01-expected-init.sql
+++
b/test/e2e/sql/src/test/resources/env/scenario/db_tbl_sql_federation/data/expected/init-sql/opengauss/01-expected-init.sql
@@ -30,6 +30,10 @@ DROP TABLE IF EXISTS t_product;
DROP TABLE IF EXISTS t_product_detail;
DROP TABLE IF EXISTS t_product_category;
DROP TABLE IF EXISTS t_country;
+DROP VIEW IF EXISTS t_order_item_join_view;
+DROP VIEW IF EXISTS t_order_subquery_view;
+DROP VIEW IF EXISTS t_order_aggregation_view;
+DROP VIEW IF EXISTS t_order_union_view;
CREATE TABLE t_order(order_id INT PRIMARY KEY, user_id INT NOT NULL, status
VARCHAR(50) NOT NULL, merchant_id INT NOT NULL, remark VARCHAR(50) NOT NULL,
creation_date DATE NOT NULL);
CREATE TABLE t_order_item(item_id INT PRIMARY KEY, order_id INT NOT NULL,
user_id INT NOT NULL, product_id INT NOT NULL, quantity INT NOT NULL,
creation_date DATE NOT NULL);
@@ -39,5 +43,9 @@ CREATE TABLE t_product (product_id INT PRIMARY KEY,
product_name VARCHAR(50) NOT
CREATE TABLE t_product_detail (detail_id INT PRIMARY KEY, product_id INT NOT
NULL, description VARCHAR(50) NOT NULL, creation_date DATE NOT NULL);
CREATE TABLE t_product_category( category_id INT PRIMARY KEY, category_name
VARCHAR(50) NOT NULL, parent_id INT NOT NULL, level INT NOT NULL, creation_date
DATE NOT NULL);
CREATE TABLE t_country (country_id INT PRIMARY KEY, country_name VARCHAR(50),
continent_name VARCHAR(50), creation_date DATE NOT NULL);
+CREATE VIEW t_order_item_join_view AS SELECT o.order_id, o.user_id, i.item_id
FROM t_order o INNER JOIN t_order_item i ON o.order_id = i.order_id ORDER BY
o.order_id, i.item_id;
+CREATE VIEW t_order_subquery_view AS SELECT * FROM t_order o WHERE o.order_id
IN (SELECT i.order_id FROM t_order_item i INNER JOIN t_product p ON
i.product_id = p.product_id WHERE p.product_id = 10);
+CREATE VIEW t_order_aggregation_view AS 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) > 10000 ORDER BY max_price;
+CREATE VIEW t_order_union_view AS SELECT * FROM t_order WHERE order_id > 2000
UNION SELECT * FROM t_order WHERE order_id > 1500;
CREATE INDEX order_index_t_order ON t_order (order_id);
diff --git
a/test/e2e/sql/src/test/resources/env/scenario/db_tbl_sql_federation/data/expected/init-sql/postgresql/01-expected-init.sql
b/test/e2e/sql/src/test/resources/env/scenario/db_tbl_sql_federation/data/expected/init-sql/postgresql/01-expected-init.sql
index 2ea877feb32..28b4578921a 100644
---
a/test/e2e/sql/src/test/resources/env/scenario/db_tbl_sql_federation/data/expected/init-sql/postgresql/01-expected-init.sql
+++
b/test/e2e/sql/src/test/resources/env/scenario/db_tbl_sql_federation/data/expected/init-sql/postgresql/01-expected-init.sql
@@ -30,6 +30,10 @@ DROP TABLE IF EXISTS t_product;
DROP TABLE IF EXISTS t_product_detail;
DROP TABLE IF EXISTS t_product_category;
DROP TABLE IF EXISTS t_country;
+DROP VIEW IF EXISTS t_order_item_join_view;
+DROP VIEW IF EXISTS t_order_subquery_view;
+DROP VIEW IF EXISTS t_order_aggregation_view;
+DROP VIEW IF EXISTS t_order_union_view;
CREATE TABLE t_order(order_id INT PRIMARY KEY, user_id INT NOT NULL, status
VARCHAR(50) NOT NULL, merchant_id INT NOT NULL, remark VARCHAR(50) NOT NULL,
creation_date DATE NOT NULL);
CREATE TABLE t_order_item(item_id INT PRIMARY KEY, order_id INT NOT NULL,
user_id INT NOT NULL, product_id INT NOT NULL, quantity INT NOT NULL,
creation_date DATE NOT NULL);
@@ -39,5 +43,9 @@ CREATE TABLE t_product (product_id INT PRIMARY KEY,
product_name VARCHAR(50) NOT
CREATE TABLE t_product_detail (detail_id INT PRIMARY KEY, product_id INT NOT
NULL, description VARCHAR(50) NOT NULL, creation_date DATE NOT NULL);
CREATE TABLE t_product_category( category_id INT PRIMARY KEY, category_name
VARCHAR(50) NOT NULL, parent_id INT NOT NULL, level INT NOT NULL, creation_date
DATE NOT NULL);
CREATE TABLE t_country (country_id INT PRIMARY KEY, country_name VARCHAR(50),
continent_name VARCHAR(50), creation_date DATE NOT NULL);
+CREATE VIEW t_order_item_join_view AS SELECT o.order_id, o.user_id, i.item_id
FROM t_order o INNER JOIN t_order_item i ON o.order_id = i.order_id ORDER BY
o.order_id, i.item_id;
+CREATE VIEW t_order_subquery_view AS SELECT * FROM t_order o WHERE o.order_id
IN (SELECT i.order_id FROM t_order_item i INNER JOIN t_product p ON
i.product_id = p.product_id WHERE p.product_id = 10);
+CREATE VIEW t_order_aggregation_view AS 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) > 10000 ORDER BY max_price;
+CREATE VIEW t_order_union_view AS SELECT * FROM t_order WHERE order_id > 2000
UNION SELECT * FROM t_order WHERE order_id > 1500;
CREATE INDEX order_index_t_order ON t_order (order_id);
diff --git
a/test/e2e/sql/src/test/resources/env/scenario/tbl/proxy/mode/cluster/server.yaml
b/test/e2e/sql/src/test/resources/env/scenario/tbl/proxy/mode/cluster/server.yaml
index d2e91bb212a..7d2f3119890 100644
---
a/test/e2e/sql/src/test/resources/env/scenario/tbl/proxy/mode/cluster/server.yaml
+++
b/test/e2e/sql/src/test/resources/env/scenario/tbl/proxy/mode/cluster/server.yaml
@@ -43,7 +43,7 @@ sqlParser:
maximumSize: 1024
sqlFederation:
- sqlFederationEnabled: true
+ sqlFederationEnabled: false
executionPlanCache:
initialCapacity: 2000
maximumSize: 65535
diff --git
a/test/e2e/sql/src/test/resources/env/scenario/tbl/proxy/mode/standalone/server.yaml
b/test/e2e/sql/src/test/resources/env/scenario/tbl/proxy/mode/standalone/server.yaml
index aef1f5d0d8e..6cbc30c11ce 100644
---
a/test/e2e/sql/src/test/resources/env/scenario/tbl/proxy/mode/standalone/server.yaml
+++
b/test/e2e/sql/src/test/resources/env/scenario/tbl/proxy/mode/standalone/server.yaml
@@ -31,7 +31,7 @@ sqlParser:
maximumSize: 1024
sqlFederation:
- sqlFederationEnabled: true
+ sqlFederationEnabled: false
executionPlanCache:
initialCapacity: 2000
maximumSize: 65535
diff --git a/test/e2e/sql/src/test/resources/env/scenario/tbl/rules.yaml
b/test/e2e/sql/src/test/resources/env/scenario/tbl/rules.yaml
index fa5344a63bc..1420aed17a3 100644
--- a/test/e2e/sql/src/test/resources/env/scenario/tbl/rules.yaml
+++ b/test/e2e/sql/src/test/resources/env/scenario/tbl/rules.yaml
@@ -76,7 +76,7 @@ rules:
- t_broadcast_table_for_ddl
sqlFederation:
- sqlFederationEnabled: true
+ sqlFederationEnabled: false
executionPlanCache:
initialCapacity: 2000
maximumSize: 65535
