This is an automated email from the ASF dual-hosted git repository.
yx9o 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 f57a76c5597 Fix select from view limit wrong result when view contains
order by (#22486)
f57a76c5597 is described below
commit f57a76c5597bdb296325484dcc679520c697aad2
Author: Zhengqiang Duan <[email protected]>
AuthorDate: Mon Nov 28 20:14:38 2022 +0800
Fix select from view limit wrong result when view contains order by (#22486)
---
.../optimizer/util/SQLFederationPlannerUtil.java | 3 ++-
.../cases/dql/dql-integration-test-cases.xml | 20 ++++++++++----------
.../data/actual/init-sql/h2/actual-logic_db-init.sql | 2 +-
.../actual/init-sql/mysql/actual-logic_db-init.sql | 2 +-
.../init-sql/opengauss/actual-logic_db-init.sql | 2 +-
.../init-sql/postgresql/actual-logic_db-init.sql | 2 +-
.../data/expected/init-sql/h2/01-expected-init.sql | 2 +-
.../expected/init-sql/mysql/01-expected-init.sql | 2 +-
.../expected/init-sql/opengauss/01-expected-init.sql | 2 +-
.../init-sql/postgresql/01-expected-init.sql | 2 +-
10 files changed, 20 insertions(+), 19 deletions(-)
diff --git
a/kernel/sql-federation/optimizer/src/main/java/org/apache/shardingsphere/sqlfederation/optimizer/util/SQLFederationPlannerUtil.java
b/kernel/sql-federation/optimizer/src/main/java/org/apache/shardingsphere/sqlfederation/optimizer/util/SQLFederationPlannerUtil.java
index 4d6b0eeffff..711d21c9eb0 100644
---
a/kernel/sql-federation/optimizer/src/main/java/org/apache/shardingsphere/sqlfederation/optimizer/util/SQLFederationPlannerUtil.java
+++
b/kernel/sql-federation/optimizer/src/main/java/org/apache/shardingsphere/sqlfederation/optimizer/util/SQLFederationPlannerUtil.java
@@ -257,7 +257,8 @@ public final class SQLFederationPlannerUtil {
final
SQLParserRule sqlParserRule, final DatabaseType databaseType, final boolean
needsViewExpand) {
ViewExpander expander = needsViewExpand ? new
ShardingSphereViewExpander(sqlParserRule, databaseType,
createSqlToRelConverter(catalogReader, validator, cluster,
sqlParserRule, databaseType, false)) : (rowType, queryString, schemaPath,
viewPath) -> null;
- Config converterConfig =
SqlToRelConverter.config().withTrimUnusedFields(true);
+ // TODO remove withRemoveSortInSubQuery when calcite can expand view
which contains order by correctly
+ Config converterConfig =
SqlToRelConverter.config().withTrimUnusedFields(true).withRemoveSortInSubQuery(false);
return new SqlToRelConverter(expander, validator, catalogReader,
cluster, StandardConvertletTable.INSTANCE, converterConfig);
}
diff --git
a/test/integration-test/test-suite/src/test/resources/cases/dql/dql-integration-test-cases.xml
b/test/integration-test/test-suite/src/test/resources/cases/dql/dql-integration-test-cases.xml
index b55ebaf0c9d..cc8a1868dcd 100644
---
a/test/integration-test/test-suite/src/test/resources/cases/dql/dql-integration-test-cases.xml
+++
b/test/integration-test/test-suite/src/test/resources/cases/dql/dql-integration-test-cases.xml
@@ -648,7 +648,7 @@
<assertion parameters="10:int"
expected-data-source-name="read_dataset" />
</test-case>
- <test-case sql="SELECT * FROM t_order o INNER JOIN t_order_item i ON
o.order_id = i.order_id WHERE o.user_id = ? ORDER BY o.order_id, 7 LIMIT 1, 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">
<assertion parameters="10:int"
expected-data-source-name="read_dataset" />
</test-case>
@@ -656,7 +656,7 @@
<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 1, 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">
<assertion parameters="10:int"
expected-data-source-name="read_dataset" />
</test-case>
@@ -664,7 +664,7 @@
<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 1, 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">
<assertion parameters="10:int"
expected-data-source-name="read_dataset" />
</test-case>
@@ -693,7 +693,7 @@
<assertion parameters="10:int"
expected-data-source-name="read_dataset" />
</test-case>
- <test-case sql="SELECT * FROM t_order o INNER JOIN t_merchant m ON
o.merchant_id = m.merchant_id WHERE o.user_id = ? ORDER BY o.order_id LIMIT 1,
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">
<assertion parameters="10:int"
expected-data-source-name="read_dataset" />
</test-case>
@@ -714,7 +714,7 @@
<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 1, 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">
<assertion parameters="10:int"
expected-data-source-name="read_dataset" />
</test-case>
@@ -735,7 +735,7 @@
<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 1, 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">
<assertion parameters="10:int"
expected-data-source-name="read_dataset" />
</test-case>
@@ -983,7 +983,7 @@
<assertion parameters="1000:long"
expected-data-source-name="read_dataset" />
</test-case>
- <test-case sql="SELECT * FROM t_order_item_join_view LIMIT 1, 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">
<assertion expected-data-source-name="read_dataset" />
</test-case>
@@ -995,7 +995,7 @@
<assertion parameters="1201:long"
expected-data-source-name="read_dataset" />
</test-case>
- <test-case sql="SELECT * FROM t_order_subquery_view ORDER BY order_id
LIMIT 1, 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">
<assertion expected-data-source-name="read_dataset" />
</test-case>
@@ -1007,7 +1007,7 @@
<assertion parameters="8200:int"
expected-data-source-name="read_dataset" />
</test-case>
- <test-case sql="SELECT * FROM t_order_aggregation_view LIMIT 1, 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">
<assertion expected-data-source-name="read_dataset" />
</test-case>
@@ -1019,7 +1019,7 @@
<assertion parameters="2500:long"
expected-data-source-name="read_dataset" />
</test-case>
- <test-case sql="SELECT * FROM t_order_union_view ORDER BY order_id LIMIT
1, 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">
<assertion expected-data-source-name="read_dataset" />
</test-case>
diff --git
a/test/integration-test/test-suite/src/test/resources/env/scenario/db/data/actual/init-sql/h2/actual-logic_db-init.sql
b/test/integration-test/test-suite/src/test/resources/env/scenario/db/data/actual/init-sql/h2/actual-logic_db-init.sql
index dfbd322a73e..9e6322e78f9 100644
---
a/test/integration-test/test-suite/src/test/resources/env/scenario/db/data/actual/init-sql/h2/actual-logic_db-init.sql
+++
b/test/integration-test/test-suite/src/test/resources/env/scenario/db/data/actual/init-sql/h2/actual-logic_db-init.sql
@@ -20,7 +20,7 @@ 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 WHERE
o.user_id = 10 ORDER BY o.order_id, i.item_id;
+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/integration-test/test-suite/src/test/resources/env/scenario/db/data/actual/init-sql/mysql/actual-logic_db-init.sql
b/test/integration-test/test-suite/src/test/resources/env/scenario/db/data/actual/init-sql/mysql/actual-logic_db-init.sql
index e34dbc86608..e64ee300da9 100644
---
a/test/integration-test/test-suite/src/test/resources/env/scenario/db/data/actual/init-sql/mysql/actual-logic_db-init.sql
+++
b/test/integration-test/test-suite/src/test/resources/env/scenario/db/data/actual/init-sql/mysql/actual-logic_db-init.sql
@@ -20,7 +20,7 @@ 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 WHERE
o.user_id = 10 ORDER BY o.order_id, i.item_id;
+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/integration-test/test-suite/src/test/resources/env/scenario/db/data/actual/init-sql/opengauss/actual-logic_db-init.sql
b/test/integration-test/test-suite/src/test/resources/env/scenario/db/data/actual/init-sql/opengauss/actual-logic_db-init.sql
index e34dbc86608..e64ee300da9 100644
---
a/test/integration-test/test-suite/src/test/resources/env/scenario/db/data/actual/init-sql/opengauss/actual-logic_db-init.sql
+++
b/test/integration-test/test-suite/src/test/resources/env/scenario/db/data/actual/init-sql/opengauss/actual-logic_db-init.sql
@@ -20,7 +20,7 @@ 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 WHERE
o.user_id = 10 ORDER BY o.order_id, i.item_id;
+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/integration-test/test-suite/src/test/resources/env/scenario/db/data/actual/init-sql/postgresql/actual-logic_db-init.sql
b/test/integration-test/test-suite/src/test/resources/env/scenario/db/data/actual/init-sql/postgresql/actual-logic_db-init.sql
index e34dbc86608..e64ee300da9 100644
---
a/test/integration-test/test-suite/src/test/resources/env/scenario/db/data/actual/init-sql/postgresql/actual-logic_db-init.sql
+++
b/test/integration-test/test-suite/src/test/resources/env/scenario/db/data/actual/init-sql/postgresql/actual-logic_db-init.sql
@@ -20,7 +20,7 @@ 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 WHERE
o.user_id = 10 ORDER BY o.order_id, i.item_id;
+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/integration-test/test-suite/src/test/resources/env/scenario/db/data/expected/init-sql/h2/01-expected-init.sql
b/test/integration-test/test-suite/src/test/resources/env/scenario/db/data/expected/init-sql/h2/01-expected-init.sql
index 386bc0f98fe..f82c824a154 100644
---
a/test/integration-test/test-suite/src/test/resources/env/scenario/db/data/expected/init-sql/h2/01-expected-init.sql
+++
b/test/integration-test/test-suite/src/test/resources/env/scenario/db/data/expected/init-sql/h2/01-expected-init.sql
@@ -43,7 +43,7 @@ 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 WHERE
o.user_id = 10 ORDER BY o.order_id, i.item_id;
+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/integration-test/test-suite/src/test/resources/env/scenario/db/data/expected/init-sql/mysql/01-expected-init.sql
b/test/integration-test/test-suite/src/test/resources/env/scenario/db/data/expected/init-sql/mysql/01-expected-init.sql
index 5b07c53cc7b..f5f4fbcae66 100644
---
a/test/integration-test/test-suite/src/test/resources/env/scenario/db/data/expected/init-sql/mysql/01-expected-init.sql
+++
b/test/integration-test/test-suite/src/test/resources/env/scenario/db/data/expected/init-sql/mysql/01-expected-init.sql
@@ -29,7 +29,7 @@ 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 WHERE o.user_id = 10
ORDER BY o.order_id, i.item_id;
+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;
diff --git
a/test/integration-test/test-suite/src/test/resources/env/scenario/db/data/expected/init-sql/opengauss/01-expected-init.sql
b/test/integration-test/test-suite/src/test/resources/env/scenario/db/data/expected/init-sql/opengauss/01-expected-init.sql
index 6202d9e870d..aa505a845dc 100644
---
a/test/integration-test/test-suite/src/test/resources/env/scenario/db/data/expected/init-sql/opengauss/01-expected-init.sql
+++
b/test/integration-test/test-suite/src/test/resources/env/scenario/db/data/expected/init-sql/opengauss/01-expected-init.sql
@@ -50,7 +50,7 @@ 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 WHERE
o.user_id = 10 ORDER BY o.order_id, i.item_id;
+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/integration-test/test-suite/src/test/resources/env/scenario/db/data/expected/init-sql/postgresql/01-expected-init.sql
b/test/integration-test/test-suite/src/test/resources/env/scenario/db/data/expected/init-sql/postgresql/01-expected-init.sql
index 63f23297ee7..1264961aa46 100644
---
a/test/integration-test/test-suite/src/test/resources/env/scenario/db/data/expected/init-sql/postgresql/01-expected-init.sql
+++
b/test/integration-test/test-suite/src/test/resources/env/scenario/db/data/expected/init-sql/postgresql/01-expected-init.sql
@@ -50,7 +50,7 @@ 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 WHERE
o.user_id = 10 ORDER BY o.order_id, i.item_id;
+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;
