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 47e9685f09f Support parsing SQL Server SELECT s.session_id, sql #29161
(#29800)
47e9685f09f is described below
commit 47e9685f09f83462b368459744ef47b24670bc83
Author: yydeng626 <[email protected]>
AuthorDate: Sun Jan 28 20:27:48 2024 -0600
Support parsing SQL Server SELECT s.session_id, sql #29161 (#29800)
---
.../src/main/antlr4/imports/sqlserver/BaseRule.g4 | 1 +
.../statement/SQLServerStatementVisitor.java | 2 +
.../parser/src/main/resources/case/dml/insert.xml | 30 ++++++
.../main/resources/case/dml/select-group-by.xml | 35 ++++++
.../resources/case/dml/select-special-function.xml | 70 ++++++++++++
.../parser/src/main/resources/case/dml/select.xml | 117 +++++++++++++--------
.../main/resources/sql/supported/dml/insert.xml | 1 +
.../sql/supported/dml/select-group-by.xml | 1 +
.../sql/supported/dml/select-special-function.xml | 1 +
.../main/resources/sql/supported/dml/select.xml | 3 +-
10 files changed, 215 insertions(+), 46 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 c4f086dbd4a..eab5ea28c2e 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
@@ -121,6 +121,7 @@ unreservedWord
| 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
+ | TIMESTAMP | TRIM
;
databaseName
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 044d083672f..ea867652e3e 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
@@ -752,6 +752,8 @@ public abstract class SQLServerStatementVisitor extends
SQLServerStatementBaseVi
result.getParameters().add((ColumnSegment) exprSegment);
} else if (exprSegment instanceof LiteralExpressionSegment) {
result.getParameters().add((LiteralExpressionSegment) exprSegment);
+ } else if (exprSegment instanceof FunctionSegment) {
+ result.getParameters().add((FunctionSegment) exprSegment);
}
result.getParameters().add((DataTypeSegment) visit(ctx.dataType()));
return result;
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 5d7ba83eece..bad62ceb897 100644
--- a/test/it/parser/src/main/resources/case/dml/insert.xml
+++ b/test/it/parser/src/main/resources/case/dml/insert.xml
@@ -3413,4 +3413,34 @@
</from>
</select>
</insert>
+
+ <insert sql-case-id="insert_into_production_location">
+ <table name="Location" start-index="12" stop-index="49">
+ <owner name="Production" start-index="31" stop-index="40">
+ <owner name="AdventureWorks2022" start-index="12"
stop-index="29" />
+ </owner>
+ </table>
+ <columns start-index="50" stop-index="93">
+ <column name="Name" start-index="51" stop-index="54" />
+ <column name="CostRate" start-index="57" stop-index="64" />
+ <column name="Availability" start-index="67" stop-index="78" />
+ <column name="ModifiedDate" start-index="81" stop-index="92" />
+ </columns>
+ <values>
+ <value>
+ <assignment-value>
+ <function function-name="NEWID" text="NEWID()"
start-index="103" stop-index="109" />
+ </assignment-value>
+ <assignment-value>
+ <literal-expression value="0.5" start-index="112"
stop-index="113" />
+ </assignment-value>
+ <assignment-value>
+ <literal-expression value="5.2" start-index="116"
stop-index="118" />
+ </assignment-value>
+ <assignment-value>
+ <function function-name="GETDATE" text="GETDATE()"
start-index="121" stop-index="129" />
+ </assignment-value>
+ </value>
+ </values>
+ </insert>
</sql-parser-test-cases>
diff --git a/test/it/parser/src/main/resources/case/dml/select-group-by.xml
b/test/it/parser/src/main/resources/case/dml/select-group-by.xml
index 3481eae446f..da471754d24 100644
--- a/test/it/parser/src/main/resources/case/dml/select-group-by.xml
+++ b/test/it/parser/src/main/resources/case/dml/select-group-by.xml
@@ -924,4 +924,39 @@
<column-item name="tumblingwindow" order-direction="ASC"
start-index="60" stop-index="73"/>
</group-by>
</select>
+
+ <select sql-case-id="select_group_by_top_column_value">
+ <projections start-index="7" stop-index="34">
+ <top-projection start-index="7" stop-index="12">
+ <top-value value="10" start-index="26" stop-index="26" />
+ </top-projection>
+ <column-projection name="hash_unique_bigint_id" start-index="14"
stop-index="34" />
+ </projections>
+ <from start-index="41" stop-index="55">
+ <simple-table name="TelemetryDS" start-index="41" stop-index="55">
+ <owner name="dbo" start-index="41" stop-index="43" />
+ </simple-table>
+ </from>
+ <where start-index="57" stop-index="103">
+ <expr>
+ <between-expression start-index="63" stop-index="103">
+ <left>
+ <column name="Timestamp" start-index="63"
stop-index="71"/>
+ </left>
+ <between-expr>
+ <column name="@StartTime" start-index="81"
stop-index="90"/>
+ </between-expr>
+ <and-expr>
+ <column name="@EndTime" start-index="96"
stop-index="103" />
+ </and-expr>
+ </between-expression>
+ </expr>
+ </where>
+ <group-by start-index="57" stop-index="103">
+ <column-item name="hash_unique_bigint_id" order-direction="ASC"
start-index="114" stop-index="134" />
+ </group-by>
+ <order-by start-index="136" stop-index="179">
+ <expression-item expression="MAX(max_elapsed_time_microsec)"
order-direction="DESC" start-index="145" stop-index="174" />
+ </order-by>
+ </select>
</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 e6a797c6312..b48088bf69e 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
@@ -917,4 +917,74 @@
</expression-projection>
</projections>
</select>
+
+ <select sql-case-id="select_dm_exec_sessions_with_json_object_function">
+ <projections start-index="7" stop-index="109">
+ <column-projection name="session_id" start-index="7"
stop-index="18">
+ <owner name="s" start-index="7" stop-index="7" />
+ </column-projection>
+ <expression-projection
text="JSON_OBJECT('security_id':s.security_id, 'login':s.login_name,
'status':s.status)" alias="info" start-index="21" stop-index="109">
+ <expr>
+ <function function-name="JSON_OBJECT"
text="JSON_OBJECT('security_id':s.security_id, 'login':s.login_name,
'status':s.status)" start-index="21" stop-index="101">
+ <parameter>
+ <key-value text="'security_id':s.security_id"
start-index="33" stop-index="59">
+ <key>
+ <literal-expression value="security_id"
start-index="33" stop-index="45"/>
+ </key>
+ <value>
+ <column name="security_id"
start-index="47" stop-index="59">
+ <owner name="s" start-index="47"
stop-index="47" />
+ </column>
+ </value>
+ </key-value>
+ </parameter>
+ <parameter>
+ <key-value text="'login':s.login_name"
start-index="62" stop-index="81">
+ <key>
+ <literal-expression value="login"
start-index="62" stop-index="68" />
+ </key>
+ <value>
+ <column name="login_name" start-index="70"
stop-index="81">
+ <owner name="s" start-index="70"
stop-index="70" />
+ </column>
+ </value>
+ </key-value>
+ </parameter>
+ <parameter>
+ <key-value
text="JSON_OBJECT('security_id':s.security_id, 'login':s.login_name,
'status':s.status)" start-index="84" stop-index="100">
+ <key>
+ <literal-expression value="status"
start-index="84" stop-index="91" />
+ </key>
+ <value>
+ <column name="status" start-index="93"
stop-index="100">
+ <owner name="s" start-index="93"
stop-index="93" />
+ </column>
+ </value>
+ </key-value>
+ </parameter>
+ </function>
+ </expr>
+ </expression-projection>
+ </projections>
+ <from start-index="116" stop-index="140">
+ <simple-table name="dm_exec_sessions" alias="s" start-index="116"
stop-index="140">
+ <owner name="sys" start-index="116" stop-index="118" />
+ </simple-table>
+ </from>
+ <where start-index="142" stop-index="168">
+ <expr>
+ <binary-operation-expression start-index="148"
stop-index="168" text="s.is_user_process = 1">
+ <left>
+ <column name="is_user_process" start-index="148"
stop-index="164">
+ <owner name="s" start-index="148" stop-index="148"
/>
+ </column>
+ </left>
+ <right>
+ <literal-expression value="1" start-index="168"
stop-index="168" />
+ </right>
+ <operator>=</operator>
+ </binary-operation-expression>
+ </expr>
+ </where>
+ </select>
</sql-parser-test-cases>
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 4ce1ab9be81..637581e7fb6 100644
--- a/test/it/parser/src/main/resources/case/dml/select.xml
+++ b/test/it/parser/src/main/resources/case/dml/select.xml
@@ -3996,6 +3996,9 @@
<expression-projection alias="UsingCast" text="CAST(GETDATE() AS
NVARCHAR(30))" start-index="41" stop-index="84">
<expr>
<function function-name="CAST" text="CAST(GETDATE() AS
NVARCHAR(30))" start-index="41" stop-index="71">
+ <parameter>
+ <function function-name="GETDATE" text="GETDATE()"
start-index="46" stop-index="54" />
+ </parameter>
<parameter>
<data-type value="NVARCHAR" start-index="59"
stop-index="70" />
</parameter>
@@ -8093,51 +8096,7 @@
</expr>
</where>
</select>
-
- <select sql-case-id="select_with_database_files">
- <from start-index="108" stop-index="125">
- <simple-table name="database_files" start-index="108"
stop-index="125">
- <owner name="sys" start-index="108" stop-index="110"/>
- </simple-table>
- </from>
- <projections start-index="7" stop-index="101">
- <column-projection name="name" start-index="7" stop-index="10" />
- <expression-projection text="size / 128.0 -
CAST(FILEPROPERTY(name, 'SpaceUsed') AS INT) / 128.0"
alias="AvailableSpaceInMB" start-index="13" stop-index="101">
- <expr>
- <binary-operation-expression text="size / 128.0 -
CAST(FILEPROPERTY(name, 'SpaceUsed') AS INT) / 128.0" start-index="13"
stop-index="79">
- <left>
- <binary-operation-expression text="size / 128.0 -
CAST(FILEPROPERTY(name, 'SpaceUsed') AS INT)" start-index="13" stop-index="71">
- <left>
- <column name="size" start-index="13"
stop-index="16" />
- </left>
- <operator>/</operator>
- <right>
- <binary-operation-expression text="128.0 -
CAST(FILEPROPERTY(name, 'SpaceUsed') AS INT)" start-index="20" stop-index="71">
- <left>
- <literal-expression value="128.0"
start-index="20" stop-index="24" />
- </left>
- <operator>-</operator>
- <right>
- <function function-name="CAST"
text="CAST(FILEPROPERTY(name, 'SpaceUsed') AS INT)" start-index="28"
stop-index="71">
- <parameter>
- <data-type value="INT"
start-index="68" stop-index="70"/>
- </parameter>
- </function>
- </right>
- </binary-operation-expression>
- </right>
- </binary-operation-expression>
- </left>
- <operator>/</operator>
- <right>
- <literal-expression value="128.0" start-index="75"
stop-index="79" />
- </right>
- </binary-operation-expression>
- </expr>
- </expression-projection>
- </projections>
- </select>
-
+
<select sql-case-id="select_with_contains_function">
<projections start-index="7" stop-index="16">
<column-projection name="product_id" start-index="7"
stop-index="16"/>
@@ -8174,6 +8133,74 @@
</expr>
</where>
</select>
+
+ <select sql-case-id="select_from_database_files">
+ <projections start-index="7" stop-index="101">
+ <column-projection name="name" start-index="7" stop-index="10" />
+ <expression-projection text="size / 128.0 -
CAST(FILEPROPERTY(name, 'SpaceUsed') AS INT) / 128.0"
alias="AvailableSpaceInMB" start-index="13" stop-index="101">
+ <expr>
+ <binary-operation-expression text="size / 128.0 -
CAST(FILEPROPERTY(name, 'SpaceUsed') AS INT) / 128.0" start-index="13"
stop-index="79">
+ <left>
+ <binary-operation-expression text="size / 128.0 -
CAST(FILEPROPERTY(name, 'SpaceUsed') AS INT)" start-index="13" stop-index="71">
+ <left>
+ <column name="size" start-index="13"
stop-index="16" />
+ </left>
+ <right>
+ <binary-operation-expression text="128.0 -
CAST(FILEPROPERTY(name, 'SpaceUsed') AS INT)" start-index="20" stop-index="71">
+ <left>
+ <literal-expression value="128.0"
start-index="20" stop-index="24" />
+ </left>
+ <right>
+ <function function-name="CAST"
text="CAST(FILEPROPERTY(name, 'SpaceUsed') AS INT)" start-index="28"
stop-index="71">
+ <parameter>
+ <function
function-name="FILEPROPERTY" text="FILEPROPERTY(name, 'SpaceUsed')"
start-index="33" stop-index="63">
+ <parameter>
+ <column name="name"
start-index="46" stop-index="49" />
+ </parameter>
+ <parameter>
+ <literal-expression
value="SpaceUsed" start-index="52" stop-index="62" />
+ </parameter>
+ </function>
+ </parameter>
+ <parameter>
+ <data-type value="INT"
start-index="68" stop-index="70" />
+ </parameter>
+ </function>
+ </right>
+ <operator>-</operator>
+ </binary-operation-expression>
+ </right>
+ <operator>/</operator>
+ </binary-operation-expression>
+ </left>
+ <right>
+ <literal-expression value="128.0" start-index="75"
stop-index="79" />
+ </right>
+ <operator>/</operator>
+ </binary-operation-expression>
+ </expr>
+ </expression-projection>
+ </projections>
+ <from>
+ <simple-table name="database_files" start-index="108"
stop-index="125">
+ <owner name="sys" start-index="108" stop-index="110" />
+ </simple-table>
+ </from>
+ </select>
+
+ <select sql-case-id="select_trim_2">
+ <projections start-index="7" stop-index="38">
+ <expression-projection text="TRIM( ' test ')"
alias="Result" start-index="7" stop-index="38">
+ <expr>
+ <function function-name="TRIM" text="TRIM( ' test
')" start-index="7" stop-index="28">
+ <parameter>
+ <literal-expression value=" test "
start-index="13" stop-index="27" />
+ </parameter>
+ </function>
+ </expr>
+ </expression-projection>
+ </projections>
+ </select>
<select sql-case-id="select_with_default_schema">
<projections start-index="7" stop-index="44">
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 62eea73a42e..233753880b6 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
@@ -125,4 +125,5 @@
<sql-case id="insert_with_select_from_open_row_2" value="INSERT INTO
achievements with (TABLOCK) (id, description) SELECT * FROM OPENROWSET(BULK
'csv/achievements.csv', DATA_SOURCE = 'MyAzureBlobStorage', FORMAT ='CSV',
FORMATFILE='csv/achievements-c.xml', FORMATFILE_DATA_SOURCE =
'MyAzureBlobStorage') AS DataFile" db-types="SQLServer"/>
<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-cases>
diff --git
a/test/it/parser/src/main/resources/sql/supported/dml/select-group-by.xml
b/test/it/parser/src/main/resources/sql/supported/dml/select-group-by.xml
index 706efb3f22e..6cf8220f523 100644
--- a/test/it/parser/src/main/resources/sql/supported/dml/select-group-by.xml
+++ b/test/it/parser/src/main/resources/sql/supported/dml/select-group-by.xml
@@ -44,4 +44,5 @@
<sql-case id="select_with_datepart_group_by_with_order_by" value="SELECT
DATEPART(yyyy,OrderDate) AS N'Year', SUM(TotalDue) AS N'Total Order Amount'
FROM Sales.SalesOrderHeader GROUP BY DATEPART(yyyy,OrderDate) ORDER BY
DATEPART(yyyy,OrderDate)" db-types="SQLServer"/>
<sql-case id="select_with_datepart_group_by_with_having_order_by"
value="SELECT DATEPART(yyyy,OrderDate) AS N'Year', SUM(TotalDue) AS N'Total
Order Amount' FROM Sales.SalesOrderHeader GROUP BY DATEPART(yyyy,OrderDate)
HAVING DATEPART(yyyy,OrderDate) >= N'2003' ORDER BY
DATEPART(yyyy,OrderDate)" db-types="SQLServer"/>
<sql-case id="select_from_input_table" value="SELECT count(*) FROM input
GROUP BY PartitionId, clusterid, tumblingwindow;" db-types="SQLServer"/>
+ <sql-case id="select_group_by_top_column_value" value="SELECT TOP 10
hash_unique_bigint_id FROM dbo.TelemetryDS WHERE Timestamp BETWEEN @StartTime
AND @EndTime GROUP BY hash_unique_bigint_id ORDER BY
MAX(max_elapsed_time_microsec) DESC" 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 7abcf363d65..07b03cb04c8 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
@@ -54,4 +54,5 @@
<sql-case id="select_json_array" value="SELECT JSON_OBJECT('name':'value',
'type':JSON_ARRAY(1, 2))" db-types="SQLServer" />
<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-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 0336c183f4a..b40b0b7f8cc 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
@@ -246,8 +246,9 @@
<sql-case id="select_with_substring_function" value="SELECT ProductID,
Name, ProductNumber FROM [Production].[Product] WHERE SUBSTRING(ProductNumber,
0, 4) = 'HN-'" db-types="SQLServer"/>
<sql-case id="select_with_not_distinct_from" value="SELECT * FROM
#SampleTempTable WHERE id IS NOT DISTINCT FROM NULL" db-types="SQLServer"/>
<sql-case id="select_with_distinct_from" value="SELECT * FROM
#SampleTempTable WHERE id IS DISTINCT FROM 17;" db-types="SQLServer"/>
- <sql-case id="select_with_database_files" value="SELECT name, size / 128.0
- CAST(FILEPROPERTY(name, 'SpaceUsed') AS INT) / 128.0 AS AvailableSpaceInMB
FROM sys.database_files" db-types="SQLServer"/>
<sql-case id="select_with_contains_function" value="SELECT product_id FROM
products WHERE CONTAINS(product_description, '"Snap Happy 100EZ" OR
FORMSOF(THESAURUS,"Snap Happy") OR "100EZ"') AND
product_cost < 200" db-types="SQLServer"/>
<sql-case id="select_with_default_schema" value="SELECT
has_backup_checksums, database_name, * FROM msdb..backupset"
db-types="SQLServer"/>
<sql-case id="select_from_dbms_table" value="select * from
table(dbms_xplan.display);" db-types="Oracle"/>
+ <sql-case id="select_from_database_files" value="SELECT name, size / 128.0
- CAST(FILEPROPERTY(name, 'SpaceUsed') AS INT) / 128.0 AS AvailableSpaceInMB
FROM sys.database_files" db-types="SQLServer"/>
+ <sql-case id="select_trim_2" value="SELECT TRIM( ' test ') AS
Result" db-types="SQLServer"/>
</sql-cases>
