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 19d27b98ef2 Support parsing SQL Server SELECT
pglogical.replication_set_add_all_tables('myreplicationset', sql #29175 (#30189)
19d27b98ef2 is described below
commit 19d27b98ef2a62dc7696daaad8afc7d0c71cf19b
Author: yydeng626 <[email protected]>
AuthorDate: Mon Feb 19 10:04:05 2024 +0800
Support parsing SQL Server SELECT
pglogical.replication_set_add_all_tables('myreplicationset', sql #29175 (#30189)
---
.../src/main/antlr4/imports/sqlserver/BaseRule.g4 | 6 +-
.../main/resources/case/dml/select-group-by.xml | 24 +++++++
.../src/main/resources/case/dml/select-join.xml | 77 ++++++++++++++++++++++
.../main/resources/case/dml/select-sub-query.xml | 53 +++++++++++++++
.../sql/supported/dml/select-group-by.xml | 1 +
.../resources/sql/supported/dml/select-join.xml | 1 +
.../sql/supported/dml/select-sub-query.xml | 1 +
7 files changed, 162 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 66298dd7e22..b0829e761ea 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
@@ -314,7 +314,11 @@ distinct
;
specialFunction
- : conversionFunction | charFunction | openJsonFunction | jsonFunction |
openRowSetFunction | windowFunction | approxFunction | openDatasourceFunction
+ : conversionFunction | charFunction | openJsonFunction | jsonFunction |
openRowSetFunction | windowFunction | approxFunction | openDatasourceFunction |
rowNumberFunction
+ ;
+
+rowNumberFunction
+ : ROW_NUMBER LP_ RP_ overClause
;
openDatasourceFunction
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 f8ba0e4b67f..d900b75d55b 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
@@ -961,4 +961,28 @@
<expression-item expression="MAX(max_elapsed_time_microsec)"
order-direction="DESC" start-index="145" stop-index="174" />
</order-by>
</select>
+
+ <select sql-case-id="select_group_by_count_with_tumblingwindow_function">
+ <projections start-index="7" stop-index="14">
+ <aggregation-projection expression="count(*)" type="COUNT"
start-index="7" stop-index="14" />
+ </projections>
+ <from start-index="21" stop-index="25">
+ <simple-table name="input" start-index="21" stop-index="25" />
+ </from>
+ <group-by start-index="27" stop-index="71">
+ <column-item name="clusterid" order-direction="ASC"
start-index="36" stop-index="44" />
+ <expression-item expression="tumblingwindow(minutes, 5)"
order-direction="ASC" start-index="46" stop-index="71">
+ <expr>
+ <function function-name="tumblingwindow"
text="tumblingwindow(minutes, 5)" start-index="46" stop-index="71">
+ <parameter>
+ <column name="minutes" start-index="61"
stop-index="67" />
+ </parameter>
+ <parameter>
+ <literal-expression value="5" start-index="70"
stop-index="70" />
+ </parameter>
+ </function>
+ </expr>
+ </expression-item>
+ </group-by>
+ </select>
</sql-parser-test-cases>
diff --git a/test/it/parser/src/main/resources/case/dml/select-join.xml
b/test/it/parser/src/main/resources/case/dml/select-join.xml
index fc988e0a56b..a1a05944e21 100644
--- a/test/it/parser/src/main/resources/case/dml/select-join.xml
+++ b/test/it/parser/src/main/resources/case/dml/select-join.xml
@@ -3214,4 +3214,81 @@
</expr>
</where>
</select>
+
+ <select sql-case-id="select_cross_join_sys_dm_exec_requests">
+ <projections start-index="7" stop-index="168">
+ <column-projection name="session_id" alias="SPID" start-index="7"
stop-index="24" />
+ <column-projection name="command" start-index="27" stop-index="33"
/>
+ <column-projection name="text" alias="Query" start-index="36"
stop-index="50">
+ <owner name="a" start-index="36" stop-index="36" />
+ </column-projection>
+ <column-projection name="start_time" start-index="53"
stop-index="62" />
+ <column-projection name="percent_complete" start-index="65"
stop-index="80" />
+ <expression-projection
text="dateadd(second,estimated_completion_time/1000, getdate())"
alias="estimated_completion_time" start-index="83" stop-index="168">
+ <expr>
+ <function function-name="dateadd"
text="dateadd(second,estimated_completion_time/1000, getdate())"
start-index="83" stop-index="139">
+ <parameter>
+ <column name="second" start-index="91"
stop-index="96" />
+ </parameter>
+ <parameter>
+ <binary-operation-expression start-index="98"
stop-index="127">
+ <left>
+ <column name="estimated_completion_time"
start-index="98" stop-index="122" />
+ </left>
+ <right>
+ <literal-expression value="1000"
start-index="124" stop-index="127" />
+ </right>
+ <operator>/</operator>
+ </binary-operation-expression>
+ </parameter>
+ <parameter>
+ <function function-name="getdate" text="getdate()"
start-index="130" stop-index="138" />
+ </parameter>
+ </function>
+ </expr>
+ </expression-projection>
+ </projections>
+ <from start-index="175" stop-index="245">
+ <join-table natural="false" join-type="CROSS">
+ <left>
+ <simple-table name="dm_exec_requests" alias="r"
start-index="175" stop-index="196">
+ <owner name="sys" start-index="175" stop-index="177" />
+ </simple-table>
+ </left>
+ <right>
+ <function-table table-alias="a" start-index="210"
stop-index="245">
+ <table-function function-name="sys.dm_exec_sql_text"
text="sys.dm_exec_sql_text(r.sql_handle)">
+ <parameter>
+ <column name="sql_handle" start-index="231"
stop-index="242">
+ <owner name="r" start-index="231"
stop-index="231" />
+ </column>
+ </parameter>
+ </table-function>
+ </function-table>
+ </right>
+ </join-table>
+ </from>
+ <where start-index="247" stop-index="303">
+ <expr>
+ <in-expression start-index="253" stop-index="303">
+ <left>
+ <column name="command" start-index="253"
stop-index="261">
+ <owner name="r" start-index="253" stop-index="253"
/>
+ </column>
+ </left>
+ <right>
+ <list-expression start-index="266" stop-index="303">
+ <items>
+ <literal-expression value="BACKUP DATABASE"
start-index="267" stop-index="283" />
+ </items>
+ <items>
+ <literal-expression value="RESTORE DATABASE"
start-index="285" stop-index="302" />
+ </items>
+ </list-expression>
+ </right>
+ <not>false</not>
+ </in-expression>
+ </expr>
+ </where>
+ </select>
</sql-parser-test-cases>
diff --git a/test/it/parser/src/main/resources/case/dml/select-sub-query.xml
b/test/it/parser/src/main/resources/case/dml/select-sub-query.xml
index 87b6690b263..be88402519b 100644
--- a/test/it/parser/src/main/resources/case/dml/select-sub-query.xml
+++ b/test/it/parser/src/main/resources/case/dml/select-sub-query.xml
@@ -850,4 +850,57 @@
</subquery-table>
</from>
</select>
+
+ <select sql-case-id="select_sub_query_with_rownumber">
+ <projections start-index="7" stop-index="7">
+ <shorthand-projection start-index="7" stop-index="7" />
+ </projections>
+ <from start-index="14" stop-index="110">
+ <function-table>
+ <subquery-table>
+ <subquery start-index="14" stop-index="110">
+ <select>
+ <projections start-index="22" stop-index="82">
+ <column-projection name="col1"
start-index="22" stop-index="25" />
+ <expression-projection alias="rn"
text="ROW_NUMBER () OVER (PARTITION by col1 ORDER BY col1)" start-index="28"
stop-index="82">
+ <expr>
+ <function function-name="ROW_NUMBER"
text="ROW_NUMBER () OVER (PARTITION by col1 ORDER BY col1)" start-index="28"
stop-index="79" />
+ </expr>
+ </expression-projection>
+ </projections>
+ <from start-index="89" stop-index="92">
+ <simple-table name="tab1" start-index="89"
stop-index="92" />
+ </from>
+ <where start-index="94" stop-index="109">
+ <expr>
+ <binary-operation-expression
start-index="100" stop-index="109">
+ <left>
+ <column name="col1"
start-index="100" stop-index="103" />
+ </left>
+ <right>
+ <literal-expression value="XYZ"
start-index="105" stop-index="109" />
+ </right>
+ <operator>=</operator>
+ </binary-operation-expression>
+ </expr>
+ </where>
+ </select>
+ </subquery>
+ </subquery-table>
+ </function-table>
+ </from>
+ <where start-index="112" stop-index="123">
+ <expr>
+ <binary-operation-expression start-index="118"
stop-index="123">
+ <left>
+ <column name="rn" start-index="118" stop-index="119" />
+ </left>
+ <right>
+ <literal-expression value="1" start-index="123"
stop-index="123" />
+ </right>
+ <operator>=</operator>
+ </binary-operation-expression>
+ </expr>
+ </where>
+ </select>
</sql-parser-test-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 6cf8220f523..8a010cb55dc 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
@@ -45,4 +45,5 @@
<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-case id="select_group_by_count_with_tumblingwindow_function"
value="SELECT count(*) FROM input GROUP BY clusterid,tumblingwindow(minutes,
5)" db-types="SQLServer" />
</sql-cases>
diff --git
a/test/it/parser/src/main/resources/sql/supported/dml/select-join.xml
b/test/it/parser/src/main/resources/sql/supported/dml/select-join.xml
index 43018c662c8..48937483c45 100644
--- a/test/it/parser/src/main/resources/sql/supported/dml/select-join.xml
+++ b/test/it/parser/src/main/resources/sql/supported/dml/select-join.xml
@@ -59,4 +59,5 @@
<sql-case id="select_distinct_with_inner_join_subquery" value="SELECT
DISTINCT user.FirstName, user.LastName INTO ms_user FROM user INNER JOIN
(SELECT * FROM ClickStream WHERE cs.url = 'www.microsoft.com') AS ms ON
user.user_ip = ms.user_ip" db-types="SQLServer"/>
<sql-case id="select_cross_join_sys_log_info_with_count" value="SELECT
[name], COUNT(l.database_id) AS 'vlf_count' FROM sys.databases AS s CROSS APPLY
sys.dm_db_log_info(s.database_id) AS l GROUP BY [name] HAVING
COUNT(l.database_id) > 100" db-types="SQLServer"/>
<sql-case id="select_from_sys_columns_inner_join_sys_types" value="SELECT
OBJECT_NAME(object_id) AS object_name,c.name AS
column_name,SCHEMA_NAME(t.schema_id) AS schema_name,TYPE_NAME(c.user_type_id)
AS user_type_name,c.max_length,c.precision,c.scale,c.is_nullable,c.is_computed
FROM sys.columns AS c INNER JOIN sys.types AS t ON c.user_type_id =
t.user_type_id WHERE c.user_type_id =
TYPE_ID('<schema_name.data_type_name>')" db-types="SQLServer"/>
+ <sql-case id="select_cross_join_sys_dm_exec_requests" value="SELECT
session_id as SPID, command, a.text AS Query, start_time, percent_complete,
dateadd(second,estimated_completion_time/1000, getdate()) as
estimated_completion_time FROM sys.dm_exec_requests r CROSS APPLY
sys.dm_exec_sql_text(r.sql_handle) a WHERE r.command in ('BACKUP
DATABASE','RESTORE DATABASE')" db-types="SQLServer"/>
</sql-cases>
diff --git
a/test/it/parser/src/main/resources/sql/supported/dml/select-sub-query.xml
b/test/it/parser/src/main/resources/sql/supported/dml/select-sub-query.xml
index 09e8559dfb2..9c5ce2ede08 100644
--- a/test/it/parser/src/main/resources/sql/supported/dml/select-sub-query.xml
+++ b/test/it/parser/src/main/resources/sql/supported/dml/select-sub-query.xml
@@ -36,4 +36,5 @@
<sql-case id="select_sub_query_with_cast_function" value="SELECT
[T1_1].[BusinessEntityID] AS [BusinessEntityID], [T1_1].[rowguid] AS [rowguid],
[T1_1].[ModifiedDate] AS [ModifiedDate] FROM (SELECT [T2_1].[BusinessEntityID]
AS [BusinessEntityID], [T2_1].[rowguid] AS [rowguid], [T2_1].[ModifiedDate] AS
[ModifiedDate] FROM [AdventureWorks2022].[Person].[BusinessEntity] AS T2_1
WHERE ([T2_1].[BusinessEntityID] = CAST ((17907) AS INT))) AS T1_1"
db-types="SQLServer"/>
<sql-case id="select_sub_query_with_inner_join" value="SELECT
[T1_1].[BusinessEntityID] AS [BusinessEntityID], [T1_1].[AddressID] AS
[AddressID] FROM (SELECT [T2_2].[BusinessEntityID] AS [BusinessEntityID],
[T2_1].[AddressID] AS [AddressID] FROM
[AdventureWorks2022].[Person].[BusinessEntityAddress] AS T2_1 INNER JOIN
[AdventureWorks2022].[Person].[BusinessEntity] AS T2_2 ON
([T2_1].[BusinessEntityID] = [T2_2].[BusinessEntityID])) AS T1_1"
db-types="SQLServer"/>
<sql-case id="select_sub_query_with_sum" value="SELECT [T1_1].[col] AS
[col] FROM (SELECT SUM([T2_1].[Quantity]) AS [col] FROM
[AdventureWorks2022].[Production].[ProductInventory] AS T2_1) AS T1_1"
db-types="SQLServer"/>
+ <sql-case id="select_sub_query_with_rownumber" value="SELECT * FROM
(SELECT col1, ROW_NUMBER () OVER (PARTITION by col1 ORDER BY col1) rn FROM tab1
WHERE col1='XYZ') WHERE rn = 1" db-types="SQLServer"/>
</sql-cases>
