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 150c1a49923 Support parsing SQL Server UPDATE Production.Product sql
#29183 (#29960)
150c1a49923 is described below
commit 150c1a499233956ee42aa88932dbb33895f7236a
Author: yydeng626 <[email protected]>
AuthorDate: Fri Feb 2 01:29:51 2024 -0600
Support parsing SQL Server UPDATE Production.Product sql #29183 (#29960)
* add tmp commit
* Support parsing SQL Server UPDATE Production.Product sql #29183
---
.../src/main/antlr4/imports/sqlserver/BaseRule.g4 | 3 +-
.../main/antlr4/imports/sqlserver/DMLStatement.g4 | 2 +-
.../parser/src/main/resources/case/dml/update.xml | 194 +++++++++++++++++++++
.../main/resources/sql/supported/dml/update.xml | 5 +
4 files changed, 202 insertions(+), 2 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 e73ed6dcee8..652be6cd033 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,7 +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 | YEAR
- | TIMESTAMP | TRIM | USER | RIGHT | WRITE
+ | TIMESTAMP | TRIM | USER | RIGHT
;
databaseName
@@ -288,6 +288,7 @@ simpleExpr
| variableName
| simpleExpr OR_ simpleExpr
| (PLUS_ | MINUS_ | TILDE_ | NOT_ | BINARY | DOLLAR_) simpleExpr
+ | CURRENT OF GLOBAL? expr
| ROW? LP_ expr (COMMA_ expr)* RP_
| EXISTS? subquery
| LBE_ identifier expr RBE_
diff --git
a/parser/sql/dialect/sqlserver/src/main/antlr4/imports/sqlserver/DMLStatement.g4
b/parser/sql/dialect/sqlserver/src/main/antlr4/imports/sqlserver/DMLStatement.g4
index 16e844ed23a..a5a5f091fae 100644
---
a/parser/sql/dialect/sqlserver/src/main/antlr4/imports/sqlserver/DMLStatement.g4
+++
b/parser/sql/dialect/sqlserver/src/main/antlr4/imports/sqlserver/DMLStatement.g4
@@ -52,7 +52,7 @@ update
;
assignment
- : columnName (EQ_ | DOT_) assignmentValue
+ : columnName ((PLUS_ | MINUS_ | ASTERISK_ | SLASH_ | MOD_)? EQ_ | DOT_)
assignmentValue
;
setAssignmentsClause
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 ab717e8f7ac..cc6f683b04f 100644
--- a/test/it/parser/src/main/resources/case/dml/update.xml
+++ b/test/it/parser/src/main/resources/case/dml/update.xml
@@ -1896,4 +1896,198 @@
</expr>
</where>
</update>
+
+ <update sql-case-id="update_production_product_with_like">
+ <table start-index="7" stop-index="24">
+ <simple-table name="Product" start-index="7" stop-index="24">
+ <owner name="Production" start-index="7" stop-index="16" />
+ </simple-table>
+ </table>
+ <set start-index="26" stop-index="52">
+ <assignment start-index="30" stop-index="52">
+ <column name="Color" start-index="30" stop-index="34" />
+ <assignment-value>
+ <literal-expression value="Metallic Red" start-index="38"
stop-index="52" />
+ </assignment-value>
+ </assignment>
+ </set>
+ <where start-index="54" stop-index="100">
+ <expr>
+ <binary-operation-expression start-index="60" stop-index="100">
+ <left>
+ <binary-operation-expression start-index="60"
stop-index="81">
+ <left>
+ <column name="Name" start-index="60"
stop-index="63" />
+ </left>
+ <right>
+ <list-expression start-index="70"
stop-index="81">
+ <items>
+ <literal-expression value="Road-250%"
start-index="70" stop-index="81" />
+ </items>
+ </list-expression>
+ </right>
+ <operator>LIKE</operator>
+ </binary-operation-expression>
+ </left>
+ <right>
+ <binary-operation-expression start-index="87"
stop-index="100">
+ <left>
+ <column name="Color" start-index="87"
stop-index="91" />
+ </left>
+ <right>
+ <literal-expression value="Red"
start-index="95" stop-index="100" />
+ </right>
+ <operator>=</operator>
+ </binary-operation-expression>
+ </right>
+ <operator>AND</operator>
+ </binary-operation-expression>
+ </expr>
+ </where>
+ </update>
+
+ <update sql-case-id="update_with_current_of">
+ <table start-index="7" stop-index="39">
+ <simple-table name="EmployeePayHistory" start-index="7"
stop-index="39">
+ <owner name="HumanResources" start-index="7" stop-index="20" />
+ </simple-table>
+ </table>
+ <set start-index="41" stop-index="60">
+ <assignment start-index="45" stop-index="60">
+ <column name="PayFrequency" start-index="45" stop-index="56" />
+ <assignment-value>
+ <literal-expression value="2" start-index="60"
stop-index="60" />
+ </assignment-value>
+ </assignment>
+ </set>
+ <where start-index="62" stop-index="92">
+ <expr>
+ <common-expression text="CURRENT OF complex_cursor"
start-index="68" stop-index="92" />
+ </expr>
+ </where>
+ </update>
+
+ <update sql-case-id="update_with_plus_eq_symbol">
+ <table start-index="7" stop-index="24">
+ <simple-table name="Product" start-index="7" stop-index="24">
+ <owner name="Production" start-index="7" stop-index="16" />
+ </simple-table>
+ </table>
+ <set start-index="26" stop-index="51">
+ <assignment start-index="30" stop-index="51">
+ <column name="ListPrice" start-index="30" stop-index="38" />
+ <assignment-value>
+ <column name="@NewPrice" start-index="43" stop-index="51"
/>
+ </assignment-value>
+ </assignment>
+ </set>
+ <where start-index="53" stop-index="72">
+ <expr>
+ <binary-operation-expression start-index="59" stop-index="72">
+ <left>
+ <column name="Color" start-index="59" stop-index="63"
/>
+ </left>
+ <right>
+ <literal-expression value="Red" start-index="67"
stop-index="72" />
+ </right>
+ <operator>=</operator>
+ </binary-operation-expression>
+ </expr>
+ </where>
+ </update>
+
+ <update sql-case-id="update_scrapreason_with_between_and">
+ <table start-index="7" stop-index="28">
+ <simple-table name="ScrapReason" start-index="7" stop-index="28">
+ <owner name="Production" start-index="7" stop-index="16" />
+ </simple-table>
+ </table>
+ <set start-index="30" stop-index="62">
+ <assignment start-index="34" stop-index="62">
+ <column name="Name" start-index="34" stop-index="37" />
+ <assignment-value>
+ <literal-expression value=" - tool malfunction"
start-index="42" stop-index="62" />
+ </assignment-value>
+ </assignment>
+ </set>
+ <where start-index="64" stop-index="100">
+ <expr>
+ <between-expression start-index="70" stop-index="100">
+ <left>
+ <column name="ScrapReasonID" start-index="70"
stop-index="82" />
+ </left>
+ <between-expr>
+ <literal-expression value="10" start-index="92"
stop-index="93" />
+ </between-expr>
+ <and-expr>
+ <literal-expression value="12" start-index="99"
stop-index="100" />
+ </and-expr>
+ <not>false</not>
+ </between-expression>
+ </expr>
+ </where>
+ </update>
+
+ <update sql-case-id="update_sr_with_join_subquery">
+ <table start-index="7" stop-index="8">
+ <simple-table name="sr" start-index="7" stop-index="8" />
+ </table>
+ <set start-index="10" stop-index="175">
+ <assignment start-index="14" stop-index="45">
+ <column name="Name" start-index="14" stop-index="20">
+ <owner name="sr" start-index="14" stop-index="15" />
+ </column>
+ <assignment-value>
+ <literal-expression value=" - tool malfunction"
start-index="25" stop-index="45" />
+ </assignment-value>
+ </assignment>
+ <from start-index="52" stop-index="175">
+ <join-table join-type="INNER">
+ <left>
+ <simple-table name="ScrapReason" alias="sr"
start-index="52" stop-index="79">
+ <owner name="Production" start-index="52"
stop-index="61" />
+ </simple-table>
+ </left>
+ <right>
+ <simple-table name="WorkOrder" alias="wo"
start-index="86" stop-index="111">
+ <owner name="Production" start-index="86"
stop-index="95" />
+ </simple-table>
+ </right>
+ <on-condition>
+ <binary-operation-expression start-index="116"
stop-index="175">
+ <left>
+ <binary-operation-expression start-index="116"
stop-index="150">
+ <left>
+ <column name="ScrapReasonID"
start-index="116" stop-index="131">
+ <owner name="sr" start-index="116"
stop-index="117" />
+ </column>
+ </left>
+ <right>
+ <column name="ScrapReasonID"
start-index="135" stop-index="150">
+ <owner name="wo" start-index="135"
stop-index="136" />
+ </column>
+ </right>
+ <operator>=</operator>
+ </binary-operation-expression>
+ </left>
+ <right>
+ <binary-operation-expression start-index="156"
stop-index="175">
+ <left>
+ <column name="ScrappedQty"
start-index="156" stop-index="169">
+ <owner name="wo" start-index="156"
stop-index="169" />
+ </column>
+ </left>
+ <right>
+ <literal-expression value="300"
start-index="173" stop-index="175" />
+ </right>
+ <operator>></operator>
+ </binary-operation-expression>
+ </right>
+ <operator>AND</operator>
+ </binary-operation-expression>
+ </on-condition>
+ </join-table>
+ </from>
+ </set>
+ </update>
</sql-parser-test-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 1693c49e63a..9043c94a55a 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
@@ -58,4 +58,9 @@
<sql-case id="update_with_write_function_and_output_clause" value="UPDATE
Production.Document SET DocumentSummary .WRITE (N'features',28,10) OUTPUT
deleted.DocumentSummary, inserted.DocumentSummary INTO @MyTableVar WHERE Title
= N'Front Reflector Bracket Installation'" db-types="SQLServer"/>
<sql-case id="update_with_open_rowset_function_and_subquery" value="UPDATE
Production.ProductPhoto SET ThumbNailPhoto = ( SELECT * FROM OPENROWSET(BULK
'c:Tires.jpg', SINGLE_BLOB) AS x ) WHERE ProductPhotoID = 1"
db-types="SQLServer"/>
<sql-case id="update_with_filestream" value="UPDATE Archive.dbo.Records
SET [Chart] = CAST('Xray 1' as VARBINARY(max)) WHERE [SerialNumber] = 2"
db-types="SQLServer"/>
+ <sql-case id="update_production_product_with_like" value="UPDATE
Production.Product SET Color = N'Metallic Red' WHERE Name LIKE N'Road-250%' AND
Color = N'Red'" db-types="SQLServer"/>
+ <sql-case id="update_with_current_of" value="UPDATE
HumanResources.EmployeePayHistory SET PayFrequency = 2 WHERE CURRENT OF
complex_cursor" db-types="SQLServer"/>
+ <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-cases>
