You are viewing a plain text version of this content. The canonical link for it is here.
Posted to notifications@shardingsphere.apache.org by yx...@apache.org on 2022/11/28 12:14:46 UTC
[shardingsphere] branch master updated: Fix select from view limit wrong result when view contains order by (#22486)
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 <du...@apache.org>
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;