You are viewing a plain text version of this content. The canonical link for it is here.
Posted to notifications@shardingsphere.apache.org by ji...@apache.org on 2022/11/16 08:45:48 UTC
[shardingsphere] branch master updated: Update Oracle DML SLELECT DatetimeExpression statement parse (#22201)
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 35a97dd82bd Update Oracle DML SLELECT DatetimeExpression statement parse (#22201)
35a97dd82bd is described below
commit 35a97dd82bdad130379f7aa570e3baa3b7347942
Author: Zichao <57...@users.noreply.github.com>
AuthorDate: Wed Nov 16 21:45:40 2022 +1300
Update Oracle DML SLELECT DatetimeExpression statement parse (#22201)
* Update Oracle DML SLELECT DatetimeExpression statement parse
* Update Oracle DML SLELECT DatetimeExpression statement parse
* Update Oracle DML SLELECT DatetimeExpression statement parse
* Update Oracle DML SLELECT DatetimeExpression statement parse
* Update Oracle DML SLELECT DatetimeExpression statement parse
---
.../src/main/antlr4/imports/oracle/BaseRule.g4 | 6 ++-
.../src/main/antlr4/imports/oracle/Keyword.g4 | 20 ++++++++
.../impl/OracleDMLStatementSQLVisitor.java | 10 ++++
.../statement/impl/OracleStatementSQLVisitor.java | 16 ++++++
.../segment/dml/expr/DatetimeExpression.java | 58 ++++++++++++++++++++++
.../dml/item/DatetimeProjectionSegment.java | 58 ++++++++++++++++++++++
.../main/resources/case/dml/select-expression.xml | 12 ++++-
.../sql/supported/dml/select-expression.xml | 1 +
8 files changed, 179 insertions(+), 2 deletions(-)
diff --git a/sql-parser/dialect/oracle/src/main/antlr4/imports/oracle/BaseRule.g4 b/sql-parser/dialect/oracle/src/main/antlr4/imports/oracle/BaseRule.g4
index 2837a2d81fe..016f9302258 100644
--- a/sql-parser/dialect/oracle/src/main/antlr4/imports/oracle/BaseRule.g4
+++ b/sql-parser/dialect/oracle/src/main/antlr4/imports/oracle/BaseRule.g4
@@ -163,7 +163,7 @@ unreservedWord
| HOST | PORT | EVERY | MINUTES | HOURS | NORELOCATE | SAVE | DISCARD | APPLICATION | INSTALL
| MINIMUM | VERSION | UNINSTALL | COMPATIBILITY | MATERIALIZE | SUBTYPE | RECORD | CONSTANT | CURSOR
| OTHERS | EXCEPTION | CPU_PER_SESSION | CONNECT_TIME | LOGICAL_READS_PER_SESSION | PRIVATE_SGA | PERCENT_RANK | ROWID
- | LPAD
+ | LPAD | ZONE | SESSIONTIMEZONE
;
schemaName
@@ -444,6 +444,7 @@ expr
| booleanPrimary
| aggregationFunction
| analyticFunction
+ | expr datetimeExpr
;
andOperator
@@ -1609,3 +1610,6 @@ maxNumberOfSnapshots
: INTEGER_
;
+datetimeExpr
+ : AT (LOCAL | TIME ZONE expr)
+ ;
diff --git a/sql-parser/dialect/oracle/src/main/antlr4/imports/oracle/Keyword.g4 b/sql-parser/dialect/oracle/src/main/antlr4/imports/oracle/Keyword.g4
index cf27cd895c8..178166ec443 100644
--- a/sql-parser/dialect/oracle/src/main/antlr4/imports/oracle/Keyword.g4
+++ b/sql-parser/dialect/oracle/src/main/antlr4/imports/oracle/Keyword.g4
@@ -643,3 +643,23 @@ REGR_SXY
LPAD
: L P A D
;
+
+ZONE
+ : Z O N E
+ ;
+
+AT
+ : A T
+ ;
+
+SESSIONTIMEZONE
+ : S E S S I O N T I M E Z O N E
+ ;
+
+SYSTIMESTAMP
+ : S Y S T I M E S T A M P
+ ;
+
+DBTIMEZONE
+ : D B T I M E Z O N E
+ ;
diff --git a/sql-parser/dialect/oracle/src/main/java/org/apache/shardingsphere/sql/parser/oracle/visitor/statement/impl/OracleDMLStatementSQLVisitor.java b/sql-parser/dialect/oracle/src/main/java/org/apache/shardingsphere/sql/parser/oracle/visitor/statement/impl/OracleDMLStatementSQLVisitor.java
index 55ac57d85fa..13ea408e76f 100644
--- a/sql-parser/dialect/oracle/src/main/java/org/apache/shardingsphere/sql/parser/oracle/visitor/statement/impl/OracleDMLStatementSQLVisitor.java
+++ b/sql-parser/dialect/oracle/src/main/java/org/apache/shardingsphere/sql/parser/oracle/visitor/statement/impl/OracleDMLStatementSQLVisitor.java
@@ -112,6 +112,7 @@ import org.apache.shardingsphere.sql.parser.sql.common.segment.dml.assignment.Se
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.expr.BinaryOperationExpression;
+import org.apache.shardingsphere.sql.parser.sql.common.segment.dml.expr.DatetimeExpression;
import org.apache.shardingsphere.sql.parser.sql.common.segment.dml.expr.ExpressionSegment;
import org.apache.shardingsphere.sql.parser.sql.common.segment.dml.expr.FunctionSegment;
import org.apache.shardingsphere.sql.parser.sql.common.segment.dml.expr.complex.CommonExpressionSegment;
@@ -126,6 +127,7 @@ import org.apache.shardingsphere.sql.parser.sql.common.segment.dml.item.Projecti
import org.apache.shardingsphere.sql.parser.sql.common.segment.dml.item.ProjectionsSegment;
import org.apache.shardingsphere.sql.parser.sql.common.segment.dml.item.ShorthandProjectionSegment;
import org.apache.shardingsphere.sql.parser.sql.common.segment.dml.item.SubqueryProjectionSegment;
+import org.apache.shardingsphere.sql.parser.sql.common.segment.dml.item.DatetimeProjectionSegment;
import org.apache.shardingsphere.sql.parser.sql.common.segment.dml.order.GroupBySegment;
import org.apache.shardingsphere.sql.parser.sql.common.segment.dml.order.OrderBySegment;
import org.apache.shardingsphere.sql.parser.sql.common.segment.dml.order.item.ColumnOrderByItemSegment;
@@ -730,6 +732,14 @@ public final class OracleDMLStatementSQLVisitor extends OracleStatementSQLVisito
result.setAlias(alias);
return result;
}
+ if (projection instanceof DatetimeExpression) {
+ DatetimeExpression datetimeExpression = (DatetimeExpression) projection;
+ if (null != datetimeExpression.getRight()) {
+ return new DatetimeProjectionSegment(datetimeExpression.getStartIndex(), datetimeExpression.getStopIndex(), datetimeExpression.getLeft(), datetimeExpression.getRight(),
+ datetimeExpression.getText());
+ }
+ return new DatetimeProjectionSegment(datetimeExpression.getStartIndex(), datetimeExpression.getStopIndex(), datetimeExpression.getLeft(), datetimeExpression.getText());
+ }
LiteralExpressionSegment column = (LiteralExpressionSegment) projection;
ExpressionProjectionSegment result = null == alias ? new ExpressionProjectionSegment(column.getStartIndex(), column.getStopIndex(), String.valueOf(column.getLiterals()), column)
: new ExpressionProjectionSegment(column.getStartIndex(), ctx.alias().stop.getStopIndex(), String.valueOf(column.getLiterals()), column);
diff --git a/sql-parser/dialect/oracle/src/main/java/org/apache/shardingsphere/sql/parser/oracle/visitor/statement/impl/OracleStatementSQLVisitor.java b/sql-parser/dialect/oracle/src/main/java/org/apache/shardingsphere/sql/parser/oracle/visitor/statement/impl/OracleStatementSQLVisitor.java
index 2768051943a..9ea2221bfd0 100644
--- a/sql-parser/dialect/oracle/src/main/java/org/apache/shardingsphere/sql/parser/oracle/visitor/statement/impl/OracleStatementSQLVisitor.java
+++ b/sql-parser/dialect/oracle/src/main/java/org/apache/shardingsphere/sql/parser/oracle/visitor/statement/impl/OracleStatementSQLVisitor.java
@@ -67,6 +67,7 @@ import org.apache.shardingsphere.sql.parser.autogen.OracleStatementParser.TableN
import org.apache.shardingsphere.sql.parser.autogen.OracleStatementParser.TypeNameContext;
import org.apache.shardingsphere.sql.parser.autogen.OracleStatementParser.UnreservedWordContext;
import org.apache.shardingsphere.sql.parser.autogen.OracleStatementParser.ViewNameContext;
+import org.apache.shardingsphere.sql.parser.autogen.OracleStatementParser.DatetimeExprContext;
import org.apache.shardingsphere.sql.parser.sql.common.constant.AggregationType;
import org.apache.shardingsphere.sql.parser.sql.common.constant.OrderDirection;
import org.apache.shardingsphere.sql.parser.sql.common.constant.ParameterMarkerType;
@@ -84,6 +85,7 @@ import org.apache.shardingsphere.sql.parser.sql.common.segment.dml.expr.Function
import org.apache.shardingsphere.sql.parser.sql.common.segment.dml.expr.InExpression;
import org.apache.shardingsphere.sql.parser.sql.common.segment.dml.expr.ListExpression;
import org.apache.shardingsphere.sql.parser.sql.common.segment.dml.expr.NotExpression;
+import org.apache.shardingsphere.sql.parser.sql.common.segment.dml.expr.DatetimeExpression;
import org.apache.shardingsphere.sql.parser.sql.common.segment.dml.expr.complex.CommonExpressionSegment;
import org.apache.shardingsphere.sql.parser.sql.common.segment.dml.expr.simple.LiteralExpressionSegment;
import org.apache.shardingsphere.sql.parser.sql.common.segment.dml.expr.simple.ParameterMarkerExpressionSegment;
@@ -312,9 +314,23 @@ public abstract class OracleStatementSQLVisitor extends OracleStatementBaseVisit
if (null != ctx.orOperator()) {
return createBinaryOperationExpression(ctx, ctx.orOperator().getText());
}
+ if (null != ctx.datetimeExpr()) {
+ return createDatetimeExpression(ctx, ctx.datetimeExpr());
+ }
return new NotExpression(ctx.start.getStartIndex(), ctx.stop.getStopIndex(), (ExpressionSegment) visit(ctx.expr(0)));
}
+ private ASTNode createDatetimeExpression(final ExprContext ctx, final DatetimeExprContext datetimeExpr) {
+ ExpressionSegment left = (ExpressionSegment) visit(ctx.expr(0));
+ String text = ctx.start.getInputStream().getText(new Interval(ctx.start.getStartIndex(), ctx.stop.getStopIndex()));
+ if (null != datetimeExpr.expr()) {
+ ExpressionSegment right = new ExpressionProjectionSegment(datetimeExpr.getStart().getStartIndex(),
+ datetimeExpr.getStop().getStopIndex(), datetimeExpr.getText(), (ExpressionSegment) visit(datetimeExpr.expr()));
+ return new DatetimeExpression(ctx.start.getStartIndex(), ctx.stop.getStopIndex(), left, right, text);
+ }
+ return new DatetimeExpression(ctx.start.getStartIndex(), ctx.stop.getStopIndex(), left, text);
+ }
+
private ASTNode createBinaryOperationExpression(final ExprContext ctx, final String operator) {
ExpressionSegment left = (ExpressionSegment) visit(ctx.expr(0));
ExpressionSegment right = (ExpressionSegment) visit(ctx.expr(1));
diff --git a/sql-parser/statement/src/main/java/org/apache/shardingsphere/sql/parser/sql/common/segment/dml/expr/DatetimeExpression.java b/sql-parser/statement/src/main/java/org/apache/shardingsphere/sql/parser/sql/common/segment/dml/expr/DatetimeExpression.java
new file mode 100644
index 00000000000..de9a3420526
--- /dev/null
+++ b/sql-parser/statement/src/main/java/org/apache/shardingsphere/sql/parser/sql/common/segment/dml/expr/DatetimeExpression.java
@@ -0,0 +1,58 @@
+/*
+ * 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.
+ */
+
+package org.apache.shardingsphere.sql.parser.sql.common.segment.dml.expr;
+
+import lombok.Getter;
+import lombok.Setter;
+import lombok.ToString;
+
+/**
+ * Datetime expression.
+ */
+
+@Getter
+@Setter
+@ToString
+public final class DatetimeExpression implements ExpressionSegment {
+
+ private final int startIndex;
+
+ private final int stopIndex;
+
+ private final ExpressionSegment left;
+
+ private final ExpressionSegment right;
+
+ private final String text;
+
+ public DatetimeExpression(final int startIndex, final int stopIndex, final ExpressionSegment left, final ExpressionSegment right, final String text) {
+ this.startIndex = startIndex;
+ this.stopIndex = stopIndex;
+ this.left = left;
+ this.right = right;
+ this.text = text;
+ }
+
+ public DatetimeExpression(final int startIndex, final int stopIndex, final ExpressionSegment left, final String text) {
+ this.startIndex = startIndex;
+ this.stopIndex = stopIndex;
+ this.left = left;
+ this.right = null;
+ this.text = text;
+ }
+}
diff --git a/sql-parser/statement/src/main/java/org/apache/shardingsphere/sql/parser/sql/common/segment/dml/item/DatetimeProjectionSegment.java b/sql-parser/statement/src/main/java/org/apache/shardingsphere/sql/parser/sql/common/segment/dml/item/DatetimeProjectionSegment.java
new file mode 100644
index 00000000000..246dc39b16c
--- /dev/null
+++ b/sql-parser/statement/src/main/java/org/apache/shardingsphere/sql/parser/sql/common/segment/dml/item/DatetimeProjectionSegment.java
@@ -0,0 +1,58 @@
+/*
+ * 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.
+ */
+
+package org.apache.shardingsphere.sql.parser.sql.common.segment.dml.item;
+
+import lombok.Getter;
+import lombok.Setter;
+import lombok.ToString;
+import org.apache.shardingsphere.sql.parser.sql.common.segment.dml.expr.ExpressionSegment;
+
+/**
+ * Datetime projection segment.
+ */
+@Getter
+@Setter
+@ToString
+public final class DatetimeProjectionSegment implements ProjectionSegment {
+
+ private final int startIndex;
+
+ private final int stopIndex;
+
+ private final ExpressionSegment left;
+
+ private final ExpressionSegment right;
+
+ private final String text;
+
+ public DatetimeProjectionSegment(final int startIndex, final int stopIndex, final ExpressionSegment left, final ExpressionSegment right, final String text) {
+ this.startIndex = startIndex;
+ this.stopIndex = stopIndex;
+ this.left = left;
+ this.right = right;
+ this.text = text;
+ }
+
+ public DatetimeProjectionSegment(final int startIndex, final int stopIndex, final ExpressionSegment left, final String text) {
+ this.startIndex = startIndex;
+ this.stopIndex = stopIndex;
+ this.left = left;
+ this.right = null;
+ this.text = text;
+ }
+}
diff --git a/test/parser/src/main/resources/case/dml/select-expression.xml b/test/parser/src/main/resources/case/dml/select-expression.xml
index 4b7300d2d11..14065b95bf7 100644
--- a/test/parser/src/main/resources/case/dml/select-expression.xml
+++ b/test/parser/src/main/resources/case/dml/select-expression.xml
@@ -2084,7 +2084,8 @@
</expr>
</where>
</select>
- <select sql-case-id="select_age_for_postgres" >
+
+ <select sql-case-id="select_age_for_postgres">
<projections start-index="7" stop-index="7">
<shorthand-projection start-index="7" stop-index="7" />
</projections>
@@ -2092,4 +2093,13 @@
<simple-table name="not support" start-index="14" stop-index="70" />
</from>
</select>
+
+ <select sql-case-id="select_datetime_expression">
+ <projections start-index="7" stop-index="37">
+ <expression-projection text="SYSTIMESTAMP AT TIME ZONE 'UTC'" start-index="7" stop-index="37" />
+ </projections>
+ <from>
+ <simple-table name="DUAL" start-index="44" stop-index="47" />
+ </from>
+ </select>
</sql-parser-test-cases>
diff --git a/test/parser/src/main/resources/sql/supported/dml/select-expression.xml b/test/parser/src/main/resources/sql/supported/dml/select-expression.xml
index 5690e34774f..e77883e3d5b 100644
--- a/test/parser/src/main/resources/sql/supported/dml/select-expression.xml
+++ b/test/parser/src/main/resources/sql/supported/dml/select-expression.xml
@@ -91,4 +91,5 @@
<sql-case id="select_with_regular_function_utc_timestamp" value="SELECT TIMEDIFF(NOW(), UTC_TIMESTAMP())" db-types="MySQL" />
<sql-case id="select_with_collate_with_marker" value="SELECT * FROM t_order WHERE order_id COLLATE ?" db-types="MySQL" />
<sql-case id="select_age_for_postgres" value="SELECT * FROM cypher('sharding_test_1', $$ CREATE (n) $$) as (a agtype)" db-types="PostgreSQL" />
+ <sql-case id="select_datetime_expression" value="SELECT SYSTIMESTAMP AT TIME ZONE 'UTC' FROM DUAL;" db-types="Oracle" />
</sql-cases>