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 2d3bd86aa2e Support Oracle ORDER BY after FOR UPDATE and HAVING
without GROUP BY (#39877)
2d3bd86aa2e is described below
commit 2d3bd86aa2e267220a15acf98cca8ef373fd30c6
Author: ZhangCheng <[email protected]>
AuthorDate: Thu Sep 17 16:27:57 2026 +0800
Support Oracle ORDER BY after FOR UPDATE and HAVING without GROUP BY
(#39877)
---
.../src/main/antlr4/imports/oracle/DMLStatement.g4 | 6 ++--
.../oracle/src/main/antlr4/imports/oracle/PLSQL.g4 | 2 +-
.../statement/type/OracleDMLStatementVisitor.java | 24 +++++++++++---
.../type/OracleDMLStatementVisitorTest.java | 37 ++++++++++++++++++++++
.../main/resources/case/dml/select-group-by.xml | 22 +++++++++++++
.../src/main/resources/case/dml/select-lock.xml | 26 +++++++++++++++
.../sql/supported/dml/select-group-by.xml | 1 +
.../resources/sql/supported/dml/select-lock.xml | 1 +
8 files changed, 110 insertions(+), 9 deletions(-)
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 c3667591472..63a7ecacdfb 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
@@ -156,7 +156,7 @@ deleteSpecification
;
select
- : selectSubquery forUpdateClause?
+ : selectSubquery forUpdateClause? orderByClause?
;
selectSubquery
@@ -173,7 +173,7 @@ parenthesisSelectSubquery
queryBlock
: unquotedTextQueryBlock
- | withClause? SELECT hint? duplicateSpecification? selectList
selectIntoClause? selectFromClause whereClause? hierarchicalQueryClause?
groupByClause? modelClause?
+ | withClause? SELECT hint? duplicateSpecification? selectList
selectIntoClause? selectFromClause whereClause? hierarchicalQueryClause?
groupByClause? havingClause? modelClause?
;
unquotedTextQueryBlock
@@ -679,7 +679,7 @@ hierarchicalQueryClause
;
groupByClause
- : GROUP BY groupByItem (COMMA_ groupByItem)* havingClause?
+ : GROUP BY groupByItem (COMMA_ groupByItem)*
;
groupByItem
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 633548f770b..54b6b486388 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
@@ -478,7 +478,7 @@ returnStatement
;
selectIntoStatement
- : SELECT (DISTINCT | UNIQUE | ALL)? selectList (selectIntoClause |
bulkCollectIntoClause) FROM fromClauseList whereClause?
hierarchicalQueryClause? groupByClause? modelClause? windowClause?
orderByClause? rowLimitingClause? SEMI_
+ : SELECT (DISTINCT | UNIQUE | ALL)? selectList (selectIntoClause |
bulkCollectIntoClause) FROM fromClauseList whereClause?
hierarchicalQueryClause? groupByClause? havingClause? modelClause?
windowClause? orderByClause? rowLimitingClause? SEMI_
;
selectIntoClause
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 4e8f0641d91..220cbb83206 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
@@ -122,6 +122,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.engine.exception.SQLParsingException;
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;
@@ -576,6 +577,9 @@ public final class OracleDMLStatementVisitor extends
OracleStatementVisitor impl
if (null != ctx.groupByClause()) {
result.groupBy((GroupBySegment) visit(ctx.groupByClause()));
}
+ if (null != ctx.havingClause()) {
+ result.having((HavingSegment) visit(ctx.havingClause()));
+ }
if (null != ctx.modelClause()) {
result.model((ModelSegment) visit(ctx.modelClause()));
}
@@ -608,9 +612,19 @@ public final class OracleDMLStatementVisitor extends
OracleStatementVisitor impl
@Override
public ASTNode visitSelect(final SelectContext ctx) {
SelectStatement result = (SelectStatement) visit(ctx.selectSubquery());
- if (null != ctx.forUpdateClause()) {
+ if (null != ctx.orderByClause() && result.getOrderBy().isPresent()) {
+ throw new SQLParsingException("Duplicated ORDER BY clause.");
+ }
+ if (null != ctx.forUpdateClause() || null != ctx.orderByClause()) {
SelectStatement previous = result;
- result = createSelectStatementBuilder(previous).lock((LockSegment)
visit(ctx.forUpdateClause())).build();
+ SelectStatement.SelectStatementBuilder builder =
createSelectStatementBuilder(previous);
+ if (null != ctx.forUpdateClause()) {
+ builder.lock((LockSegment) visit(ctx.forUpdateClause()));
+ }
+ if (null != ctx.orderByClause()) {
+ builder.orderBy((OrderBySegment) visit(ctx.orderByClause()));
+ }
+ result = builder.build();
result.addParameterMarkers(previous.getParameterMarkers());
result.getVariableNames().addAll(previous.getVariableNames());
result.getComments().addAll(previous.getComments());
@@ -806,9 +820,9 @@ public final class OracleDMLStatementVisitor extends
OracleStatementVisitor impl
}
if (null != ctx.groupByClause()) {
result.groupBy((GroupBySegment) visit(ctx.groupByClause()));
- if (null != ctx.groupByClause().havingClause()) {
- result.having((HavingSegment)
visit(ctx.groupByClause().havingClause()));
- }
+ }
+ if (null != ctx.havingClause()) {
+ result.having((HavingSegment) visit(ctx.havingClause()));
}
if (null != ctx.modelClause()) {
result.model((ModelSegment) visit(ctx.modelClause()));
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
new file mode 100644
index 00000000000..6c1bae49841
--- /dev/null
+++
b/parser/sql/engine/dialect/oracle/src/test/java/org/apache/shardingsphere/sql/parser/engine/oracle/visitor/statement/type/OracleDMLStatementVisitorTest.java
@@ -0,0 +1,37 @@
+/*
+ * 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.engine.oracle.visitor.statement.type;
+
+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.junit.jupiter.api.Test;
+
+import static org.junit.jupiter.api.Assertions.assertThrows;
+
+class OracleDMLStatementVisitorTest {
+
+ @Test
+ void assertVisitSelect() {
+ 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)));
+ }
+}
diff --git a/test/it/parser/src/main/resources/case/dml/select-group-by.xml
b/test/it/parser/src/main/resources/case/dml/select-group-by.xml
index 2d65d9dd44d..86c44f36712 100644
--- a/test/it/parser/src/main/resources/case/dml/select-group-by.xml
+++ b/test/it/parser/src/main/resources/case/dml/select-group-by.xml
@@ -1092,4 +1092,26 @@
</expression-item>
</group-by>
</select>
+ <select sql-case-id="select_having_without_group_by_oracle" parameters="1">
+ <projections start-index="7" stop-index="19">
+ <aggregation-projection type="MIN" expression="MIN(order_id)"
start-index="7" stop-index="19" />
+ </projections>
+ <from>
+ <simple-table name="t_order" start-index="26" stop-index="32" />
+ </from>
+ <having start-index="34" stop-index="57">
+ <expr>
+ <binary-operation-expression start-index="41" stop-index="57">
+ <left>
+ <aggregation-projection type="MIN"
expression="MIN(order_id)" start-index="41" stop-index="53" />
+ </left>
+ <operator><</operator>
+ <right>
+ <literal-expression value="1" start-index="57"
stop-index="57" />
+ <parameter-marker-expression parameter-index="0"
start-index="57" stop-index="57" />
+ </right>
+ </binary-operation-expression>
+ </expr>
+ </having>
+ </select>
</sql-parser-test-cases>
diff --git a/test/it/parser/src/main/resources/case/dml/select-lock.xml
b/test/it/parser/src/main/resources/case/dml/select-lock.xml
index 30a3d377787..6e07d26e147 100644
--- a/test/it/parser/src/main/resources/case/dml/select-lock.xml
+++ b/test/it/parser/src/main/resources/case/dml/select-lock.xml
@@ -345,4 +345,30 @@
</right>
</combine>
</select>
+ <select sql-case-id="select_lock_with_order_by_after_for_update_oracle"
parameters="1">
+ <projections start-index="7" stop-index="7">
+ <shorthand-projection start-index="7" stop-index="7" />
+ </projections>
+ <from start-index="14" stop-index="20">
+ <simple-table name="t_order" start-index="14" stop-index="20" />
+ </from>
+ <where start-index="22" stop-index="39">
+ <expr>
+ <binary-operation-expression start-index="28" stop-index="39">
+ <left>
+ <column name="order_id" start-index="28"
stop-index="35" />
+ </left>
+ <operator>=</operator>
+ <right>
+ <literal-expression value="1" start-index="39"
stop-index="39" />
+ <parameter-marker-expression parameter-index="0"
start-index="39" stop-index="39" />
+ </right>
+ </binary-operation-expression>
+ </expr>
+ </where>
+ <lock start-index="41" stop-index="57" />
+ <order-by start-index="59" stop-index="80">
+ <column-item name="order_id" order-direction="DESC"
start-index="68" stop-index="75" />
+ </order-by>
+ </select>
</sql-parser-test-cases>
diff --git
a/test/it/parser/src/main/resources/sql/supported/dml/select-group-by.xml
b/test/it/parser/src/main/resources/sql/supported/dml/select-group-by.xml
index 3196d33fd40..9ad7a9b1bc5 100644
--- a/test/it/parser/src/main/resources/sql/supported/dml/select-group-by.xml
+++ b/test/it/parser/src/main/resources/sql/supported/dml/select-group-by.xml
@@ -53,4 +53,5 @@
<sql-case id="select_group_by_count_with_tumblingwindow_function"
value="SELECT count(*) FROM input GROUP BY clusterid,tumblingwindow(minutes,
5)" db-types="SQLServer" />
<sql-case id="select_group_by_rollup_oracle" value="SELECT dept,
SUM(salary) FROM employees GROUP BY ROLLUP(dept)" db-types="Oracle" />
<sql-case id="select_group_by_null_expression" value="SELECT COUNT(*) FROM
t_order GROUP BY NULL" db-types="MySQL,PostgreSQL,openGauss,Oracle,SQLServer" />
+ <sql-case id="select_having_without_group_by_oracle" value="SELECT
MIN(order_id) FROM t_order HAVING MIN(order_id) < ?" db-types="Oracle" />
</sql-cases>
diff --git
a/test/it/parser/src/main/resources/sql/supported/dml/select-lock.xml
b/test/it/parser/src/main/resources/sql/supported/dml/select-lock.xml
index dc8146c0706..92008b6ce41 100644
--- a/test/it/parser/src/main/resources/sql/supported/dml/select-lock.xml
+++ b/test/it/parser/src/main/resources/sql/supported/dml/select-lock.xml
@@ -28,4 +28,5 @@
<sql-case id="select_lock_with_for_update_table_column" value="SELECT
order_id FROM t_order FOR UPDATE OF t_order.order_id" db-types="Oracle" />
<sql-case id="select_lock_with_for_update_of_column_skip_locked"
value="SELECT * FROM employees FOR UPDATE OF employees.salary SKIP LOCKED"
db-types="Oracle" />
<sql-case id="select_lock_with_union_all" value="SELECT order_id FROM
t_order FOR UPDATE UNION ALL SELECT order_id FROM t_order2 FOR UPDATE"
db-types="MySQL" />
+ <sql-case id="select_lock_with_order_by_after_for_update_oracle"
value="SELECT * FROM t_order WHERE order_id = ? FOR UPDATE NOWAIT ORDER BY
order_id DESC" db-types="Oracle" />
</sql-cases>
