This is an automated email from the ASF dual-hosted git repository.
chengzhang 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 c4bcc5da2c0 fix:Support for the LAG and LEAD analytical functions
(#35928)
c4bcc5da2c0 is described below
commit c4bcc5da2c0a6e3cde5c7c7f06b785fd15f06f3e
Author: cxy <[email protected]>
AuthorDate: Wed Jul 9 16:18:03 2025 +0800
fix:Support for the LAG and LEAD analytical functions (#35928)
* Fix：Full support for xml methods
* Fix：Full support for xml methods
* Fix：Full support for xml methods
* fix:Support for the CHANGETABLE function
* fix:Support for the CHANGETABLE function
* fix:Support for the CHANGETABLE function
* fix:Support for the AI_GENERATE_EMBEDDINGS function
* fix:Support for the LAG and LEAD analytical functions
---
.../src/main/antlr4/imports/sqlserver/BaseRule.g4 | 5 +
.../antlr4/imports/sqlserver/SQLServerKeyword.g4 | 8 ++
.../statement/SQLServerStatementVisitor.java | 33 ++++-
.../parser/src/main/resources/case/dml/select.xml | 152 +++++++++++++++++++++
.../main/resources/sql/supported/dml/select.xml | 2 +
5 files changed, 199 insertions(+), 1 deletion(-)
diff --git
a/parser/sql/dialect/sqlserver/src/main/antlr4/imports/sqlserver/BaseRule.g4
b/parser/sql/dialect/sqlserver/src/main/antlr4/imports/sqlserver/BaseRule.g4
index be5f06d959d..de09739a190 100644
--- a/parser/sql/dialect/sqlserver/src/main/antlr4/imports/sqlserver/BaseRule.g4
+++ b/parser/sql/dialect/sqlserver/src/main/antlr4/imports/sqlserver/BaseRule.g4
@@ -501,6 +501,11 @@ convertExpr
windowFunction
: funcName = (FIRST_VALUE | LAST_VALUE) LP_ expr RP_ nullTreatment?
overClause
+ | lagLeadFunction
+ ;
+
+lagLeadFunction
+ : funcName = (LAG | LEAD) LP_ expr (COMMA_ expr)? (COMMA_ expr)? RP_
nullTreatment? overClause
;
nullTreatment
diff --git
a/parser/sql/dialect/sqlserver/src/main/antlr4/imports/sqlserver/SQLServerKeyword.g4
b/parser/sql/dialect/sqlserver/src/main/antlr4/imports/sqlserver/SQLServerKeyword.g4
index 1fbd8317bf7..a648231cce6 100644
---
a/parser/sql/dialect/sqlserver/src/main/antlr4/imports/sqlserver/SQLServerKeyword.g4
+++
b/parser/sql/dialect/sqlserver/src/main/antlr4/imports/sqlserver/SQLServerKeyword.g4
@@ -2022,3 +2022,11 @@ AI_GENERATE_EMBEDDINGS
PARAMETERS
: P A R A M E T E R S
;
+
+LAG
+ : L A G
+ ;
+
+LEAD
+ : L E A D
+ ;
diff --git
a/parser/sql/dialect/sqlserver/src/main/java/org/apache/shardingsphere/sql/parser/sqlserver/visitor/statement/SQLServerStatementVisitor.java
b/parser/sql/dialect/sqlserver/src/main/java/org/apache/shardingsphere/sql/parser/sqlserver/visitor/statement/SQLServerStatementVisitor.java
index a46ff082bf4..55c36235d4f 100644
---
a/parser/sql/dialect/sqlserver/src/main/java/org/apache/shardingsphere/sql/parser/sqlserver/visitor/statement/SQLServerStatementVisitor.java
+++
b/parser/sql/dialect/sqlserver/src/main/java/org/apache/shardingsphere/sql/parser/sqlserver/visitor/statement/SQLServerStatementVisitor.java
@@ -239,6 +239,7 @@ import
org.apache.shardingsphere.sql.parser.autogen.SQLServerStatementParser.Cha
import
org.apache.shardingsphere.sql.parser.autogen.SQLServerStatementParser.XmlMethodCallContext;
import
org.apache.shardingsphere.sql.parser.autogen.SQLServerStatementParser.AiFunctionContext;
import
org.apache.shardingsphere.sql.parser.autogen.SQLServerStatementParser.AiGenerateEmbeddingsFunctionContext;
+import
org.apache.shardingsphere.sql.parser.autogen.SQLServerStatementParser.LagLeadFunctionContext;
import java.util.Collection;
import java.util.Collections;
@@ -835,8 +836,38 @@ public abstract class SQLServerStatementVisitor extends
SQLServerStatementBaseVi
@Override
public final ASTNode visitWindowFunction(final WindowFunctionContext ctx) {
+ if (null != ctx.lagLeadFunction()) {
+ return visit(ctx.lagLeadFunction());
+ }
FunctionSegment result = new
FunctionSegment(ctx.getStart().getStartIndex(), ctx.getStop().getStopIndex(),
ctx.funcName.getText(), getOriginalText(ctx));
- result.getParameters().add((ExpressionSegment) visit(ctx.expr()));
+ result.getParameters().add((ExpressionSegment) visit(ctx.getChild(2)));
+ return result;
+ }
+
+ @Override
+ public final ASTNode visitLagLeadFunction(final LagLeadFunctionContext
ctx) {
+ FunctionSegment result = new
FunctionSegment(ctx.getStart().getStartIndex(), ctx.getStop().getStopIndex(),
ctx.funcName.getText(), getOriginalText(ctx));
+ Collection<ExpressionSegment> parameters =
getLagLeadFunctionParameters(ctx);
+ result.getParameters().addAll(parameters);
+ return result;
+ }
+
+ private Collection<ExpressionSegment> getLagLeadFunctionParameters(final
LagLeadFunctionContext ctx) {
+ Collection<ExpressionSegment> result = new LinkedList<>();
+ boolean foundLP = false;
+ for (int i = 0; i < ctx.getChildCount(); i++) {
+ String childText = ctx.getChild(i).getText();
+ if ("(".equals(childText)) {
+ foundLP = true;
+ continue;
+ }
+ if (")".equals(childText) && foundLP) {
+ break;
+ }
+ if (foundLP && ctx.getChild(i) instanceof ExprContext) {
+ result.add((ExpressionSegment) visit(ctx.getChild(i)));
+ }
+ }
return result;
}
diff --git a/test/it/parser/src/main/resources/case/dml/select.xml
b/test/it/parser/src/main/resources/case/dml/select.xml
index bec6138a59e..873a6c6531c 100644
--- a/test/it/parser/src/main/resources/case/dml/select.xml
+++ b/test/it/parser/src/main/resources/case/dml/select.xml
@@ -10396,4 +10396,156 @@
<simple-table name="myTable" start-index="80" stop-index="86" />
</from>
</select>
+
+ <select sql-case-id="select_lag_over">
+ <projections start-index="7" stop-index="151">
+ <column-projection name="BusinessEntityID" start-index="7"
stop-index="22" />
+ <expression-projection text="YEAR(QuotaDate)" alias="SalesYear"
start-index="25" stop-index="52">
+ <expr>
+ <function function-name="YEAR" text="YEAR(QuotaDate)"
start-index="25" stop-index="39">
+ <parameter>
+ <column name="QuotaDate" start-index="30"
stop-index="38" />
+ </parameter>
+ </function>
+ </expr>
+ </expression-projection>
+ <column-projection name="SalesQuota" alias="CurrentQuota"
start-index="55" stop-index="80" />
+ <expression-projection text="LAG(SalesQuota, 1,0) OVER (ORDER BY
YEAR(QuotaDate))" alias="PreviousQuota" start-index="83" stop-index="151">
+ <expr>
+ <function function-name="LAG" text="LAG(SalesQuota, 1,0)
OVER (ORDER BY YEAR(QuotaDate))" start-index="83" stop-index="134">
+ <parameter>
+ <column name="SalesQuota" start-index="87"
stop-index="96" />
+ </parameter>
+ <parameter>
+ <literal-expression value="1" start-index="99"
stop-index="99" />
+ </parameter>
+ <parameter>
+ <literal-expression value="0" start-index="101"
stop-index="101" />
+ </parameter>
+ </function>
+ </expr>
+ </expression-projection>
+ </projections>
+ <from>
+ <simple-table name="SalesPersonQuotaHistory" start-index="158"
stop-index="186">
+ <owner name="Sales" start-index="158" stop-index="162" />
+ </simple-table>
+ </from>
+ <where start-index="188" stop-index="254">
+ <expr start-index="194" stop-index="254">
+ <binary-operation-expression start-index="194"
stop-index="254">
+ <left>
+ <binary-operation-expression start-index="194"
stop-index="215">
+ <left>
+ <column name="BusinessEntityID"
start-index="194" stop-index="209" />
+ </left>
+ <right>
+ <literal-expression value="275"
start-index="213" stop-index="215" />
+ </right>
+ <operator>=</operator>
+ </binary-operation-expression>
+ </left>
+ <right>
+ <in-expression start-index="221" stop-index="254">
+ <not>false</not>
+ <left>
+ <function function-name="YEAR"
text="YEAR(QuotaDate)" start-index="221" stop-index="235">
+ <parameter>
+ <column name="QuotaDate"
start-index="226" stop-index="234" />
+ </parameter>
+ </function>
+ </left>
+ <right>
+ <list-expression start-index="240"
stop-index="254">
+ <items>
+ <literal-expression value="2005"
start-index="241" stop-index="246" />
+ </items>
+ <items>
+ <literal-expression value="2006"
start-index="248" stop-index="253" />
+ </items>
+ </list-expression>
+ </right>
+ </in-expression>
+ </right>
+ <operator>AND</operator>
+ </binary-operation-expression>
+ </expr>
+ </where>
+ </select>
+
+ <select sql-case-id="select_lead_over">
+ <projections start-index="7" stop-index="148">
+ <column-projection name="BusinessEntityID" start-index="7"
stop-index="22" />
+ <expression-projection text="YEAR(QuotaDate)" alias="SalesYear"
start-index="25" stop-index="52">
+ <expr>
+ <function function-name="YEAR" text="YEAR(QuotaDate)"
start-index="25" stop-index="39">
+ <parameter>
+ <column name="QuotaDate" start-index="30"
stop-index="38" />
+ </parameter>
+ </function>
+ </expr>
+ </expression-projection>
+ <column-projection name="SalesQuota" alias="CurrentQuota"
start-index="55" stop-index="80" />
+ <expression-projection text="LEAD(SalesQuota, 1,0) OVER (ORDER BY
YEAR(QuotaDate))" alias="NextQuota" start-index="83" stop-index="148">
+ <expr>
+ <function function-name="LEAD" text="LEAD(SalesQuota, 1,0)
OVER (ORDER BY YEAR(QuotaDate))" start-index="83" stop-index="135">
+ <parameter>
+ <column name="SalesQuota" start-index="88"
stop-index="97" />
+ </parameter>
+ <parameter>
+ <literal-expression value="1" start-index="100"
stop-index="100" />
+ </parameter>
+ <parameter>
+ <literal-expression value="0" start-index="102"
stop-index="102" />
+ </parameter>
+ </function>
+ </expr>
+ </expression-projection>
+ </projections>
+ <from>
+ <simple-table name="SalesPersonQuotaHistory" start-index="155"
stop-index="183">
+ <owner name="Sales" start-index="155" stop-index="159" />
+ </simple-table>
+ </from>
+ <where start-index="185" stop-index="251">
+ <expr start-index="191" stop-index="251">
+ <binary-operation-expression start-index="191"
stop-index="251">
+ <left>
+ <binary-operation-expression start-index="191"
stop-index="212">
+ <left>
+ <column name="BusinessEntityID"
start-index="191" stop-index="206" />
+ </left>
+ <right>
+ <literal-expression value="275"
start-index="210" stop-index="212" />
+ </right>
+ <operator>=</operator>
+ </binary-operation-expression>
+ </left>
+ <right>
+ <in-expression start-index="218" stop-index="251">
+ <not>false</not>
+ <left>
+ <function function-name="YEAR"
text="YEAR(QuotaDate)" start-index="218" stop-index="232">
+ <parameter>
+ <column name="QuotaDate"
start-index="223" stop-index="231" />
+ </parameter>
+ </function>
+ </left>
+ <right>
+ <list-expression start-index="237"
stop-index="251">
+ <items>
+ <literal-expression value="2005"
start-index="238" stop-index="243" />
+ </items>
+ <items>
+ <literal-expression value="2006"
start-index="245" stop-index="250" />
+ </items>
+ </list-expression>
+ </right>
+ </in-expression>
+ </right>
+ <operator>AND</operator>
+ </binary-operation-expression>
+ </expr>
+ </where>
+ </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 1d4ba111223..b2c00f4267a 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
@@ -325,4 +325,6 @@
<sql-case id="select_instruction_nodes" value="SELECT T2.Loc.query('.')
FROM T CROSS APPLY Instructions.nodes('/root/Location') AS T2(Loc) "
db-types="SQLServer"/>
<sql-case id="select_cross_apply_changetable_version" value="SELECT e.[Emp
ID], e.SSN, e.FirstName, e.LastName, c.SYS_CHANGE_VERSION, c.SYS_CHANGE_CONTEXT
FROM Employees AS e CROSS APPLY CHANGETABLE (VERSION Employees, ([Emp ID],
SSN), (e.[Emp ID], e.SSN)) AS c;" db-types="SQLServer"/>
<sql-case id="select_ai_generate_embeddings" value="SELECT id,
AI_GENERATE_EMBEDDINGS(large_text USE MODEL MyAzureOpenAIModel) FROM myTable;"
db-types="SQLServer"/>
+ <sql-case id="select_lag_over" value="SELECT BusinessEntityID,
YEAR(QuotaDate) AS SalesYear, SalesQuota AS CurrentQuota, LAG(SalesQuota, 1,0)
OVER (ORDER BY YEAR(QuotaDate)) AS PreviousQuota FROM
Sales.SalesPersonQuotaHistory WHERE BusinessEntityID = 275 AND YEAR(QuotaDate)
IN ('2005','2006'); " db-types="SQLServer"/>
+ <sql-case id="select_lead_over" value="SELECT BusinessEntityID,
YEAR(QuotaDate) AS SalesYear, SalesQuota AS CurrentQuota, LEAD(SalesQuota, 1,0)
OVER (ORDER BY YEAR(QuotaDate)) AS NextQuota FROM Sales.SalesPersonQuotaHistory
WHERE BusinessEntityID = 275 AND YEAR(QuotaDate) IN ('2005','2006'); "
db-types="SQLServer"/>
</sql-cases>
