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/02/19 02:04:11 UTC
(shardingsphere) branch master updated: Support parsing SQL Server SELECT pglogical.replication_set_add_all_tables('myreplicationset', sql #29175 (#30189)
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 <yy...@gmail.com>
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>