This is an automated email from the ASF dual-hosted git repository.
jianglongtao 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 551fec79ba6 [Oracle SQL] Support HR-7-1 of Oracle SQL (#27604)
551fec79ba6 is described below
commit 551fec79ba6c8f9993a9f4176c0ef7cf9dcd8b04
Author: Liao Lanyu <[email protected]>
AuthorDate: Tue Aug 1 14:45:25 2023 +0800
[Oracle SQL] Support HR-7-1 of Oracle SQL (#27604)
* support HR-7-1
* support HR-7-1 test
* support HR-7-1
---
.../visitor/statement/OracleStatementVisitor.java | 14 ++++++-
.../main/resources/case/dml/select-sub-query.xml | 47 ++++++++++++++++++++++
.../sql/supported/dml/select-sub-query.xml | 1 +
3 files changed, 60 insertions(+), 2 deletions(-)
diff --git
a/parser/sql/dialect/oracle/src/main/java/org/apache/shardingsphere/sql/parser/oracle/visitor/statement/OracleStatementVisitor.java
b/parser/sql/dialect/oracle/src/main/java/org/apache/shardingsphere/sql/parser/oracle/visitor/statement/OracleStatementVisitor.java
index bc093ead672..468dc72ed78 100644
---
a/parser/sql/dialect/oracle/src/main/java/org/apache/shardingsphere/sql/parser/oracle/visitor/statement/OracleStatementVisitor.java
+++
b/parser/sql/dialect/oracle/src/main/java/org/apache/shardingsphere/sql/parser/oracle/visitor/statement/OracleStatementVisitor.java
@@ -405,8 +405,18 @@ public abstract class OracleStatementVisitor extends
OracleStatementBaseVisitor<
private ASTNode createCompareSegment(final BooleanPrimaryContext ctx) {
ExpressionSegment left = (ExpressionSegment)
visit(ctx.booleanPrimary());
- String operator = null == ctx.SAFE_EQ_() ?
ctx.comparisonOperator().getText() : ctx.SAFE_EQ_().getText();
- ExpressionSegment right = null != ctx.predicate() ?
(ExpressionSegment) visit(ctx.predicate()) : (ExpressionSegment)
visit(ctx.subquery());
+ ExpressionSegment right;
+ String operator;
+ if (null != ctx.ALL()) {
+ operator = null != ctx.SAFE_EQ_() ? ctx.SAFE_EQ_().getText() :
ctx.comparisonOperator().getText() + " ALL";
+ } else {
+ operator = null != ctx.SAFE_EQ_() ? ctx.SAFE_EQ_().getText() :
ctx.comparisonOperator().getText();
+ }
+ if (null != ctx.predicate()) {
+ right = (ExpressionSegment) visit(ctx.predicate());
+ } else {
+ right = new SubqueryExpressionSegment(new
SubquerySegment(ctx.subquery().start.getStartIndex(),
ctx.subquery().stop.getStopIndex(), (OracleSelectStatement)
visit(ctx.subquery())));
+ }
String text = ctx.start.getInputStream().getText(new
Interval(ctx.start.getStartIndex(), ctx.stop.getStopIndex()));
return new BinaryOperationExpression(ctx.start.getStartIndex(),
ctx.stop.getStopIndex(), left, right, operator, text);
}
diff --git a/test/it/parser/src/main/resources/case/dml/select-sub-query.xml
b/test/it/parser/src/main/resources/case/dml/select-sub-query.xml
index 5fd48e9c9d1..c6d128750bc 100644
--- a/test/it/parser/src/main/resources/case/dml/select-sub-query.xml
+++ b/test/it/parser/src/main/resources/case/dml/select-sub-query.xml
@@ -187,6 +187,53 @@
</where>
</select>
+ <select sql-case-id="select_with_any_subquery">
+ <from>
+ <simple-table name="employees" start-index="14" stop-index="22" />
+ </from>
+ <projections start-index="7" stop-index="7">
+ <shorthand-projection start-index="7" stop-index="7" />
+ </projections>
+ <where start-index="24" stop-index="97">
+ <expr>
+ <binary-operation-expression start-index="30" stop-index="97">
+ <left>
+ <column name="salary" start-index="30" stop-index="35"
/>
+ </left>
+ <operator>=</operator>
+ <right>
+ <subquery start-index="43" stop-index="97">
+ <select>
+ <from start-index="63" stop-index="71">
+ <simple-table name="employees"
start-index="63" stop-index="71"/>
+ </from>
+ <projections start-index="51" stop-index="56">
+ <column-projection name="salary"
start-index="51" stop-index="56"/>
+ </projections>
+ <where start-index="73" stop-index="96">
+ <expr>
+ <binary-operation-expression
start-index="79" stop-index="96">
+ <left>
+ <column name="department_id"
start-index="79" stop-index="91" />
+ </left>
+ <operator>=</operator>
+ <right>
+ <literal-expression value="30"
start-index="95" stop-index="96" />
+ </right>
+ </binary-operation-expression>
+ </expr>
+ </where>
+ </select>
+ </subquery>
+ </right>
+ </binary-operation-expression>
+ </expr>
+ </where>
+ <order-by>
+ <column-item name="employee_id" order-direction="ASC"
start-index="108" stop-index="118" />
+ </order-by>
+ </select>
+
<select sql-case-id="select_with_in_subquery">
<from>
<simple-table name="t_order" start-index="14" stop-index="20" />
diff --git
a/test/it/parser/src/main/resources/sql/supported/dml/select-sub-query.xml
b/test/it/parser/src/main/resources/sql/supported/dml/select-sub-query.xml
index d833661a4f4..0bc79bdd0b3 100644
--- a/test/it/parser/src/main/resources/sql/supported/dml/select-sub-query.xml
+++ b/test/it/parser/src/main/resources/sql/supported/dml/select-sub-query.xml
@@ -21,6 +21,7 @@
<sql-case id="select_sub_query_with_project" value="SELECT order_id,
(SELECT 1) AS num FROM t_order" db-types="MySQL, PostgreSQL,openGauss,
SQLServer" />
<sql-case id="select_sub_query_with_table" value="SELECT t.* FROM (SELECT
* FROM t_order WHERE order_id IN (?, ?)) t" />
<sql-case id="select_with_equal_subquery" value="SELECT * FROM t_order
WHERE user_id = (SELECT user_id FROM t_order_item WHERE id = 10)"
db-types="MySQL, PostgreSQL,openGauss" />
+ <sql-case id="select_with_any_subquery" value="SELECT * FROM employees
WHERE salary = ANY (SELECT salary FROM employees WHERE department_id = 30)
ORDER BY employee_id;" db-types="Oracle" />
<sql-case id="select_with_in_subquery" value="SELECT * FROM t_order WHERE
user_id IN (SELECT user_id FROM t_order_item WHERE id IN (10, 11))"
db-types="MySQL, PostgreSQL,openGauss" />
<sql-case id="select_with_between_subquery" value="SELECT * FROM t_order
WHERE user_id BETWEEN (SELECT user_id FROM t_order_item WHERE order_id = 10)
AND ?" db-types="MySQL, PostgreSQL,openGauss" />
<sql-case id="select_with_exists_sub_query_with_project" value="SELECT
EXISTS (SELECT 1 FROM t_order)" db-types="MySQL, PostgreSQL,openGauss" />
