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/05/27 07:47:14 UTC
[shardingsphere] branch master updated: Proofread Oracle `SELECT`
subquery clause (#10381)
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 384bd1d Proofread Oracle `SELECT` subquery clause (#10381)
384bd1d is described below
commit 384bd1d2722e9e40f25c40196ea77515e586df9a
Author: Thanoshan MV <48...@users.noreply.github.com>
AuthorDate: Thu May 27 13:16:41 2021 +0530
Proofread Oracle `SELECT` subquery clause (#10381)
* add SQL definition for selectSubquery
* add selectSubquery clause definition
* rename to queryBlock, modify selectSubquery
---
.../src/main/antlr4/imports/oracle/BaseRule.g4 | 16 +++-
.../src/main/antlr4/imports/oracle/DMLStatement.g4 | 38 +++++++--
.../main/antlr4/imports/oracle/OracleKeyword.g4 | 36 +++++++++
.../impl/OracleDMLStatementSQLVisitor.java | 89 +++++++++++++++++++---
.../common/segment/dml/predicate/LockSegment.java | 3 +
.../asserts/segment/lock/LockClauseAssert.java | 15 +++-
.../segment/impl/lock/ExpectedLockClause.java | 4 +
.../src/main/resources/case/dml/select-lock.xml | 25 ++++++
.../main/resources/case/dml/select-order-by.xml | 39 ++++++++++
.../main/resources/case/dml/select-pagination.xml | 13 ++++
.../resources/sql/supported/dml/select-lock.xml | 2 +
.../sql/supported/dml/select-order-by.xml | 2 +
.../sql/supported/dml/select-pagination.xml | 1 +
13 files changed, 261 insertions(+), 22 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 a9f66bc..8d09612 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
@@ -428,11 +428,11 @@ subquery
;
orderByClause
- : ORDER BY orderByItem (COMMA_ orderByItem)*
+ : ORDER SIBLINGS? BY orderByItem (COMMA_ orderByItem)*
;
orderByItem
- : (columnName | numberLiterals | expr) (ASC | DESC)?
+ : (columnName | numberLiterals | expr) (ASC | DESC)? (NULLS FIRST | NULLS LAST)?
;
attributeName
@@ -654,3 +654,15 @@ certificateId
categoryName
: identifier
;
+
+offset
+ : numberLiterals | expr | nullValueLiterals
+ ;
+
+rowcount
+ : numberLiterals | expr | nullValueLiterals
+ ;
+
+percent
+ : numberLiterals | expr | nullValueLiterals
+ ;
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 607b94f..28a78ce 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
@@ -101,15 +101,27 @@ multipleTableNames
;
select
- : unionClause
+ : selectSubquery forUpdateClause?
+ ;
+
+selectSubquery
+ : (queryBlock | selectUnionClause | parenthesisSelectSubquery) orderByClause? rowLimitingClause
+ ;
+
+selectUnionClause
+ : ((queryBlock | parenthesisSelectSubquery) orderByClause? rowLimitingClause) ((UNION ALL? | INTERSECT | MINUS) selectSubquery)+
+ ;
+
+parenthesisSelectSubquery
+ : LP_ selectSubquery RP_
;
unionClause
- : selectClause (UNION (ALL | DISTINCT)? selectClause)*
+ : queryBlock (UNION (ALL | DISTINCT)? queryBlock)*
;
-selectClause
- : SELECT duplicateSpecification? projections fromClause? whereClause? groupByClause? havingClause? orderByClause? lockClause?
+queryBlock
+ : SELECT duplicateSpecification? projections fromClause? whereClause? groupByClause? havingClause?
;
duplicateSpecification
@@ -174,11 +186,23 @@ havingClause
;
subquery
- : LP_ unionClause RP_
+ : LP_ selectSubquery RP_
+ ;
+
+forUpdateClause
+ : FOR UPDATE (OF forUpdateClauseList)? ((NOWAIT | WAIT INTEGER_) | SKIP_SYMBOL LOCKED)?
+ ;
+
+forUpdateClauseList
+ : forUpdateClauseOption (COMMA_ forUpdateClauseOption)*
+ ;
+
+forUpdateClauseOption
+ : ((tableName | viewName) DOT_)? columnName
;
-lockClause
- : FOR UPDATE
+rowLimitingClause
+ : (OFFSET offset (ROW | ROWS))? (FETCH (FIRST | NEXT) (rowcount | percent PERCENT)? (ROW | ROWS) (ONLY | WITH TIES))?
;
merge
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 4178530..dd3d83b 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
@@ -1922,3 +1922,39 @@ USE_STORED_OUTLINES
GLOBAL_TOPIC_ENABLED
: G L O B A L UL_ T O P I C UL_ E N A B L E D
;
+
+INTERSECT
+ : I N T E R S E C T
+ ;
+
+MINUS
+ : M I N U S
+ ;
+
+LOCKED
+ : L O C K E D
+ ;
+
+FETCH
+ : F E T C H
+ ;
+
+PERCENT
+ : P E R C E N T
+ ;
+
+TIES
+ : T I E S
+ ;
+
+SIBLINGS
+ : S I B L I N G S
+ ;
+
+NULLS
+ : N U L L S
+ ;
+
+LAST
+ : L A S T
+ ;
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 6f5a552..927ce18 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
@@ -21,6 +21,7 @@ import lombok.NoArgsConstructor;
import org.apache.shardingsphere.sql.parser.api.visitor.ASTNode;
import org.apache.shardingsphere.sql.parser.api.visitor.operation.SQLStatementVisitor;
import org.apache.shardingsphere.sql.parser.api.visitor.type.DMLSQLVisitor;
+import org.apache.shardingsphere.sql.parser.autogen.OracleStatementParser;
import org.apache.shardingsphere.sql.parser.autogen.OracleStatementParser.AliasContext;
import org.apache.shardingsphere.sql.parser.autogen.OracleStatementParser.AssignmentContext;
import org.apache.shardingsphere.sql.parser.autogen.OracleStatementParser.AssignmentValueContext;
@@ -31,6 +32,8 @@ import org.apache.shardingsphere.sql.parser.autogen.OracleStatementParser.Delete
import org.apache.shardingsphere.sql.parser.autogen.OracleStatementParser.DeleteWhereClauseContext;
import org.apache.shardingsphere.sql.parser.autogen.OracleStatementParser.DuplicateSpecificationContext;
import org.apache.shardingsphere.sql.parser.autogen.OracleStatementParser.ExprContext;
+import org.apache.shardingsphere.sql.parser.autogen.OracleStatementParser.ForUpdateClauseContext;
+import org.apache.shardingsphere.sql.parser.autogen.OracleStatementParser.ForUpdateClauseListContext;
import org.apache.shardingsphere.sql.parser.autogen.OracleStatementParser.FromClauseContext;
import org.apache.shardingsphere.sql.parser.autogen.OracleStatementParser.GroupByClauseContext;
import org.apache.shardingsphere.sql.parser.autogen.OracleStatementParser.InsertContext;
@@ -38,7 +41,6 @@ import org.apache.shardingsphere.sql.parser.autogen.OracleStatementParser.Insert
import org.apache.shardingsphere.sql.parser.autogen.OracleStatementParser.IntoClauseContext;
import org.apache.shardingsphere.sql.parser.autogen.OracleStatementParser.JoinSpecificationContext;
import org.apache.shardingsphere.sql.parser.autogen.OracleStatementParser.JoinedTableContext;
-import org.apache.shardingsphere.sql.parser.autogen.OracleStatementParser.LockClauseContext;
import org.apache.shardingsphere.sql.parser.autogen.OracleStatementParser.MergeAssignmentContext;
import org.apache.shardingsphere.sql.parser.autogen.OracleStatementParser.MergeAssignmentValueContext;
import org.apache.shardingsphere.sql.parser.autogen.OracleStatementParser.MergeContext;
@@ -47,11 +49,14 @@ import org.apache.shardingsphere.sql.parser.autogen.OracleStatementParser.MergeU
import org.apache.shardingsphere.sql.parser.autogen.OracleStatementParser.MultipleTableNamesContext;
import org.apache.shardingsphere.sql.parser.autogen.OracleStatementParser.MultipleTablesClauseContext;
import org.apache.shardingsphere.sql.parser.autogen.OracleStatementParser.OrderByItemContext;
+import org.apache.shardingsphere.sql.parser.autogen.OracleStatementParser.ParenthesisSelectSubqueryContext;
import org.apache.shardingsphere.sql.parser.autogen.OracleStatementParser.ProjectionContext;
import org.apache.shardingsphere.sql.parser.autogen.OracleStatementParser.ProjectionsContext;
import org.apache.shardingsphere.sql.parser.autogen.OracleStatementParser.QualifiedShorthandContext;
-import org.apache.shardingsphere.sql.parser.autogen.OracleStatementParser.SelectClauseContext;
+import org.apache.shardingsphere.sql.parser.autogen.OracleStatementParser.QueryBlockContext;
import org.apache.shardingsphere.sql.parser.autogen.OracleStatementParser.SelectContext;
+import org.apache.shardingsphere.sql.parser.autogen.OracleStatementParser.SelectSubqueryContext;
+import org.apache.shardingsphere.sql.parser.autogen.OracleStatementParser.SelectUnionClauseContext;
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;
@@ -244,19 +249,38 @@ public final class OracleDMLStatementSQLVisitor extends OracleStatementSQLVisito
@Override
public ASTNode visitSelect(final SelectContext ctx) {
// TODO :Unsupported for withClause.
- OracleSelectStatement result = (OracleSelectStatement) visit(ctx.unionClause());
+ OracleSelectStatement result = (OracleSelectStatement) visit(ctx.selectSubquery());
result.setParameterCount(getCurrentParameterIndex());
+ if (null != ctx.forUpdateClause()) {
+ result.setLock((LockSegment) visit(ctx.forUpdateClause()));
+ }
return result;
}
@Override
public ASTNode visitUnionClause(final UnionClauseContext ctx) {
// TODO :Unsupported for union SQL.
- return visit(ctx.selectClause(0));
+ return visit(ctx.queryBlock(0));
}
@Override
- public ASTNode visitSelectClause(final SelectClauseContext ctx) {
+ public ASTNode visitSelectSubquery(final SelectSubqueryContext ctx) {
+ OracleSelectStatement result;
+ if (null != ctx.queryBlock()) {
+ result = (OracleSelectStatement) visit(ctx.queryBlock());
+ } else if (null != ctx.selectUnionClause()) {
+ result = (OracleSelectStatement) visit(ctx.selectUnionClause());
+ } else {
+ result = (OracleSelectStatement) visit(ctx.parenthesisSelectSubquery());
+ }
+ if (null != ctx.orderByClause()) {
+ result.setOrderBy((OrderBySegment) visit(ctx.orderByClause()));
+ }
+ return result;
+ }
+
+ @Override
+ public ASTNode visitQueryBlock(final QueryBlockContext ctx) {
OracleSelectStatement result = new OracleSelectStatement();
result.setProjections((ProjectionsSegment) visit(ctx.projections()));
if (null != ctx.duplicateSpecification()) {
@@ -272,16 +296,32 @@ public final class OracleDMLStatementSQLVisitor extends OracleStatementSQLVisito
if (null != ctx.groupByClause()) {
result.setGroupBy((GroupBySegment) visit(ctx.groupByClause()));
}
+ return result;
+ }
+
+ @Override
+ public ASTNode visitSelectUnionClause(final SelectUnionClauseContext ctx) {
+ OracleSelectStatement result;
+ if (null != ctx.queryBlock()) {
+ result = (OracleSelectStatement) visit(ctx.queryBlock());
+ } else {
+ result = (OracleSelectStatement) visit(ctx.parenthesisSelectSubquery());
+ }
if (null != ctx.orderByClause()) {
result.setOrderBy((OrderBySegment) visit(ctx.orderByClause()));
}
- if (null != ctx.lockClause()) {
- result.setLock((LockSegment) visit(ctx.lockClause()));
+ for (SelectSubqueryContext each : ctx.selectSubquery()) {
+ visit(each);
}
return result;
}
-
- private boolean isDistinct(final SelectClauseContext ctx) {
+
+ @Override
+ public ASTNode visitParenthesisSelectSubquery(final ParenthesisSelectSubqueryContext ctx) {
+ return visit(ctx.selectSubquery());
+ }
+
+ private boolean isDistinct(final QueryBlockContext ctx) {
return ((BooleanLiteralValue) visit(ctx.duplicateSpecification())).getValue();
}
@@ -478,12 +518,37 @@ public final class OracleDMLStatementSQLVisitor extends OracleStatementSQLVisito
@Override
public ASTNode visitSubquery(final SubqueryContext ctx) {
- return visit(ctx.unionClause());
+ return visit(ctx.selectSubquery());
}
@Override
- public ASTNode visitLockClause(final LockClauseContext ctx) {
- return new LockSegment(ctx.getStart().getStartIndex(), ctx.getStop().getStopIndex());
+ public ASTNode visitForUpdateClause(final ForUpdateClauseContext ctx) {
+ LockSegment result = new LockSegment(ctx.getStart().getStartIndex(), ctx.getStop().getStopIndex());
+ if (null != ctx.forUpdateClauseList()) {
+ result.getTables().addAll(generateTablesFromforUpdateClauseOption(ctx.forUpdateClauseList()));
+ result.getColumns().addAll(generateColumnsFromforUpdateClauseOption(ctx.forUpdateClauseList()));
+ }
+ return result;
+ }
+
+ private List<SimpleTableSegment> generateTablesFromforUpdateClauseOption(final ForUpdateClauseListContext ctx) {
+ List<SimpleTableSegment> result = new LinkedList<>();
+ for (OracleStatementParser.ForUpdateClauseOptionContext each : ctx.forUpdateClauseOption()) {
+ if (null != each.tableName()) {
+ result.add((SimpleTableSegment) visit(each.tableName()));
+ }
+ }
+ return result;
+ }
+
+ private List<ColumnSegment> generateColumnsFromforUpdateClauseOption(final ForUpdateClauseListContext ctx) {
+ List<ColumnSegment> result = new LinkedList<>();
+ for (OracleStatementParser.ForUpdateClauseOptionContext each : ctx.forUpdateClauseOption()) {
+ if (null != each.columnName()) {
+ result.add((ColumnSegment) visit(each.columnName()));
+ }
+ }
+ return result;
}
@Override
diff --git a/shardingsphere-sql-parser/shardingsphere-sql-parser-statement/src/main/java/org/apache/shardingsphere/sql/parser/sql/common/segment/dml/predicate/LockSegment.java b/shardingsphere-sql-parser/shardingsphere-sql-parser-statement/src/main/java/org/apache/shardingsphere/sql/parser/sql/common/segment/dml/predicate/LockSegment.java
index 92ad7ba..e4d812b 100644
--- a/shardingsphere-sql-parser/shardingsphere-sql-parser-statement/src/main/java/org/apache/shardingsphere/sql/parser/sql/common/segment/dml/predicate/LockSegment.java
+++ b/shardingsphere-sql-parser/shardingsphere-sql-parser-statement/src/main/java/org/apache/shardingsphere/sql/parser/sql/common/segment/dml/predicate/LockSegment.java
@@ -20,6 +20,7 @@ package org.apache.shardingsphere.sql.parser.sql.common.segment.dml.predicate;
import lombok.Getter;
import lombok.RequiredArgsConstructor;
import org.apache.shardingsphere.sql.parser.sql.common.segment.SQLSegment;
+import org.apache.shardingsphere.sql.parser.sql.common.segment.dml.column.ColumnSegment;
import org.apache.shardingsphere.sql.parser.sql.common.segment.generic.table.SimpleTableSegment;
import java.util.LinkedList;
@@ -38,4 +39,6 @@ public final class LockSegment implements SQLSegment {
private final int stopIndex;
private List<SimpleTableSegment> tables = new LinkedList<>();
+
+ private List<ColumnSegment> columns = new LinkedList<>();
}
diff --git a/shardingsphere-sql-parser/shardingsphere-sql-parser-test/src/main/java/org/apache/shardingsphere/test/sql/parser/parameterized/asserts/segment/lock/LockClauseAssert.java b/shardingsphere-sql-parser/shardingsphere-sql-parser-test/src/main/java/org/apache/shardingsphere/test/sql/parser/parameterized/asserts/segment/lock/LockClauseAssert.java
index de9d0c6..5dd1556 100644
--- a/shardingsphere-sql-parser/shardingsphere-sql-parser-test/src/main/java/org/apache/shardingsphere/test/sql/parser/parameterized/asserts/segment/lock/LockClauseAssert.java
+++ b/shardingsphere-sql-parser/shardingsphere-sql-parser-test/src/main/java/org/apache/shardingsphere/test/sql/parser/parameterized/asserts/segment/lock/LockClauseAssert.java
@@ -19,11 +19,14 @@ package org.apache.shardingsphere.test.sql.parser.parameterized.asserts.segment.
import lombok.AccessLevel;
import lombok.NoArgsConstructor;
+import org.apache.shardingsphere.sql.parser.sql.common.segment.dml.column.ColumnSegment;
import org.apache.shardingsphere.sql.parser.sql.common.segment.dml.predicate.LockSegment;
import org.apache.shardingsphere.sql.parser.sql.common.segment.generic.table.SimpleTableSegment;
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.table.TableAssert;
+import org.apache.shardingsphere.test.sql.parser.parameterized.jaxb.cases.domain.segment.impl.column.ExpectedColumn;
import org.apache.shardingsphere.test.sql.parser.parameterized.jaxb.cases.domain.segment.impl.lock.ExpectedLockClause;
import org.apache.shardingsphere.test.sql.parser.parameterized.jaxb.cases.domain.segment.impl.table.ExpectedSimpleTable;
@@ -46,11 +49,21 @@ public final class LockClauseAssert {
public static void assertIs(final SQLCaseAssertContext assertContext, final LockSegment actual, final ExpectedLockClause expected) {
SQLSegmentAssert.assertIs(assertContext, actual, expected);
List<SimpleTableSegment> actualTables = actual.getTables();
+ List<ColumnSegment> actualColumns = actual.getColumns();
List<ExpectedSimpleTable> expectedTables = expected.getTables();
+ List<ExpectedColumn> expectedColumns = expected.getColumns();
if (actualTables.isEmpty()) {
assertTrue(assertContext.getText("lock tables should not exist."), expectedTables.isEmpty());
- return;
}
TableAssert.assertIs(assertContext, actualTables, expectedTables);
+ if (actualColumns.isEmpty()) {
+ assertTrue(assertContext.getText("lock columns should not exist."), expectedColumns.isEmpty());
+ return;
+ }
+ int count = 0;
+ for (ColumnSegment each : actual.getColumns()) {
+ ColumnAssert.assertIs(assertContext, each, expected.getColumns().get(count));
+ count++;
+ }
}
}
diff --git a/shardingsphere-sql-parser/shardingsphere-sql-parser-test/src/main/java/org/apache/shardingsphere/test/sql/parser/parameterized/jaxb/cases/domain/segment/impl/lock/ExpectedLockClause.java b/shardingsphere-sql-parser/shardingsphere-sql-parser-test/src/main/java/org/apache/shardingsphere/test/sql/parser/parameterized/jaxb/cases/domain/segment/impl/lock/ExpectedLockClause.java
index 90cce97..f964d0f 100644
--- a/shardingsphere-sql-parser/shardingsphere-sql-parser-test/src/main/java/org/apache/shardingsphere/test/sql/parser/parameterized/jaxb/cases/domain/segment/impl/lock/ExpectedLockClause.java
+++ b/shardingsphere-sql-parser/shardingsphere-sql-parser-test/src/main/java/org/apache/shardingsphere/test/sql/parser/parameterized/jaxb/cases/domain/segment/impl/lock/ExpectedLockClause.java
@@ -20,6 +20,7 @@ package org.apache.shardingsphere.test.sql.parser.parameterized.jaxb.cases.domai
import lombok.Getter;
import lombok.Setter;
import org.apache.shardingsphere.test.sql.parser.parameterized.jaxb.cases.domain.segment.AbstractExpectedSQLSegment;
+import org.apache.shardingsphere.test.sql.parser.parameterized.jaxb.cases.domain.segment.impl.column.ExpectedColumn;
import org.apache.shardingsphere.test.sql.parser.parameterized.jaxb.cases.domain.segment.impl.table.ExpectedSimpleTable;
import javax.xml.bind.annotation.XmlElement;
@@ -35,4 +36,7 @@ public final class ExpectedLockClause extends AbstractExpectedSQLSegment {
@XmlElement(name = "table")
private final List<ExpectedSimpleTable> tables = new LinkedList<>();
+
+ @XmlElement(name = "column")
+ private final List<ExpectedColumn> columns = new LinkedList<>();
}
diff --git a/shardingsphere-sql-parser/shardingsphere-sql-parser-test/src/main/resources/case/dml/select-lock.xml b/shardingsphere-sql-parser/shardingsphere-sql-parser-test/src/main/resources/case/dml/select-lock.xml
index 5f4ca0c..d0fd5b3 100644
--- a/shardingsphere-sql-parser/shardingsphere-sql-parser-test/src/main/resources/case/dml/select-lock.xml
+++ b/shardingsphere-sql-parser/shardingsphere-sql-parser-test/src/main/resources/case/dml/select-lock.xml
@@ -276,4 +276,29 @@
<table name="t_user" start-index="202" stop-index="207"/>
</lock>
</select>
+
+ <select sql-case-id="select_lock_with_for_update_column">
+ <projections start-index="7" stop-index="14">
+ <column-projection name="order_id" start-index="7" stop-index="14"/>
+ </projections>
+ <from start-index="21" stop-index="27">
+ <simple-table name="t_order" start-index="21" stop-index="27"/>
+ </from>
+ <lock start-index="29" stop-index="50" >
+ <column name="order_id" start-index="43" stop-index="50"/>
+ </lock>
+ </select>
+
+ <select sql-case-id="select_lock_with_for_update_table_column">
+ <projections start-index="7" stop-index="14">
+ <column-projection name="order_id" start-index="7" stop-index="14"/>
+ </projections>
+ <from start-index="21" stop-index="27">
+ <simple-table name="t_order" start-index="21" stop-index="27"/>
+ </from>
+ <lock start-index="29" stop-index="58" >
+ <table name="t_order" start-index="43" stop-index="49"/>
+ <column name="order_id" start-index="51" stop-index="58"/>
+ </lock>
+ </select>
</sql-parser-test-cases>
diff --git a/shardingsphere-sql-parser/shardingsphere-sql-parser-test/src/main/resources/case/dml/select-order-by.xml b/shardingsphere-sql-parser/shardingsphere-sql-parser-test/src/main/resources/case/dml/select-order-by.xml
index aafeb6b..35a8f1e 100644
--- a/shardingsphere-sql-parser/shardingsphere-sql-parser-test/src/main/resources/case/dml/select-order-by.xml
+++ b/shardingsphere-sql-parser/shardingsphere-sql-parser-test/src/main/resources/case/dml/select-order-by.xml
@@ -223,6 +223,45 @@
</order-by>
</select>
+ <select sql-case-id="select_order_by_desc">
+ <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="48">
+ <expr>
+ <binary-operation-expression start-index="30" stop-index="48">
+ <left>
+ <column name="job_id" start-index="30" stop-index="35" />
+ </left>
+ <operator>=</operator>
+ <right>
+ <literal-expression value="PU_CLERK" start-index="39" stop-index="48" />
+ </right>
+ </binary-operation-expression>
+ </expr>
+ </where>
+ <order-by>
+ <column-item name="salary" order-direction="DESC" start-index="59" stop-index="64" />
+ </order-by>
+ </select>
+
+ <select sql-case-id="select_order_by_asc_desc">
+ <from>
+ <simple-table name="employees" start-index="30" stop-index="38" />
+ </from>
+ <projections start-index="7" stop-index="23">
+ <column-projection name="salary" start-index="7" stop-index="12" />
+ <column-projection name="last_name" start-index="15" stop-index="23" />
+ </projections>
+ <order-by>
+ <column-item name="salary" order-direction="ASC" start-index="49" stop-index="54" />
+ <column-item name="last_name" order-direction="DESC" start-index="61" stop-index="69" />
+ </order-by>
+ </select>
+
<select sql-case-id="select_order_by_with_alias_star_alias_name">
<from>
<simple-table name="t_order" alias="o" start-index="16" stop-index="24" />
diff --git a/shardingsphere-sql-parser/shardingsphere-sql-parser-test/src/main/resources/case/dml/select-pagination.xml b/shardingsphere-sql-parser/shardingsphere-sql-parser-test/src/main/resources/case/dml/select-pagination.xml
index c356671..dc7884d 100644
--- a/shardingsphere-sql-parser/shardingsphere-sql-parser-test/src/main/resources/case/dml/select-pagination.xml
+++ b/shardingsphere-sql-parser/shardingsphere-sql-parser-test/src/main/resources/case/dml/select-pagination.xml
@@ -1982,6 +1982,19 @@
<row-count value="20" parameter-index="0" />
</select>
+ <select sql-case-id="select_pagination_with_fetch_first_with_row_number">
+ <from>
+ <simple-table name="t_order" start-index="14" stop-index="20"/>
+ </from>
+ <projections start-index="7" stop-index="7">
+ <shorthand-projection start-index="7" stop-index="7" />
+ </projections>
+ <order-by>
+ <column-item name="order_id" start-index="31" stop-index="38" />
+ </order-by>
+ <row-count value="5" start-index="52" stop-index="52"/>
+ </select>
+
<select sql-case-id="select_pagination_with_offset_fetch" parameters="20">
<from>
<simple-table name="t_order" start-index="14" stop-index="20"/>
diff --git a/shardingsphere-sql-parser/shardingsphere-sql-parser-test/src/main/resources/sql/supported/dml/select-lock.xml b/shardingsphere-sql-parser/shardingsphere-sql-parser-test/src/main/resources/sql/supported/dml/select-lock.xml
index 24c09de..70d49d9 100644
--- a/shardingsphere-sql-parser/shardingsphere-sql-parser-test/src/main/resources/sql/supported/dml/select-lock.xml
+++ b/shardingsphere-sql-parser/shardingsphere-sql-parser-test/src/main/resources/sql/supported/dml/select-lock.xml
@@ -24,4 +24,6 @@
<sql-case id="select_lock_with_skip_locked" value="SELECT * FROM t_order WHERE order_id = ? FOR UPDATE SKIP LOCKED" db-types="MySQL"/>
<sql-case id="select_lock_with_of" value="SELECT * FROM t_order, t_order_item WHERE t_order.order_id = t_order_item.order_id AND t_order.order_id = ? FOR UPDATE OF t_order FOR SHARE OF t_order_item" db-types="MySQL"/>
<sql-case id="select_lock_with_of_multi_tables" value="SELECT * FROM t_order, t_order_item, t_user WHERE t_order.order_id = t_order_item.order_id AND t_order.user_id = t_user.user_id AND t_order.order_id = ? FOR UPDATE OF t_order, t_order_item FOR SHARE OF t_user" db-types="MySQL"/>
+ <sql-case id="select_lock_with_for_update_column" value="SELECT order_id FROM t_order FOR UPDATE OF order_id" db-types="Oracle"/>
+ <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-cases>
diff --git a/shardingsphere-sql-parser/shardingsphere-sql-parser-test/src/main/resources/sql/supported/dml/select-order-by.xml b/shardingsphere-sql-parser/shardingsphere-sql-parser-test/src/main/resources/sql/supported/dml/select-order-by.xml
index a006e9e..ce812b1 100644
--- a/shardingsphere-sql-parser/shardingsphere-sql-parser-test/src/main/resources/sql/supported/dml/select-order-by.xml
+++ b/shardingsphere-sql-parser/shardingsphere-sql-parser-test/src/main/resources/sql/supported/dml/select-order-by.xml
@@ -24,6 +24,8 @@
<sql-case id="select_order_by_for_nulls_first" value="SELECT o.order_id as gen_order_id_ FROM t_order o ORDER BY o.order_id NULLS FIRST" db-types="Oracle" />
<sql-case id="select_order_by_for_nulls_last" value="SELECT o.order_id as gen_order_id_ FROM t_order o ORDER BY o.order_id ASC NULLS LAST" db-types="Oracle" />
<sql-case id="select_order_by_with_multiple_stars" value="SELECT *, order_id, o.* FROM t_order o ORDER BY o.order_id" />
+ <sql-case id="select_order_by_desc" value="SELECT * FROM employees WHERE job_id = 'PU_CLERK' ORDER BY salary DESC" db-types="Oracle"/>
+ <sql-case id="select_order_by_asc_desc" value="SELECT salary, last_name FROM employees ORDER BY salary ASC, last_name DESC" db-types="Oracle"/>
<sql-case id="select_order_by_with_alias_star_alias_name" value="SELECT o.* FROM t_order o ORDER BY o.order_id" db-types="H2,MySQL" />
<sql-case id="select_order_by_with_star_table_alias" value="SELECT * FROM t_order o ORDER BY order_id" db-types="H2,MySQL" />
<!--TODO need to fix on visitor-->
diff --git a/shardingsphere-sql-parser/shardingsphere-sql-parser-test/src/main/resources/sql/supported/dml/select-pagination.xml b/shardingsphere-sql-parser/shardingsphere-sql-parser-test/src/main/resources/sql/supported/dml/select-pagination.xml
index 8cb56db..d2d9e6d 100644
--- a/shardingsphere-sql-parser/shardingsphere-sql-parser-test/src/main/resources/sql/supported/dml/select-pagination.xml
+++ b/shardingsphere-sql-parser/shardingsphere-sql-parser-test/src/main/resources/sql/supported/dml/select-pagination.xml
@@ -33,5 +33,6 @@
<sql-case id="select_pagination_with_row_number_for_greater_than" value="SELECT * FROM (SELECT row_.*, rownum rownum_ FROM (SELECT order0_.order_id as order_id, order0_.status as status, order0_.user_id as user_id FROM t_order order0_ JOIN t_order_item i ON order0_.user_id = i.user_id AND order0_.order_id = i.order_id WHERE order0_.user_id IN (?, ?) AND order0_.order_id BETWEEN ? AND ? ORDER BY i.item_id DESC) row_ WHERE rownum <= ?) tt WHERE tt.rownum_ > ?" db-types="Oracle" />
<sql-case id="select_pagination_with_row_number_for_greater_than_and_equal" value="SELECT * FROM (SELECT row_.*, rownum rownum_ FROM (SELECT order0_.order_id as order_id, order0_.status as status, order0_.user_id as user_id FROM t_order order0_ JOIN t_order_item i ON order0_.user_id = i.user_id AND order0_.order_id = i.order_id WHERE order0_.user_id IN (?, ?) AND order0_.order_id BETWEEN ? AND ? ORDER BY i.item_id DESC) row_ WHERE rownum <= ?) tt WHERE tt.rownum_ >= ?" db-types [...]
<sql-case id="select_pagination_with_row_number_not_at_end" value="SELECT * FROM t_order WHERE ROWNUM <= ? ORDER BY order_id" db-types="Oracle" />
+ <sql-case id="select_pagination_with_fetch_first_with_row_number" value="SELECT * FROM t_order ORDER BY order_id FETCH FIRST 5 ROWS ONLY" db-types="Oracle" />
<sql-case id="select_pagination_with_offset_fetch" value="SELECT * FROM t_order ORDER BY order_id OFFSET 0 ROW FETCH NEXT ? ROWS ONLY" db-types="SQLServer" />
</sql-cases>