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 04968495bb7 Support scalar expressions in Oracle IN predicates (#39876)
04968495bb7 is described below
commit 04968495bb7bde791f98a58a9bd416f198a76380
Author: ZhangCheng <[email protected]>
AuthorDate: Thu Sep 17 16:36:45 2026 +0800
Support scalar expressions in Oracle IN predicates (#39876)
* Support scalar expressions in Oracle IN predicates
Co-authored-by: ZhangCheng <[email protected]>
* Add Oracle parser enhancements to release notes
---
RELEASE-NOTES.md | 2 +
.../src/main/antlr4/imports/oracle/BaseRule.g4 | 2 +-
.../visitor/statement/OracleStatementVisitor.java | 4 +-
.../parser/src/main/resources/case/dml/select.xml | 56 +++++++++++++++++++++-
.../main/resources/sql/supported/dml/select.xml | 2 +
5 files changed, 61 insertions(+), 5 deletions(-)
diff --git a/RELEASE-NOTES.md b/RELEASE-NOTES.md
index 9cfb0e8f3ec..9c611bfd94b 100644
--- a/RELEASE-NOTES.md
+++ b/RELEASE-NOTES.md
@@ -127,6 +127,8 @@
1. SQL Parser: Support Oracle pseudo-record collection index parsing -
[#39566](https://github.com/apache/shardingsphere/pull/39566)
1. SQL Parser: Support Oracle DML error logging parser model and binder -
[#39711](https://github.com/apache/shardingsphere/pull/39711)
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 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/BaseRule.g4
b/parser/sql/engine/dialect/oracle/src/main/antlr4/imports/oracle/BaseRule.g4
index 80fdb0903b0..13415627cab 100644
---
a/parser/sql/engine/dialect/oracle/src/main/antlr4/imports/oracle/BaseRule.g4
+++
b/parser/sql/engine/dialect/oracle/src/main/antlr4/imports/oracle/BaseRule.g4
@@ -452,7 +452,7 @@ predicate
| PRIOR predicate
| bitExpr NOT? IN LP_ expr (COMMA_ expr)* RP_
| bitExpr NOT? IN LP_ expr (COMMA_ expr)* RP_ AND predicate
- | bitExpr NOT? IN stringLiterals
+ | bitExpr NOT? IN bitExpr
| bitExpr NOT? BETWEEN bitExpr AND predicate
| bitExpr NOT? (LIKE | LIKEC | LIKE2 | LIKE4) simpleExpr (ESCAPE
simpleExpr)?
| bitExpr
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 44de9435938..c39e7d95c92 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
@@ -739,9 +739,7 @@ public abstract class OracleStatementVisitor extends
OracleStatementBaseVisitor<
ExpressionSegment right;
if (null == ctx.subquery()) {
if (null == ctx.LP_() || null == ctx.RP_()) {
- StringLiteralsContext stringLiteralsContext =
ctx.stringLiterals();
- right = new
LiteralExpressionSegment(stringLiteralsContext.start.getStartIndex(),
stringLiteralsContext.stop.getStopIndex(),
- ((StringLiteralValue)
visit(stringLiteralsContext)).getValue());
+ right = (ExpressionSegment) visit(ctx.bitExpr(1));
} else {
ListExpression listExpression = new
ListExpression(ctx.LP_().getSymbol().getStartIndex(),
ctx.RP_().getSymbol().getStopIndex());
for (ExprContext each : ctx.expr()) {
diff --git a/test/it/parser/src/main/resources/case/dml/select.xml
b/test/it/parser/src/main/resources/case/dml/select.xml
index f183c2eff37..68cfcc19ffb 100644
--- a/test/it/parser/src/main/resources/case/dml/select.xml
+++ b/test/it/parser/src/main/resources/case/dml/select.xml
@@ -15305,5 +15305,59 @@
<column-projection name="SYSTEM_USER" start-index="7"
stop-index="17" />
</projections>
</select>
-
+ <select sql-case-id="select_in_with_owner_column_rhs_oracle">
+ <projections start-index="7" stop-index="7">
+ <shorthand-projection start-index="7" stop-index="7" />
+ </projections>
+ <from>
+ <simple-table name="t_order" alias="o" start-index="14"
stop-index="22" />
+ </from>
+ <where start-index="24" stop-index="52">
+ <expr>
+ <in-expression start-index="30" stop-index="52">
+ <left>
+ <column name="user_id" start-index="30"
stop-index="38">
+ <owner name="o" start-index="30" stop-index="30" />
+ </column>
+ </left>
+ <right>
+ <column name="order_id" start-index="43"
stop-index="52">
+ <owner name="o" start-index="43" stop-index="43" />
+ </column>
+ </right>
+ </in-expression>
+ </expr>
+ </where>
+ </select>
+ <select sql-case-id="select_in_with_function_rhs_oracle">
+ <projections start-index="7" stop-index="7">
+ <shorthand-projection start-index="7" stop-index="7" />
+ </projections>
+ <from>
+ <simple-table name="t_order" alias="o" start-index="14"
stop-index="22" />
+ </from>
+ <where start-index="24" stop-index="60">
+ <expr>
+ <in-expression start-index="30" stop-index="60">
+ <left>
+ <column name="user_id" start-index="30"
stop-index="38">
+ <owner name="o" start-index="30" stop-index="30" />
+ </column>
+ </left>
+ <right>
+ <function function-name="NVL" text="NVL(o.order_id,
0)" start-index="43" stop-index="60">
+ <parameter>
+ <column name="order_id" start-index="47"
stop-index="56">
+ <owner name="o" start-index="47"
stop-index="47" />
+ </column>
+ </parameter>
+ <parameter>
+ <literal-expression value="0" start-index="59"
stop-index="59" />
+ </parameter>
+ </function>
+ </right>
+ </in-expression>
+ </expr>
+ </where>
+ </select>
</sql-parser-test-cases>
diff --git a/test/it/parser/src/main/resources/sql/supported/dml/select.xml
b/test/it/parser/src/main/resources/sql/supported/dml/select.xml
index 09ed6e6edb5..28967d8c60e 100644
--- a/test/it/parser/src/main/resources/sql/supported/dml/select.xml
+++ b/test/it/parser/src/main/resources/sql/supported/dml/select.xml
@@ -578,4 +578,6 @@
<sql-case id="select_chinese_columns_with_ascii_comma_from_dual"
value="SELECT 中文列一, 中文列二 AS RESULT FROM DUAL" db-types="Oracle" />
<sql-case id="select_chinese_columns_from_table" value="SELECT 中文列一，中文列二
AS RESULT FROM t_order" db-types="Oracle" />
<sql-case id="select_for_system_version_as_of_hive" value="SELECT * FROM
table_a FOR SYSTEM_VERSION AS OF 1234567;" db-types="Hive" />
+ <sql-case id="select_in_with_owner_column_rhs_oracle" value="SELECT * FROM
t_order o WHERE o.user_id IN o.order_id" db-types="Oracle" />
+ <sql-case id="select_in_with_function_rhs_oracle" value="SELECT * FROM
t_order o WHERE o.user_id IN NVL(o.order_id, 0)" db-types="Oracle" />
</sql-cases>
