This is an automated email from the ASF dual-hosted git repository.
strongduanmu 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 cf2c169ed16 Support parenthesized WITH queries and qualified PL/SQL
collection types in Oracle parser (#39914)
cf2c169ed16 is described below
commit cf2c169ed1692d84d7df79f85bda75b699143e26
Author: ZhangCheng <[email protected]>
AuthorDate: Tue Sep 22 14:07:12 2026 +0800
Support parenthesized WITH queries and qualified PL/SQL collection types in
Oracle parser (#39914)
* Support additional Oracle parser syntax
* Apply Spotless formatting to Oracle parser test
* Fix Oracle parser edge cases
* Update release notes for Oracle parser enhancements
---
RELEASE-NOTES.md | 2 ++
.../src/main/antlr4/imports/oracle/DMLStatement.g4 | 2 +-
.../oracle/src/main/antlr4/imports/oracle/PLSQL.g4 | 9 +++++++--
.../statement/type/OracleDDLStatementVisitor.java | 7 ++++---
.../statement/type/OracleDMLStatementVisitor.java | 11 +++++++++++
.../type/OracleDMLStatementVisitorTest.java | 23 ++++++++++++++++++++--
.../main/resources/case/ddl/create-procedure.xml | 1 +
.../src/main/resources/case/dml/select-with.xml | 23 ++++++++++++++++++++++
.../sql/supported/ddl/create-procedure.xml | 1 +
.../resources/sql/supported/dml/select-with.xml | 1 +
10 files changed, 72 insertions(+), 8 deletions(-)
diff --git a/RELEASE-NOTES.md b/RELEASE-NOTES.md
index fd18eaf67c8..2d29ec08451 100644
--- a/RELEASE-NOTES.md
+++ b/RELEASE-NOTES.md
@@ -131,6 +131,8 @@
1. SQL Parser: Support Oracle PL/SQL syntax parsing and binding -
[#39723](https://github.com/apache/shardingsphere/pull/39723)
1. SQL Parser: Support scalar expressions in Oracle `IN` predicates -
[#39876](https://github.com/apache/shardingsphere/pull/39876)
1. SQL Parser: Support Oracle `ORDER BY` after `FOR UPDATE` and `HAVING`
without `GROUP BY` -
[#39877](https://github.com/apache/shardingsphere/pull/39877)
+1. SQL Parser: Support Oracle parenthesized `WITH` queries and qualified
PL/SQL collection types -
[#39914](https://github.com/apache/shardingsphere/pull/39914)
+1. SQL Parser: Support Oracle `DROP UNUSED COLUMNS` syntax -
[#39917](https://github.com/apache/shardingsphere/pull/39917)
1. SQL Binder: Support select order by index bind metadata -
[#38386](https://github.com/apache/shardingsphere/pull/38386)
1. SQL Binder: Support SQL bind when with temp table name is same with
physical table - [#38411](https://github.com/apache/shardingsphere/pull/38411)
1. SQL Binder: Support PostgreSQL whole-row projection binding -
[#39276](https://github.com/apache/shardingsphere/pull/39276)
diff --git
a/parser/sql/engine/dialect/oracle/src/main/antlr4/imports/oracle/DMLStatement.g4
b/parser/sql/engine/dialect/oracle/src/main/antlr4/imports/oracle/DMLStatement.g4
index 63a7ecacdfb..0c2c213c31b 100644
---
a/parser/sql/engine/dialect/oracle/src/main/antlr4/imports/oracle/DMLStatement.g4
+++
b/parser/sql/engine/dialect/oracle/src/main/antlr4/imports/oracle/DMLStatement.g4
@@ -160,7 +160,7 @@ select
;
selectSubquery
- : selectSubquery combineType selectSubquery | ((queryBlock |
parenthesisSelectSubquery) pivotClause? orderByClause? rowLimitingClause)
+ : selectSubquery combineType selectSubquery | ((queryBlock | withClause
parenthesisSelectSubquery | parenthesisSelectSubquery) pivotClause?
orderByClause? rowLimitingClause)
;
combineType
diff --git
a/parser/sql/engine/dialect/oracle/src/main/antlr4/imports/oracle/PLSQL.g4
b/parser/sql/engine/dialect/oracle/src/main/antlr4/imports/oracle/PLSQL.g4
index 54b6b486388..f781265e017 100644
--- a/parser/sql/engine/dialect/oracle/src/main/antlr4/imports/oracle/PLSQL.g4
+++ b/parser/sql/engine/dialect/oracle/src/main/antlr4/imports/oracle/PLSQL.g4
@@ -581,13 +581,18 @@ itemDeclaration
collectionVariableDecl
: variableName
(
- typeName (ASSIGNMENT_OPERATOR_ (qualifiedExpression | functionCall |
variableName))?
- | typeName (ASSIGNMENT_OPERATOR_ (collectionConstructor |
variableName))?
+ collectionTypeName (ASSIGNMENT_OPERATOR_ (qualifiedExpression |
functionCall | variableName))?
+ | collectionTypeName (ASSIGNMENT_OPERATOR_ (collectionConstructor |
variableName))?
| typeName MOD_ TYPE
)
SEMI_
;
+collectionTypeName
+ : typeName
+ | schemaName DOT_ owner DOT_ name
+ ;
+
qualifiedExpression
: typemark LP_ aggregate RP_
;
diff --git
a/parser/sql/engine/dialect/oracle/src/main/java/org/apache/shardingsphere/sql/parser/engine/oracle/visitor/statement/type/OracleDDLStatementVisitor.java
b/parser/sql/engine/dialect/oracle/src/main/java/org/apache/shardingsphere/sql/parser/engine/oracle/visitor/statement/type/OracleDDLStatementVisitor.java
index 84dbfd5a7da..c9ece94fcec 100644
---
a/parser/sql/engine/dialect/oracle/src/main/java/org/apache/shardingsphere/sql/parser/engine/oracle/visitor/statement/type/OracleDDLStatementVisitor.java
+++
b/parser/sql/engine/dialect/oracle/src/main/java/org/apache/shardingsphere/sql/parser/engine/oracle/visitor/statement/type/OracleDDLStatementVisitor.java
@@ -2381,9 +2381,10 @@ public final class OracleDDLStatementVisitor extends
OracleStatementVisitor impl
@Override
public Void visitCollectionVariableDecl(final
CollectionVariableDeclContext ctx) {
- if (null != ctx.TYPE() && null != ctx.MOD_() && null !=
ctx.typeName().owner()) {
-
result.getTables().add(createTypeAttributeTable(ctx.typeName()));
-
result.getColumns().add(createTypeAttributeColumn(ctx.typeName()));
+ TypeNameContext typeName = null == ctx.typeName() ?
ctx.collectionTypeName().typeName() : ctx.typeName();
+ if (null != ctx.TYPE() && null != ctx.MOD_() && null !=
typeName.owner()) {
+ result.getTables().add(createTypeAttributeTable(typeName));
+ result.getColumns().add(createTypeAttributeColumn(typeName));
}
return super.visitCollectionVariableDecl(ctx);
}
diff --git
a/parser/sql/engine/dialect/oracle/src/main/java/org/apache/shardingsphere/sql/parser/engine/oracle/visitor/statement/type/OracleDMLStatementVisitor.java
b/parser/sql/engine/dialect/oracle/src/main/java/org/apache/shardingsphere/sql/parser/engine/oracle/visitor/statement/type/OracleDMLStatementVisitor.java
index 220cbb83206..5ce990d4955 100644
---
a/parser/sql/engine/dialect/oracle/src/main/java/org/apache/shardingsphere/sql/parser/engine/oracle/visitor/statement/type/OracleDMLStatementVisitor.java
+++
b/parser/sql/engine/dialect/oracle/src/main/java/org/apache/shardingsphere/sql/parser/engine/oracle/visitor/statement/type/OracleDMLStatementVisitor.java
@@ -762,9 +762,20 @@ public final class OracleDMLStatementVisitor extends
OracleStatementVisitor impl
result = createSelectCombineClause(ctx, left);
} else {
result = null == ctx.queryBlock() ? (SelectStatement)
visit(ctx.parenthesisSelectSubquery()) : (SelectStatement)
visit(ctx.queryBlock());
+ if (null != ctx.withClause()) {
+ SelectStatement previous = result;
+ result =
createSelectStatementBuilder(previous).with((WithSegment)
visit(ctx.withClause())).build();
+ result.addParameterMarkers(previous.getParameterMarkers());
+ result.getVariableNames().addAll(previous.getVariableNames());
+ result.getComments().addAll(previous.getComments());
+ }
}
if (null != ctx.orderByClause()) {
+ SelectStatement previous = result;
result =
createSelectStatementBuilder(result).orderBy((OrderBySegment)
visit(ctx.orderByClause())).build();
+ result.addParameterMarkers(previous.getParameterMarkers());
+ result.getVariableNames().addAll(previous.getVariableNames());
+ result.getComments().addAll(previous.getComments());
}
result.addParameterMarkers(ctx.getParent() instanceof ExecuteContext ?
getGlobalParameterMarkerSegments() : popAllStatementParameterMarkerSegments());
result.getVariableNames().addAll(getVariableNames());
diff --git
a/parser/sql/engine/dialect/oracle/src/test/java/org/apache/shardingsphere/sql/parser/engine/oracle/visitor/statement/type/OracleDMLStatementVisitorTest.java
b/parser/sql/engine/dialect/oracle/src/test/java/org/apache/shardingsphere/sql/parser/engine/oracle/visitor/statement/type/OracleDMLStatementVisitorTest.java
index 6c1bae49841..104643517de 100644
---
a/parser/sql/engine/dialect/oracle/src/test/java/org/apache/shardingsphere/sql/parser/engine/oracle/visitor/statement/type/OracleDMLStatementVisitorTest.java
+++
b/parser/sql/engine/dialect/oracle/src/test/java/org/apache/shardingsphere/sql/parser/engine/oracle/visitor/statement/type/OracleDMLStatementVisitorTest.java
@@ -21,17 +21,36 @@ import
org.apache.shardingsphere.sql.parser.engine.api.CacheOption;
import org.apache.shardingsphere.sql.parser.engine.api.SQLParserEngine;
import
org.apache.shardingsphere.sql.parser.engine.api.SQLStatementVisitorEngine;
import
org.apache.shardingsphere.sql.parser.engine.exception.SQLParsingException;
+import
org.apache.shardingsphere.sql.parser.statement.core.statement.type.dml.SelectStatement;
import org.junit.jupiter.api.Test;
+import static org.hamcrest.MatcherAssert.assertThat;
+import static org.hamcrest.Matchers.is;
import static org.junit.jupiter.api.Assertions.assertThrows;
+import static org.junit.jupiter.api.Assertions.assertTrue;
class OracleDMLStatementVisitorTest {
@Test
void assertVisitSelect() {
+ assertThrows(SQLParsingException.class, () -> parse("SELECT 1 FROM
DUAL ORDER BY 1 FOR UPDATE NOWAIT ORDER BY 1"));
+ }
+
+ @Test
+ void assertVisitWithParenthesizedMainQuery() {
+ SelectStatement actual = parse("WITH q AS (SELECT 1 AS n FROM DUAL)
(SELECT n FROM q)");
+ assertTrue(actual.getWith().isPresent());
+ }
+
+ @Test
+ void assertVisitWithParenthesizedMainQueryWithOrderBy() {
+ SelectStatement actual = parse("WITH q AS (SELECT 1 AS n FROM DUAL)
(SELECT ? FROM q) ORDER BY 1");
+ assertThat(actual.getParameterMarkers().size(), is(1));
+ }
+
+ private SelectStatement parse(final String sql) {
CacheOption cacheOption = new CacheOption(128, 1024L);
SQLParserEngine parserEngine = new SQLParserEngine("Oracle",
cacheOption);
- SQLStatementVisitorEngine visitorEngine = new
SQLStatementVisitorEngine("Oracle");
- assertThrows(SQLParsingException.class, () ->
visitorEngine.visit(parserEngine.parse("SELECT 1 FROM DUAL ORDER BY 1 FOR
UPDATE NOWAIT ORDER BY 1", false)));
+ return (SelectStatement) new
SQLStatementVisitorEngine("Oracle").visit(parserEngine.parse(sql, false));
}
}
diff --git a/test/it/parser/src/main/resources/case/ddl/create-procedure.xml
b/test/it/parser/src/main/resources/case/ddl/create-procedure.xml
index 27f48287021..407afce7268 100644
--- a/test/it/parser/src/main/resources/case/ddl/create-procedure.xml
+++ b/test/it/parser/src/main/resources/case/ddl/create-procedure.xml
@@ -51,6 +51,7 @@
</sql-statements>
</create-procedure>
<create-procedure sql-case-id="create_plsql_block" />
+ <create-procedure
sql-case-id="create_plsql_block_with_three_part_collection_type" />
<create-procedure sql-case-id="create_procedure_with_insert_into_values">
<procedure-name name="T522_PROC" />
<sql-statements>
diff --git a/test/it/parser/src/main/resources/case/dml/select-with.xml
b/test/it/parser/src/main/resources/case/dml/select-with.xml
index 93545f7863d..ac4cd883019 100644
--- a/test/it/parser/src/main/resources/case/dml/select-with.xml
+++ b/test/it/parser/src/main/resources/case/dml/select-with.xml
@@ -484,6 +484,29 @@
</from>
</select>
+ <select sql-case-id="select_with_parenthesized_main_query_oracle">
+ <with start-index="0" stop-index="26">
+ <common-table-expression name="q" start-index="5" stop-index="26">
+ <subquery-expression start-index="11" stop-index="25">
+ <select>
+ <projections start-index="18" stop-index="18">
+ <column-projection name="n" start-index="18"
stop-index="18" />
+ </projections>
+ <from>
+ <simple-table name="t" start-index="25"
stop-index="25" />
+ </from>
+ </select>
+ </subquery-expression>
+ </common-table-expression>
+ </with>
+ <projections start-index="36" stop-index="36">
+ <column-projection name="n" start-index="36" stop-index="36" />
+ </projections>
+ <from>
+ <simple-table name="q" start-index="43" stop-index="43" />
+ </from>
+ </select>
+
<select sql-case-id="select_with_oracle_cte_column_aliases">
<with start-index="0" stop-index="108">
<common-table-expression name="user_cte" start-index="5"
stop-index="108">
diff --git
a/test/it/parser/src/main/resources/sql/supported/ddl/create-procedure.xml
b/test/it/parser/src/main/resources/sql/supported/ddl/create-procedure.xml
index b7d6bdce229..6687c84b1da 100644
--- a/test/it/parser/src/main/resources/sql/supported/ddl/create-procedure.xml
+++ b/test/it/parser/src/main/resources/sql/supported/ddl/create-procedure.xml
@@ -47,6 +47,7 @@
<sql-case id="create_procedure_with_cursor_definition" value="CREATE OR
REPLACE EDITIONABLE PROCEDURE my_proc AS cursor cursor1 is select distinct a
from tbl; var1 varchar2(500); var2 varchar2(500); BEGIN INSERT INTO t VALUES
(var1, var2); END;" db-types="Oracle" />
<sql-case id="create_procedure_with_collection_type_definition"
value="CREATE OR REPLACE EDITIONABLE PROCEDURE my_proc AS TYPE my_type is table
of my_table.id%TYPE; BEGIN INSERT INTO t VALUES (var1, var2); END;"
db-types="Oracle" />
<sql-case id="create_plsql_block" value="DECLARE warehouse NUMBER := 1;
ground NUMBER := 1; insured NUMBER := 1; result NUMBER; BEGIN
SELECT BIN_TO_NUM(warehouse, ground, insured) INTO result FROM DUAL; UPDATE
orders SET order_status = result WHERE order_id = 2441; END;" db-types="Oracle"
/>
+ <sql-case id="create_plsql_block_with_three_part_collection_type"
value="DECLARE foo_values SYS.DBMS_SQL.VARCHAR2A; BEGIN NULL; END;"
db-types="Oracle" />
<sql-case id="create_procedure_with_insert_into_values" value="create
procedure T522_PROC (i int =10) as begin insert into T522 (ROW_INT) values
(:i); end" db-types="Firebird"/>
<sql-case
id="create_procedure_with_declare_and_cursor_for_in_select_and_open"
value="CREATE PROCEDURE F865_PROC (offset_value INT, page_size INT) as DECLARE
catalog_page CURSOR FOR (SELECT * FROM company ORDER BY namecompany OFFSET
:offset_value ROWS FETCH NEXT :page_size ROWS ONLY); BEGIN OPEN catalog_page;
END" db-types="Firebird"/>
<sql-case id="create_procedure_with_handler_and_table_creation"
value="CREATE PROCEDURE peter1 () BEGIN DECLARE v INTEGER DEFAULT 1234; DECLARE
CONTINUE HANDLER FOR SQLWARNING BEGIN SHOW WARNINGS; SELECT "handler done:
",v; END; CREATE TABLE gg (smallint_column SMALLINT); CALL peter2(v); END"
db-types="MySQL" />
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 a1457492490..3d8fa0ca8cb 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
@@ -26,6 +26,7 @@
<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,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_parenthesized_main_query_oracle" value="WITH q
AS (SELECT n FROM t) (SELECT n FROM q)" 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" />
