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 9a7e1403dd7 Support parsing SQL Server INSERT INTO sql (#30161)
9a7e1403dd7 is described below
commit 9a7e1403dd7327b53b8ee11c1d51517278d34af8
Author: LotusMoon <[email protected]>
AuthorDate: Sun Feb 18 11:41:21 2024 +0800
Support parsing SQL Server INSERT INTO sql (#30161)
---
.../src/main/resources/case/dml/select-join.xml | 43 +++++++
.../parser/src/main/resources/case/dml/update.xml | 137 +++++++++++++++++++++
.../resources/sql/supported/dml/select-join.xml | 1 +
.../main/resources/sql/supported/dml/update.xml | 3 +
4 files changed, 184 insertions(+)
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 4fa8046cbd4..c9c8e1c09d9 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
@@ -3069,4 +3069,47 @@
</join-table>
</from>
</select>
+
+ <select sql-case-id="select_cross_join_sys_log_info_with_count">
+ <projections start-index="7" stop-index="49">
+ <column-projection name="name" start-index="7" stop-index="12"
start-delimiter="[" end-delimiter="]"/>
+ <aggregation-projection type="COUNT" alias="vlf_count"
start-index="15" stop-index="34" expression="COUNT(l.database_id)"/>
+ </projections>
+ <from>
+ <join-table join-type="CROSS">
+ <left>
+ <simple-table name="databases" start-index="56"
stop-index="73" alias="s">
+ <owner name="sys" start-index="56" stop-index="58"/>
+ </simple-table>
+ </left>
+ <right>
+ <function-table start-index="87" stop-index="119"
table-alias="l">
+ <table-function
text="sys.dm_db_log_info(s.database_id)" function-name="sys.dm_db_log_info">
+ <parameter>
+ <column name="database_id" start-index="106"
stop-index="118">
+ <owner name="s" start-index="106"
stop-index="106"/>
+ </column>
+ </parameter>
+ </table-function>
+ </function-table>
+ </right>
+ </join-table>
+ </from>
+ <group-by>
+ <column-item name="name" start-index="135" stop-index="140"
order-direction="ASC" start-delimiter="[" end-delimiter="]"/>
+ </group-by>
+ <having start-index="142" stop-index="174">
+ <expr>
+ <binary-operation-expression start-index="149"
stop-index="174">
+ <left>
+ <aggregation-projection type="COUNT"
expression="COUNT(l.database_id)" start-index="149" stop-index="168"/>
+ </left>
+ <right>
+ <literal-expression value="100" start-index="172"
stop-index="174"/>
+ </right>
+ <operator>></operator>
+ </binary-operation-expression>
+ </expr>
+ </having>
+ </select>
</sql-parser-test-cases>
diff --git a/test/it/parser/src/main/resources/case/dml/update.xml
b/test/it/parser/src/main/resources/case/dml/update.xml
index 722e05076a6..075f6a975ec 100644
--- a/test/it/parser/src/main/resources/case/dml/update.xml
+++ b/test/it/parser/src/main/resources/case/dml/update.xml
@@ -2119,4 +2119,141 @@
</expr>
</where>
</update>
+
+ <update sql-case-id="update_with_inner_join">
+ <table start-index="7" stop-index="16">
+ <simple-table name="Table2" start-index="7" stop-index="16">
+ <owner name="dbo" start-index="7" stop-index="9"/>
+ </simple-table>
+ </table>
+ <set start-index="18" stop-index="149">
+ <assignment start-index="22" stop-index="72">
+ <column name="ColB" start-index="22" stop-index="36">
+ <owner name="Table2" start-index="26" stop-index="31">
+ <owner name="dbo" start-index="22" stop-index="24"/>
+ </owner>
+ </column>
+ <assignment-value>
+ <binary-operation-expression start-index="40"
stop-index="72">
+ <left>
+ <column name="ColB" start-index="40"
stop-index="54">
+ <owner name="Table2" start-index="44"
stop-index="49">
+ <owner name="dbo" start-index="40"
stop-index="42"/>
+ </owner>
+ </column>
+ </left>
+ <right>
+ <column name="ColB" start-index="58"
stop-index="72">
+ <owner name="Table1" start-index="62"
stop-index="67">
+ <owner name="dbo" start-index="58"
stop-index="60"/>
+ </owner>
+ </column>
+ </right>
+ <operator>+</operator>
+ </binary-operation-expression>
+ </assignment-value>
+ </assignment>
+ <from start-index="79" stop-index="149">
+ <join-table join-type="INNER">
+ <left>
+ <simple-table name="Table2" start-index="79"
stop-index="88">
+ <owner name="dbo" start-index="79"
stop-index="81"/>
+ </simple-table>
+ </left>
+ <right>
+ <simple-table name="Table1" start-index="101"
stop-index="110">
+ <owner name="dbo" start-index="101"
stop-index="103"/>
+ </simple-table>
+ </right>
+ <on-condition>
+ <binary-operation-expression start-index="116"
stop-index="148">
+ <left>
+ <column name="ColA" start-index="116"
stop-index="130">
+ <owner name="Table2" start-index="120"
stop-index="125">
+ <owner name="dbo" start-index="116"
stop-index="118"/>
+ </owner>
+ </column>
+ </left>
+ <right>
+ <column name="ColA" start-index="134"
stop-index="148">
+ <owner name="Table1" start-index="138"
stop-index="143">
+ <owner name="dbo" start-index="134"
stop-index="136"/>
+ </owner>
+ </column>
+ </right>
+ <operator>=</operator>
+ </binary-operation-expression>
+ </on-condition>
+ </join-table>
+ </from>
+ </set>
+ </update>
+
+ <update sql-case-id="update_with_current_of_abc">
+ <table start-index="7" stop-index="16">
+ <simple-table name="Table1" start-index="7" stop-index="16">
+ <owner name="dbo" start-index="7" stop-index="9"/>
+ </simple-table>
+ </table>
+ <set start-index="18" stop-index="49">
+ <assignment start-index="22" stop-index="33">
+ <column name="c2" start-index="22" stop-index="23"/>
+ <assignment-value>
+ <binary-operation-expression start-index="27"
stop-index="33">
+ <left>
+ <column name="c2" start-index="27"
stop-index="28"/>
+ </left>
+ <right>
+ <column name="d2" start-index="32"
stop-index="33"/>
+ </right>
+ <operator>+</operator>
+ </binary-operation-expression>
+ </assignment-value>
+ </assignment>
+ <from start-index="40" stop-index="49">
+ <simple-table name="Table2" start-index="40" stop-index="49">
+ <owner name="dbo" start-index="40" stop-index="42"/>
+ </simple-table>
+ </from>
+ </set>
+ <where start-index="51" stop-index="70">
+ <expr>
+ <common-expression text="CURRENT OF abc" start-index="57"
stop-index="70"/>
+ </expr>
+ </where>
+ </update>
+
+ <update sql-case-id="update_with_location_setXY">
+ <table start-index="7" stop-index="12">
+ <simple-table name="Cities" start-index="7" stop-index="12"/>
+ </table>
+ <set start-index="14" stop-index="43">
+ <assignment start-index="14" stop-index="43">
+ <column name="Location" start-index="18" stop-index="25"/>
+ <assignment-value>
+ <function text="SetXY(23.5, 23.5)" function-name="SetXY"
start-index="27" stop-index="43">
+ <parameter>
+ <literal-expression value="23.5" start-index="33"
stop-index="36"/>
+ </parameter>
+ <parameter>
+ <literal-expression value="23.5" start-index="39"
stop-index="42"/>
+ </parameter>
+ </function>
+ </assignment-value>
+ </assignment>
+ </set>
+ <where start-index="45" stop-index="68">
+ <expr>
+ <binary-operation-expression start-index="51" stop-index="68">
+ <left>
+ <column name="Name" start-index="51" stop-index="54"/>
+ </left>
+ <right>
+ <literal-expression value="Anchorage" start-index="58"
stop-index="68"/>
+ </right>
+ <operator>=</operator>
+ </binary-operation-expression>
+ </expr>
+ </where>
+ </update>
</sql-parser-test-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 05b56996d35..b2a678899ec 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
@@ -57,4 +57,5 @@
<sql-case id="select_sales_order_record_with_cross_apply" value="SELECT
Tab.Id,SalesOrderJsonData.Customer,SalesOrderJsonData.Date FROM
SalesOrderRecord AS Tab CROSS APPLY OPENJSON(Tab.json, N'$.Orders.OrdersArray')
WITH (Number VARCHAR(200) N'$.Order.Number',Date DATETIME
N'$.Order.Date',Customer VARCHAR(200) N'$.AccountNumber',Quantity INT
N'$.Item.Quantity') AS SalesOrderJsonData WHERE JSON_VALUE(Tab.json,
'$.Status') = N'Closed' ORDER BY JSON_VALUE(Tab.json, '$.Group'),Tab.DateMo
[...]
<sql-case id="select_sys_databases_join_sys_logins" value="SELECT d.name,
d.owner_sid, sl.name FROM sys.databases AS d JOIN sys.sql_logins AS sl ON
d.owner_sid = sl.sid" db-types="SQLServer"/>
<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-cases>
diff --git a/test/it/parser/src/main/resources/sql/supported/dml/update.xml
b/test/it/parser/src/main/resources/sql/supported/dml/update.xml
index 7a66ca8a754..9aabc069432 100644
--- a/test/it/parser/src/main/resources/sql/supported/dml/update.xml
+++ b/test/it/parser/src/main/resources/sql/supported/dml/update.xml
@@ -64,4 +64,7 @@
<sql-case id="update_scrapreason_with_between_and" value="UPDATE
Production.ScrapReason SET Name += ' - tool malfunction' WHERE ScrapReasonID
BETWEEN 10 and 12" db-types="SQLServer"/>
<sql-case id="update_sr_with_join_subquery" value="UPDATE sr SET sr.Name
+= ' - tool malfunction' FROM Production.ScrapReason AS sr JOIN
Production.WorkOrder AS wo ON sr.ScrapReasonID = wo.ScrapReasonID AND
wo.ScrappedQty > 300" db-types="SQLServer"/>
<sql-case id="update_with_print" value="UPDATE HumanResources.Employee SET
JobTitle = N'Executive' WHERE NationalIDNumber = 123456789 IF @@ROWCOUNT = 0
PRINT 'Warning: No rows were updated'" db-types="SQLServer" />
+ <sql-case id="update_with_inner_join" value="UPDATE dbo.Table2 SET
dbo.Table2.ColB = dbo.Table2.ColB + dbo.Table1.ColB FROM dbo.Table2 INNER JOIN
dbo.Table1 ON (dbo.Table2.ColA = dbo.Table1.ColA)" db-types="SQLServer"/>
+ <sql-case id="update_with_current_of_abc" value="UPDATE dbo.Table1 SET c2
= c2 + d2 FROM dbo.Table2 WHERE CURRENT OF abc" db-types="SQLServer"/>
+ <sql-case id="update_with_location_setXY" value="UPDATE Cities SET
Location.SetXY(23.5, 23.5) WHERE Name = 'Anchorage'" db-types="SQLServer"/>
</sql-cases>
