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 5030207c7ce Support parsing SQL Server default schema (#29814)
5030207c7ce is described below
commit 5030207c7cee6c98ecfcef1bf3a9204be996216c
Author: LotusMoon <[email protected]>
AuthorDate: Tue Jan 23 19:02:43 2024 +0800
Support parsing SQL Server default schema (#29814)
---
.../src/main/antlr4/imports/sqlserver/BaseRule.g4 | 2 +-
.../parser/src/main/resources/case/dml/insert.xml | 97 ++++++++++++++++++++++
.../main/resources/case/dml/select-expression.xml | 53 ++++++++++++
.../src/main/resources/case/dml/select-join.xml | 44 ++++++++++
.../parser/src/main/resources/case/dml/select.xml | 13 +++
.../main/resources/sql/supported/dml/insert.xml | 2 +
.../sql/supported/dml/select-expression.xml | 1 +
.../resources/sql/supported/dml/select-join.xml | 1 +
.../main/resources/sql/supported/dml/select.xml | 1 +
9 files changed, 213 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 6d32dd9cba6..5efe8ba0cae 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
@@ -152,7 +152,7 @@ sequenceName
;
tableName
- : (databaseName DOT_ (owner DOT_)? | (owner DOT_)?) name
+ : (databaseName DOT_)? (owner? DOT_)? name
;
queueName
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 59e61ebecf1..a8e4800478d 100644
--- a/test/it/parser/src/main/resources/case/dml/insert.xml
+++ b/test/it/parser/src/main/resources/case/dml/insert.xml
@@ -3283,4 +3283,101 @@
</value>
</values>
</insert>
+
+ <insert sql-case-id="insert_with_select_from_open_row_1">
+ <table name="myTable" start-index="12" stop-index="18"/>
+ <columns start-index="19" stop-index="48">
+ <column name="FileName" start-index="20" stop-index="27"/>
+ <column name="FileType" start-index="30" stop-index="37"/>
+ <column name="Document" start-index="40" stop-index="47"/>
+ </columns>
+ <select>
+ <projections start-index="57" stop-index="102">
+ <expression-projection text="Text1.txt" start-index="57"
stop-index="79" alias="FileName"/>
+ <expression-projection text=".txt" start-index="82"
stop-index="99" alias="FileType"/>
+ <shorthand-projection start-index="102" stop-index="102"/>
+ </projections>
+ <from>
+ <function-table start-index="109" stop-index="153"
table-alias="Document">
+ <table-function text="OPENROWSET(BULK N'C:\Text1.txt',
SINGLE_BLOB)" function-name="OPENROWSET">
+ <parameter>
+ <literal-expression value="C:\Text1.txt"
start-index="125" stop-index="139"/>
+ </parameter>
+ <parameter>
+ <column name="SINGLE_BLOB" start-index="142"
stop-index="152"/>
+ </parameter>
+ </table-function>
+ </function-table>
+ </from>
+ </select>
+ </insert>
+
+ <insert sql-case-id="insert_with_select_from_open_row_2">
+ <table name="achievements" start-index="12" stop-index="23"/>
+ <table-hints start-index="25" stop-index="38">
+ <table-hint value="TABLOCK" start-index="31" stop-index="37"/>
+ </table-hints>
+ <columns start-index="40" stop-index="56">
+ <column name="id" start-index="41" stop-index="42"/>
+ <column name="description" start-index="45" stop-index="55"/>
+ </columns>
+ <select>
+ <projections start-index="65" stop-index="65">
+ <shorthand-projection start-index="65" stop-index="65"/>
+ </projections>
+ <from>
+ <function-table start-index="72" stop-index="245"
table-alias="DataFile">
+ <table-function function-name="OPENROWSET"
text="OPENROWSET(BULK 'csv/achievements.csv', DATA_SOURCE =
'MyAzureBlobStorage', FORMAT ='CSV', FORMATFILE='csv/achievements-c.xml',
FORMATFILE_DATA_SOURCE = 'MyAzureBlobStorage')">
+ <parameter>
+ <literal-expression value="csv/achievements.csv"
start-index="88" stop-index="109"/>
+ </parameter>
+ <parameter>
+ <binary-operation-expression start-index="112"
stop-index="145">
+ <left>
+ <column name="DATA_SOURCE"
start-index="112" stop-index="122"/>
+ </left>
+ <right>
+ <literal-expression
value="MyAzureBlobStorage" start-index="126" stop-index="145"/>
+ </right>
+ <operator>=</operator>
+ </binary-operation-expression>
+ </parameter>
+ <parameter>
+ <binary-operation-expression start-index="148"
stop-index="160">
+ <left>
+ <column name="FORMAT" start-index="148"
stop-index="153"/>
+ </left>
+ <right>
+ <literal-expression value="CSV"
start-index="156" stop-index="140"/>
+ </right>
+ <operator>=</operator>
+ </binary-operation-expression>
+ </parameter>
+ <parameter>
+ <binary-operation-expression start-index="163"
stop-index="197">
+ <left>
+ <column name="FORMATFILE"
start-index="163" stop-index="172"/>
+ </left>
+ <right>
+ <literal-expression
value="csv/achievements-c.xml" start-index="174" stop-index="197"/>
+ </right>
+ <operator>=</operator>
+ </binary-operation-expression>
+ </parameter>
+ <parameter>
+ <binary-operation-expression start-index="200"
stop-index="244">
+ <left>
+ <column name="FORMATFILE_DATA_SOURCE"
start-index="200" stop-index="221"/>
+ </left>
+ <right>
+ <literal-expression
value="MyAzureBlobStorage" start-index="225" stop-index="244"/>
+ </right>
+ <operator>=</operator>
+ </binary-operation-expression>
+ </parameter>
+ </table-function>
+ </function-table>
+ </from>
+ </select>
+ </insert>
</sql-parser-test-cases>
diff --git a/test/it/parser/src/main/resources/case/dml/select-expression.xml
b/test/it/parser/src/main/resources/case/dml/select-expression.xml
index b44c4ddc474..c9721ada78c 100644
--- a/test/it/parser/src/main/resources/case/dml/select-expression.xml
+++ b/test/it/parser/src/main/resources/case/dml/select-expression.xml
@@ -3125,4 +3125,57 @@
</expression-projection>
</projections>
</select>
+
+ <select sql-case-id="select_with_case_when_from_sys">
+ <from start-index="651" stop-index="681">
+ <simple-table name="database_files" start-index="651"
stop-index="681" alias="df">
+ <owner name="sys" start-index="658" stop-index="660">
+ <owner name="tempdb" start-index="651" stop-index="656"/>
+ </owner>
+ </simple-table>
+ </from>
+ <projections start-index="7" stop-index="644">
+ <column-projection name="name" start-index="7" stop-index="24"
alias="FileName">
+ <owner name="df" start-index="18" stop-index="19"/>
+ </column-projection>
+ <expression-projection text="df.size*1.0/128" start-index="27"
stop-index="64" alias="current_file_size_MB">
+ <expr>
+ <binary-operation-expression start-index="50"
stop-index="64">
+ <left>
+ <binary-operation-expression start-index="50"
stop-index="60">
+ <left>
+ <column name="size" start-index="50"
stop-index="56">
+ <owner name="df" start-index="50"
stop-index="51"/>
+ </column>
+ </left>
+ <right>
+ <literal-expression value="1.0"
start-index="58" stop-index="60"/>
+ </right>
+ <operator>*</operator>
+ </binary-operation-expression>
+ </left>
+ <right>
+ <literal-expression value="128" start-index="62"
stop-index="64"/>
+ </right>
+ <operator>/</operator>
+ </binary-operation-expression>
+ </expr>
+ </expression-projection>
+ <expression-projection start-index="67" stop-index="212"
alias="max_size" text="CASEdf.max_sizeWHEN0THEN'Autogrowth is
off.'WHEN-1THEN'Autogrowth is on.'ELSE'Log file grows to a maximum size of 2
TB.'END">
+ <expr>
+ <common-expression
literal-text="CASEdf.max_sizeWHEN0THEN'Autogrowth is off.'WHEN-1THEN'Autogrowth
is on.'ELSE'Log file grows to a maximum size of 2 TB.'END" start-index="78"
stop-index="212"/>
+ </expr>
+ </expression-projection>
+ <expression-projection start-index="215" stop-index="407"
alias="growth_value"
text="CASEWHENdf.growth=0THENdf.growthWHENdf.growth>0ANDdf.is_percent_growth=0THENdf.growth*1.0/128.0WHENdf.growth>0ANDdf.is_percent_growth=1THENdf.growthEND">
+ <expr>
+ <common-expression
literal-text="CASEWHENdf.growth=0THENdf.growthWHENdf.growth>0ANDdf.is_percent_growth=0THENdf.growth*1.0/128.0WHENdf.growth>0ANDdf.is_percent_growth=1THENdf.growthEND"
start-index="230" stop-index="407"/>
+ </expr>
+ </expression-projection>
+ <expression-projection start-index="410" stop-index="644"
alias="growth_increment_unit" text="CASEWHENdf.growth=0THEN'Size is
fixed.'WHENdf.growth>0ANDdf.is_percent_growth=0THEN'Growth value is
MB.'WHENdf.growth>0ANDdf.is_percent_growth=1THEN'Growth value is a
percentage.'END">
+ <expr>
+ <common-expression
literal-text="CASEWHENdf.growth=0THEN'Size is
fixed.'WHENdf.growth>0ANDdf.is_percent_growth=0THEN'Growth value is
MB.'WHENdf.growth>0ANDdf.is_percent_growth=1THEN'Growth value is a
percentage.'END" start-index="434" stop-index="644"/>
+ </expr>
+ </expression-projection>
+ </projections>
+ </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 726ac331907..e1be7bae1a3 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
@@ -1572,4 +1572,48 @@
</expr>
</where>
</select>
+
+ <select sql-case-id="select_cross_apply_join_with_like_nchar">
+ <projections start-index="7" stop-index="26">
+ <column-projection name="plan_handle" start-index="7"
stop-index="17"/>
+ <column-projection name="text" start-index="20" stop-index="26">
+ <owner name="st" start-index="20" stop-index="21"/>
+ </column-projection>
+ </projections>
+ <from start-index="33" stop-index="108">
+ <join-table join-type="CROSS">
+ <left>
+ <simple-table name="dm_exec_cached_plans" start-index="33"
stop-index="56">
+ <owner name="sys" start-index="33" stop-index="35"/>
+ </simple-table>
+ </left>
+ <right>
+ <function-table start-index="70" stop-index="102"
table-alias="st">
+ <table-function
text="sys.dm_exec_sql_text(plan_handle)" function-name="sys.dm_exec_sql_text">
+ <parameter>
+ <column name="plan_handle" start-index="91"
stop-index="101"/>
+ </parameter>
+ </table-function>
+ </function-table>
+ </right>
+ </join-table>
+ </from>
+ <where start-index="110" stop-index="157">
+ <expr>
+ <binary-operation-expression start-index="116"
stop-index="157">
+ <left>
+ <column name="text" start-index="116"
stop-index="119"/>
+ </left>
+ <right>
+ <list-expression start-index="126" stop-index="157">
+ <items>
+ <literal-expression value="SELECT * FROM
Person.Address%" start-index="126" stop-index="157"/>
+ </items>
+ </list-expression>
+ </right>
+ <operator>LIKE</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 1cd9add7632..423914b6bf7 100644
--- a/test/it/parser/src/main/resources/case/dml/select.xml
+++ b/test/it/parser/src/main/resources/case/dml/select.xml
@@ -8174,4 +8174,17 @@
</expr>
</where>
</select>
+
+ <select sql-case-id="select_with_default_schema">
+ <projections start-index="7" stop-index="44">
+ <column-projection name="has_backup_checksums" start-index="7"
stop-index="26"/>
+ <column-projection name="database_name" start-index="29"
stop-index="41"/>
+ <shorthand-projection start-index="44" stop-index="44"/>
+ </projections>
+ <from start-index="51" stop-index="65">
+ <simple-table name="backupset" start-index="51" stop-index="65">
+ <owner name="msdb" start-index="51" stop-index="54"/>
+ </simple-table>
+ </from>
+ </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 f6f6102dd00..44b8e240e62 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
@@ -121,4 +121,6 @@
<sql-case id="insert_sales_with_nchar" value="INSERT INTO sales VALUES
(N'Canada', N'British Columbia', 300)" db-types="SQLServer"/>
<sql-case id="insert_sales_with_nchar_2" value="INSERT INTO sales VALUES
(N'United States', N'Montana', 100)" db-types="SQLServer"/>
<sql-case id="insert_with_currency_value" value="INSERT INTO
[HR].[Employees]([SSN], [FirstName], [LastName], [Salary]) VALUES
('795-73-9838', N'Catherine', N'Abel', $31692)" db-types="SQLServer"/>
+ <sql-case id="insert_with_select_from_open_row_1" value="INSERT INTO
myTable(FileName, FileType, Document) SELECT 'Text1.txt' AS FileName, '.txt' AS
FileType, * FROM OPENROWSET(BULK N'C:\Text1.txt', SINGLE_BLOB) AS Document"
db-types="SQLServer"/>
+ <sql-case id="insert_with_select_from_open_row_2" value="INSERT INTO
achievements with (TABLOCK) (id, description) SELECT * FROM OPENROWSET(BULK
'csv/achievements.csv', DATA_SOURCE = 'MyAzureBlobStorage', FORMAT ='CSV',
FORMATFILE='csv/achievements-c.xml', FORMATFILE_DATA_SOURCE =
'MyAzureBlobStorage') AS DataFile" db-types="SQLServer"/>
</sql-cases>
diff --git
a/test/it/parser/src/main/resources/sql/supported/dml/select-expression.xml
b/test/it/parser/src/main/resources/sql/supported/dml/select-expression.xml
index 96e30ebb469..6ecfeceb5f7 100644
--- a/test/it/parser/src/main/resources/sql/supported/dml/select-expression.xml
+++ b/test/it/parser/src/main/resources/sql/supported/dml/select-expression.xml
@@ -136,4 +136,5 @@
<sql-case id="select_set_masklen_function" value="SELECT
set_masklen('192.168.1.0/24'::cidr, 16) AS RESULT;" db-types="openGauss" />
<sql-case id="select_text_inet_function" value="SELECT text(inet
'192.168.1.5') AS RESULT;" db-types="openGauss" />
<sql-case id="select_trunc_function" value="SELECT trunc(macaddr
'12:34:56:78:90:ab') AS RESULT;" db-types="openGauss" />
+ <sql-case id="select_with_case_when_from_sys" value="SELECT FileName =
df.name, current_file_size_MB = df.size*1.0/128, max_size = CASE df.max_size
WHEN 0 THEN 'Autogrowth is off.' WHEN -1 THEN 'Autogrowth is on.' ELSE 'Log
file grows to a maximum size of 2 TB.' END, growth_value = CASE WHEN df.growth
= 0 THEN df.growth WHEN df.growth > 0 AND df.is_percent_growth = 0 THEN
df.growth*1.0/128.0 WHEN df.growth > 0 AND df.is_percent_growth = 1 THEN
df.growth END, growth_increment_unit = C [...]
</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 d1e26721f01..186d1e84fc2 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
@@ -44,4 +44,5 @@
<sql-case id="select_with_multi_join_02" value="SELECT text, 'DBCC
FREEPROCCACHE (0x' + CONVERT(VARCHAR (512), plan_handle, 2) + ')' AS
dbcc_freeproc_command FROM sys.dm_exec_cached_plans CROSS APPLY
sys.dm_exec_query_plan(plan_handle) CROSS APPLY
sys.dm_exec_sql_text(plan_handle) WHERE text LIKE '%person.person%'"
db-types="SQLServer"/>
<sql-case id="select_cross_apply_join_with_open_json_function"
value="SELECT reason, score, script = JSON_VALUE(details,
'$.implementationDetails.script'), planForceDetails.*, estimated_gain =
(regressedPlanExecutionCount + recommendedPlanExecutionCount) *
(regressedPlanCpuTimeAverage - recommendedPlanCpuTimeAverage)/1000000,
error_prone = IIF(regressedPlanErrorCount > recommendedPlanErrorCount,
'YES','NO') FROM sys.dm_db_tuning_recommendations CROSS APPLY OPENJSON
(Details, '$.planF [...]
<sql-case id="select_cross_apply_join_with_sys_dm_exec_function"
value="SELECT cp.memory_object_address, cp.objtype, refcounts, usecounts,
qs.query_plan_hash, qs.query_hash, qs.plan_handle, qs.sql_handle FROM
sys.dm_exec_cached_plans AS cp CROSS APPLY sys.dm_exec_sql_text
(cp.plan_handle) CROSS APPLY sys.dm_exec_query_plan (cp.plan_handle) INNER JOIN
sys.dm_exec_query_stats AS qs ON qs.plan_handle = cp.plan_handle WHERE text
LIKE '%usp_SalesByCustomer%'" db-types="SQLServer"/>
+ <sql-case id="select_cross_apply_join_with_like_nchar" value="SELECT
plan_handle, st.text FROM sys.dm_exec_cached_plans CROSS APPLY
sys.dm_exec_sql_text(plan_handle) AS st WHERE text LIKE N'SELECT * FROM
Person.Address%'" 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 a75ed268f28..352eecf1182 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
@@ -248,4 +248,5 @@
<sql-case id="select_with_distinct_from" value="SELECT * FROM
#SampleTempTable WHERE id IS DISTINCT FROM 17;" db-types="SQLServer"/>
<sql-case id="select_with_database_files" value="SELECT name, size / 128.0
- CAST(FILEPROPERTY(name, 'SpaceUsed') AS INT) / 128.0 AS AvailableSpaceInMB
FROM sys.database_files" db-types="SQLServer"/>
<sql-case id="select_with_contains_function" value="SELECT product_id FROM
products WHERE CONTAINS(product_description, '"Snap Happy 100EZ" OR
FORMSOF(THESAURUS,"Snap Happy") OR "100EZ"') AND
product_cost < 200" db-types="SQLServer"/>
+ <sql-case id="select_with_default_schema" value="SELECT
has_backup_checksums, database_name, * FROM msdb..backupset"
db-types="SQLServer"/>
</sql-cases>
