This is an automated email from the ASF dual-hosted git repository.
duanzhengqiang pushed a commit to branch master
in repository https://gitbox.apache.org/repos/asf/shardingsphere.git
The following commit(s) were added to refs/heads/master by this push:
new 3e96a79d518 Support oracle LEAD and LAG function parsing (#27351)
3e96a79d518 is described below
commit 3e96a79d51859a015050576a249880cf591c26cc
Author: ZhangCheng <[email protected]>
AuthorDate: Fri Jul 21 15:45:22 2023 +0800
Support oracle LEAD and LAG function parsing (#27351)
* Support oracle LEAD and LAG function parsing
* Support oracle LEAD and LAG function parsing
---
.../src/main/antlr4/imports/oracle/BaseRule.g4 | 9 ++++--
.../visitor/statement/OracleStatementVisitor.java | 3 +-
.../resources/case/dml/select-special-function.xml | 35 ++++++++++++++++++++++
.../main/resources/sql/supported/dml/select.xml | 1 +
4 files changed, 45 insertions(+), 3 deletions(-)
diff --git
a/parser/sql/dialect/oracle/src/main/antlr4/imports/oracle/BaseRule.g4
b/parser/sql/dialect/oracle/src/main/antlr4/imports/oracle/BaseRule.g4
index 38fd1bc02c1..5411814275a 100644
--- a/parser/sql/dialect/oracle/src/main/antlr4/imports/oracle/BaseRule.g4
+++ b/parser/sql/dialect/oracle/src/main/antlr4/imports/oracle/BaseRule.g4
@@ -684,7 +684,7 @@ functionCall
;
aggregationFunction
- : aggregationFunctionName LP_ (((DISTINCT | ALL)? expr (COMMA_ expr)*) |
ASTERISK_) (COMMA_ stringLiterals)? listaggOverflowClause? RP_ (WITHIN GROUP
LP_ orderByClause RP_)? (OVER LP_ analyticClause RP_)? (OVER LP_ analyticClause
RP_)?
+ : aggregationFunctionName LP_ (((DISTINCT | ALL)? expr (COMMA_ expr)*) |
ASTERISK_) (COMMA_ stringLiterals)? listaggOverflowClause? RP_ (WITHIN GROUP
LP_ orderByClause RP_)? overClause? overClause?
;
aggregationFunctionName
@@ -710,7 +710,12 @@ windowingClause
;
analyticFunction
- : analyticFunctionName LP_ dataType* RP_ OVER LP_ analyticClause RP_
+ : specifiedAnalyticFunctionName = (LEAD | LAG) ((LP_ expr leadLagInfo? RP_
respectOrIgnoreNulls?) | (LP_ expr respectOrIgnoreNulls? leadLagInfo? RP_))
overClause
+ | analyticFunctionName LP_ dataType* RP_ overClause
+ ;
+
+leadLagInfo
+ : COMMA_ expr (COMMA_ expr)?
;
specialFunction
diff --git
a/parser/sql/dialect/oracle/src/main/java/org/apache/shardingsphere/sql/parser/oracle/visitor/statement/OracleStatementVisitor.java
b/parser/sql/dialect/oracle/src/main/java/org/apache/shardingsphere/sql/parser/oracle/visitor/statement/OracleStatementVisitor.java
index 9ee6e6c8925..fcf0d798f1d 100644
---
a/parser/sql/dialect/oracle/src/main/java/org/apache/shardingsphere/sql/parser/oracle/visitor/statement/OracleStatementVisitor.java
+++
b/parser/sql/dialect/oracle/src/main/java/org/apache/shardingsphere/sql/parser/oracle/visitor/statement/OracleStatementVisitor.java
@@ -531,7 +531,8 @@ public abstract class OracleStatementVisitor extends
OracleStatementBaseVisitor<
@Override
public ASTNode visitAnalyticFunction(final AnalyticFunctionContext ctx) {
- FunctionSegment result = new
FunctionSegment(ctx.getStart().getStartIndex(), ctx.getStop().getStopIndex(),
ctx.analyticFunctionName().getText(), getOriginalText(ctx));
+ String functionName = null == ctx.analyticFunctionName() ?
ctx.specifiedAnalyticFunctionName.getText() :
ctx.analyticFunctionName().getText();
+ FunctionSegment result = new
FunctionSegment(ctx.getStart().getStartIndex(), ctx.getStop().getStopIndex(),
functionName, getOriginalText(ctx));
for (DataTypeContext each : ctx.dataType()) {
result.getParameters().add((DataTypeSegment) visit(each));
}
diff --git
a/test/it/parser/src/main/resources/case/dml/select-special-function.xml
b/test/it/parser/src/main/resources/case/dml/select-special-function.xml
index 6c92baf4930..3a8f960ca10 100644
--- a/test/it/parser/src/main/resources/case/dml/select-special-function.xml
+++ b/test/it/parser/src/main/resources/case/dml/select-special-function.xml
@@ -391,4 +391,39 @@
<simple-table name="t_order" start-index="29" stop-index="35" />
</from>
</select>
+
+ <select sql-case-id="select_with_lead_and_lag_function">
+ <projections start-index="7" stop-index="123" literal-start-index="7"
literal-stop-index="123">
+ <column-projection name="hire_date" start-index="7"
stop-index="15" literal-start-index="7" literal-stop-index="15" />
+ <expression-projection text="LAG(hire_date, 1) OVER (ORDER BY
hire_date)" alias="LAG1" start-index="18" stop-index="68"
literal-start-index="18" literal-stop-index="68">
+ <expr>
+ <function function-name="LAG" text="LAG(hire_date, 1) OVER
(ORDER BY hire_date)" start-index="18" stop-index="60" literal-start-index="18"
literal-stop-index="60" />
+ </expr>
+ </expression-projection>
+ <expression-projection text="LEAD(hire_date, 1) OVER (ORDER BY
hire_date)" literal-text="LEAD(hire_date, 1) OVER (ORDER BY hire_date)"
alias="LEAD1" start-index="71" stop-index="123" literal-start-index="71"
literal-stop-index="123">
+ <expr>
+ <function function-name="LEAD" text="LEAD(hire_date, 1)
OVER (ORDER BY hire_date)" literal-text="LEAD(hire_date, 1) OVER (ORDER BY
hire_date)" start-index="71" stop-index="114" literal-start-index="71"
literal-stop-index="114" />
+ </expr>
+ </expression-projection>
+ </projections>
+ <from>
+ <simple-table name="employees" start-index="130" stop-index="138"
literal-start-index="130" literal-stop-index="138" />
+ </from>
+ <where start-index="140" stop-index="163" literal-start-index="140"
literal-stop-index="163">
+ <expr>
+ <binary-operation-expression start-index="146"
stop-index="163" literal-start-index="146" literal-stop-index="163">
+ <left>
+ <column name="department_id" start-index="146"
stop-index="158" literal-start-index="146" literal-stop-index="158" />
+ </left>
+ <operator>=</operator>
+ <right>
+ <literal-expression value="30" start-index="162"
stop-index="163" literal-start-index="162" literal-stop-index="163" />
+ </right>
+ </binary-operation-expression>
+ </expr>
+ </where>
+ <order-by>
+ <column-item name="hire_date" order-direction="ASC"
start-index="174" stop-index="182" literal-start-index="174"
literal-stop-index="182" />
+ </order-by>
+ </select>
</sql-parser-test-cases>
diff --git a/test/it/parser/src/main/resources/sql/supported/dml/select.xml
b/test/it/parser/src/main/resources/sql/supported/dml/select.xml
index 1a93e8e5e34..394896f2b1d 100644
--- a/test/it/parser/src/main/resources/sql/supported/dml/select.xml
+++ b/test/it/parser/src/main/resources/sql/supported/dml/select.xml
@@ -187,4 +187,5 @@
<sql-case id="select_with_format_function" value="SELECT
wi.code.format(null,'PURE_IDENTITY') as PURE_IDENTITY FROM warehouse_info wi;"
db-types="Oracle" />
<sql-case id="select_with_xml_is_schema_valid_function" value="SELECT
x.xmlcol.isSchemaValid('http://www.example.com/schemas/ipo.xsd','purchaseOrder')
FROM po_tab x;" db-types="Oracle" />
<sql-case id="select_with_last_value_function" value="SELECT
LAST_VALUE(AGE IGNORE NULLS) OVER (PARTITION BY AGE ORDER BY AGE) from TEST;"
db-types="Oracle" />
+ <sql-case id="select_with_lead_and_lag_function" value="SELECT hire_date,
LAG(hire_date, 1) OVER (ORDER BY hire_date) AS LAG1, LEAD(hire_date, 1) OVER
(ORDER BY hire_date) AS LEAD1 FROM employees WHERE department_id = 30 ORDER BY
hire_date;" db-types="Oracle" />
</sql-cases>
