You are viewing a plain text version of this content. The canonical link for it is here.
Posted to notifications@shardingsphere.apache.org by pa...@apache.org on 2021/06/09 01:26:25 UTC
[shardingsphere] branch master updated: Add with clause for Oracle
`SELECT` statement (#10558)
This is an automated email from the ASF dual-hosted git repository.
panjuan 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 484b9ac Add with clause for Oracle `SELECT` statement (#10558)
484b9ac is described below
commit 484b9ac2e739a3f4f16e0229cbf807b7e9599eb6
Author: Thanoshan MV <48...@users.noreply.github.com>
AuthorDate: Wed Jun 9 06:55:39 2021 +0530
Add with clause for Oracle `SELECT` statement (#10558)
* add definition for with clause
* add start, stop indices for subquery segment
* modify alias rule
* add with clause test cases
* add new rule to predicate
---
.../src/main/antlr4/imports/oracle/BaseRule.g4 | 55 +++-
.../src/main/antlr4/imports/oracle/DMLStatement.g4 | 224 ++++++++++++++-
.../main/antlr4/imports/oracle/OracleKeyword.g4 | 192 +++++++++++++
.../impl/OracleDMLStatementSQLVisitor.java | 30 ++
.../impl/SQLServerDMLStatementSQLVisitor.java | 2 +-
.../sql/common/statement/dml/SelectStatement.java | 12 +
.../handler/dml/SelectStatementHandler.java | 14 +
.../asserts/segment/with/WithClauseAssert.java | 4 +
.../statement/dml/impl/SelectStatementAssert.java | 13 +
.../statement/dml/SelectStatementTestCase.java | 4 +
.../src/main/resources/case/dml/delete.xml | 4 +-
.../src/main/resources/case/dml/insert.xml | 4 +-
.../src/main/resources/case/dml/select-with.xml | 310 +++++++++++++++++++++
.../resources/sql/supported/dml/select-with.xml | 25 ++
14 files changed, 881 insertions(+), 12 deletions(-)
diff --git a/shardingsphere-sql-parser/shardingsphere-sql-parser-dialect/shardingsphere-sql-parser-oracle/src/main/antlr4/imports/oracle/BaseRule.g4 b/shardingsphere-sql-parser/shardingsphere-sql-parser-dialect/shardingsphere-sql-parser-oracle/src/main/antlr4/imports/oracle/BaseRule.g4
index 007ab91..a47d4c3 100644
--- a/shardingsphere-sql-parser/shardingsphere-sql-parser-dialect/shardingsphere-sql-parser-oracle/src/main/antlr4/imports/oracle/BaseRule.g4
+++ b/shardingsphere-sql-parser/shardingsphere-sql-parser-dialect/shardingsphere-sql-parser-oracle/src/main/antlr4/imports/oracle/BaseRule.g4
@@ -296,7 +296,7 @@ columnCollationName
;
alias
- : IDENTIFIER_
+ : identifier | STRING_
;
dataTypeLength
@@ -346,6 +346,7 @@ comparisonOperator
predicate
: bitExpr NOT? IN subquery
| bitExpr NOT? IN LP_ expr (COMMA_ expr)* RP_
+ | bitExpr NOT? IN LP_ expr (COMMA_ expr)* RP_ AND predicate
| bitExpr NOT? BETWEEN bitExpr AND predicate
| bitExpr NOT? LIKE simpleExpr (ESCAPE simpleExpr)?
| bitExpr
@@ -674,3 +675,55 @@ percent
rollbackSegment
: identifier
;
+
+queryName
+ : (owner DOT_)? name
+ ;
+
+cycleValue
+ : STRING_
+ ;
+
+noCycleValue
+ : STRING_
+ ;
+
+orderingColumn
+ : columnName
+ ;
+
+subavName
+ : (owner DOT_)? name
+ ;
+
+baseAvName
+ : (owner DOT_)? name
+ ;
+
+measName
+ : identifier
+ ;
+
+levelRef
+ : identifier
+ ;
+
+offsetExpr
+ : expr | numberLiterals
+ ;
+
+memberKeyExpr
+ : identifier
+ ;
+
+depthExpression
+ : identifier
+ ;
+
+unitName
+ : (owner DOT_)? name
+ ;
+
+procedureName
+ : identifier
+ ;
diff --git a/shardingsphere-sql-parser/shardingsphere-sql-parser-dialect/shardingsphere-sql-parser-oracle/src/main/antlr4/imports/oracle/DMLStatement.g4 b/shardingsphere-sql-parser/shardingsphere-sql-parser-dialect/shardingsphere-sql-parser-oracle/src/main/antlr4/imports/oracle/DMLStatement.g4
index 28a78ce..8ad77e5 100644
--- a/shardingsphere-sql-parser/shardingsphere-sql-parser-dialect/shardingsphere-sql-parser-oracle/src/main/antlr4/imports/oracle/DMLStatement.g4
+++ b/shardingsphere-sql-parser/shardingsphere-sql-parser-dialect/shardingsphere-sql-parser-oracle/src/main/antlr4/imports/oracle/DMLStatement.g4
@@ -100,7 +100,7 @@ multipleTableNames
: tableName DOT_ASTERISK_? (COMMA_ tableName DOT_ASTERISK_?)*
;
-select
+select
: selectSubquery forUpdateClause?
;
@@ -121,7 +121,223 @@ unionClause
;
queryBlock
- : SELECT duplicateSpecification? projections fromClause? whereClause? groupByClause? havingClause?
+ : withClause? SELECT duplicateSpecification? projections fromClause? whereClause? groupByClause? havingClause?
+ ;
+
+withClause
+ : WITH plsqlDeclarations? ((subqueryFactoringClause | subavFactoringClause) (COMMA_ (subqueryFactoringClause | subavFactoringClause))*)?
+ ;
+
+plsqlDeclarations
+ : (functionDeclaration | procedureDeclaration)+
+ ;
+
+functionDeclaration
+ : functionHeading ((DETERMINISTIC | PIPELINED | PARALLEL_ENABLE | RESULT_CACHE)+)?
+ ;
+
+functionHeading
+ : FUNCTION functionName (LP_ parameterDeclaration (SQ_ parameterDeclaration)* RP_)? RETURN dataType
+ ;
+
+parameterDeclaration
+ : parameterName ((IN? dataType ((COLON_ EQ_ | DEFAULT) expr)?) | (IN? OUT NOCOPY? dataType))?
+ ;
+
+procedureDeclaration
+ : procedureHeading procedureProperties
+ ;
+
+procedureHeading
+ : PROCEDURE procedureName (LP_ parameterDeclaration (SQ_ parameterDeclaration)* RP_)?
+ ;
+
+procedureProperties
+ : (accessibleByClause | defaultCollationClause | invokerRightsClause)*
+ ;
+
+accessibleByClause
+ : ACCESSIBLE BY LP_ accessor (COMMA_ accessor)* RP_
+ ;
+
+accessor
+ : unitKind? unitName
+ ;
+
+unitKind
+ : FUNCTION | PROCEDURE | PACKAGE | TRIGGER | TYPE
+ ;
+
+defaultCollationClause
+ : DEFAULT COLLATION collationOption
+ ;
+
+collationOption
+ : USING_NLS_COMP
+ ;
+
+invokerRightsClause
+ : AUTHID (CURRENT_USER | DEFINER)
+ ;
+
+subqueryFactoringClause
+ : queryName (LP_ alias (COMMA_ alias)* RP_)? AS LP_ selectSubquery RP_ searchClause? cycleClause?
+ ;
+
+searchClause
+ : SEARCH (DEPTH | BREADTH) FIRST BY (alias (ASC | DESC)? (NULLS FIRST | NULLS LAST)?) (COMMA_ (alias (ASC | DESC)? (NULLS FIRST | NULLS LAST)?))* SET orderingColumn
+ ;
+
+cycleClause
+ : CYCLE alias (COMMA_ alias)* SET alias TO cycleValue DEFAULT noCycleValue
+ ;
+
+subavFactoringClause
+ : subavName ANALYTIC VIEW AS LP_ subavClause RP_
+ ;
+
+subavClause
+ : USING baseAvName hierarchiesClause? filterClauses? addCalcsClause?
+ ;
+
+hierarchiesClause
+ : HIERARCHIES LP_ ((alias DOT_)? alias (COMMA_ (alias DOT_)? alias)*)? RP_
+ ;
+
+filterClauses
+ : FILTER FACT LP_ filterClause (COMMA_ filterClause)* RP_
+ ;
+
+filterClause
+ : (MEASURES | (alias DOT_)? alias) TO predicate
+ ;
+
+addCalcsClause
+ : ADD MEASURES LP_ calcMeasClause (COMMA_ calcMeasClause)* RP_
+ ;
+
+calcMeasClause
+ : measName AS LP_ calcMeasExpression RP_
+ ;
+
+calcMeasExpression
+ : avExpression | expr
+ ;
+
+avExpression
+ : avMeasExpression | avHierExpression
+ ;
+
+avMeasExpression
+ : leadLagExpression | windowExpression | rankExpression | shareOfExpression | qdrExpression
+ ;
+
+leadLagExpression
+ : leadLagFunctionName LP_ calcMeasExpression RP_ OVER LP_ leadLagClause RP_
+ ;
+
+leadLagFunctionName
+ : LAG | LAG_DIFF | LAG_DIF_PERCENT | LEAD | LEAD_DIFF | LEAD_DIFF_PERCENT
+ ;
+
+leadLagClause
+ : HIERARCHY hierarchyRef OFFSET offsetExpr ((WITHIN (LEVEL | PARENT)) | (ACROSS ANCESTOR AT LEVEL levelRef (POSITION FROM (BEGINNING | END))?))?
+ ;
+
+hierarchyRef
+ : (alias DOT_)? alias
+ ;
+
+windowExpression
+ : aggregationFunction OVER LP_ windowClause RP_
+ ;
+
+windowClause
+ : HIERARCHY hierarchyRef BETWEEN (precedingBoundary | followingBoundary) (WITHIN (LEVEL | PARENT | ANCESTOR AT LEVEL levelRef))?
+ ;
+
+precedingBoundary
+ : (UNBOUNDED PRECEDING | offsetExpr PRECEDING) AND (CURRENT MEMBER | offsetExpr (PRECEDING | FOLLOWING) | UNBOUNDED FOLLOWING)
+ ;
+
+followingBoundary
+ : (CURRENT MEMBER | offsetExpr FOLLOWING) AND (offsetExpr FOLLOWING | UNBOUNDED FOLLOWING)
+ ;
+
+rankExpression
+ : rankFunctionName LP_ RP_ OVER LP_ rankClause RP_
+ ;
+
+rankFunctionName
+ : RANK | DENSE_RANK | AVERAGE_RANK | ROW_NUMBER
+ ;
+
+rankClause
+ : HIERARCHY hierarchyRef ORDER BY calcMeasOrderByClause (COMMA_ calcMeasOrderByClause)* (WITHIN (LEVEL | PARENT | ANCESTOR AT LEVEL levelRef))?
+ ;
+
+calcMeasOrderByClause
+ : calcMeasExpression (ASC | DESC)? (NULLS (FIRST | LAST))?
+ ;
+
+shareOfExpression
+ : SHARE_OF LP_ calcMeasExpression shareClause RP_
+ ;
+
+shareClause
+ : HIERARCHY hierarchyRef (PARENT | LEVEL levelRef | MEMBER memberExpression)
+ ;
+
+memberExpression
+ : levelMemberLiteral | hierNavigationExpression | CURRENT MEMBER | NULL | ALL
+ ;
+
+levelMemberLiteral
+ : levelRef (posMemberKeys | namedMemberKeys)
+ ;
+
+posMemberKeys
+ : SQ_ LBT_ SQ_ memberKeyExpr (COMMA_ memberKeyExpr)* SQ_ RBT_ SQ_
+ ;
+
+namedMemberKeys
+ : SQ_ LBT_ SQ_ (attributeName EQ_ memberKeyExpr) (COMMA_ (attributeName EQ_ memberKeyExpr))* SQ_ RBT_ SQ_
+ ;
+
+hierNavigationExpression
+ : hierAncestorExpression | hierParentExpression | hierLeadLagExpression
+ ;
+
+hierAncestorExpression
+ : HIER_ANCESTOR LP_ memberExpression AT (LEVEL levelRef | DEPTH depthExpression) RP_
+ ;
+
+hierParentExpression
+ : HIER_PARENT LP_ memberExpression RP_
+ ;
+
+hierLeadLagExpression
+ : (HIER_LEAD | HIER_LAG) LP_ hierLeadLagClause RP_
+ ;
+
+hierLeadLagClause
+ : memberExpression OFFSET offsetExpr (WITHIN ((LEVEL | PARENT) | (ACROSS ANCESTOR AT LEVEL levelRef (POSITION FROM (BEGINNING | END))?)))?
+ ;
+
+qdrExpression
+ : QUALIFY LP_ calcMeasExpression COMMA_ qualifier RP_
+ ;
+
+qualifier
+ : hierarchyRef EQ_ memberExpression
+ ;
+
+avHierExpression
+ : hierFunctionName LP_ memberExpression WITHIN HIERARCHY hierarchyRef RP_
+ ;
+
+hierFunctionName
+ : HIER_CAPTION | HIER_DEPTH | HIER_DESCRIPTION | HIER_LEVEL | HIER_MEMBER_NAME | HIER_MEMBER_UNIQUE_NAME
;
duplicateSpecification
@@ -136,10 +352,6 @@ projection
: (columnName | expr) (AS? alias)? | qualifiedShorthand
;
-alias
- : identifier | STRING_
- ;
-
unqualifiedShorthand
: ASTERISK_
;
diff --git a/shardingsphere-sql-parser/shardingsphere-sql-parser-dialect/shardingsphere-sql-parser-oracle/src/main/antlr4/imports/oracle/OracleKeyword.g4 b/shardingsphere-sql-parser/shardingsphere-sql-parser-dialect/shardingsphere-sql-parser-oracle/src/main/antlr4/imports/oracle/OracleKeyword.g4
index f8b8ba2..70cfc5c 100644
--- a/shardingsphere-sql-parser/shardingsphere-sql-parser-dialect/shardingsphere-sql-parser-oracle/src/main/antlr4/imports/oracle/OracleKeyword.g4
+++ b/shardingsphere-sql-parser/shardingsphere-sql-parser-dialect/shardingsphere-sql-parser-oracle/src/main/antlr4/imports/oracle/OracleKeyword.g4
@@ -1986,3 +1986,195 @@ SERIALIZABLE
COMMITTED
: C O M M I T T E D
;
+
+FILTER
+ : F I L T E R
+ ;
+
+FACT
+ : F A C T
+ ;
+
+DETERMINISTIC
+ : D E T E R M I N I S T I C
+ ;
+
+PIPELINED
+ : P I P E L I N E D
+ ;
+
+PARALLEL_ENABLE
+ : P A R A L L E L UL_ E N A B L E
+ ;
+
+OUT
+ : O U T
+ ;
+
+NOCOPY
+ : N O C O P Y
+ ;
+
+ACCESSIBLE
+ : A C C E S S I B L E
+ ;
+
+PACKAGE
+ : P A C K A G E
+ ;
+
+USING_NLS_COMP
+ : U S I N G UL_ N L S UL_ C O M P
+ ;
+
+AUTHID
+ : A U T H I D
+ ;
+
+SEARCH
+ : S E A R C H
+ ;
+
+DEPTH
+ : D E P T H
+ ;
+
+BREADTH
+ : B R E A D T H
+ ;
+
+ANALYTIC
+ : A N A L Y T I C
+ ;
+
+HIERARCHIES
+ : H I E R A R C H I E S
+ ;
+
+MEASURES
+ : M E A S U R E S
+ ;
+
+OVER
+ : O V E R
+ ;
+
+LAG
+ : L A G
+ ;
+
+LAG_DIFF
+ : L A G UL_ D I F F
+ ;
+
+LAG_DIF_PERCENT
+ : L A G UL_ D I F UL_ P E R C E N T
+ ;
+
+LEAD
+ : L E A D
+ ;
+
+LEAD_DIFF
+ : L E A D UL_ D I F F
+ ;
+
+LEAD_DIFF_PERCENT
+ : L E A D UL_ D I F F UL_ P E R C E N T
+ ;
+
+HIERARCHY
+ : H I E R A R C H Y
+ ;
+
+WITHIN
+ : W I T H I N
+ ;
+
+ACROSS
+ : A C R O S S
+ ;
+
+ANCESTOR
+ : A N C E S T O R
+ ;
+
+BEGINNING
+ : B E G I N N I N G
+ ;
+
+UNBOUNDED
+ : U N B O U N D E D
+ ;
+
+PRECEDING
+ : P R E C E D I N G
+ ;
+
+FOLLOWING
+ : F O L L O W I N G
+ ;
+
+RANK
+ : R A N K
+ ;
+
+DENSE_RANK
+ : D E N S E UL_ R A N K
+ ;
+
+AVERAGE_RANK
+ : A V E R A G E UL_ R A N K
+ ;
+
+ROW_NUMBER
+ : R O W UL_ N U M B E R
+ ;
+
+SHARE_OF
+ : S H A R E UL_ O F
+ ;
+
+HIER_ANCESTOR
+ : H I E R UL_ A N C E S T O R
+ ;
+
+HIER_PARENT
+ : H I E R UL_ P A R E N T
+ ;
+
+HIER_LEAD
+ : H I E R UL_ L E A D
+ ;
+
+HIER_LAG
+ : H I E R UL_ L A G
+ ;
+
+QUALIFY
+ : Q U A L I F Y
+ ;
+
+HIER_CAPTION
+ : H I E R UL_ C A P T I O N
+ ;
+
+HIER_DEPTH
+ : H I E R UL_ D E P T H
+ ;
+
+HIER_DESCRIPTION
+ : H I E R UL_ D E S C R I P T I O N
+ ;
+
+HIER_LEVEL
+ : H I E R UL_ L E V E L
+ ;
+
+HIER_MEMBER_NAME
+ : H I E R UL_ M E M B E R UL_ N A M E
+ ;
+
+HIER_MEMBER_UNIQUE_NAME
+ : H I E R UL_ M E M B E R UL_ U N I Q U E UL_ N A M E
+ ;
diff --git a/shardingsphere-sql-parser/shardingsphere-sql-parser-dialect/shardingsphere-sql-parser-oracle/src/main/java/org/apache/shardingsphere/sql/parser/oracle/visitor/statement/impl/OracleDMLStatementSQLVisitor.java b/shardingsphere-sql-parser/shardingsphere-sql-parser-dialect/shardingsphere-sql-parser-oracle/src/main/java/org/apache/shardingsphere/sql/parser/oracle/visitor/statement/impl/OracleDMLStatementSQLVisitor.java
index 8cbfc6c..2b0ce33 100644
--- a/shardingsphere-sql-parser/shardingsphere-sql-parser-dialect/shardingsphere-sql-parser-oracle/src/main/java/org/apache/shardingsphere/sql/parser/oracle/visitor/statement/impl/OracleDMLStatementSQLVisitor.java
+++ b/shardingsphere-sql-parser/shardingsphere-sql-parser-dialect/shardingsphere-sql-parser-oracle/src/main/java/org/apache/shardingsphere/sql/parser/oracle/visitor/statement/impl/OracleDMLStatementSQLVisitor.java
@@ -62,6 +62,7 @@ import org.apache.shardingsphere.sql.parser.autogen.OracleStatementParser.Select
import org.apache.shardingsphere.sql.parser.autogen.OracleStatementParser.SetAssignmentsClauseContext;
import org.apache.shardingsphere.sql.parser.autogen.OracleStatementParser.SingleTableClauseContext;
import org.apache.shardingsphere.sql.parser.autogen.OracleStatementParser.SubqueryContext;
+import org.apache.shardingsphere.sql.parser.autogen.OracleStatementParser.SubqueryFactoringClauseContext;
import org.apache.shardingsphere.sql.parser.autogen.OracleStatementParser.TableFactorContext;
import org.apache.shardingsphere.sql.parser.autogen.OracleStatementParser.TableNameContext;
import org.apache.shardingsphere.sql.parser.autogen.OracleStatementParser.TableReferenceContext;
@@ -70,6 +71,7 @@ import org.apache.shardingsphere.sql.parser.autogen.OracleStatementParser.UnionC
import org.apache.shardingsphere.sql.parser.autogen.OracleStatementParser.UpdateContext;
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.sql.common.segment.dml.assignment.AssignmentSegment;
import org.apache.shardingsphere.sql.parser.sql.common.segment.dml.assignment.InsertValuesSegment;
import org.apache.shardingsphere.sql.parser.sql.common.segment.dml.assignment.SetAssignmentSegment;
@@ -78,6 +80,7 @@ import org.apache.shardingsphere.sql.parser.sql.common.segment.dml.column.Insert
import org.apache.shardingsphere.sql.parser.sql.common.segment.dml.expr.BinaryOperationExpression;
import org.apache.shardingsphere.sql.parser.sql.common.segment.dml.expr.ExpressionSegment;
import org.apache.shardingsphere.sql.parser.sql.common.segment.dml.expr.complex.CommonExpressionSegment;
+import org.apache.shardingsphere.sql.parser.sql.common.segment.dml.expr.complex.CommonTableExpressionSegment;
import org.apache.shardingsphere.sql.parser.sql.common.segment.dml.expr.simple.LiteralExpressionSegment;
import org.apache.shardingsphere.sql.parser.sql.common.segment.dml.expr.subquery.SubqueryExpressionSegment;
import org.apache.shardingsphere.sql.parser.sql.common.segment.dml.expr.subquery.SubquerySegment;
@@ -96,6 +99,7 @@ import org.apache.shardingsphere.sql.parser.sql.common.segment.dml.predicate.Loc
import org.apache.shardingsphere.sql.parser.sql.common.segment.dml.predicate.WhereSegment;
import org.apache.shardingsphere.sql.parser.sql.common.segment.generic.AliasSegment;
import org.apache.shardingsphere.sql.parser.sql.common.segment.generic.OwnerSegment;
+import org.apache.shardingsphere.sql.parser.sql.common.segment.generic.WithSegment;
import org.apache.shardingsphere.sql.parser.sql.common.segment.generic.table.DeleteMultiTableSegment;
import org.apache.shardingsphere.sql.parser.sql.common.segment.generic.table.JoinTableSegment;
import org.apache.shardingsphere.sql.parser.sql.common.segment.generic.table.SimpleTableSegment;
@@ -286,6 +290,9 @@ public final class OracleDMLStatementSQLVisitor extends OracleStatementSQLVisito
public ASTNode visitQueryBlock(final QueryBlockContext ctx) {
OracleSelectStatement result = new OracleSelectStatement();
result.setProjections((ProjectionsSegment) visit(ctx.projections()));
+ if (null != ctx.withClause()) {
+ result.setWithSegment((WithSegment) visit(ctx.withClause()));
+ }
if (null != ctx.duplicateSpecification()) {
result.getProjections().setDistinctRow(isDistinct(ctx));
}
@@ -333,6 +340,29 @@ public final class OracleDMLStatementSQLVisitor extends OracleStatementSQLVisito
return visit(ctx.selectSubquery());
}
+ @Override
+ public ASTNode visitWithClause(final WithClauseContext ctx) {
+ Collection<CommonTableExpressionSegment> commonTableExpressions = new LinkedList<>();
+ if (null != ctx.subqueryFactoringClause()) {
+ for (SubqueryFactoringClauseContext each : ctx.subqueryFactoringClause()) {
+ SubquerySegment subquery = new SubquerySegment(each.selectSubquery().start.getStartIndex(), each.selectSubquery().stop.getStopIndex(), (OracleSelectStatement) visit(each));
+ IdentifierValue identifier = (IdentifierValue) visit(each.queryName().name().identifier());
+ CommonTableExpressionSegment commonTableExpression = new CommonTableExpressionSegment(each.start.getStartIndex(), each.stop.getStopIndex(), identifier, subquery);
+ if (null != each.searchClause()) {
+ ColumnNameContext columnName = each.searchClause().orderingColumn().columnName();
+ commonTableExpression.getColumns().add((ColumnSegment) visit(columnName));
+ }
+ commonTableExpressions.add(commonTableExpression);
+ }
+ }
+ return new WithSegment(ctx.start.getStartIndex(), ctx.stop.getStopIndex(), commonTableExpressions);
+ }
+
+ @Override
+ public ASTNode visitSubqueryFactoringClause(final SubqueryFactoringClauseContext ctx) {
+ return visit(ctx.selectSubquery());
+ }
+
private boolean isDistinct(final QueryBlockContext ctx) {
return ((BooleanLiteralValue) visit(ctx.duplicateSpecification())).getValue();
}
diff --git a/shardingsphere-sql-parser/shardingsphere-sql-parser-dialect/shardingsphere-sql-parser-sqlserver/src/main/java/org/apache/shardingsphere/sql/parser/sqlserver/visitor/statement/impl/SQLServerDMLStatementSQLVisitor.java b/shardingsphere-sql-parser/shardingsphere-sql-parser-dialect/shardingsphere-sql-parser-sqlserver/src/main/java/org/apache/shardingsphere/sql/parser/sqlserver/visitor/statement/impl/SQLServerDMLStatementSQLVisitor.java
index 194b968..4706647 100644
--- a/shardingsphere-sql-parser/shardingsphere-sql-parser-dialect/shardingsphere-sql-parser-sqlserver/src/main/java/org/apache/shardingsphere/sql/parser/sqlserver/visitor/statement/impl/SQLServerDMLStatementSQLVisitor.java
+++ b/shardingsphere-sql-parser/shardingsphere-sql-parser-dialect/shardingsphere-sql-parser-sqlserver/src/main/java/org/apache/shardingsphere/sql/parser/sqlserver/visitor/statement/impl/SQLServerDMLStatementSQLVisitor.java
@@ -244,7 +244,7 @@ public final class SQLServerDMLStatementSQLVisitor extends SQLServerStatementSQL
List<CteClauseContext> cteClauses = ctx.cteClause();
Collection<CommonTableExpressionSegment> commonTableExpressions = new LinkedList<>();
for (CteClauseContext cte : cteClauses) {
- SubquerySegment subquery = new SubquerySegment(cte.start.getStartIndex(), cte.stop.getStopIndex(), (SQLServerSelectStatement) visit(cte.subquery()));
+ SubquerySegment subquery = new SubquerySegment(cte.subquery().start.getStartIndex(), cte.subquery().stop.getStopIndex(), (SQLServerSelectStatement) visit(cte.subquery()));
IdentifierValue identifier = (IdentifierValue) visit(cte.identifier());
CommonTableExpressionSegment commonTableExpression = new CommonTableExpressionSegment(cte.start.getStartIndex(), cte.stop.getStopIndex(), identifier, subquery);
if (null != cte.columnNames()) {
diff --git a/shardingsphere-sql-parser/shardingsphere-sql-parser-statement/src/main/java/org/apache/shardingsphere/sql/parser/sql/common/statement/dml/SelectStatement.java b/shardingsphere-sql-parser/shardingsphere-sql-parser-statement/src/main/java/org/apache/shardingsphere/sql/parser/sql/common/statement/dml/SelectStatement.java
index f0eb050..72c53f6 100644
--- a/shardingsphere-sql-parser/shardingsphere-sql-parser-statement/src/main/java/org/apache/shardingsphere/sql/parser/sql/common/statement/dml/SelectStatement.java
+++ b/shardingsphere-sql-parser/shardingsphere-sql-parser-statement/src/main/java/org/apache/shardingsphere/sql/parser/sql/common/statement/dml/SelectStatement.java
@@ -25,6 +25,7 @@ import org.apache.shardingsphere.sql.parser.sql.common.segment.dml.order.GroupBy
import org.apache.shardingsphere.sql.parser.sql.common.segment.dml.order.OrderBySegment;
import org.apache.shardingsphere.sql.parser.sql.common.segment.dml.predicate.HavingSegment;
import org.apache.shardingsphere.sql.parser.sql.common.segment.dml.predicate.WhereSegment;
+import org.apache.shardingsphere.sql.parser.sql.common.segment.generic.WithSegment;
import org.apache.shardingsphere.sql.parser.sql.common.segment.generic.table.TableSegment;
import org.apache.shardingsphere.sql.parser.sql.common.statement.AbstractSQLStatement;
@@ -50,6 +51,8 @@ public abstract class SelectStatement extends AbstractSQLStatement implements DM
private OrderBySegment orderBy;
+ private WithSegment withSegment;
+
/**
* Get where.
*
@@ -85,4 +88,13 @@ public abstract class SelectStatement extends AbstractSQLStatement implements DM
public Optional<OrderBySegment> getOrderBy() {
return Optional.ofNullable(orderBy);
}
+
+ /**
+ * Get with segment.
+ *
+ * @return with segment.
+ */
+ public Optional<WithSegment> getWithSegment() {
+ return Optional.ofNullable(withSegment);
+ }
}
diff --git a/shardingsphere-sql-parser/shardingsphere-sql-parser-statement/src/main/java/org/apache/shardingsphere/sql/parser/sql/dialect/handler/dml/SelectStatementHandler.java b/shardingsphere-sql-parser/shardingsphere-sql-parser-statement/src/main/java/org/apache/shardingsphere/sql/parser/sql/dialect/handler/dml/SelectStatementHandler.java
index b828476..d49aecb 100644
--- a/shardingsphere-sql-parser/shardingsphere-sql-parser-statement/src/main/java/org/apache/shardingsphere/sql/parser/sql/dialect/handler/dml/SelectStatementHandler.java
+++ b/shardingsphere-sql-parser/shardingsphere-sql-parser-statement/src/main/java/org/apache/shardingsphere/sql/parser/sql/dialect/handler/dml/SelectStatementHandler.java
@@ -22,6 +22,7 @@ import lombok.NoArgsConstructor;
import org.apache.shardingsphere.sql.parser.sql.common.segment.dml.pagination.limit.LimitSegment;
import org.apache.shardingsphere.sql.parser.sql.common.segment.dml.predicate.LockSegment;
import org.apache.shardingsphere.sql.parser.sql.common.segment.generic.WindowSegment;
+import org.apache.shardingsphere.sql.parser.sql.common.segment.generic.WithSegment;
import org.apache.shardingsphere.sql.parser.sql.common.statement.dml.SelectStatement;
import org.apache.shardingsphere.sql.parser.sql.dialect.handler.SQLStatementHandler;
import org.apache.shardingsphere.sql.parser.sql.dialect.statement.mysql.MySQLStatement;
@@ -99,4 +100,17 @@ public final class SelectStatementHandler implements SQLStatementHandler {
}
return Optional.empty();
}
+
+ /**
+ * Get with segment.
+ *
+ * @param selectStatement select statement
+ * @return with segment
+ */
+ public static Optional<WithSegment> getWithSegment(final SelectStatement selectStatement) {
+ if (selectStatement instanceof OracleStatement) {
+ return ((OracleSelectStatement) selectStatement).getWithSegment();
+ }
+ return Optional.empty();
+ }
}
diff --git a/shardingsphere-sql-parser/shardingsphere-sql-parser-test/src/main/java/org/apache/shardingsphere/test/sql/parser/parameterized/asserts/segment/with/WithClauseAssert.java b/shardingsphere-sql-parser/shardingsphere-sql-parser-test/src/main/java/org/apache/shardingsphere/test/sql/parser/parameterized/asserts/segment/with/WithClauseAssert.java
index 61a632c..92a237c 100644
--- a/shardingsphere-sql-parser/shardingsphere-sql-parser-test/src/main/java/org/apache/shardingsphere/test/sql/parser/parameterized/asserts/segment/with/WithClauseAssert.java
+++ b/shardingsphere-sql-parser/shardingsphere-sql-parser-test/src/main/java/org/apache/shardingsphere/test/sql/parser/parameterized/asserts/segment/with/WithClauseAssert.java
@@ -25,6 +25,7 @@ import org.apache.shardingsphere.sql.parser.sql.common.segment.generic.WithSegme
import org.apache.shardingsphere.test.sql.parser.parameterized.asserts.SQLCaseAssertContext;
import org.apache.shardingsphere.test.sql.parser.parameterized.asserts.segment.SQLSegmentAssert;
import org.apache.shardingsphere.test.sql.parser.parameterized.asserts.segment.column.ColumnAssert;
+import org.apache.shardingsphere.test.sql.parser.parameterized.asserts.segment.expression.ExpressionAssert;
import org.apache.shardingsphere.test.sql.parser.parameterized.jaxb.cases.domain.segment.impl.with.ExpectedCommonTableExpressionClause;
import org.apache.shardingsphere.test.sql.parser.parameterized.jaxb.cases.domain.segment.impl.with.ExpectedWithClause;
@@ -67,6 +68,9 @@ public final class WithClauseAssert {
ColumnAssert.assertIs(assertContext, each, expected.getCommonTableExpressColumns().getColumns().get(count));
count++;
}
+ if (null != expected.getSubquery()) {
+ ExpressionAssert.assertSubquery(assertContext, actual.getSubquery(), expected.getSubquery());
+ }
SQLSegmentAssert.assertIs(assertContext, actual, expected);
}
}
diff --git a/shardingsphere-sql-parser/shardingsphere-sql-parser-test/src/main/java/org/apache/shardingsphere/test/sql/parser/parameterized/asserts/statement/dml/impl/SelectStatementAssert.java b/shardingsphere-sql-parser/shardingsphere-sql-parser-test/src/main/java/org/apache/shardingsphere/test/sql/parser/parameterized/asserts/statement/dml/impl/SelectStatementAssert.java
index f964e3f..5922cde 100644
--- a/shardingsphere-sql-parser/shardingsphere-sql-parser-test/src/main/java/org/apache/shardingsphere/test/sql/parser/parameterized/asserts/statement/dml/impl/SelectStatementAssert.java
+++ b/shardingsphere-sql-parser/shardingsphere-sql-parser-test/src/main/java/org/apache/shardingsphere/test/sql/parser/parameterized/asserts/statement/dml/impl/SelectStatementAssert.java
@@ -22,6 +22,7 @@ import lombok.NoArgsConstructor;
import org.apache.shardingsphere.sql.parser.sql.common.segment.dml.pagination.limit.LimitSegment;
import org.apache.shardingsphere.sql.parser.sql.common.segment.dml.predicate.LockSegment;
import org.apache.shardingsphere.sql.parser.sql.common.segment.generic.WindowSegment;
+import org.apache.shardingsphere.sql.parser.sql.common.segment.generic.WithSegment;
import org.apache.shardingsphere.sql.parser.sql.common.statement.dml.SelectStatement;
import org.apache.shardingsphere.sql.parser.sql.dialect.handler.dml.SelectStatementHandler;
import org.apache.shardingsphere.test.sql.parser.parameterized.asserts.SQLCaseAssertContext;
@@ -34,6 +35,7 @@ import org.apache.shardingsphere.test.sql.parser.parameterized.asserts.segment.o
import org.apache.shardingsphere.test.sql.parser.parameterized.asserts.segment.projection.ProjectionAssert;
import org.apache.shardingsphere.test.sql.parser.parameterized.asserts.segment.table.TableAssert;
import org.apache.shardingsphere.test.sql.parser.parameterized.asserts.segment.where.WhereClauseAssert;
+import org.apache.shardingsphere.test.sql.parser.parameterized.asserts.segment.with.WithClauseAssert;
import org.apache.shardingsphere.test.sql.parser.parameterized.jaxb.cases.domain.statement.dml.SelectStatementTestCase;
import java.util.Optional;
@@ -65,6 +67,7 @@ public final class SelectStatementAssert {
assertLimitClause(assertContext, actual, expected);
assertTable(assertContext, actual, expected);
assertLockClause(assertContext, actual, expected);
+ assertWithClause(assertContext, actual, expected);
}
private static void assertWindowClause(final SQLCaseAssertContext assertContext, final SelectStatement actual, final SelectStatementTestCase expected) {
@@ -149,4 +152,14 @@ public final class SelectStatementAssert {
assertFalse(assertContext.getText("Actual lock segment should not exist."), actualLock.isPresent());
}
}
+
+ private static void assertWithClause(final SQLCaseAssertContext assertContext, final SelectStatement actual, final SelectStatementTestCase expected) {
+ Optional<WithSegment> withSegment = SelectStatementHandler.getWithSegment(actual);
+ if (null != expected.getWithClause()) {
+ assertTrue(assertContext.getText("Actual with segment should exist."), withSegment.isPresent());
+ WithClauseAssert.assertIs(assertContext, withSegment.get(), expected.getWithClause());
+ } else {
+ assertFalse(assertContext.getText("Actual with segment should not exist."), withSegment.isPresent());
+ }
+ }
}
diff --git a/shardingsphere-sql-parser/shardingsphere-sql-parser-test/src/main/java/org/apache/shardingsphere/test/sql/parser/parameterized/jaxb/cases/domain/statement/dml/SelectStatementTestCase.java b/shardingsphere-sql-parser/shardingsphere-sql-parser-test/src/main/java/org/apache/shardingsphere/test/sql/parser/parameterized/jaxb/cases/domain/statement/dml/SelectStatementTestCase.java
index 0826892..7cafc8f 100644
--- a/shardingsphere-sql-parser/shardingsphere-sql-parser-test/src/main/java/org/apache/shardingsphere/test/sql/parser/parameterized/jaxb/cases/domain/statement/dml/SelectStatementTestCase.java
+++ b/shardingsphere-sql-parser/shardingsphere-sql-parser-test/src/main/java/org/apache/shardingsphere/test/sql/parser/parameterized/jaxb/cases/domain/statement/dml/SelectStatementTestCase.java
@@ -27,6 +27,7 @@ import org.apache.shardingsphere.test.sql.parser.parameterized.jaxb.cases.domain
import org.apache.shardingsphere.test.sql.parser.parameterized.jaxb.cases.domain.segment.impl.table.ExpectedTable;
import org.apache.shardingsphere.test.sql.parser.parameterized.jaxb.cases.domain.segment.impl.where.ExpectedWhereClause;
import org.apache.shardingsphere.test.sql.parser.parameterized.jaxb.cases.domain.segment.impl.window.ExpectedWindowClause;
+import org.apache.shardingsphere.test.sql.parser.parameterized.jaxb.cases.domain.segment.impl.with.ExpectedWithClause;
import org.apache.shardingsphere.test.sql.parser.parameterized.jaxb.cases.domain.statement.SQLParserTestCase;
import javax.xml.bind.annotation.XmlElement;
@@ -64,4 +65,7 @@ public final class SelectStatementTestCase extends SQLParserTestCase {
@XmlElement(name = "lock")
private ExpectedLockClause lockClause;
+
+ @XmlElement(name = "with")
+ private ExpectedWithClause withClause;
}
diff --git a/shardingsphere-sql-parser/shardingsphere-sql-parser-test/src/main/resources/case/dml/delete.xml b/shardingsphere-sql-parser/shardingsphere-sql-parser-test/src/main/resources/case/dml/delete.xml
index dc4bb16..a5b7127 100644
--- a/shardingsphere-sql-parser/shardingsphere-sql-parser-test/src/main/resources/case/dml/delete.xml
+++ b/shardingsphere-sql-parser/shardingsphere-sql-parser-test/src/main/resources/case/dml/delete.xml
@@ -366,7 +366,7 @@
<column name="order_id" start-index="10" stop-index="17"/>
<column name="user_id" start-index="20" stop-index="26"/>
</columns>
- <subquery-expression>
+ <subquery-expression start-index="32" stop-index="70">
<select>
<from>
<simple-table name="t_order" start-index="63" stop-index="69" />
@@ -402,7 +402,7 @@
<delete sql-case-id="delete_without_columns_with_with_clause">
<with start-index="0" stop-index="50">
<common-table-expression name="cte" start-index="5" stop-index="50">
- <subquery-expression>
+ <subquery-expression start-index="12" stop-index="50">
<select>
<from>
<simple-table name="t_order" start-index="43" stop-index="49" />
diff --git a/shardingsphere-sql-parser/shardingsphere-sql-parser-test/src/main/resources/case/dml/insert.xml b/shardingsphere-sql-parser/shardingsphere-sql-parser-test/src/main/resources/case/dml/insert.xml
index ff2665a..dba938f 100644
--- a/shardingsphere-sql-parser/shardingsphere-sql-parser-test/src/main/resources/case/dml/insert.xml
+++ b/shardingsphere-sql-parser/shardingsphere-sql-parser-test/src/main/resources/case/dml/insert.xml
@@ -1360,7 +1360,7 @@
<column name="order_id" start-index="10" stop-index="17"/>
<column name="user_id" start-index="20" stop-index="26"/>
</columns>
- <subquery-expression>
+ <subquery-expression start-index="32" stop-index="70">
<select>
<from>
<simple-table name="t_order" start-index="63" stop-index="69" />
@@ -1392,7 +1392,7 @@
<insert sql-case-id="insert_without_columns_with_with_clause">
<with start-index="0" stop-index="50">
<common-table-expression name="cte" start-index="5" stop-index="50">
- <subquery-expression>
+ <subquery-expression start-index="12" stop-index="50">
<select>
<from>
<simple-table name="t_order" start-index="43" stop-index="49" />
diff --git a/shardingsphere-sql-parser/shardingsphere-sql-parser-test/src/main/resources/case/dml/select-with.xml b/shardingsphere-sql-parser/shardingsphere-sql-parser-test/src/main/resources/case/dml/select-with.xml
new file mode 100644
index 0000000..fd888e7
--- /dev/null
+++ b/shardingsphere-sql-parser/shardingsphere-sql-parser-test/src/main/resources/case/dml/select-with.xml
@@ -0,0 +1,310 @@
+<?xml version="1.0" encoding="UTF-8"?>
+<!--
+ ~ 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.
+ -->
+
+<sql-parser-test-cases>
+ <select sql-case-id="select_with_subquery_factoring">
+ <with start-index="0" stop-index="110">
+ <common-table-expression name="dept_costs" start-index="5" stop-index="110">
+ <subquery-expression start-index="20" stop-index="109">
+ <select>
+ <from>
+ <simple-table name="departments" alias="d" start-index="72" stop-index="84" />
+ </from>
+ <projections start-index="27" stop-index="65">
+ <column-projection name="department_name" start-index="27" stop-index="41" />
+ <aggregation-projection type="SUM" alias="dept_total" inner-expression="(salary)" start-index="44" stop-index="54" />
+ </projections>
+ <group-by>
+ <column-item name="department_name" start-index="95" stop-index="109" />
+ </group-by>
+ </select>
+ </subquery-expression>
+ </common-table-expression>
+ </with>
+ <from>
+ <simple-table name="dept_costs" start-index="126" stop-index="135" />
+ </from>
+ <projections start-index="119" stop-index="119">
+ <shorthand-projection start-index="119" stop-index="119" />
+ </projections>
+ <where start-index="137" stop-index="161">
+ <expr>
+ <binary-operation-expression start-index="143" stop-index="161">
+ <left>
+ <column name="dept_total" start-index="143" stop-index="152" />
+ </left>
+ <operator>></operator>
+ <right>
+ <literal-expression value="304500" start-index="156" stop-index="161" />
+ </right>
+ </binary-operation-expression>
+ </expr>
+ </where>
+ <order-by>
+ <column-item name="department_name" start-index="172" stop-index="186" />
+ </order-by>
+ </select>
+
+ <select sql-case-id="select_with_subquery_factoring_with_binding_tables_without_join">
+ <with start-index="0" stop-index="230">
+ <common-table-expression name = "dept_costs" start-index="5" stop-index="163">
+ <subquery-expression start-index="20" stop-index="162">
+ <select>
+ <from>
+ <join-table>
+ <left>
+ <simple-table name="employees" alias="e" start-index="72" stop-index="82" />
+ </left>
+ <right>
+ <simple-table name="departments" alias="d" start-index="85" stop-index="97" />
+ </right>
+ </join-table>
+ </from>
+ <projections start-index="27" stop-index="65">
+ <column-projection name="department_name" start-index="27" stop-index="41" />
+ <aggregation-projection type="SUM" alias="dept_total" inner-expression="(salary)" start-index="44" stop-index="54" />
+ </projections>
+ <where start-index="99" stop-index="137">
+ <expr>
+ <binary-operation-expression start-index="105" stop-index="137">
+ <left>
+ <column name="department_id" start-index="105" stop-index="119" >
+ <owner name="e" start-index="105" stop-index="105" />
+ </column>
+ </left>
+ <operator>=</operator>
+ <right>
+ <column name="department_id" start-index="123" stop-index="137" >
+ <owner name="d" start-index="123" stop-index="123" />
+ </column>
+ </right>
+ </binary-operation-expression>
+ </expr>
+ </where>
+ <group-by>
+ <column-item name="department_name" start-index="148" stop-index="162" />
+ </group-by>
+ </select>
+ </subquery-expression>
+ </common-table-expression>
+ <common-table-expression name="avg_cost" start-index="166" stop-index="230">
+ <subquery-expression start-index="179" stop-index="229">
+ <select>
+ <from>
+ <simple-table name="dept_costs" start-index="220" stop-index="229" />
+ </from>
+ <projections start-index="186" stop-index="213">
+ <expression-projection text="SUM(dept_total)/COUNT(*)" alias="avg" start-index="186" stop-index="213" />
+ </projections>
+ </select>
+ </subquery-expression>
+ </common-table-expression>
+ </with>
+ <from>
+ <simple-table name="dept_costs" start-index="246" stop-index="255" />
+ </from>
+ <projections start-index="239" stop-index="239">
+ <shorthand-projection start-index="239" stop-index="239" />
+ </projections>
+ <where start-index="257" stop-index="301">
+ <expr>
+ <binary-operation-expression start-index="263" stop-index="301">
+ <left>
+ <column name="dept_total" start-index="263" stop-index="272" />
+ </left>
+ <operator>></operator>
+ <right>
+ <subquery start-index="276" stop-index="301">
+ <select>
+ <from start-index="293" stop-index="300">
+ <simple-table name="avg_cost" start-index="293" stop-index="300"/>
+ </from>
+ <projections start-index="284" stop-index="286">
+ <column-projection name="avg" start-index="284" stop-index="286"/>
+ </projections>
+ </select>
+ </subquery>
+ </right>
+ </binary-operation-expression>
+ </expr>
+ </where>
+ <order-by>
+ <column-item name="department_name" start-index="312" stop-index="326" />
+ </order-by>
+ </select>
+
+ <select sql-case-id="select_with_subquery_factoring_with_search_depth_first">
+ <with start-index="0" stop-index="224">
+ <common-table-expression name="org_chart" start-index="5" stop-index="224">
+ <subquery-expression start-index="72" stop-index="181">
+ <select>
+ <from>
+ <simple-table name="employees" start-index="148" stop-index="156" />
+ </from>
+ <projections start-index="79" stop-index="141">
+ <column-projection name="employee_id" start-index="79" stop-index="89" />
+ <column-projection name="last_name" start-index="92" stop-index="100" />
+ <column-projection name="manager_id" start-index="103" stop-index="112" />
+ <column-projection name="reportLevel" start-index="115" stop-index="125" />
+ <column-projection name="salary" start-index="128" stop-index="133" />
+ <column-projection name="job_id" start-index="136" stop-index="141" />
+ </projections>
+ <where start-index="158" stop-index="181">
+ <expr>
+ <binary-operation-expression start-index="164" stop-index="181">
+ <left>
+ <column name="manager_id" start-index="164" stop-index="173" />
+ </left>
+ <operator>IS</operator>
+ <right>
+ <literal-expression value="177..181" start-index="177" stop-index="181" />
+ </right>
+ </binary-operation-expression>
+ </expr>
+ </where>
+ </select>
+ </subquery-expression>
+ <columns start-index="219" stop-index="224">
+ <column name="order1" start-index="219" stop-index="224" />
+ </columns>
+ </common-table-expression>
+ </with>
+ <from>
+ <simple-table name="org_chart" start-index="276" stop-index="284" />
+ </from>
+ <projections start-index="233" stop-index="269">
+ <column-projection name="emp_name" start-index="233" stop-index="240" />
+ <column-projection name="eid" start-index="243" stop-index="245" />
+ <column-projection name="mgr_id" start-index="248" stop-index="253" />
+ <column-projection name="salary" start-index="256" stop-index="261" />
+ <column-projection name="job_id" start-index="264" stop-index="269" />
+ </projections>
+ <order-by>
+ <column-item name="order1" start-index="295" stop-index="300" />
+ </order-by>
+ </select>
+
+ <select sql-case-id="select_with_subquery_factoring_with_search_depth_first_with_cycle">
+ <with start-index="0" stop-index="282">
+ <common-table-expression name="dup_hiredate" start-index="5" stop-index="282">
+ <subquery-expression start-index="78" stop-index="190">
+ <select>
+ <from>
+ <simple-table name="employees" start-index="157" stop-index="165" />
+ </from>
+ <projections start-index="85" stop-index="150">
+ <column-projection name="employee_id" start-index="85" stop-index="95" />
+ <column-projection name="last_name" start-index="98" stop-index="106" />
+ <column-projection name="manager_id" start-index="109" stop-index="118" />
+ <column-projection name="reportLevel" start-index="121" stop-index="131" />
+ <column-projection name="hire_date" start-index="134" stop-index="142" />
+ <column-projection name="job_id" start-index="145" stop-index="150" />
+ </projections>
+ <where start-index="167" stop-index="190">
+ <expr>
+ <binary-operation-expression start-index="173" stop-index="190">
+ <left>
+ <column name="manager_id" start-index="173" stop-index="182" />
+ </left>
+ <operator>IS</operator>
+ <right>
+ <literal-expression value="186..190" start-index="186" stop-index="190" />
+ </right>
+ </binary-operation-expression>
+ </expr>
+ </where>
+ </select>
+ </subquery-expression>
+ <columns start-index="229" stop-index="234">
+ <column name="order1" start-index="229" stop-index="234" />
+ </columns>
+ </common-table-expression>
+ </with>
+ <from>
+ <simple-table name="dup_hiredate" start-index="381" stop-index="392" />
+ </from>
+ <projections start-index="291" stop-index="374">
+ <expression-projection text="lpad(' ',2*reportLevel)||emp_last" alias="emp_name" start-index="291" stop-index="332" />
+ <column-projection name="eid" start-index="335" stop-index="337" />
+ <column-projection name="mgr_id" start-index="340" stop-index="345" />
+ <column-projection name="hire_date" start-index="348" stop-index="356" />
+ <column-projection name="job_id" start-index="359" stop-index="364" />
+ <column-projection name="is_cycle" start-index="367" stop-index="374" />
+ </projections>
+ <order-by>
+ <column-item name="order1" start-index="403" stop-index="408" />
+ </order-by>
+ </select>
+
+ <select sql-case-id="select_with_subquery_factoring_with_search_depth_first_with_having">
+ <with start-index="0" stop-index="207">
+ <common-table-expression name="emp_count" start-index="5" stop-index="207">
+ <subquery-expression start-index="76" stop-index="164">
+ <select>
+ <from>
+ <simple-table name="employees" start-index="156" stop-index="164" />
+ </from>
+ <projections start-index="83" stop-index="149">
+ <column-projection name="employee_id" start-index="83" stop-index="93" />
+ <column-projection name="last_name" start-index="96" stop-index="104" />
+ <column-projection name="manager_id" start-index="107" stop-index="116" />
+ <column-projection name="mgrLevel" start-index="119" stop-index="126" />
+ <column-projection name="salary" start-index="129" stop-index="134" />
+ <column-projection name="cnt_employees" start-index="137" stop-index="149" />
+ </projections>
+ </select>
+ </subquery-expression>
+ <columns start-index="202" stop-index="207">
+ <column name="order1" start-index="202" stop-index="207" />
+ </columns>
+ </common-table-expression>
+ </with>
+ <from>
+ <simple-table name="emp_count" start-index="251" stop-index="259" />
+ </from>
+ <projections start-index="216" stop-index="244">
+ <column-projection name="emp_last" start-index="216" stop-index="223" />
+ <column-projection name="eid" start-index="226" stop-index="228" />
+ <column-projection name="mgr_id" start-index="231" stop-index="236" />
+ <column-projection name="salary" start-index="239" stop-index="244" />
+ </projections>
+ <group-by>
+ <column-item name="emp_last" start-index="270" stop-index="277" />
+ <column-item name="eid" start-index="280" stop-index="282" />
+ <column-item name="mgr_id" start-index="285" stop-index="290" />
+ <column-item name="salary" start-index="293" stop-index="298" />
+ </group-by>
+ <having start-index="300" stop-index="320">
+ <expr>
+ <binary-operation-expression start-index="307" stop-index="320">
+ <left>
+ <column name="salary" start-index="307" stop-index="312" />
+ </left>
+ <operator>></operator>
+ <right>
+ <literal-expression start-index="316" stop-index="320" value="24000" />
+ </right>
+ </binary-operation-expression>
+ </expr>
+ </having>
+ <order-by>
+ <column-item name="mgr_id" start-index="331" stop-index="336" />
+ <column-item name="emp_last" start-index="351" stop-index="358" />
+ </order-by>
+ </select>
+</sql-parser-test-cases>
diff --git a/shardingsphere-sql-parser/shardingsphere-sql-parser-test/src/main/resources/sql/supported/dml/select-with.xml b/shardingsphere-sql-parser/shardingsphere-sql-parser-test/src/main/resources/sql/supported/dml/select-with.xml
new file mode 100644
index 0000000..dd2dd50
--- /dev/null
+++ b/shardingsphere-sql-parser/shardingsphere-sql-parser-test/src/main/resources/sql/supported/dml/select-with.xml
@@ -0,0 +1,25 @@
+<?xml version="1.0" encoding="UTF-8"?>
+<!--
+ ~ 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.
+ -->
+
+<sql-cases>
+ <sql-case id="select_with_subquery_factoring" value="WITH dept_costs AS (SELECT department_name, SUM(salary) dept_total FROM departments d GROUP BY department_name) SELECT * FROM dept_costs WHERE dept_total > 304500 ORDER BY department_name" db-types="Oracle"/>
+ <sql-case id="select_with_subquery_factoring_with_binding_tables_without_join" value="WITH dept_costs AS (SELECT department_name, SUM(salary) dept_total FROM employees e, departments d WHERE e.department_id = d.department_id GROUP BY department_name), avg_cost AS (SELECT SUM(dept_total)/COUNT(*) avg FROM dept_costs) SELECT * FROM dept_costs WHERE dept_total > (SELECT avg FROM avg_cost) ORDER BY department_name" db-types="Oracle"/>
+ <sql-case id="select_with_subquery_factoring_with_search_depth_first" value="WITH org_chart (eid, emp_last, mgr_id, reportLevel, salary, job_id) AS (SELECT employee_id, last_name, manager_id, reportLevel, salary, job_id FROM employees WHERE manager_id IS NULL) SEARCH DEPTH FIRST BY emp_last SET order1 SELECT emp_name, eid, mgr_id, salary, job_id FROM org_chart ORDER BY order1" db-types="Oracle"/>
+ <sql-case id="select_with_subquery_factoring_with_search_depth_first_with_cycle" value="WITH dup_hiredate (eid, emp_last, mgr_id, reportLevel, hire_date, job_id) AS (SELECT employee_id, last_name, manager_id, reportLevel, hire_date, job_id FROM employees WHERE manager_id IS NULL) SEARCH DEPTH FIRST BY hire_date SET order1 CYCLE hire_date SET is_cycle TO 'Y' DEFAULT 'N' SELECT lpad(' ',2*reportLevel)||emp_last emp_name, eid, mgr_id, hire_date, job_id, is_cycle FROM dup_hiredate ORDER [...]
+ <sql-case id="select_with_subquery_factoring_with_search_depth_first_with_having" value="WITH emp_count (eid, emp_last, mgr_id, mgrLevel, salary, cnt_employees) AS (SELECT employee_id, last_name, manager_id, mgrLevel, salary, cnt_employees FROM employees) SEARCH DEPTH FIRST BY emp_last SET order1 SELECT emp_last, eid, mgr_id, salary FROM emp_count GROUP BY emp_last, eid, mgr_id, salary HAVING salary > 24000 ORDER BY mgr_id NULLS FIRST, emp_last" db-types="Oracle"/>
+</sql-cases>