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 55706067d1c Support Oracle SQL parser correctly extract REGEXP_SUBSTR
parameters. (#37924)
55706067d1c is described below
commit 55706067d1c7721c336361b526e70f8c904cdf07
Author: Claire <[email protected]>
AuthorDate: Mon Feb 2 14:41:15 2026 +0800
Support Oracle SQL parser correctly extract REGEXP_SUBSTR parameters.
(#37924)
* support Oracle SQL parser
* support new version
* update
* update
* support test
* realese-notes
* realese-notes
* realese-notes
---
RELEASE-NOTES.md | 2 +
.../visitor/statement/OracleStatementVisitor.java | 110 ++++++++++++++++++++-
.../statement/type/OracleDMLStatementVisitor.java | 31 +++++-
.../dml/predicate/HierarchicalQuerySegment.java | 43 ++++++++
.../core/statement/type/dml/SelectStatement.java | 12 +++
.../case/dml/select-start-with-connect-by.xml | 34 +++++++
.../supported/dml/select-start-with-connect-by.xml | 1 +
7 files changed, 231 insertions(+), 2 deletions(-)
diff --git a/RELEASE-NOTES.md b/RELEASE-NOTES.md
index b4215e244ce..567841328ed 100644
--- a/RELEASE-NOTES.md
+++ b/RELEASE-NOTES.md
@@ -6,6 +6,8 @@
### Bug Fixes
+1. SQL Parser: Support Oracle SQL parser correctly extract REGEXP_SUBSTR
parameters - [#37924](https://github.com/apache/shardingsphere/pull/37924)
+
## Release 5.5.3
### CVE
diff --git
a/parser/sql/engine/dialect/oracle/src/main/java/org/apache/shardingsphere/sql/parser/engine/oracle/visitor/statement/OracleStatementVisitor.java
b/parser/sql/engine/dialect/oracle/src/main/java/org/apache/shardingsphere/sql/parser/engine/oracle/visitor/statement/OracleStatementVisitor.java
index 46ef6ce099e..cbf3b099279 100644
---
a/parser/sql/engine/dialect/oracle/src/main/java/org/apache/shardingsphere/sql/parser/engine/oracle/visitor/statement/OracleStatementVisitor.java
+++
b/parser/sql/engine/dialect/oracle/src/main/java/org/apache/shardingsphere/sql/parser/engine/oracle/visitor/statement/OracleStatementVisitor.java
@@ -224,7 +224,96 @@ public abstract class OracleStatementVisitor extends
OracleStatementBaseVisitor<
@Override
public final ASTNode visitParameterMarker(final ParameterMarkerContext
ctx) {
- return new ParameterMarkerValue(globalParameterMarkerSegments.size(),
ParameterMarkerType.QUESTION);
+ return new ParameterMarkerValue(getParameterMarkerIndex(ctx),
ParameterMarkerType.QUESTION);
+ }
+
+ private int getParameterMarkerIndex(final ParameterMarkerContext ctx) {
+ int startIndex = ctx.getStart().getStartIndex();
+ if (startIndex <= 0) {
+ return 0;
+ }
+ String sql = ctx.getStart().getInputStream().getText(new Interval(0,
startIndex - 1));
+ return countParameterMarkers(sql);
+ }
+
+ private int countParameterMarkers(final String sql) {
+ ParameterMarkerScanState state = new ParameterMarkerScanState();
+ while (state.index < sql.length()) {
+ if (advanceInLineComment(sql, state)) {
+ continue;
+ }
+ if (advanceInBlockComment(sql, state)) {
+ continue;
+ }
+ if (advanceInOrToggleStringLiteral(sql, state)) {
+ continue;
+ }
+ if (enterLineOrBlockComment(sql, state)) {
+ continue;
+ }
+ if (!state.inStringLiteral && sql.charAt(state.index) == '?') {
+ state.result++;
+ }
+ state.index++;
+ }
+ return state.result;
+ }
+
+ private boolean advanceInLineComment(final String sql, final
ParameterMarkerScanState state) {
+ if (!state.inLineComment) {
+ return false;
+ }
+ char ch = sql.charAt(state.index);
+ if ('\n' == ch || '\r' == ch) {
+ state.inLineComment = false;
+ }
+ state.index++;
+ return true;
+ }
+
+ private boolean advanceInBlockComment(final String sql, final
ParameterMarkerScanState state) {
+ if (!state.inBlockComment) {
+ return false;
+ }
+ char ch = sql.charAt(state.index);
+ if ('*' == ch && state.index + 1 < sql.length() && '/' ==
sql.charAt(state.index + 1)) {
+ state.inBlockComment = false;
+ state.index += 2;
+ } else {
+ state.index++;
+ }
+ return true;
+ }
+
+ private boolean advanceInOrToggleStringLiteral(final String sql, final
ParameterMarkerScanState state) {
+ if (sql.charAt(state.index) != '\'') {
+ return false;
+ }
+ if (state.inStringLiteral && state.index + 1 < sql.length() && '\'' ==
sql.charAt(state.index + 1)) {
+ state.index += 2;
+ } else {
+ state.inStringLiteral = !state.inStringLiteral;
+ state.index++;
+ }
+ return true;
+ }
+
+ private boolean enterLineOrBlockComment(final String sql, final
ParameterMarkerScanState state) {
+ if (state.inStringLiteral) {
+ return false;
+ }
+ char ch = sql.charAt(state.index);
+ if ('-' == ch && state.index + 1 < sql.length() && '-' ==
sql.charAt(state.index + 1)) {
+ state.inLineComment = true;
+ state.index += 2;
+ return true;
+ }
+ if ('/' == ch && state.index + 1 < sql.length() && '*' ==
sql.charAt(state.index + 1)) {
+ state.inBlockComment = true;
+ state.index += 2;
+ return true;
+ }
+ return false;
}
@Override
@@ -533,6 +622,12 @@ public abstract class OracleStatementVisitor extends
OracleStatementBaseVisitor<
if (null != ctx.LIKE()) {
return createBinaryOperationExpressionFromLike(ctx);
}
+ if (null != ctx.PRIOR()) {
+ return null == ctx.predicate() ? new
CommonExpressionSegment(ctx.start.getStartIndex(), ctx.stop.getStopIndex(),
getOriginalText(ctx)) : visit(ctx.predicate());
+ }
+ if (null == ctx.bitExpr(0)) {
+ return new CommonExpressionSegment(ctx.start.getStartIndex(),
ctx.stop.getStopIndex(), getOriginalText(ctx));
+ }
return visit(ctx.bitExpr(0));
}
@@ -1317,4 +1412,17 @@ public abstract class OracleStatementVisitor extends
OracleStatementBaseVisitor<
protected void decreaseCursorForLoopLevel() {
--cursorForLoopLevel;
}
+
+ private static final class ParameterMarkerScanState {
+
+ private int index;
+
+ private int result;
+
+ private boolean inStringLiteral;
+
+ private boolean inLineComment;
+
+ private boolean inBlockComment;
+ }
}
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 6a2884aef76..439e94f8dcb 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
@@ -107,6 +107,7 @@ import
org.apache.shardingsphere.sql.parser.autogen.OracleStatementParser.Update
import
org.apache.shardingsphere.sql.parser.autogen.OracleStatementParser.UsingClauseContext;
import
org.apache.shardingsphere.sql.parser.autogen.OracleStatementParser.WhereClauseContext;
import
org.apache.shardingsphere.sql.parser.autogen.OracleStatementParser.WithClauseContext;
+import
org.apache.shardingsphere.sql.parser.autogen.OracleStatementParser.HierarchicalQueryClauseContext;
import
org.apache.shardingsphere.sql.parser.engine.oracle.visitor.statement.OracleStatementVisitor;
import org.apache.shardingsphere.sql.parser.statement.core.enums.CombineType;
import org.apache.shardingsphere.sql.parser.statement.core.enums.JoinType;
@@ -150,6 +151,7 @@ import
org.apache.shardingsphere.sql.parser.statement.core.segment.dml.order.ite
import
org.apache.shardingsphere.sql.parser.statement.core.segment.dml.order.item.IndexOrderByItemSegment;
import
org.apache.shardingsphere.sql.parser.statement.core.segment.dml.order.item.OrderByItemSegment;
import
org.apache.shardingsphere.sql.parser.statement.core.segment.dml.predicate.HavingSegment;
+import
org.apache.shardingsphere.sql.parser.statement.core.segment.dml.predicate.HierarchicalQuerySegment;
import
org.apache.shardingsphere.sql.parser.statement.core.segment.dml.predicate.LockSegment;
import
org.apache.shardingsphere.sql.parser.statement.core.segment.dml.predicate.WhereSegment;
import
org.apache.shardingsphere.sql.parser.statement.core.segment.dml.table.MultiTableConditionalIntoElseSegment;
@@ -441,10 +443,12 @@ public final class OracleDMLStatementVisitor extends
OracleStatementVisitor impl
if (null != ctx.whereClause()) {
result.setWhere((WhereSegment) visit(ctx.whereClause()));
}
+ if (null != ctx.hierarchicalQueryClause()) {
+ result.setHierarchicalQuery((HierarchicalQuerySegment)
visit(ctx.hierarchicalQueryClause()));
+ }
if (null != ctx.groupByClause()) {
result.setGroupBy((GroupBySegment) visit(ctx.groupByClause()));
}
- // TODO Visit hierarchicalQueryClause
if (null != ctx.modelClause()) {
result.setModel((ModelSegment) visit(ctx.modelClause()));
}
@@ -649,6 +653,9 @@ public final class OracleDMLStatementVisitor extends
OracleStatementVisitor impl
if (null != ctx.whereClause()) {
result.setWhere((WhereSegment) visit(ctx.whereClause()));
}
+ if (null != ctx.hierarchicalQueryClause()) {
+ result.setHierarchicalQuery((HierarchicalQuerySegment)
visit(ctx.hierarchicalQueryClause()));
+ }
if (null != ctx.groupByClause()) {
result.setGroupBy((GroupBySegment) visit(ctx.groupByClause()));
if (null != ctx.groupByClause().havingClause()) {
@@ -661,6 +668,28 @@ public final class OracleDMLStatementVisitor extends
OracleStatementVisitor impl
return result;
}
+ @Override
+ public ASTNode visitHierarchicalQueryClause(final
HierarchicalQueryClauseContext ctx) {
+ HierarchicalQuerySegment result = new
HierarchicalQuerySegment(ctx.getStart().getStartIndex(),
ctx.getStop().getStopIndex());
+ result.setNoCycle(null != ctx.NOCYCLE());
+ if (ctx.expr().isEmpty()) {
+ return result;
+ }
+ boolean connectByFirst =
"CONNECT".equalsIgnoreCase(ctx.getStart().getText());
+ if (connectByFirst) {
+ result.setConnectBy((ExpressionSegment) visit(ctx.expr(0)));
+ if (ctx.expr().size() > 1) {
+ result.setStartWith((ExpressionSegment) visit(ctx.expr(1)));
+ }
+ } else {
+ result.setStartWith((ExpressionSegment) visit(ctx.expr(0)));
+ if (ctx.expr().size() > 1) {
+ result.setConnectBy((ExpressionSegment) visit(ctx.expr(1)));
+ }
+ }
+ return result;
+ }
+
@Override
public ASTNode visitHavingClause(final HavingClauseContext ctx) {
ExpressionSegment expr = (ExpressionSegment) visit(ctx.expr());
diff --git
a/parser/sql/statement/core/src/main/java/org/apache/shardingsphere/sql/parser/statement/core/segment/dml/predicate/HierarchicalQuerySegment.java
b/parser/sql/statement/core/src/main/java/org/apache/shardingsphere/sql/parser/statement/core/segment/dml/predicate/HierarchicalQuerySegment.java
new file mode 100644
index 00000000000..1c2aec4ae86
--- /dev/null
+++
b/parser/sql/statement/core/src/main/java/org/apache/shardingsphere/sql/parser/statement/core/segment/dml/predicate/HierarchicalQuerySegment.java
@@ -0,0 +1,43 @@
+/*
+ * 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.
+ */
+
+package
org.apache.shardingsphere.sql.parser.statement.core.segment.dml.predicate;
+
+import lombok.Getter;
+import lombok.RequiredArgsConstructor;
+import lombok.Setter;
+import org.apache.shardingsphere.sql.parser.statement.core.segment.SQLSegment;
+import
org.apache.shardingsphere.sql.parser.statement.core.segment.dml.expr.ExpressionSegment;
+
+/**
+ * Hierarchical query segment for Oracle START WITH / CONNECT BY.
+ */
+@RequiredArgsConstructor
+@Getter
+@Setter
+public final class HierarchicalQuerySegment implements SQLSegment {
+
+ private final int startIndex;
+
+ private final int stopIndex;
+
+ private boolean noCycle;
+
+ private ExpressionSegment startWith;
+
+ private ExpressionSegment connectBy;
+}
diff --git
a/parser/sql/statement/core/src/main/java/org/apache/shardingsphere/sql/parser/statement/core/statement/type/dml/SelectStatement.java
b/parser/sql/statement/core/src/main/java/org/apache/shardingsphere/sql/parser/statement/core/statement/type/dml/SelectStatement.java
index fa82ab269f7..a13423521aa 100644
---
a/parser/sql/statement/core/src/main/java/org/apache/shardingsphere/sql/parser/statement/core/statement/type/dml/SelectStatement.java
+++
b/parser/sql/statement/core/src/main/java/org/apache/shardingsphere/sql/parser/statement/core/statement/type/dml/SelectStatement.java
@@ -28,6 +28,7 @@ import
org.apache.shardingsphere.sql.parser.statement.core.segment.dml.order.Gro
import
org.apache.shardingsphere.sql.parser.statement.core.segment.dml.order.OrderBySegment;
import
org.apache.shardingsphere.sql.parser.statement.core.segment.dml.pagination.limit.LimitSegment;
import
org.apache.shardingsphere.sql.parser.statement.core.segment.dml.predicate.HavingSegment;
+import
org.apache.shardingsphere.sql.parser.statement.core.segment.dml.predicate.HierarchicalQuerySegment;
import
org.apache.shardingsphere.sql.parser.statement.core.segment.dml.predicate.LockSegment;
import
org.apache.shardingsphere.sql.parser.statement.core.segment.dml.predicate.WhereSegment;
import
org.apache.shardingsphere.sql.parser.statement.core.segment.generic.ModelSegment;
@@ -54,6 +55,8 @@ public final class SelectStatement extends DMLStatement {
private WhereSegment where;
+ private HierarchicalQuerySegment hierarchicalQuery;
+
private GroupBySegment groupBy;
private HavingSegment having;
@@ -104,6 +107,15 @@ public final class SelectStatement extends DMLStatement {
return Optional.ofNullable(where);
}
+ /**
+ * Get hierarchical query.
+ *
+ * @return hierarchical query segment
+ */
+ public Optional<HierarchicalQuerySegment> getHierarchicalQuery() {
+ return Optional.ofNullable(hierarchicalQuery);
+ }
+
/**
* Get group by segment.
*
diff --git
a/test/it/parser/src/main/resources/case/dml/select-start-with-connect-by.xml
b/test/it/parser/src/main/resources/case/dml/select-start-with-connect-by.xml
index b8bfa42de90..5739e38bad8 100644
---
a/test/it/parser/src/main/resources/case/dml/select-start-with-connect-by.xml
+++
b/test/it/parser/src/main/resources/case/dml/select-start-with-connect-by.xml
@@ -70,4 +70,38 @@
</expr>
</where>
</select>
+
+ <select sql-case-id="select_with_connect_by" parameters="2, 1">
+ <projections start-index="7" stop-index="7">
+ <shorthand-projection start-index="7" stop-index="7" />
+ </projections>
+ <from>
+ <subquery-table start-index="14" stop-index="167">
+ <subquery>
+ <select>
+ <projections start-index="22" stop-index="97">
+ <expression-projection alias="order_id"
text="REGEXP_SUBSTR('5214|1521|5152|1616|218|8226', '[^|]+', 1, LEVEL)"
start-index="22" stop-index="97" />
+ </projections>
+ <from>
+ <simple-table name="dual" start-index="104"
stop-index="107" />
+ </from>
+ </select>
+ </subquery>
+ </subquery-table>
+ </from>
+ <where start-index="169" stop-index="185">
+ <expr>
+ <binary-operation-expression start-index="175"
stop-index="185">
+ <left>
+ <column name="ROWNUM" start-index="175"
stop-index="180" />
+ </left>
+ <operator><=</operator>
+ <right>
+ <literal-expression value="1" start-index="185"
stop-index="185" />
+ <parameter-marker-expression parameter-index="1"
start-index="185" stop-index="185" />
+ </right>
+ </binary-operation-expression>
+ </expr>
+ </where>
+ </select>
</sql-parser-test-cases>
diff --git
a/test/it/parser/src/main/resources/sql/supported/dml/select-start-with-connect-by.xml
b/test/it/parser/src/main/resources/sql/supported/dml/select-start-with-connect-by.xml
index 3e5bfcefd68..e007f677255 100644
---
a/test/it/parser/src/main/resources/sql/supported/dml/select-start-with-connect-by.xml
+++
b/test/it/parser/src/main/resources/sql/supported/dml/select-start-with-connect-by.xml
@@ -18,4 +18,5 @@
<sql-cases>
<sql-case id="select_start_with_connect_by" value="SELECT * FROM (SELECT
level, o.* FROM t_order o WHERE o.order_id = 1 START WITH o.user_id = 1 CONNECT
BY PRIOR user_id = user_id ORDER BY level) temp WHERE ROWNUM = 1"
db-types="Oracle" />
+ <sql-case id="select_with_connect_by" value="SELECT * FROM (SELECT
REGEXP_SUBSTR('5214|1521|5152|1616|218|8226', '[^|]+', 1, LEVEL) AS order_id
FROM dual CONNECT BY REGEXP_SUBSTR(?, '[^|]+', 1, LEVEL) IS NOT NULL) WHERE
ROWNUM <= ?" db-types="Oracle"/>
</sql-cases>
