You are viewing a plain text version of this content. The canonical link for it is here.
Posted to notifications@shardingsphere.apache.org by zh...@apache.org on 2026/09/06 08:36:08 UTC
(shardingsphere) branch master updated: Fix PostgreSQL and openGauss CTE column alias parsing (#39753)
This is an automated email from the ASF dual-hosted git repository.
terrymanu 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 c92b7f2c21a Fix PostgreSQL and openGauss CTE column alias parsing (#39753)
c92b7f2c21a is described below
commit c92b7f2c21a1d1a354c42f8ceab66dd28e0039c9
Author: Liang Zhang <zh...@apache.org>
AuthorDate: Sun Sep 6 16:36:02 2026 +0800
Fix PostgreSQL and openGauss CTE column alias parsing (#39753)
---
.../opengauss/visitor/statement/OpenGaussStatementVisitor.java | 6 +++++-
.../postgresql/visitor/statement/PostgreSQLStatementVisitor.java | 6 +++++-
.../test/resources/cases/dql/e2e-dql-select-using-with-clause.xml | 4 ++++
test/it/parser/src/main/resources/sql/supported/dml/select-with.xml | 4 ++--
4 files changed, 16 insertions(+), 4 deletions(-)
diff --git a/parser/sql/engine/dialect/opengauss/src/main/java/org/apache/shardingsphere/sql/parser/engine/opengauss/visitor/statement/OpenGaussStatementVisitor.java b/parser/sql/engine/dialect/opengauss/src/main/java/org/apache/shardingsphere/sql/parser/engine/opengauss/visitor/statement/OpenGaussStatementVisitor.java
index cb4aa1d4cd9..c7958796413 100644
--- a/parser/sql/engine/dialect/opengauss/src/main/java/org/apache/shardingsphere/sql/parser/engine/opengauss/visitor/statement/OpenGaussStatementVisitor.java
+++ b/parser/sql/engine/dialect/opengauss/src/main/java/org/apache/shardingsphere/sql/parser/engine/opengauss/visitor/statement/OpenGaussStatementVisitor.java
@@ -1026,9 +1026,13 @@ public abstract class OpenGaussStatementVisitor extends OpenGaussStatementParser
@Override
public ASTNode visitCommonTableExpr(final CommonTableExprContext ctx) {
- return new CommonTableExpressionSegment(ctx.getStart().getStartIndex(), ctx.getStop().getStopIndex(), (AliasSegment) visit(ctx.alias()),
+ CommonTableExpressionSegment result = new CommonTableExpressionSegment(ctx.getStart().getStartIndex(), ctx.getStop().getStopIndex(), (AliasSegment) visit(ctx.alias()),
new SubquerySegment(ctx.getStart().getStartIndex(), ctx.getStop().getStopIndex(), (SelectStatement) visit(ctx.preparableStmt().select()),
getOriginalText(ctx.preparableStmt().select())));
+ if (null != ctx.optNameList().nameList()) {
+ result.getColumns().addAll(generateUsingColumn(ctx.optNameList().nameList()));
+ }
+ return result;
}
@Override
diff --git a/parser/sql/engine/dialect/postgresql/src/main/java/org/apache/shardingsphere/sql/parser/engine/postgresql/visitor/statement/PostgreSQLStatementVisitor.java b/parser/sql/engine/dialect/postgresql/src/main/java/org/apache/shardingsphere/sql/parser/engine/postgresql/visitor/statement/PostgreSQLStatementVisitor.java
index 918bd787c1d..399ffd14d31 100644
--- a/parser/sql/engine/dialect/postgresql/src/main/java/org/apache/shardingsphere/sql/parser/engine/postgresql/visitor/statement/PostgreSQLStatementVisitor.java
+++ b/parser/sql/engine/dialect/postgresql/src/main/java/org/apache/shardingsphere/sql/parser/engine/postgresql/visitor/statement/PostgreSQLStatementVisitor.java
@@ -1060,9 +1060,13 @@ public abstract class PostgreSQLStatementVisitor extends PostgreSQLStatementPars
@Override
public ASTNode visitCommonTableExpr(final CommonTableExprContext ctx) {
- return new CommonTableExpressionSegment(ctx.getStart().getStartIndex(), ctx.getStop().getStopIndex(), (AliasSegment) visit(ctx.alias()),
+ CommonTableExpressionSegment result = new CommonTableExpressionSegment(ctx.getStart().getStartIndex(), ctx.getStop().getStopIndex(), (AliasSegment) visit(ctx.alias()),
new SubquerySegment(ctx.getStart().getStartIndex(), ctx.getStop().getStopIndex(), (SelectStatement) visit(ctx.preparableStmt().select()),
getOriginalText(ctx.preparableStmt().select())));
+ if (null != ctx.optNameList().nameList()) {
+ result.getColumns().addAll(generateUsingColumn(ctx.optNameList().nameList()));
+ }
+ return result;
}
@Override
diff --git a/test/e2e/sql/src/test/resources/cases/dql/e2e-dql-select-using-with-clause.xml b/test/e2e/sql/src/test/resources/cases/dql/e2e-dql-select-using-with-clause.xml
index edef0106899..31d1c92ce37 100644
--- a/test/e2e/sql/src/test/resources/cases/dql/e2e-dql-select-using-with-clause.xml
+++ b/test/e2e/sql/src/test/resources/cases/dql/e2e-dql-select-using-with-clause.xml
@@ -23,4 +23,8 @@
<test-case sql="WITH products (col1, col2, col3, col4, col5, col6) AS (SELECT * FROM t_product) SELECT col1 FROM products" db-types="MySQL" scenario-types="db">
<assertion />
</test-case>
+
+ <test-case sql="WITH temp_tb(i, j, k) AS (SELECT user_id, order_id, status FROM t_order) SELECT * FROM temp_tb WHERE j = 1001" db-types="PostgreSQL,openGauss" scenario-types="tbl">
+ <assertion />
+ </test-case>
</e2e-test-cases>
diff --git a/test/it/parser/src/main/resources/sql/supported/dml/select-with.xml b/test/it/parser/src/main/resources/sql/supported/dml/select-with.xml
index 33909e24ec2..a1457492490 100644
--- a/test/it/parser/src/main/resources/sql/supported/dml/select-with.xml
+++ b/test/it/parser/src/main/resources/sql/supported/dml/select-with.xml
@@ -24,14 +24,14 @@
<sql-case id="select_with_subquery_factoring_with_cycle_parameter_marker" value="WITH dup_hiredate (eid) AS (SELECT employee_id FROM employees) CYCLE eid SET is_cycle TO ? DEFAULT ? SELECT eid, is_cycle FROM dup_hiredate" db-types="Oracle" />
<sql-case id="select_with_subquery_factoring_with_search_depth_first_with_having" value="WITH emp_count (eid, emp_last, mgr_id, mgrLevel, salary, cnt_employees) AS (SELECT employee_id, last_name, manager_id, mgrLevel, salary, cnt_employees FROM employees) SEARCH DEPTH FIRST BY emp_last SET order1 SELECT emp_last, eid, mgr_id, salary FROM emp_count GROUP BY emp_last, eid, mgr_id, salary HAVING salary > 24000 ORDER BY mgr_id NULLS FIRST, emp_last" db-types="Oracle" />
<sql-case id="select_with_multiple_cte_definitions" value="WITH cte1(status, user_id) AS (SELECT status, user_id FROM t_order), cte2(item_id) AS (SELECT item_id FROM t_order_item) SELECT status, user_id, item_id FROM cte1 INNER JOIN cte2 ON cte1.user_id = cte2.user_id" db-types="SQLServer" />
- <sql-case id="select_with_single_subquery" value="WITH t AS (SELECT a+2 c,b FROM t1) SELECT c,b FROM t" db-types="MySQL,Doris,Firebird" />
+ <sql-case id="select_with_single_subquery" value="WITH t AS (SELECT a+2 c,b FROM t1) SELECT c,b FROM t" db-types="MySQL,PostgreSQL,openGauss,Doris,Firebird" />
<sql-case id="select_with_oracle_single_subquery" value="WITH t AS (SELECT a+2 c,b FROM t1) SELECT c,b FROM t" db-types="Oracle" />
<sql-case id="select_with_oracle_cte_column_aliases" value="WITH user_cte (user_id_alias, user_name_alias, email_alias) AS (SELECT user_id, user_name, email FROM t_user) SELECT user_id_alias, user_name_alias, email_alias FROM user_cte ORDER BY user_id_alias" db-types="Oracle" />
<sql-case id="select_with_multiple_subquery" value="WITH cte1(col1, col2, col3) as (SELECT emp_no,first_name,last_name FROM employees WHERE emp_no=10012), cte2(col1, col2, col3) as (SELECT emp_no,first_name,last_name from employees WHERE emp_no=10012) SELECT col1, col2, col3 FROM cte1" db-types="MySQL" />
<sql-case id="select_with_recursive_union_all1" value="WITH RECURSIVE DirectoryCTE as (SELECT * FROM table1 WHERE id = 1 AND project_id = 2 UNION ALL SELECT * FROM project_file_catalog t INNER JOIN DirectoryCTE cte ON t.project_id = cte.project_id AND t.parent_id = cte.id) SELECT * FROM DirectoryCTE ORDER BY level" db-types="MySQL" />
<sql-case id="select_with_oracle_recursive_union_all1" value="WITH DirectoryCTE as (SELECT table1.col1, table1.col2 FROM table1 WHERE id = 1 AND project_id = 2 UNION ALL SELECT t.col1, t.col2 FROM project_file_catalog t INNER JOIN DirectoryCTE cte ON t.project_id = cte.project_id AND t.parent_id = cte.id) SELECT DirectoryCTE.col1, DirectoryCTE.col2 FROM DirectoryCTE ORDER BY level" db-types="Oracle" />
<sql-case id="select_with_recursive_union_all2" value="WITH cte AS (SELECT 1 AS col1, 2 AS col2 UNION ALL SELECT 3, 4) SELECT col1, col2 FROM cte" db-types="MySQL" />
- <sql-case id="select_with_columns_cte" value="WITH cte(a,b) AS (SELECT 1,2) SELECT * FROM cte" db-types="MySQL" />
+ <sql-case id="select_with_columns_cte" value="WITH cte(a,b) AS (SELECT 1,2) SELECT * FROM cte" db-types="MySQL,PostgreSQL,openGauss" />
<sql-case id="select_with_projection_parameter_mysql" value="WITH combined_users AS (SELECT user_id FROM t_user WHERE user_id = ? UNION ALL SELECT merchant_id AS user_id FROM t_merchant WHERE merchant_id = ?) SELECT t_order.order_id + 5000, combined_users.user_id, ?, t_order.merchant_id, t_order.remark, t_order.creation_date FROM t_order JOIN combined_users ON t_order.user_id = combined_users.user_id ORDER BY combined_users.user_id ASC, t_order.order_id ASC LIMIT ?" case-types="PLACE [...]
<sql-case id="select_with_recursive_firebird" value="WITH RECURSIVE t(id) AS (SELECT 1) SELECT id FROM t" db-types="Firebird" />
</sql-cases>