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 ce30c16a460 Support parsing SQL Server delimited brackets identifier
(#30184)
ce30c16a460 is described below
commit ce30c16a46054dc7465add616c6bcd2051a419f3
Author: LotusMoon <[email protected]>
AuthorDate: Mon Feb 19 08:00:31 2024 +0800
Support parsing SQL Server delimited brackets identifier (#30184)
---
.../src/main/antlr4/imports/sqlserver/Literals.g4 | 2 +-
.../parser/src/main/resources/case/dml/insert.xml | 42 ++++++++
.../src/main/resources/case/dml/select-join.xml | 102 ++++++++++++++++++
.../parser/src/main/resources/case/dml/select.xml | 119 +++++++++++++++++++++
.../main/resources/sql/supported/dml/insert.xml | 1 +
.../resources/sql/supported/dml/select-join.xml | 1 +
.../main/resources/sql/supported/dml/select.xml | 1 +
7 files changed, 267 insertions(+), 1 deletion(-)
diff --git
a/parser/sql/dialect/sqlserver/src/main/antlr4/imports/sqlserver/Literals.g4
b/parser/sql/dialect/sqlserver/src/main/antlr4/imports/sqlserver/Literals.g4
index 6c44c58ac19..a013bde69be 100644
--- a/parser/sql/dialect/sqlserver/src/main/antlr4/imports/sqlserver/Literals.g4
+++ b/parser/sql/dialect/sqlserver/src/main/antlr4/imports/sqlserver/Literals.g4
@@ -24,7 +24,7 @@ IDENTIFIER_
;
DELIMITED_IDENTIFIER_
- : (LBT_ | DQ_) [a-zA-Z0-9@$#_,.\-\\/\u0080-\uFFFF ]+ (DQ_ | RBT_)
+ : (LBT_ | DQ_) LP_? [a-zA-Z0-9@$#_,.\-\\/\u0080-\uFFFF ]+ RP_? (DQ_ | RBT_)
;
STRING_
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 adaa95a4eb6..c61be4d51a3 100644
--- a/test/it/parser/src/main/resources/case/dml/insert.xml
+++ b/test/it/parser/src/main/resources/case/dml/insert.xml
@@ -3898,4 +3898,46 @@
</value>
</values>
</insert>
+
+ <insert sql-case-id="insert_into_with_multi_nchar">
+ <table name="UnitMeasure" start-index="12" stop-index="33">
+ <owner name="Production" start-index="12" stop-index="21"/>
+ </table>
+ <columns start-index="34" stop-index="34"/>
+ <values>
+ <value>
+ <assignment-value>
+ <literal-expression value="FT2" start-index="43"
stop-index="48"/>
+ </assignment-value>
+ <assignment-value>
+ <literal-expression value="Square Feet " start-index="51"
stop-index="65"/>
+ </assignment-value>
+ <assignment-value>
+ <literal-expression value="20080923" start-index="68"
stop-index="77"/>
+ </assignment-value>
+ </value>
+ <value>
+ <assignment-value>
+ <literal-expression value="Y" start-index="82"
stop-index="85"/>
+ </assignment-value>
+ <assignment-value>
+ <literal-expression value="Yards" start-index="88"
stop-index="95"/>
+ </assignment-value>
+ <assignment-value>
+ <literal-expression value="20080923" start-index="98"
stop-index="107"/>
+ </assignment-value>
+ </value>
+ <value>
+ <assignment-value>
+ <literal-expression value="Y3" start-index="112"
stop-index="116"/>
+ </assignment-value>
+ <assignment-value>
+ <literal-expression value="Cubic Yards" start-index="119"
stop-index="132"/>
+ </assignment-value>
+ <assignment-value>
+ <literal-expression value="20080923" start-index="135"
stop-index="144"/>
+ </assignment-value>
+ </value>
+ </values>
+ </insert>
</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 c9c8e1c09d9..fc988e0a56b 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
@@ -3112,4 +3112,106 @@
</expr>
</having>
</select>
+
+ <select sql-case-id="select_from_sys_columns_inner_join_sys_types">
+ <projections start-index="7" stop-index="210">
+ <expression-projection text="OBJECT_NAME(object_id)"
start-index="7" stop-index="43" alias="object_name">
+ <expr>
+ <function text="OBJECT_NAME(object_id)" start-index="7"
stop-index="28" function-name="OBJECT_NAME">
+ <parameter>
+ <column name="object_id" start-index="19"
stop-index="27"/>
+ </parameter>
+ </function>
+ </expr>
+ </expression-projection>
+ <column-projection name="name" start-index="45" stop-index="65"
alias="column_name">
+ <owner name="c" start-index="45" stop-index="45"/>
+ </column-projection>
+ <expression-projection text="SCHEMA_NAME(t.schema_id)"
start-index="67" stop-index="105" alias="schema_name">
+ <expr>
+ <function text="SCHEMA_NAME(t.schema_id)" start-index="67"
stop-index="90" function-name="SCHEMA_NAME">
+ <parameter>
+ <column name="schema_id" start-index="79"
stop-index="89">
+ <owner name="t" start-index="79"
stop-index="79"/>
+ </column>
+ </parameter>
+ </function>
+ </expr>
+ </expression-projection>
+ <expression-projection text="TYPE_NAME(c.user_type_id)"
start-index="107" stop-index="149" alias="user_type_name">
+ <expr>
+ <function text="TYPE_NAME(c.user_type_id)"
start-index="107" stop-index="131" function-name="TYPE_NAME">
+ <parameter>
+ <column name="user_type_id" start-index="117"
stop-index="130">
+ <owner name="c" start-index="117"
stop-index="117"/>
+ </column>
+ </parameter>
+ </function>
+ </expr>
+ </expression-projection>
+ <column-projection name="max_length" start-index="151"
stop-index="162">
+ <owner name="c" start-index="151" stop-index="151"/>
+ </column-projection>
+ <column-projection name="precision" start-index="164"
stop-index="174">
+ <owner name="c" start-index="164" stop-index="164"/>
+ </column-projection>
+ <column-projection name="scale" start-index="176" stop-index="182">
+ <owner name="c" start-index="176" stop-index="176"/>
+ </column-projection>
+ <column-projection name="is_nullable" start-index="184"
stop-index="196">
+ <owner name="c" start-index="184" stop-index="184"/>
+ </column-projection>
+ <column-projection name="is_computed" start-index="198"
stop-index="210">
+ <owner name="c" start-index="198" stop-index="198"/>
+ </column-projection>
+ </projections>
+ <from start-index="217" stop-index="293">
+ <join-table join-type="INNER">
+ <left>
+ <simple-table name="columns" start-index="217"
stop-index="232" alias="c">
+ <owner name="sys" start-index="217" stop-index="219"/>
+ </simple-table>
+ </left>
+ <right>
+ <simple-table name="types" start-index="245"
stop-index="258" alias="t">
+ <owner name="sys" start-index="245" stop-index="247"/>
+ </simple-table>
+ </right>
+ <on-condition>
+ <binary-operation-expression start-index="263"
stop-index="293">
+ <operator>=</operator>
+ <left>
+ <column name="user_type_id" start-index="263"
stop-index="276">
+ <owner name="c" start-index="263"
stop-index="263"/>
+ </column>
+ </left>
+ <right>
+ <column name="user_type_id" start-index="280"
stop-index="293">
+ <owner name="t" start-index="280"
stop-index="280"/>
+ </column>
+ </right>
+ </binary-operation-expression>
+ </on-condition>
+ </join-table>
+ </from>
+ <where start-index="295" stop-index="356">
+ <expr>
+ <binary-operation-expression start-index="301"
stop-index="356">
+ <left>
+ <column name="user_type_id" start-index="301"
stop-index="314">
+ <owner name="c" start-index="301"
stop-index="301"/>
+ </column>
+ </left>
+ <right>
+ <function
text="TYPE_ID('<schema_name.data_type_name>')" start-index="318"
stop-index="356" function-name="TYPE_ID">
+ <parameter>
+ <literal-expression
value="<schema_name.data_type_name>" start-index="326" stop-index="355"/>
+ </parameter>
+ </function>
+ </right>
+ <operator>=</operator>
+ </binary-operation-expression>
+ </expr>
+ </where>
+ </select>
</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 9d4a8e2cb1d..26383193ef7 100644
--- a/test/it/parser/src/main/resources/case/dml/select.xml
+++ b/test/it/parser/src/main/resources/case/dml/select.xml
@@ -8604,4 +8604,123 @@
</expr>
</where>
</select>
+
+ <select sql-case-id="select_with_brackets_case_when_alias">
+ <projections start-index="7" stop-index="176">
+ <column-projection name="name" start-index="7" stop-index="25"
alias="column_name"/>
+ <column-projection name="column_id" start-index="27"
stop-index="35"/>
+ <expression-projection text="TYPE_NAME(user_type_id)"
start-index="37" stop-index="72" alias="type_name">
+ <function text="TYPE_NAME(user_type_id)"
function-name="TYPE_NAME" start-index="37" stop-index="59">
+ <parameter>
+ <column name="user_type_id" start-index="47"
stop-index="58"/>
+ </parameter>
+ </function>
+ </expression-projection>
+ <column-projection name="max_length" start-index="75"
stop-index="84"/>
+ <expression-projection
text="CASEWHENmax_length=-1ANDTYPE_NAME(user_type_id)<>'xml'THEN1ELSE0END"
start-index="86" stop-index="176" alias="(max)" start-delimiter="["
end-delimiter="]">
+ <expr>
+ <common-expression
text="CASEWHENmax_length=-1ANDTYPE_NAME(user_type_id)<>'xml'THEN1ELSE0END"
start-index="86" stop-index="165"/>
+ </expr>
+ </expression-projection>
+ </projections>
+ <from start-index="183" stop-index="193">
+ <simple-table name="columns" start-index="183" stop-index="193">
+ <owner name="sys" start-index="183" stop-index="185"/>
+ </simple-table>
+ </from>
+ <where start-index="195" stop-index="400">
+ <expr>
+ <binary-operation-expression start-index="201"
stop-index="400">
+ <operator>AND</operator>
+ <left>
+ <binary-operation-expression start-index="201"
stop-index="247">
+ <left>
+ <column name="object_id" start-index="201"
stop-index="209"/>
+ </left>
+ <right>
+ <function
text="OBJECT_ID('<schema_name.table_name>')" function-name="OBJECT_ID"
start-index="211" stop-index="247">
+ <parameter>
+ <literal-expression
value="<schema_name.table_name>" start-index="221" stop-index="246"/>
+ </parameter>
+ </function>
+ </right>
+ <operator>=</operator>
+ </binary-operation-expression>
+ </left>
+ <right>
+ <binary-operation-expression start-index="255"
stop-index="399">
+ <operator>OR</operator>
+ <left>
+ <in-expression start-index="255"
stop-index="312">
+ <left>
+ <function
text="TYPE_NAME(user_type_id)" start-index="255" stop-index="277"
function-name="TYPE_NAME">
+ <parameter>
+ <column name="user_type_id"
start-index="265" stop-index="276"/>
+ </parameter>
+ </function>
+ </left>
+ <right>
+ <list-expression start-index="282"
stop-index="312">
+ <items>
+ <literal-expression
value="xml" start-index="283" stop-index="287"/>
+ </items>
+ <items>
+ <literal-expression
value="text" start-index="289" stop-index="294"/>
+ </items>
+ <items>
+ <literal-expression
value="ntext" start-index="297" stop-index="303"/>
+ </items>
+ <items>
+ <literal-expression
value="image" start-index="305" stop-index="311"/>
+ </items>
+ </list-expression>
+ </right>
+ </in-expression>
+ </left>
+ <right>
+ <binary-operation-expression start-index="318"
stop-index="398">
+ <operator>AND</operator>
+ <left>
+ <in-expression start-index="318"
stop-index="378">
+ <left>
+ <function
text="TYPE_NAME(user_type_id)" start-index="318" stop-index="340"
function-name="TYPE_NAME">
+ <parameter>
+ <column
name="user_type_id" start-index="328" stop-index="339"/>
+ </parameter>
+ </function>
+ </left>
+ <right>
+ <list-expression
start-index="345" stop-index="378">
+ <items>
+ <literal-expression
value="varchar" start-index="346" stop-index="354"/>
+ </items>
+ <items>
+ <literal-expression
value="nvarchar" start-index="356" stop-index="365"/>
+ </items>
+ <items>
+ <literal-expression
value="varbinary" start-index="367" stop-index="377"/>
+ </items>
+ </list-expression>
+ </right>
+ </in-expression>
+ </left>
+ <right>
+ <binary-operation-expression
start-index="384" stop-index="398">
+ <operator>=</operator>
+ <left>
+ <column name="max_length"
start-index="384" stop-index="393"/>
+ </left>
+ <right>
+ <literal-expression value="-1"
start-index="397" stop-index="398"/>
+ </right>
+ </binary-operation-expression>
+ </right>
+ </binary-operation-expression>
+ </right>
+ </binary-operation-expression>
+ </right>
+ </binary-operation-expression>
+ </expr>
+ </where>
+ </select>
</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 9ae8959e3cc..e37c130f555 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
@@ -146,4 +146,5 @@
<sql-case id="insert_with_hint_and_open_row_set_function" value="INSERT
INTO HumanResources.Department WITH (IGNORE_TRIGGERS) (Name, GroupName) SELECT
b.Name, b.GroupName FROM OPENROWSET (BULK 'C:SQLFilesDepartmentData.txt',
FORMATFILE = 'C:SQLFilesBulkloadFormatFile.xml', ROWS_PER_BATCH = 15000)AS b"
db-types="SQLServer"/>
<sql-case id="insert_with_xlock_hint" value="INSERT INTO
Production.Location WITH (XLOCK) (Name, CostRate, Availability) VALUES (
N'Final Inventory', 15.00, 80.00)" db-types="SQLServer"/>
<sql-case id="insert_with_output_input" value="INSERT
Production.ScrapReason OUTPUT INSERTED.ScrapReasonID, INSERTED.Name,
INSERTED.ModifiedDate INTO @MyTableVar VALUES (N'Operator error', GETDATE())"
db-types="SQLServer"/>
+ <sql-case id="insert_into_with_multi_nchar" value="INSERT INTO
Production.UnitMeasure VALUES (N'FT2', N'Square Feet ', '20080923'), (N'Y',
N'Yards', '20080923'), (N'Y3', N'Cubic Yards', '20080923')"
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 b2a678899ec..43018c662c8 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
@@ -58,4 +58,5 @@
<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-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-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 a87db820928..f22ac3df786 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
@@ -262,4 +262,5 @@
([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-case id="select_mdx" value="SELECT {[Measures].[Internet Sales
Count], [Measures].[Internet Sales-Sales Amount]} ON COLUMNS,
{[Product].[Product Line].[Product Line].MEMBERS} ON ROWS FROM [Analysis
Services Tutorial] WHERE [Sales Territory].[Sales Territory
Country].[Australia]" db-types="SQLServer"/>
+ <sql-case id="select_with_brackets_case_when_alias" value="SELECT name AS
column_name,column_id,TYPE_NAME(user_type_id) AS type_name, max_length,CASE
WHEN max_length = -1 AND TYPE_NAME(user_type_id) <> 'xml' THEN 1 ELSE 0
END AS [(max)] FROM sys.columns WHERE
object_id=OBJECT_ID('<schema_name.table_name>') AND (
TYPE_NAME(user_type_id) IN ('xml','text', 'ntext','image') OR
(TYPE_NAME(user_type_id) IN ('varchar','nvarchar','varbinary') AND max_length =
-1))" db-types="SQLS [...]
</sql-cases>
