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 7b90120a61b Support parsing SQL Server SELECT DeptId, sql #29164
(#29906)
7b90120a61b is described below
commit 7b90120a61b553ae139831c913c31d053a15ab9c
Author: yydeng626 <[email protected]>
AuthorDate: Tue Jan 30 02:22:42 2024 -0600
Support parsing SQL Server SELECT DeptId, sql #29164 (#29906)
---
.../src/main/antlr4/imports/sqlserver/BaseRule.g4 | 6 ++-
.../src/main/antlr4/imports/sqlserver/Keyword.g4 | 12 +++++
.../statement/SQLServerStatementVisitor.java | 15 ++++++
.../parser/src/main/resources/case/dml/insert.xml | 60 ++++++++++++++++++++++
.../resources/case/dml/select-special-function.xml | 36 +++++++++++++
.../main/resources/sql/supported/dml/insert.xml | 4 ++
.../sql/supported/dml/select-special-function.xml | 1 +
7 files changed, 133 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 c1e4f38083b..78046ff879f 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
@@ -312,7 +312,11 @@ distinct
;
specialFunction
- : conversionFunction | charFunction | openJsonFunction | jsonFunction |
openRowSetFunction | windowFunction
+ : conversionFunction | charFunction | openJsonFunction | jsonFunction |
openRowSetFunction | windowFunction | approxFunction
+ ;
+
+approxFunction
+ : funcName = (APPROX_PERCENTILE_CONT | APPROX_PERCENTILE_DISC) LP_ expr
RP_ WITHIN GROUP LP_ ORDER BY expr (ASC | DESC)? RP_
;
conversionFunction
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 a6ac558e186..bcc4057ded1 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
@@ -787,3 +787,15 @@ FIRST_VALUE
LAST_VALUE
:L A S T UL_ V A L U E
;
+
+APPROX_PERCENTILE_CONT
+ : A P P R O X UL_ P E R C E N T I L E UL_ C O N T
+ ;
+
+APPROX_PERCENTILE_DISC
+ : A P P R O X UL_ P E R C E N T I L E UL_ D I S C
+ ;
+
+WITHIN
+ : W I T H I N
+ ;
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 780f7d5b29b..0b519231662 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
@@ -28,6 +28,7 @@ import
org.apache.shardingsphere.sql.parser.autogen.SQLServerStatementBaseVisito
import
org.apache.shardingsphere.sql.parser.autogen.SQLServerStatementParser.AggregationClauseContext;
import
org.apache.shardingsphere.sql.parser.autogen.SQLServerStatementParser.AggregationFunctionContext;
import
org.apache.shardingsphere.sql.parser.autogen.SQLServerStatementParser.AliasContext;
+import
org.apache.shardingsphere.sql.parser.autogen.SQLServerStatementParser.ApproxFunctionContext;
import
org.apache.shardingsphere.sql.parser.autogen.SQLServerStatementParser.AssignmentContext;
import
org.apache.shardingsphere.sql.parser.autogen.SQLServerStatementParser.AssignmentValueContext;
import
org.apache.shardingsphere.sql.parser.autogen.SQLServerStatementParser.AssignmentValuesContext;
@@ -678,9 +679,23 @@ public abstract class SQLServerStatementVisitor extends
SQLServerStatementBaseVi
if (null != ctx.windowFunction()) {
return visit(ctx.windowFunction());
}
+ if (null != ctx.approxFunction()) {
+ return visit(ctx.approxFunction());
+ }
return new FunctionSegment(ctx.getStart().getStartIndex(),
ctx.getStop().getStopIndex(), ctx.getChild(0).getChild(0).getText(),
getOriginalText(ctx));
}
+ @Override
+ public final ASTNode visitApproxFunction(final ApproxFunctionContext ctx) {
+ FunctionSegment result = new
FunctionSegment(ctx.getStart().getStartIndex(), ctx.getStop().getStopIndex(),
ctx.funcName.getText(), getOriginalText(ctx));
+ if (null != ctx.expr()) {
+ for (ExprContext each : ctx.expr()) {
+ result.getParameters().add((ExpressionSegment) visit(each));
+ }
+ }
+ return result;
+ }
+
@Override
public final ASTNode visitConversionFunction(final
ConversionFunctionContext ctx) {
if (null != ctx.castFunction()) {
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 4b632442106..eaf6f4113b9 100644
--- a/test/it/parser/src/main/resources/case/dml/insert.xml
+++ b/test/it/parser/src/main/resources/case/dml/insert.xml
@@ -3457,4 +3457,64 @@
</columns>
<exec name="predict_per_partition" start-index="129" stop-index="159"
start-delimiter="[" end-delimiter="]" />
</insert>
+
+ <insert sql-case-id="insert_into_sample_temp_table">
+ <table name="#SampleTempTable" start-index="12" stop-index="27" />
+ <columns start-index="28" stop-index="28" />
+ <values>
+ <value>
+ <assignment-value>
+ <literal-expression value="null" start-index="37"
stop-index="40" />
+ </assignment-value>
+ <assignment-value>
+ <literal-expression value="hello" start-index="43"
stop-index="49" />
+ </assignment-value>
+ </value>
+ </values>
+ </insert>
+
+ <insert sql-case-id="insert_into_sample_temp_table_2">
+ <table name="#SampleTempTable" start-index="12" stop-index="27" />
+ <columns start-index="28" stop-index="28" />
+ <values>
+ <value>
+ <assignment-value>
+ <literal-expression value="10" start-index="37"
stop-index="38" />
+ </assignment-value>
+ <assignment-value>
+ <literal-expression value="null" start-index="41"
stop-index="44" />
+ </assignment-value>
+ </value>
+ </values>
+ </insert>
+
+ <insert sql-case-id="insert_into_sample_temp_table_3">
+ <table name="#SampleTempTable" start-index="12" stop-index="27" />
+ <columns start-index="28" stop-index="28" />
+ <values>
+ <value>
+ <assignment-value>
+ <literal-expression value="17" start-index="37"
stop-index="38" />
+ </assignment-value>
+ <assignment-value>
+ <literal-expression value="abc" start-index="41"
stop-index="45" />
+ </assignment-value>
+ </value>
+ </values>
+ </insert>
+
+ <insert sql-case-id="insert_into_sample_temp_table_4">
+ <table name="#SampleTempTable" start-index="12" stop-index="27" />
+ <columns start-index="28" stop-index="28" />
+ <values>
+ <value>
+ <assignment-value>
+ <literal-expression value="17" start-index="37"
stop-index="38" />
+ </assignment-value>
+ <assignment-value>
+ <literal-expression value="yes" start-index="41"
stop-index="45" />
+ </assignment-value>
+ </value>
+ </values>
+ </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 bea78475efe..6ee40537f99 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
@@ -1095,4 +1095,40 @@
<column-item name="Quarter" order-direction="ASC"
start-index="638" stop-index="644" />
</order-by>
</select>
+
+ <select sql-case-id="select_approx_percentile_cont_function">
+ <projections start-index="7" stop-index="148">
+ <column-projection start-index="7" stop-index="12" name="DeptId" />
+ <expression-projection start-index="14" stop-index="80"
text="APPROX_PERCENTILE_CONT(0.10) WITHIN GROUP(ORDER BY Salary)" alias="P10">
+ <expr>
+ <function function-name="APPROX_PERCENTILE_CONT"
text="APPROX_PERCENTILE_CONT(0.10) WITHIN GROUP(ORDER BY Salary)"
start-index="14" stop-index="71">
+ <parameter>
+ <literal-expression value="0.10" start-index="37"
stop-index="40" />
+ </parameter>
+ <parameter>
+ <column name="Salary" start-index="65"
stop-index="70" />
+ </parameter>
+ </function>
+ </expr>
+ </expression-projection>
+ <expression-projection text="APPROX_PERCENTILE_CONT(0.90) WITHIN
GROUP(ORDER BY Salary)" start-index="82" stop-index="148" alias="P90">
+ <expr>
+ <function function-name="APPROX_PERCENTILE_CONT"
text="APPROX_PERCENTILE_CONT(0.90) WITHIN GROUP(ORDER BY Salary)"
start-index="82" stop-index="139">
+ <parameter>
+ <literal-expression value="0.90" start-index="105"
stop-index="108" />
+ </parameter>
+ <parameter>
+ <column name="Salary" start-index="133"
stop-index="138" />
+ </parameter>
+ </function>
+ </expr>
+ </expression-projection>
+ </projections>
+ <from>
+ <simple-table name="tblEmployee" start-index="155"
stop-index="165" />
+ </from>
+ <group-by start-index="167" stop-index="181">
+ <column-item name="DeptId" order-direction="ASC" start-index="176"
stop-index="181" />
+ </group-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 cf6f6407abe..5b9c5cf544d 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
@@ -127,4 +127,8 @@
<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-case id="insert_into_sample_temp_table" value="INSERT INTO
#SampleTempTable VALUES (null, 'hello')" db-types="SQLServer"/>
+ <sql-case id="insert_into_sample_temp_table_2" value="INSERT INTO
#SampleTempTable VALUES (10, null)" db-types="SQLServer"/>
+ <sql-case id="insert_into_sample_temp_table_3" value="INSERT INTO
#SampleTempTable VALUES (17, 'abc')" db-types="SQLServer"/>
+ <sql-case id="insert_into_sample_temp_table_4" value="INSERT INTO
#SampleTempTable VALUES (17, 'yes')" 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 a77c62a0f1a..3892c0cd142 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
@@ -56,4 +56,5 @@
<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-case id="select_approx_percentile_cont_function" value="SELECT
DeptId,APPROX_PERCENTILE_CONT(0.10) WITHIN GROUP(ORDER BY Salary) AS
'P10',APPROX_PERCENTILE_CONT(0.90) WITHIN GROUP(ORDER BY Salary) AS 'P90' FROM
tblEmployee GROUP BY DeptId" db-types="SQLServer" />
</sql-cases>
