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 1401f1e207d Fix oracle select union sql parse (#29001)
1401f1e207d is described below
commit 1401f1e207db4d169180b969ac679dcc4f1247fb
Author: niu niu <[email protected]>
AuthorDate: Sat Nov 11 08:26:57 2023 +0800
Fix oracle select union sql parse (#29001)
* Fix oracle select union sql parse
* Format code
* Remove combineClause loop visit
---
.../src/main/antlr4/imports/oracle/DMLStatement.g4 | 2 +-
.../statement/type/OracleDMLStatementVisitor.java | 30 +++--
.../src/main/resources/case/dml/select-combine.xml | 125 +++++++++++++++++++++
.../resources/sql/supported/dml/select-combine.xml | 3 +
4 files changed, 151 insertions(+), 9 deletions(-)
diff --git
a/parser/sql/dialect/oracle/src/main/antlr4/imports/oracle/DMLStatement.g4
b/parser/sql/dialect/oracle/src/main/antlr4/imports/oracle/DMLStatement.g4
index c511e5458f6..de4f968a4ed 100644
--- a/parser/sql/dialect/oracle/src/main/antlr4/imports/oracle/DMLStatement.g4
+++ b/parser/sql/dialect/oracle/src/main/antlr4/imports/oracle/DMLStatement.g4
@@ -136,7 +136,7 @@ select
;
selectSubquery
- : (queryBlock | selectCombineClause | parenthesisSelectSubquery)
pivotClause? orderByClause? rowLimitingClause
+ : (selectCombineClause | queryBlock | parenthesisSelectSubquery)
pivotClause? orderByClause? rowLimitingClause
;
selectCombineClause
diff --git
a/parser/sql/dialect/oracle/src/main/java/org/apache/shardingsphere/sql/parser/oracle/visitor/statement/type/OracleDMLStatementVisitor.java
b/parser/sql/dialect/oracle/src/main/java/org/apache/shardingsphere/sql/parser/oracle/visitor/statement/type/OracleDMLStatementVisitor.java
index 18a08899175..00d19ef9aff 100644
---
a/parser/sql/dialect/oracle/src/main/java/org/apache/shardingsphere/sql/parser/oracle/visitor/statement/type/OracleDMLStatementVisitor.java
+++
b/parser/sql/dialect/oracle/src/main/java/org/apache/shardingsphere/sql/parser/oracle/visitor/statement/type/OracleDMLStatementVisitor.java
@@ -108,6 +108,7 @@ import
org.apache.shardingsphere.sql.parser.autogen.OracleStatementParser.UsingC
import
org.apache.shardingsphere.sql.parser.autogen.OracleStatementParser.WhereClauseContext;
import
org.apache.shardingsphere.sql.parser.autogen.OracleStatementParser.WithClauseContext;
import
org.apache.shardingsphere.sql.parser.oracle.visitor.statement.OracleStatementVisitor;
+import org.apache.shardingsphere.sql.parser.sql.common.enums.CombineType;
import org.apache.shardingsphere.sql.parser.sql.common.enums.JoinType;
import org.apache.shardingsphere.sql.parser.sql.common.enums.OrderDirection;
import
org.apache.shardingsphere.sql.parser.sql.common.segment.dal.VariableSegment;
@@ -117,6 +118,7 @@ import
org.apache.shardingsphere.sql.parser.sql.common.segment.dml.assignment.In
import
org.apache.shardingsphere.sql.parser.sql.common.segment.dml.assignment.SetAssignmentSegment;
import
org.apache.shardingsphere.sql.parser.sql.common.segment.dml.column.ColumnSegment;
import
org.apache.shardingsphere.sql.parser.sql.common.segment.dml.column.InsertColumnsSegment;
+import
org.apache.shardingsphere.sql.parser.sql.common.segment.dml.combine.CombineSegment;
import
org.apache.shardingsphere.sql.parser.sql.common.segment.dml.expr.BetweenExpression;
import
org.apache.shardingsphere.sql.parser.sql.common.segment.dml.expr.BinaryOperationExpression;
import
org.apache.shardingsphere.sql.parser.sql.common.segment.dml.expr.CaseWhenExpression;
@@ -649,18 +651,16 @@ public final class OracleDMLStatementVisitor extends
OracleStatementVisitor impl
@Override
public ASTNode visitSelectCombineClause(final SelectCombineClauseContext
ctx) {
- OracleSelectStatement result;
- if (null != ctx.queryBlock()) {
- result = (OracleSelectStatement) visit(ctx.queryBlock());
- } else {
- result = (OracleSelectStatement)
visit(ctx.parenthesisSelectSubquery());
+ OracleSelectStatement result = new OracleSelectStatement();
+ OracleSelectStatement left = null != ctx.queryBlock() ?
(OracleSelectStatement) visit(ctx.queryBlock()) : (OracleSelectStatement)
visit(ctx.parenthesisSelectSubquery());
+ if (null != ctx.selectSubquery()) {
+ result.setProjections(left.getProjections());
+ result.setFrom(left.getFrom());
+ setSelectCombineClause(ctx, result, left);
}
if (null != ctx.orderByClause()) {
result.setOrderBy((OrderBySegment) visit(ctx.orderByClause()));
}
- for (SelectSubqueryContext each : ctx.selectSubquery()) {
- visit(each);
- }
return result;
}
@@ -669,6 +669,20 @@ public final class OracleDMLStatementVisitor extends
OracleStatementVisitor impl
return visit(ctx.selectSubquery());
}
+ private void setSelectCombineClause(final SelectCombineClauseContext ctx,
final OracleSelectStatement result, final OracleSelectStatement left) {
+ CombineType combineType;
+ if (null != ctx.UNION(0) && null != ctx.ALL(0)) {
+ combineType = CombineType.UNION_ALL;
+ } else if (null != ctx.UNION(0)) {
+ combineType = CombineType.UNION;
+ } else if (null != ctx.INTERSECT(0)) {
+ combineType = CombineType.INTERSECT;
+ } else {
+ combineType = CombineType.MINUS;
+ }
+ result.setCombine(new CombineSegment(ctx.getStart().getStartIndex(),
ctx.getStop().getStopIndex(), left, combineType, (OracleSelectStatement)
visit(ctx.selectSubquery(0))));
+ }
+
@Override
public ASTNode visitWithClause(final WithClauseContext ctx) {
Collection<CommonTableExpressionSegment> commonTableExpressions = new
LinkedList<>();
diff --git a/test/it/parser/src/main/resources/case/dml/select-combine.xml
b/test/it/parser/src/main/resources/case/dml/select-combine.xml
index a4a4985b378..22db627e62c 100644
--- a/test/it/parser/src/main/resources/case/dml/select-combine.xml
+++ b/test/it/parser/src/main/resources/case/dml/select-combine.xml
@@ -893,4 +893,129 @@
</right>
</combine>
</select>
+
+ <select sql-case-id="select_union_all_where">
+ <projections start-index="7" stop-index="7">
+ <shorthand-projection start-index="7" stop-index="7" />
+ </projections>
+ <from>
+ <simple-table name="TEST_TABLE_1" start-index="14" stop-index="25"
/>
+ </from>
+ <combine combine-type="UNION_ALL" start-index="0" stop-index="88">
+ <left>
+ <projections start-index="7" stop-index="7">
+ <shorthand-projection start-index="7" stop-index="7" />
+ </projections>
+ <from>
+ <simple-table name="TEST_TABLE_1" start-index="14"
stop-index="25" />
+ </from>
+ <where start-index="27" stop-index="38">
+ <expr>
+ <binary-operation-expression start-index="33"
stop-index="38">
+ <left>
+ <column name="ID" start-index="33"
stop-index="34" />
+ </left>
+ <right>
+ <literal-expression value="1" start-index="38"
stop-index="38" />
+ </right>
+ <operator>=</operator>
+ </binary-operation-expression>
+ </expr>
+ </where>
+ </left>
+ <right>
+ <projections start-index="57" stop-index="57">
+ <shorthand-projection start-index="57" stop-index="57" />
+ </projections>
+ <from>
+ <simple-table name="TEST_TABLE_2" start-index="64"
stop-index="75" />
+ </from>
+ <where start-index="77" stop-index="88">
+ <expr>
+ <binary-operation-expression start-index="83"
stop-index="88">
+ <left>
+ <column name="ID" start-index="83"
stop-index="84" />
+ </left>
+ <right>
+ <literal-expression value="2" start-index="88"
stop-index="88" />
+ </right>
+ <operator>=</operator>
+ </binary-operation-expression>
+ </expr>
+ </where>
+ </right>
+ </combine>
+ </select>
+
+ <select sql-case-id="select_union_all_minus">
+ <projections start-index="7" stop-index="7">
+ <shorthand-projection start-index="7" stop-index="7" />
+ </projections>
+ <from>
+ <simple-table name="TEST_TABLE_1" start-index="14" stop-index="25"
/>
+ </from>
+ <combine combine-type="UNION_ALL" start-index="0" stop-index="95">
+ <left>
+ <projections start-index="7" stop-index="7">
+ <shorthand-projection start-index="7" stop-index="7" />
+ </projections>
+ <from>
+ <simple-table name="TEST_TABLE_1" start-index="14"
stop-index="25" />
+ </from>
+ </left>
+ <right>
+ <projections start-index="44" stop-index="44">
+ <shorthand-projection start-index="44" stop-index="44" />
+ </projections>
+ <from>
+ <simple-table name="TEST_TABLE_2" start-index="51"
stop-index="62" />
+ </from>
+ <combine combine-type="MINUS" start-index="37" stop-index="95">
+ <left>
+ <projections start-index="44" stop-index="44">
+ <shorthand-projection start-index="44"
stop-index="44" />
+ </projections>
+ <from>
+ <simple-table name="TEST_TABLE_2" start-index="51"
stop-index="62" />
+ </from>
+ </left>
+ <right>
+ <projections start-index="77" stop-index="77">
+ <shorthand-projection start-index="77"
stop-index="77" />
+ </projections>
+ <from>
+ <simple-table name="TEST_TABLE_3" start-index="84"
stop-index="95" />
+ </from>
+ </right>
+ </combine>
+ </right>
+ </combine>
+ </select>
+
+ <select sql-case-id="select_union_subquery">
+ <projections start-index="8" stop-index="14">
+ <column-projection name="TEST_ID" start-index="8" stop-index="14"
/>
+ </projections>
+ <from>
+ <simple-table name="TEST_TABLE" start-index="22" stop-index="31" />
+ </from>
+ <combine combine-type="UNION" start-index="0" stop-index="71">
+ <left>
+ <projections start-index="8" stop-index="14">
+ <column-projection name="TEST_ID" start-index="8"
stop-index="14" />
+ </projections>
+ <from>
+ <simple-table name="TEST_TABLE" start-index="22"
stop-index="31" />
+ </from>
+ </left>
+ <right>
+ <projections start-index="48" stop-index="54">
+ <column-projection name="TEST_ID" start-index="48"
stop-index="54" />
+ </projections>
+ <from>
+ <simple-table name="TEST_TABLE" start-index="61"
stop-index="70" />
+ </from>
+ </right>
+ </combine>
+ </select>
</sql-parser-test-cases>
diff --git
a/test/it/parser/src/main/resources/sql/supported/dml/select-combine.xml
b/test/it/parser/src/main/resources/sql/supported/dml/select-combine.xml
index 6e4786ffdba..2b55b07faa5 100644
--- a/test/it/parser/src/main/resources/sql/supported/dml/select-combine.xml
+++ b/test/it/parser/src/main/resources/sql/supported/dml/select-combine.xml
@@ -38,4 +38,7 @@
<sql-case id="select_except_intersect" value="SELECT * FROM table1 EXCEPT
SELECT * FROM table2 INTERSECT SELECT * FROM table3"
db-types="PostgreSQL,openGauss" />
<sql-case id="select_except_intersect_union" value="SELECT * FROM table1
EXCEPT SELECT * FROM table2 INTERSECT SELECT * FROM table3 UNION SELECT * FROM
table4" db-types="PostgreSQL,openGauss" />
<sql-case id="select_sub_union" value="SELECT * FROM table1 UNION (SELECT
* FROM table2 UNION SELECT * FROM table3)"
db-types="MySQL,PostgreSQL,openGauss" />
+ <sql-case id="select_union_all_where" value="SELECT * FROM TEST_TABLE_1
WHERE ID = 1 UNION ALL SELECT * FROM TEST_TABLE_2 WHERE ID = 2"
db-types="Oracle" />
+ <sql-case id="select_union_all_minus" value="SELECT * FROM TEST_TABLE_1
UNION ALL SELECT * FROM TEST_TABLE_2 MINUS SELECT * FROM TEST_TABLE_3"
db-types="Oracle" />
+ <sql-case id="select_union_subquery" value="(SELECT TEST_ID FROM
TEST_TABLE) UNION (SELECT TEST_ID FROM TEST_TABLE)" db-types="Oracle" />
</sql-cases>
