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 ebad4d18e1c Support parsing SQL Server INSERT INTO sql #29193 (#30002)
ebad4d18e1c is described below
commit ebad4d18e1c39c0043fcc7c2c55867de3e6765bf
Author: yydeng626 <[email protected]>
AuthorDate: Thu Feb 8 13:51:11 2024 +0800
Support parsing SQL Server INSERT INTO sql #29193 (#30002)
---
.../parser/src/main/resources/case/dml/insert.xml | 76 +++++++++++
.../parser/src/main/resources/case/dml/select.xml | 151 +++++++++++++++++++++
.../parser/src/main/resources/case/dml/update.xml | 29 ++++
.../main/resources/sql/supported/dml/insert.xml | 3 +
.../main/resources/sql/supported/dml/select.xml | 3 +
.../main/resources/sql/supported/dml/update.xml | 1 +
6 files changed, 263 insertions(+)
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 0ab3f1d1e9e..d12092c498e 100644
--- a/test/it/parser/src/main/resources/case/dml/insert.xml
+++ b/test/it/parser/src/main/resources/case/dml/insert.xml
@@ -3635,4 +3635,80 @@
</value>
</values>
</insert>
+
+ <insert sql-case-id="insert_with_currency_value_2">
+ <table name="Employees" start-index="12" stop-index="27"
start-delimiter="[" end-delimiter="]">
+ <owner name="HR" start-index="12" stop-index="15"
start-delimiter="[" end-delimiter="]" />
+ </table>
+ <columns start-index="28" stop-index="69">
+ <column name="SSN" start-index="29" stop-index="33"
start-delimiter="[" end-delimiter="]" />
+ <column name="FirstName" start-index="36" stop-index="46"
start-delimiter="[" end-delimiter="]" />
+ <column name="LastName" start-index="49" stop-index="58"
start-delimiter="[" end-delimiter="]" />
+ <column name="Salary" start-index="61" stop-index="68"
start-delimiter="[" end-delimiter="]" />
+ </columns>
+ <values>
+ <value>
+ <assignment-value>
+ <literal-expression value="990-00-6818" start-index="79"
stop-index="91" />
+ </assignment-value>
+ <assignment-value>
+ <literal-expression value="Kim" start-index="94"
stop-index="99" />
+ </assignment-value>
+ <assignment-value>
+ <literal-expression value="Abercrombie" start-index="102"
stop-index="115" />
+ </assignment-value>
+ <assignment-value>
+ <common-expression text="$55415" start-index="118"
stop-index="123" />
+ </assignment-value>
+ </value>
+ </values>
+ </insert>
+
+ <insert sql-case-id="insert_with_cross_database_values">
+ <table name="SourceTable" start-index="7" stop-index="42"
start-delimiter="[" end-delimiter="]">
+ <owner name="dbo" start-index="24" stop-index="28"
start-delimiter="[" end-delimiter="]">
+ <owner name="SourceDatabase" start-index="7" stop-index="22"
start-delimiter="[" end-delimiter="]" />
+ </owner>
+ </table>
+ <columns start-index="43" stop-index="43" />
+ <values>
+ <value>
+ <assignment-value>
+ <literal-expression value="1" start-index="52"
stop-index="52" />
+ </assignment-value>
+ <assignment-value>
+ <literal-expression value="Bob" start-index="55"
stop-index="60" />
+ </assignment-value>
+ </value>
+ <value>
+ <assignment-value>
+ <literal-expression value="2" start-index="64"
stop-index="64" />
+ </assignment-value>
+ <assignment-value>
+ <literal-expression value="Susan" start-index="67"
stop-index="74" />
+ </assignment-value>
+ </value>
+ </values>
+ </insert>
+
+ <insert sql-case-id="insert_with_cross_database_select">
+ <table name="DestTable_InMem" start-index="7" stop-index="51"
start-delimiter="[" end-delimiter="]">
+ <owner name="dbo" start-index="29" stop-index="33"
start-delimiter="[" end-delimiter="]">
+ <owner name="DestinationDatabase" start-index="7"
stop-index="27" start-delimiter="[" end-delimiter="]" />
+ </owner>
+ </table>
+ <columns start-index="52" stop-index="52" />
+ <select>
+ <projections start-index="60" stop-index="60">
+ <shorthand-projection start-index="60" stop-index="60"/>
+ </projections>
+ <from>
+ <simple-table name="SourceTable" start-index="67"
stop-index="102" start-delimiter="[" end-delimiter="]">
+ <owner name="dbo" start-index="84" stop-index="88"
start-delimiter="[" end-delimiter="]">
+ <owner name="SourceDatabase" start-index="67"
stop-index="82" start-delimiter="[" end-delimiter="]" />
+ </owner>
+ </simple-table>
+ </from>
+ </select>
+ </insert>
</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 451ce16c621..80b7bcfcfa2 100644
--- a/test/it/parser/src/main/resources/case/dml/select.xml
+++ b/test/it/parser/src/main/resources/case/dml/select.xml
@@ -8426,4 +8426,155 @@
</simple-table>
</from>
</select>
+
+ <select sql-case-id="select_dm_exec_requests">
+ <from start-index="461" stop-index="556">
+ <join-table join-type="CROSS">
+ <left>
+ <simple-table name="dm_exec_requests" alias="req"
start-index="461" stop-index="493" start-delimiter="[" end-delimiter="]">
+ <owner name="sys" start-index="461" stop-index="465"
start-delimiter="[" end-delimiter="]" />
+ </simple-table>
+ </left>
+ <right>
+ <function-table table-alias="ST">
+ <table-function function-name="[sys].dm_exec_sql_text"
text="[sys].dm_exec_sql_text([req].[sql_handle])" start-index="507"
stop-index="548">
+ <parameter>
+ <column name="sql_handle" start-index="530"
stop-index="547">
+ <owner name="req" start-index="530"
stop-index="534" />
+ </column>
+ </parameter>
+ </table-function>
+ </function-table>
+ </right>
+ </join-table>
+ </from>
+ <projections start-index="7" stop-index="454">
+ <column-projection name="session_id" start-index="7"
stop-index="24" start-delimiter="[" end-delimiter="]">
+ <owner name="req" start-index="7" stop-index="11"
start-delimiter="[" end-delimiter="]" />
+ </column-projection>
+ <column-projection name="start_time" start-index="26"
stop-index="43" start-delimiter="[" end-delimiter="]">
+ <owner name="req" start-index="26" stop-index="30"
start-delimiter="[" end-delimiter="]" />
+ </column-projection>
+ <column-projection name="cpu_time" alias="cpu_time_ms"
start-index="45" stop-index="77" start-delimiter="[" end-delimiter="]">
+ <owner name="req" start-index="45" stop-index="49"
start-delimiter="[" end-delimiter="]" />
+ </column-projection>
+ <expression-projection text="OBJECT_NAME([ST].[objectid],
[ST].[dbid])" alias="ObjectName" start-index="79" stop-index="135">
+ <expr>
+ <function function-name="OBJECT_NAME"
text="OBJECT_NAME([ST].[objectid], [ST].[dbid])" start-index="79"
stop-index="119">
+ <parameter>
+ <column name="objectid" start-index="91"
stop-index="105" start-delimiter="[" end-delimiter="]">
+ <owner name="ST" start-index="91"
stop-index="94" start-delimiter="[" end-delimiter="]" />
+ </column>
+ </parameter>
+ <parameter>
+ <column name="dbid" start-index="108"
stop-index="118" start-delimiter="[" end-delimiter="]">
+ <owner name="ST" start-index="108"
stop-index="111" start-delimiter="[" end-delimiter="]" />
+ </column>
+ </parameter>
+ </function>
+ </expr>
+ </expression-projection>
+ <expression-projection
text="SUBSTRING(REPLACE(REPLACE(SUBSTRING([ST].[text],
+ ([req].[statement_start_offset] / 2) + 1, ((CASE
[req].[statement_end_offset] WHEN -1 THEN DATALENGTH([ST].[text]) ELSE
[req].[statement_end_offset] END - [req].[statement_start_offset]) / 2) + 1 ),
CHAR(10), ' ' ), CHAR(13), ' ' ), 1, 512 )" alias="statement_text"
start-index="137" stop-index="454">
+ <expr>
+ <function function-name="SUBSTRING"
text="SUBSTRING(REPLACE(REPLACE(SUBSTRING([ST].[text],
+ ([req].[statement_start_offset] / 2) + 1, ((CASE
[req].[statement_end_offset] WHEN -1 THEN DATALENGTH([ST].[text]) ELSE
[req].[statement_end_offset] END - [req].[statement_start_offset]) / 2) + 1 ),
CHAR(10), ' ' ), CHAR(13), ' ' ), 1, 512 )" start-index="137" stop-index="430">
+ <parameter>
+ <function function-name="REPLACE"
text="REPLACE(REPLACE(SUBSTRING([ST].[text],
+ ([req].[statement_start_offset] / 2) + 1, ((CASE
[req].[statement_end_offset] WHEN -1 THEN DATALENGTH([ST].[text]) ELSE
[req].[statement_end_offset] END - [req].[statement_start_offset]) / 2) + 1 ),
CHAR(10), ' ' ), CHAR(13), ' ' )" start-index="147" stop-index="420">
+ <parameter>
+ <function function-name="REPLACE"
text="REPLACE(SUBSTRING([ST].[text],
+ ([req].[statement_start_offset] / 2) + 1, ((CASE
[req].[statement_end_offset] WHEN -1 THEN DATALENGTH([ST].[text]) ELSE
[req].[statement_end_offset] END - [req].[statement_start_offset]) / 2) + 1 ),
CHAR(10), ' ' )" start-index="155" stop-index="403">
+ <parameter>
+ <function
function-name="SUBSTRING" text="SUBSTRING([ST].[text],
+ ([req].[statement_start_offset] / 2) + 1, ((CASE
[req].[statement_end_offset] WHEN -1 THEN DATALENGTH([ST].[text]) ELSE
[req].[statement_end_offset] END - [req].[statement_start_offset]) / 2) + 1 )"
start-index="163" stop-index="386">
+ <parameter>
+ <column name="text"
start-index="173" stop-index="183" start-delimiter="[" end-delimiter="]">
+ <owner name="ST"
start-index="173" stop-index="176" start-delimiter="[" end-delimiter="]" />
+ </column>
+ </parameter>
+ <parameter>
+
<binary-operation-expression text="([req].[statement_start_offset] / 2) + 1"
start-index="190" stop-index="229">
+ <left>
+
<binary-operation-expression text="[req].[statement_start_offset] / 2"
start-index="191" stop-index="224">
+ <left>
+ <column
name="statement_start_offset" start-index="191" stop-index="220"
start-delimiter="[" end-delimiter="]">
+ <owner
name="req" start-index="191" stop-index="195" start-delimiter="["
end-delimiter="]" />
+ </column>
+ </left>
+ <right>
+
<literal-expression value="2" start-index="224" stop-index="224" />
+ </right>
+
<operator>/</operator>
+
</binary-operation-expression>
+ </left>
+ <operator>+</operator>
+ <right>
+
<literal-expression value="1" start-index="229" stop-index="229" />
+ </right>
+
</binary-operation-expression>
+ </parameter>
+ <parameter>
+
<binary-operation-expression text="((CASE [req].[statement_end_offset] WHEN -1
THEN DATALENGTH([ST].[text]) ELSE [req].[statement_end_offset] END -
[req].[statement_start_offset]) / 2) + 1" start-index="232" stop-index="384">
+ <left>
+
<binary-operation-expression text="(CASE [req].[statement_end_offset] WHEN -1
THEN DATALENGTH([ST].[text]) ELSE [req].[statement_end_offset] END -
[req].[statement_start_offset]) / 2" start-index="233" stop-index="379">
+ <left>
+
<binary-operation-expression text="CASE [req].[statement_end_offset] WHEN -1
THEN DATALENGTH([ST].[text]) ELSE [req].[statement_end_offset] END -
[req].[statement_start_offset]" start-index="234" stop-index="374">
+ <left>
+
<common-expression
text="CASE[req].[statement_end_offset]WHEN-1THENDATALENGTH([ST].[text])ELSE[req].[statement_end_offset]END"
start-index="234" stop-index="341" />
+ </left>
+ <right>
+
<column name="statement_start_offset" start-index="345" stop-index="374"
start-delimiter="[" end-delimiter="]">
+
<owner name="req" start-index="345" stop-index="349" start-delimiter="["
end-delimiter="]" />
+
</column>
+
</right>
+
<operator>-</operator>
+
</binary-operation-expression>
+ </left>
+ <right>
+
<literal-expression value="2" start-index="379" stop-index="379" />
+ </right>
+
<operator>/</operator>
+
</binary-operation-expression>
+ </left>
+ <operator>+</operator>
+ <right>
+
<literal-expression value="1" start-index="384" stop-index="384" />
+ </right>
+
</binary-operation-expression>
+ </parameter>
+ </function>
+ </parameter>
+ <parameter>
+ <function function-name="CHAR"
text="CHAR(10)" start-index="389" stop-index="396" />
+ </parameter>
+ <parameter>
+ <literal-expression value=" "
start-index="399" stop-index="401" />
+ </parameter>
+ </function>
+ </parameter>
+ <parameter>
+ <function function-name="CHAR"
text="CHAR(13)" start-index="406" stop-index="413" />
+ </parameter>
+ <parameter>
+ <literal-expression value=" "
start-index="416" stop-index="418" />
+ </parameter>
+ </function>
+ </parameter>
+ <parameter>
+ <literal-expression value="1" start-index="423"
stop-index="423"/>
+ </parameter>
+ <parameter>
+ <literal-expression value="512" start-index="426"
stop-index="428"/>
+ </parameter>
+ </function>
+ </expr>
+ </expression-projection>
+ </projections>
+ <order-by>
+ <column-item name="cpu_time" start-index="567" stop-index="582"
order-direction="DESC" start-delimiter="[" end-delimiter="]">
+ <owner name="req" start-index="567" stop-index="571"
start-delimiter="[" end-delimiter="]" />
+ </column-item>
+ </order-by>
+ </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 e06e4a0eb7e..722e05076a6 100644
--- a/test/it/parser/src/main/resources/case/dml/update.xml
+++ b/test/it/parser/src/main/resources/case/dml/update.xml
@@ -2090,4 +2090,33 @@
</from>
</set>
</update>
+
+ <update sql-case-id="update_with_print">
+ <table start-index="7" stop-index="29">
+ <simple-table name="Employee" start-index="7" stop-index="29">
+ <owner name="HumanResources" start-index="7" stop-index="20" />
+ </simple-table>
+ </table>
+ <set start-index="31" stop-index="57">
+ <assignment start-index="35" stop-index="57">
+ <column name="JobTitle" start-index="35" stop-index="42" />
+ <assignment-value>
+ <literal-expression value="Executive" start-index="46"
stop-index="57" />
+ </assignment-value>
+ </assignment>
+ </set>
+ <where start-index="59" stop-index="92">
+ <expr>
+ <binary-operation-expression start-index="65" stop-index="92">
+ <left>
+ <column name="NationalIDNumber" start-index="65"
stop-index="80" />
+ </left>
+ <right>
+ <literal-expression value="123456789" start-index="84"
stop-index="92" />
+ </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/insert.xml
b/test/it/parser/src/main/resources/sql/supported/dml/insert.xml
index 39b2f27aecb..d230ffc6f6b 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
@@ -138,4 +138,7 @@
<sql-case id="insert_into_player_with_compress_function" value="INSERT
INTO player ( name,surname,info) VALUES (N'Ovidiu',
N'Cracium',COMPRESS(N'{"sport":"Tennis","age":
28,"rank":1,"points":15258, "turn":17}'))"
db-types="SQLServer"/>
<sql-case id="insert_into_tmp_with_exec" value="INSERT INTO #temp (id,
job_name, job_id, dynamic_filter_login,dynamic_filter_hostname,
dynamic_snapshot_location,frequency_type, frequency_interval,
frequency_subday_type,frequency_subday_interval, frequency_relative_interval,
frequency_recurrence_factor, active_start_date, active_end_date,
active_start_time,active_end_time) EXEC sp_helpdynamicsnapshot_job"
db-types="SQLServer"/>
<sql-case id="insert_into_player_with_compress_function_2" value="INSERT
INTO player (name,surname,info) VALUES (N'Michael',N'Raheem',COMPRESS(@info))"
db-types="SQLServer"/>
+ <sql-case id="insert_with_currency_value_2" value="INSERT INTO
[HR].[Employees]([SSN], [FirstName], [LastName], [Salary]) VALUES
('990-00-6818', N'Kim', N'Abercrombie', $55415)" db-types="SQLServer"/>
+ <sql-case id="insert_with_cross_database_values" value="INSERT
[SourceDatabase].[dbo].[SourceTable] VALUES (1, N'Bob'),(2, N'Susan')"
db-types="SQLServer"/>
+ <sql-case id="insert_with_cross_database_select" value="INSERT
[DestinationDatabase].[dbo].[DestTable_InMem] SELECT * FROM
[SourceDatabase].[dbo].[SourceTable]" 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 b44a53f9935..22fd7b5eef1 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
@@ -258,4 +258,7 @@
<sql-case id="select_from_physical_stats_function" value="SELECT
page_count, compressed_page_count, forwarded_record_count, * FROM
sys.dm_db_index_physical_stats(db_id(), object_id('t3'), NULL, NULL,
'SAMPLED')" db-types="SQLServer"/>
<sql-case id="select_from_msdb_default_schema" value="SELECT
backup_size/compressed_backup_size FROM msdb..backupset" db-types="SQLServer"/>
<sql-case id="select_from_database_files_2" value="SELECT file_id, name,
type_desc, physical_name, size, max_size FROM sys.database_files"
db-types="SQLServer"/>
+ <sql-case id="select_dm_exec_requests" value="SELECT
[req].[session_id],[req].[start_time],[req].[cpu_time] AS
[cpu_time_ms],OBJECT_NAME([ST].[objectid], [ST].[dbid]) AS
[ObjectName],SUBSTRING(REPLACE(REPLACE(SUBSTRING([ST].[text],
+ ([req].[statement_start_offset] / 2) + 1, ((CASE
[req].[statement_end_offset] WHEN -1 THEN DATALENGTH([ST].[text]) ELSE
[req].[statement_end_offset] END - [req].[statement_start_offset]) / 2) + 1 ),
CHAR(10), ' ' ), CHAR(13), ' ' ), 1, 512 )
+ AS [statement_text] FROM [sys].[dm_exec_requests] AS [req] CROSS APPLY
[sys].dm_exec_sql_text([req].[sql_handle]) AS [ST] ORDER BY [req].[cpu_time]
DESC" 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 9043c94a55a..7a66ca8a754 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
@@ -63,4 +63,5 @@
<sql-case id="update_with_plus_eq_symbol" value="UPDATE Production.Product
SET ListPrice += @NewPrice WHERE Color = N'Red'" db-types="SQLServer"/>
<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-cases>
