You are viewing a plain text version of this content. The canonical link for it is here.
Posted to notifications@shardingsphere.apache.org by du...@apache.org on 2024/01/29 10:47:06 UTC
(shardingsphere) branch master updated: Support parsing SQL Server SELECT ProductId, sql #29159 (#29868)
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 7e5fbcaf365 Support parsing SQL Server SELECT ProductId, sql #29159 (#29868)
7e5fbcaf365 is described below
commit 7e5fbcaf365b4cfdd487ba53eb5a894c32125216
Author: yydeng626 <yy...@gmail.com>
AuthorDate: Mon Jan 29 04:47:00 2024 -0600
Support parsing SQL Server SELECT ProductId, sql #29159 (#29868)
* Support parsing SQL Server SELECT ProductId, sql #29159
* fix code style
---
.../src/main/antlr4/imports/sqlserver/BaseRule.g4 | 16 +--
.../src/main/antlr4/imports/sqlserver/Keyword.g4 | 8 ++
.../statement/SQLServerStatementVisitor.java | 79 ++++++++-------
.../parser/src/main/resources/case/dml/insert.xml | 14 +++
.../resources/case/dml/select-special-function.xml | 108 +++++++++++++++++++++
.../main/resources/sql/supported/dml/insert.xml | 1 +
.../sql/supported/dml/select-special-function.xml | 1 +
7 files changed, 188 insertions(+), 39 deletions(-)
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 eab5ea28c2e..c1e4f38083b 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
@@ -120,7 +120,7 @@ unreservedWord
| DATA_RETENTION | TEMPORAL_HISTORY_RETENTION | EDITION | MIXED_PAGE_ALLOCATION | DISABLED | ALLOWED | HADR | MULTI_USER | RESTRICTED_USER | SINGLE_USER | OFFLINE | EMERGENCY | SUSPEND | DATE_CORRELATION_OPTIMIZATION
| ELASTIC_POOL | SERVICE_OBJECTIVE | DATABASE_NAME | ALLOW_CONNECTIONS | GEO | NAMED | DATEFIRST | BACKUP_STORAGE_REDUNDANCY | FORCE_FAILOVER_ALLOW_DATA_LOSS | SECONDARY | FAILOVER | DEFAULT_FULLTEXT_LANGUAGE
| DEFAULT_LANGUAGE | INLINE | NESTED_TRIGGERS | TRANSFORM_NOISE_WORDS | TWO_DIGIT_YEAR_CUTOFF | PERSISTENT_LOG_BUFFER | DIRECTORY_NAME | DATEFORMAT | DELAYED_DURABILITY | TRANSFER | SCHEMA | PASSWORD | AUTHORIZATION
- | MEMBER | SEARCH | TEXT | SECOND | PRECISION | VIEWS | PROVIDER | COLUMNS | SUBSTRING | RETURNS | SIZE | CONTAINS | MONTH | INPUT
+ | MEMBER | SEARCH | TEXT | SECOND | PRECISION | VIEWS | PROVIDER | COLUMNS | SUBSTRING | RETURNS | SIZE | CONTAINS | MONTH | INPUT | YEAR
| TIMESTAMP | TRIM
;
@@ -296,7 +296,7 @@ simpleExpr
;
functionCall
- : aggregationFunction | specialFunction | regularFunction
+ : aggregationFunction | specialFunction | regularFunction
;
aggregationFunction
@@ -312,7 +312,7 @@ distinct
;
specialFunction
- : conversionFunction | charFunction | openJsonFunction | jsonFunction | openRowSetFunction
+ : conversionFunction | charFunction | openJsonFunction | jsonFunction | openRowSetFunction | windowFunction
;
conversionFunction
@@ -390,7 +390,7 @@ caseElse
;
privateExprOfDb
- : windowedFunction | atTimeZoneExpr | castExpr | convertExpr
+ : windowFunction | atTimeZoneExpr | castExpr | convertExpr
;
orderByClause
@@ -425,8 +425,12 @@ convertExpr
: CONVERT (dataType (LP_ NUMBER_ RP_)? COMMA_ expr (COMMA_ NUMBER_)?)
;
-windowedFunction
- : functionCall overClause
+windowFunction
+ : funcName = (FIRST_VALUE | LAST_VALUE) LP_ expr RP_ nullTreatment? overClause
+ ;
+
+nullTreatment
+ : (RESPECT | IGNORE) NULLS
;
overClause
diff --git a/parser/sql/dialect/sqlserver/src/main/antlr4/imports/sqlserver/Keyword.g4 b/parser/sql/dialect/sqlserver/src/main/antlr4/imports/sqlserver/Keyword.g4
index f28dc17c8d8..a6ac558e186 100644
--- a/parser/sql/dialect/sqlserver/src/main/antlr4/imports/sqlserver/Keyword.g4
+++ b/parser/sql/dialect/sqlserver/src/main/antlr4/imports/sqlserver/Keyword.g4
@@ -779,3 +779,11 @@ JSON_OBJECT
JSON_ARRAY
: J S O N UL_ A R R A Y
;
+
+FIRST_VALUE
+ :F I R S T UL_ V A L U E
+ ;
+
+LAST_VALUE
+ :L A S T UL_ V A L U E
+ ;
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 ea867652e3e..780f7d5b29b 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
@@ -53,6 +53,7 @@ import org.apache.shardingsphere.sql.parser.autogen.SQLServerStatementParser.Dat
import org.apache.shardingsphere.sql.parser.autogen.SQLServerStatementParser.DeleteContext;
import org.apache.shardingsphere.sql.parser.autogen.SQLServerStatementParser.DelimitedIdentifierContext;
import org.apache.shardingsphere.sql.parser.autogen.SQLServerStatementParser.DuplicateSpecificationContext;
+import org.apache.shardingsphere.sql.parser.autogen.SQLServerStatementParser.ExecContext;
import org.apache.shardingsphere.sql.parser.autogen.SQLServerStatementParser.ExprContext;
import org.apache.shardingsphere.sql.parser.autogen.SQLServerStatementParser.FromClauseContext;
import org.apache.shardingsphere.sql.parser.autogen.SQLServerStatementParser.FunctionCallContext;
@@ -63,21 +64,23 @@ import org.apache.shardingsphere.sql.parser.autogen.SQLServerStatementParser.Ide
import org.apache.shardingsphere.sql.parser.autogen.SQLServerStatementParser.IndexNameContext;
import org.apache.shardingsphere.sql.parser.autogen.SQLServerStatementParser.InsertContext;
import org.apache.shardingsphere.sql.parser.autogen.SQLServerStatementParser.InsertDefaultValueContext;
+import org.apache.shardingsphere.sql.parser.autogen.SQLServerStatementParser.InsertExecClauseContext;
+import org.apache.shardingsphere.sql.parser.autogen.SQLServerStatementParser.InsertSelectClauseContext;
+import org.apache.shardingsphere.sql.parser.autogen.SQLServerStatementParser.InsertValuesClauseContext;
+import org.apache.shardingsphere.sql.parser.autogen.SQLServerStatementParser.JoinSpecificationContext;
+import org.apache.shardingsphere.sql.parser.autogen.SQLServerStatementParser.JoinedTableContext;
import org.apache.shardingsphere.sql.parser.autogen.SQLServerStatementParser.JsonArrayFunctionContext;
import org.apache.shardingsphere.sql.parser.autogen.SQLServerStatementParser.JsonFunctionContext;
import org.apache.shardingsphere.sql.parser.autogen.SQLServerStatementParser.JsonKeyValueContext;
import org.apache.shardingsphere.sql.parser.autogen.SQLServerStatementParser.JsonNullClauseContext;
import org.apache.shardingsphere.sql.parser.autogen.SQLServerStatementParser.JsonObjectFunctionContext;
-import org.apache.shardingsphere.sql.parser.autogen.SQLServerStatementParser.WithTableHintContext;
-import org.apache.shardingsphere.sql.parser.autogen.SQLServerStatementParser.InsertSelectClauseContext;
-import org.apache.shardingsphere.sql.parser.autogen.SQLServerStatementParser.InsertValuesClauseContext;
-import org.apache.shardingsphere.sql.parser.autogen.SQLServerStatementParser.JoinSpecificationContext;
-import org.apache.shardingsphere.sql.parser.autogen.SQLServerStatementParser.JoinedTableContext;
import org.apache.shardingsphere.sql.parser.autogen.SQLServerStatementParser.LiteralsContext;
import org.apache.shardingsphere.sql.parser.autogen.SQLServerStatementParser.MultipleTableNamesContext;
import org.apache.shardingsphere.sql.parser.autogen.SQLServerStatementParser.MultipleTablesClauseContext;
import org.apache.shardingsphere.sql.parser.autogen.SQLServerStatementParser.NullValueLiteralsContext;
import org.apache.shardingsphere.sql.parser.autogen.SQLServerStatementParser.NumberLiteralsContext;
+import org.apache.shardingsphere.sql.parser.autogen.SQLServerStatementParser.OpenJsonFunctionContext;
+import org.apache.shardingsphere.sql.parser.autogen.SQLServerStatementParser.OpenRowSetFunctionContext;
import org.apache.shardingsphere.sql.parser.autogen.SQLServerStatementParser.OrderByClauseContext;
import org.apache.shardingsphere.sql.parser.autogen.SQLServerStatementParser.OrderByItemContext;
import org.apache.shardingsphere.sql.parser.autogen.SQLServerStatementParser.OutputClauseContext;
@@ -87,11 +90,13 @@ import org.apache.shardingsphere.sql.parser.autogen.SQLServerStatementParser.Out
import org.apache.shardingsphere.sql.parser.autogen.SQLServerStatementParser.OwnerContext;
import org.apache.shardingsphere.sql.parser.autogen.SQLServerStatementParser.ParameterMarkerContext;
import org.apache.shardingsphere.sql.parser.autogen.SQLServerStatementParser.PredicateContext;
+import org.apache.shardingsphere.sql.parser.autogen.SQLServerStatementParser.ProcedureNameContext;
import org.apache.shardingsphere.sql.parser.autogen.SQLServerStatementParser.ProjectionContext;
import org.apache.shardingsphere.sql.parser.autogen.SQLServerStatementParser.ProjectionsContext;
import org.apache.shardingsphere.sql.parser.autogen.SQLServerStatementParser.QualifiedShorthandContext;
import org.apache.shardingsphere.sql.parser.autogen.SQLServerStatementParser.RegularFunctionContext;
import org.apache.shardingsphere.sql.parser.autogen.SQLServerStatementParser.RegularIdentifierContext;
+import org.apache.shardingsphere.sql.parser.autogen.SQLServerStatementParser.SampleOptionContext;
import org.apache.shardingsphere.sql.parser.autogen.SQLServerStatementParser.SchemaNameContext;
import org.apache.shardingsphere.sql.parser.autogen.SQLServerStatementParser.ScriptVariableNameContext;
import org.apache.shardingsphere.sql.parser.autogen.SQLServerStatementParser.SelectClauseContext;
@@ -100,9 +105,13 @@ import org.apache.shardingsphere.sql.parser.autogen.SQLServerStatementParser.Set
import org.apache.shardingsphere.sql.parser.autogen.SQLServerStatementParser.SimpleExprContext;
import org.apache.shardingsphere.sql.parser.autogen.SQLServerStatementParser.SingleTableClauseContext;
import org.apache.shardingsphere.sql.parser.autogen.SQLServerStatementParser.SpecialFunctionContext;
+import org.apache.shardingsphere.sql.parser.autogen.SQLServerStatementParser.StatisticsOptionContext;
+import org.apache.shardingsphere.sql.parser.autogen.SQLServerStatementParser.StatisticsOptionsContext;
+import org.apache.shardingsphere.sql.parser.autogen.SQLServerStatementParser.StatisticsWithClauseContext;
import org.apache.shardingsphere.sql.parser.autogen.SQLServerStatementParser.StringLiteralsContext;
import org.apache.shardingsphere.sql.parser.autogen.SQLServerStatementParser.SubqueryContext;
import org.apache.shardingsphere.sql.parser.autogen.SQLServerStatementParser.TableFactorContext;
+import org.apache.shardingsphere.sql.parser.autogen.SQLServerStatementParser.TableHintLimitedContext;
import org.apache.shardingsphere.sql.parser.autogen.SQLServerStatementParser.TableNameContext;
import org.apache.shardingsphere.sql.parser.autogen.SQLServerStatementParser.TableNamesContext;
import org.apache.shardingsphere.sql.parser.autogen.SQLServerStatementParser.TableReferenceContext;
@@ -110,20 +119,12 @@ import org.apache.shardingsphere.sql.parser.autogen.SQLServerStatementParser.Tab
import org.apache.shardingsphere.sql.parser.autogen.SQLServerStatementParser.TopContext;
import org.apache.shardingsphere.sql.parser.autogen.SQLServerStatementParser.UnreservedWordContext;
import org.apache.shardingsphere.sql.parser.autogen.SQLServerStatementParser.UpdateContext;
+import org.apache.shardingsphere.sql.parser.autogen.SQLServerStatementParser.UpdateStatisticsContext;
import org.apache.shardingsphere.sql.parser.autogen.SQLServerStatementParser.ViewNameContext;
import org.apache.shardingsphere.sql.parser.autogen.SQLServerStatementParser.WhereClauseContext;
+import org.apache.shardingsphere.sql.parser.autogen.SQLServerStatementParser.WindowFunctionContext;
import org.apache.shardingsphere.sql.parser.autogen.SQLServerStatementParser.WithClauseContext;
-import org.apache.shardingsphere.sql.parser.autogen.SQLServerStatementParser.UpdateStatisticsContext;
-import org.apache.shardingsphere.sql.parser.autogen.SQLServerStatementParser.StatisticsWithClauseContext;
-import org.apache.shardingsphere.sql.parser.autogen.SQLServerStatementParser.StatisticsOptionContext;
-import org.apache.shardingsphere.sql.parser.autogen.SQLServerStatementParser.StatisticsOptionsContext;
-import org.apache.shardingsphere.sql.parser.autogen.SQLServerStatementParser.SampleOptionContext;
-import org.apache.shardingsphere.sql.parser.autogen.SQLServerStatementParser.InsertExecClauseContext;
-import org.apache.shardingsphere.sql.parser.autogen.SQLServerStatementParser.ExecContext;
-import org.apache.shardingsphere.sql.parser.autogen.SQLServerStatementParser.ProcedureNameContext;
-import org.apache.shardingsphere.sql.parser.autogen.SQLServerStatementParser.OpenJsonFunctionContext;
-import org.apache.shardingsphere.sql.parser.autogen.SQLServerStatementParser.TableHintLimitedContext;
-import org.apache.shardingsphere.sql.parser.autogen.SQLServerStatementParser.OpenRowSetFunctionContext;
+import org.apache.shardingsphere.sql.parser.autogen.SQLServerStatementParser.WithTableHintContext;
import org.apache.shardingsphere.sql.parser.autogen.SQLServerStatementParser.ConversionFunctionContext;
import org.apache.shardingsphere.sql.parser.autogen.SQLServerStatementParser.IntoClauseContext;
import org.apache.shardingsphere.sql.parser.sql.common.enums.AggregationType;
@@ -192,8 +193,6 @@ import org.apache.shardingsphere.sql.parser.sql.common.segment.generic.table.Sim
import org.apache.shardingsphere.sql.parser.sql.common.segment.generic.table.SubqueryTableSegment;
import org.apache.shardingsphere.sql.parser.sql.common.segment.generic.table.TableNameSegment;
import org.apache.shardingsphere.sql.parser.sql.common.segment.generic.table.TableSegment;
-import org.apache.shardingsphere.sql.parser.sql.dialect.segment.sqlserver.hint.TableHintLimitedSegment;
-import org.apache.shardingsphere.sql.parser.sql.dialect.segment.sqlserver.hint.WithTableHintSegment;
import org.apache.shardingsphere.sql.parser.sql.common.util.SQLUtils;
import org.apache.shardingsphere.sql.parser.sql.common.value.collection.CollectionValue;
import org.apache.shardingsphere.sql.parser.sql.common.value.identifier.IdentifierValue;
@@ -205,6 +204,8 @@ import org.apache.shardingsphere.sql.parser.sql.common.value.literal.impl.OtherL
import org.apache.shardingsphere.sql.parser.sql.common.value.literal.impl.StringLiteralValue;
import org.apache.shardingsphere.sql.parser.sql.common.value.parametermarker.ParameterMarkerValue;
import org.apache.shardingsphere.sql.parser.sql.dialect.segment.sqlserver.exec.ExecSegment;
+import org.apache.shardingsphere.sql.parser.sql.dialect.segment.sqlserver.hint.TableHintLimitedSegment;
+import org.apache.shardingsphere.sql.parser.sql.dialect.segment.sqlserver.hint.WithTableHintSegment;
import org.apache.shardingsphere.sql.parser.sql.dialect.segment.sqlserver.json.JsonNullClauseSegment;
import org.apache.shardingsphere.sql.parser.sql.dialect.statement.sqlserver.ddl.SQLServerCreateTableStatement;
import org.apache.shardingsphere.sql.parser.sql.dialect.statement.sqlserver.ddl.SQLServerUpdateStatisticsStatement;
@@ -674,6 +675,9 @@ public abstract class SQLServerStatementVisitor extends SQLServerStatementBaseVi
if (null != ctx.jsonFunction()) {
return visit(ctx.jsonFunction());
}
+ if (null != ctx.windowFunction()) {
+ return visit(ctx.windowFunction());
+ }
return new FunctionSegment(ctx.getStart().getStartIndex(), ctx.getStop().getStopIndex(), ctx.getChild(0).getChild(0).getText(), getOriginalText(ctx));
}
@@ -688,6 +692,13 @@ public abstract class SQLServerStatementVisitor extends SQLServerStatementBaseVi
return new FunctionSegment(ctx.getStart().getStartIndex(), ctx.getStop().getStopIndex(), ctx.getChild(0).getChild(0).getText(), getOriginalText(ctx));
}
+ @Override
+ public final ASTNode visitWindowFunction(final WindowFunctionContext ctx) {
+ FunctionSegment result = new FunctionSegment(ctx.getStart().getStartIndex(), ctx.getStop().getStopIndex(), ctx.funcName.getText(), getOriginalText(ctx));
+ result.getParameters().add((ExpressionSegment) visit(ctx.expr()));
+ return result;
+ }
+
@Override
public final ASTNode visitJsonFunction(final JsonFunctionContext ctx) {
if (null != ctx.jsonArrayFunction()) {
@@ -915,7 +926,8 @@ public abstract class SQLServerStatementVisitor extends SQLServerStatementBaseVi
result.setHaving((HavingSegment) visit(ctx.havingClause()));
}
if (null != ctx.orderByClause()) {
- visitOrderBy(result, ctx.orderByClause());
+ result.setOrderBy(getOrderBySegment(ctx.orderByClause()));
+ result.setLimit(getLimitSegment(ctx.orderByClause()));
}
return result;
}
@@ -943,19 +955,10 @@ public abstract class SQLServerStatementVisitor extends SQLServerStatementBaseVi
return new HavingSegment(ctx.getStart().getStartIndex(), ctx.getStop().getStopIndex(), expr);
}
- private SQLServerSelectStatement visitOrderBy(final SQLServerSelectStatement selectStatement, final OrderByClauseContext ctx) {
- Collection<OrderByItemSegment> items = new LinkedList<>();
- int orderByStartIndex = ctx.start.getStartIndex();
- int orderByStopIndex = ctx.start.getStartIndex();
- for (OrderByItemContext each : ctx.orderByItem()) {
- items.add((OrderByItemSegment) visit(each));
- orderByStopIndex = each.stop.getStopIndex();
- }
- OrderBySegment orderBySegment = new OrderBySegment(orderByStartIndex, orderByStopIndex, items);
- selectStatement.setOrderBy(orderBySegment);
+ private LimitSegment getLimitSegment(final OrderByClauseContext ctx) {
+ LimitSegment result = null;
PaginationValueSegment offset = null;
PaginationValueSegment rowcount = null;
- LimitSegment limitSegment = null;
if (null != ctx.OFFSET()) {
ASTNode astNode = visit(ctx.expr(0));
if (astNode instanceof LiteralExpressionSegment && ((LiteralExpressionSegment) astNode).getLiterals() instanceof Number) {
@@ -975,10 +978,20 @@ public abstract class SQLServerStatementVisitor extends SQLServerStatementBaseVi
}
}
if (null != offset) {
- limitSegment = new LimitSegment(ctx.OFFSET().getSymbol().getStartIndex(), ctx.stop.getStopIndex(), offset, rowcount);
+ result = new LimitSegment(ctx.OFFSET().getSymbol().getStartIndex(), ctx.stop.getStopIndex(), offset, rowcount);
+ }
+ return result;
+ }
+
+ private OrderBySegment getOrderBySegment(final OrderByClauseContext ctx) {
+ Collection<OrderByItemSegment> items = new LinkedList<>();
+ int orderByStartIndex = ctx.start.getStartIndex();
+ int orderByStopIndex = ctx.start.getStartIndex();
+ for (OrderByItemContext each : ctx.orderByItem()) {
+ items.add((OrderByItemSegment) visit(each));
+ orderByStopIndex = each.stop.getStopIndex();
}
- selectStatement.setLimit(limitSegment);
- return selectStatement;
+ return new OrderBySegment(orderByStartIndex, orderByStopIndex, items);
}
private boolean isDistinct(final SelectClauseContext ctx) {
diff --git a/test/it/parser/src/main/resources/case/dml/insert.xml b/test/it/parser/src/main/resources/case/dml/insert.xml
index bad62ceb897..4b632442106 100644
--- a/test/it/parser/src/main/resources/case/dml/insert.xml
+++ b/test/it/parser/src/main/resources/case/dml/insert.xml
@@ -3443,4 +3443,18 @@
</value>
</values>
</insert>
+
+ <insert sql-case-id="insert_with_exec_prediction_results">
+ <table name="prediction_results" start-index="12" stop-index="29" />
+ <columns start-index="31" stop-index="127">
+ <column name="tipped_Pred" start-index="32" stop-index="42" />
+ <column name="payment_type" start-index="44" stop-index="55" />
+ <column name="tipped" start-index="57" stop-index="62" />
+ <column name="passenger_count" start-index="64" stop-index="78" />
+ <column name="trip_distance" start-index="80" stop-index="92" />
+ <column name="trip_time_in_secs" start-index="94" stop-index="110" />
+ <column name="direct_distance" start-index="112" stop-index="126" />
+ </columns>
+ <exec name="predict_per_partition" start-index="129" stop-index="159" start-delimiter="[" end-delimiter="]" />
+ </insert>
</sql-parser-test-cases>
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 b48088bf69e..bea78475efe 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
@@ -987,4 +987,112 @@
</expr>
</where>
</select>
+
+ <select sql-case-id="select_first_last_value_function">
+ <projections start-index="7" stop-index="493">
+ <column-projection name="BusinessEntityID" start-index="7" stop-index="22" />
+ <expression-projection text="DATEPART(QUARTER, QuotaDate)" alias="Quarter" start-index="25" stop-index="63">
+ <expr>
+ <function function-name="DATEPART" text="DATEPART(QUARTER, QuotaDate)" start-index="25" stop-index="52">
+ <parameter>
+ <column name="QUARTER" start-index="34" stop-index="40" />
+ </parameter>
+ <parameter>
+ <column name="QuotaDate" start-index="43" stop-index="51" />
+ </parameter>
+ </function>
+ </expr>
+ </expression-projection>
+ <expression-projection text="YEAR(QuotaDate)" start-index="66" stop-index="93" alias="SalesYear">
+ <expr>
+ <function function-name="YEAR" text="YEAR(QuotaDate)" start-index="66" stop-index="80">
+ <parameter>
+ <column name="QuotaDate" start-index="71" stop-index="79" />
+ </parameter>
+ </function>
+ </expr>
+ </expression-projection>
+ <column-projection name="SalesQuota" alias="QuotaThisQuarter" start-index="96" stop-index="125" />
+ <expression-projection text="SalesQuota - FIRST_VALUE(SalesQuota) OVER (PARTITION BY BusinessEntityID, YEAR(QuotaDate) ORDER BY DATEPART(QUARTER, QuotaDate))" alias="DifferenceFromFirstQuarter" start-index="128" stop-index="285">
+ <expr>
+ <binary-operation-expression start-index="128" stop-index="255">
+ <left>
+ <column name="SalesQuota" start-index="128" stop-index="137" />
+ </left>
+ <right>
+ <function function-name="FIRST_VALUE" text="FIRST_VALUE(SalesQuota) OVER (PARTITION BY BusinessEntityID, YEAR(QuotaDate) ORDER BY DATEPART(QUARTER, QuotaDate))" start-index="141" stop-index="255">
+ <parameter>
+ <column name="SalesQuota" start-index="153" stop-index="162" />
+ </parameter>
+ </function>
+ </right>
+ <operator>-</operator>
+ </binary-operation-expression>
+ </expr>
+ </expression-projection>
+ <expression-projection start-index="288" stop-index="493" text="SalesQuota - LAST_VALUE(SalesQuota) OVER (PARTITION BY BusinessEntityID, YEAR(QuotaDate) ORDER BY DATEPART(QUARTER, QuotaDate) RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)" alias="DifferenceFromLastQuarter">
+ <expr>
+ <binary-operation-expression start-index="288" stop-index="464">
+ <left>
+ <column name="SalesQuota" start-index="288" stop-index="297" />
+ </left>
+ <right>
+ <function function-name="LAST_VALUE" text="LAST_VALUE(SalesQuota) OVER (PARTITION BY BusinessEntityID, YEAR(QuotaDate) ORDER BY DATEPART(QUARTER, QuotaDate) RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)" start-index="301" stop-index="464">
+ <parameter>
+ <column name="SalesQuota" start-index="312" stop-index="321" />
+ </parameter>
+ </function>
+ </right>
+ <operator>-</operator>
+ </binary-operation-expression>
+ </expr>
+ </expression-projection>
+ </projections>
+ <from>
+ <simple-table name="SalesPersonQuotaHistory" start-index="500" stop-index="528">
+ <owner name="Sales" start-index="500" stop-index="504" />
+ </simple-table>
+ </from>
+ <where start-index="530" stop-index="598">
+ <expr>
+ <binary-operation-expression start-index="536" stop-index="598" text="YEAR(QuotaDate) > 2005 AND BusinessEntityID BETWEEN 274 AND 275">
+ <left>
+ <binary-operation-expression start-index="536" stop-index="557" text="YEAR(QuotaDate) > 2005">
+ <left>
+ <function function-name="YEAR" text="YEAR(QuotaDate)" start-index="536" stop-index="550">
+ <parameter>
+ <column name="QuotaDate" start-index="541" stop-index="549" />
+ </parameter>
+ </function>
+ </left>
+ <right>
+ <literal-expression value="2005" start-index="554" stop-index="557" />
+ </right>
+ <operator>></operator>
+ </binary-operation-expression>
+ </left>
+ <right>
+ <between-expression start-index="563" stop-index="598">
+ <not>false</not>
+ <left>
+ <column name="BusinessEntityID" start-index="563" stop-index="578" />
+ </left>
+ <between-expr>
+ <literal-expression value="274" start-index="588" stop-index="590" />
+ </between-expr>
+ <and-expr>
+ <literal-expression value="275" start-index="596" stop-index="598" />
+ </and-expr>
+ </between-expression>
+ </right>
+ <operator>AND</operator>
+ </binary-operation-expression>
+ </expr>
+ </where>
+ <order-by>
+ <column-item name="BusinessEntityID" order-direction="ASC" start-index="609" stop-index="624" />
+ <column-item name="SalesYear" order-direction="ASC" start-index="627" stop-index="635" />
+ <column-item name="Quarter" order-direction="ASC" start-index="638" stop-index="644" />
+ </order-by>
+ </select>
</sql-parser-test-cases>
diff --git a/test/it/parser/src/main/resources/sql/supported/dml/insert.xml b/test/it/parser/src/main/resources/sql/supported/dml/insert.xml
index 233753880b6..cf6f6407abe 100644
--- a/test/it/parser/src/main/resources/sql/supported/dml/insert.xml
+++ b/test/it/parser/src/main/resources/sql/supported/dml/insert.xml
@@ -126,4 +126,5 @@
<sql-case id="insert_into_table_get_date" value="INSERT INTO #Test VALUES (N'OC', N'Ounces', GETDATE())" db-types="SQLServer"/>
<sql-case id="insert_into_table_from_table" value="INSERT INTO #Test SELECT * FROM Production.UnitMeasure" db-types="SQLServer"/>
<sql-case id="insert_into_production_location" value="INSERT INTO AdventureWorks2022.Production.Location(Name, CostRate, Availability, ModifiedDate) VALUES (NEWID(), .5, 5.2, GETDATE())" db-types="SQLServer"/>
+ <sql-case id="insert_with_exec_prediction_results" value="INSERT INTO prediction_results (tipped_Pred,payment_type,tipped,passenger_count,trip_distance,trip_time_in_secs,direct_distance) EXECUTE [predict_per_partition]" db-types="SQLServer"/>
</sql-cases>
diff --git a/test/it/parser/src/main/resources/sql/supported/dml/select-special-function.xml b/test/it/parser/src/main/resources/sql/supported/dml/select-special-function.xml
index 07b03cb04c8..a77c62a0f1a 100644
--- a/test/it/parser/src/main/resources/sql/supported/dml/select-special-function.xml
+++ b/test/it/parser/src/main/resources/sql/supported/dml/select-special-function.xml
@@ -55,4 +55,5 @@
<sql-case id="select_nest_json_object" value="SELECT JSON_OBJECT('name':'value', 'type':JSON_OBJECT('type_id':1, 'name':'a'))" db-types="SQLServer" />
<sql-case id="select_json_object_with_subquery" value="SELECT JSON_OBJECT('user_name':USER_NAME(), @id_key:@id_value, 'sid':(SELECT @@SPID))" db-types="SQLServer" />
<sql-case id="select_dm_exec_sessions_with_json_object_function" value="SELECT s.session_id, JSON_OBJECT('security_id':s.security_id, 'login':s.login_name, 'status':s.status) as info FROM sys.dm_exec_sessions AS s WHERE s.is_user_process = 1" db-types="SQLServer" />
+ <sql-case id="select_first_last_value_function" value="SELECT BusinessEntityID, DATEPART(QUARTER, QuotaDate) AS Quarter, YEAR(QuotaDate) AS SalesYear, SalesQuota AS QuotaThisQuarter, SalesQuota - FIRST_VALUE(SalesQuota) OVER (PARTITION BY BusinessEntityID, YEAR(QuotaDate) ORDER BY DATEPART(QUARTER, QuotaDate)) AS DifferenceFromFirstQuarter, SalesQuota - LAST_VALUE(SalesQuota) OVER (PARTITION BY BusinessEntityID, YEAR(QuotaDate) ORDER BY DATEPART(QUARTER, QuotaDate) RANGE BETWEEN CURR [...]
</sql-cases>