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 ece7d12e290 Support parsing SQL Server INSERT INTO sql #29192 (#29785)
ece7d12e290 is described below
commit ece7d12e290b20b1393c3f30208012ccd3f318e5
Author: yydeng626 <[email protected]>
AuthorDate: Fri Jan 19 22:02:04 2024 -0600
Support parsing SQL Server INSERT INTO sql #29192 (#29785)
* Support parsing SQL Server SELECT JSON_OBJECT('name':'value', sql #29160
* Support parsing SQL Server INSERT INTO sql #29192
* fix code style
---
.../src/main/antlr4/imports/sqlserver/BaseRule.g4 | 4 +-
.../statement/SQLServerStatementVisitor.java | 3 +
.../parser/src/main/resources/case/dml/insert.xml | 64 ++++++++++++++++
.../main/resources/case/dml/select-group-by.xml | 86 ++++++++++++++++++++++
.../main/resources/sql/supported/dml/insert.xml | 3 +
.../sql/supported/dml/select-group-by.xml | 2 +
6 files changed, 160 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 d0290de3dcc..6d32dd9cba6 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
@@ -208,7 +208,7 @@ collationName
;
alias
- : identifier | STRING_
+ : identifier | STRING_ | NCHAR_TEXT
;
dataTypeLength
@@ -286,7 +286,7 @@ simpleExpr
| columnName
| variableName
| simpleExpr OR_ simpleExpr
- | (PLUS_ | MINUS_ | TILDE_ | NOT_ | BINARY) simpleExpr
+ | (PLUS_ | MINUS_ | TILDE_ | NOT_ | BINARY | DOLLAR_) simpleExpr
| ROW? LP_ expr (COMMA_ expr)* RP_
| EXISTS? subquery
| LBE_ identifier expr RBE_
diff --git
a/parser/sql/dialect/sqlserver/src/main/java/org/apache/shardingsphere/sql/parser/sqlserver/visitor/statement/SQLServerStatementVisitor.java
b/parser/sql/dialect/sqlserver/src/main/java/org/apache/shardingsphere/sql/parser/sqlserver/visitor/statement/SQLServerStatementVisitor.java
index e160f2896db..ffe0b2ba610 100644
---
a/parser/sql/dialect/sqlserver/src/main/java/org/apache/shardingsphere/sql/parser/sqlserver/visitor/statement/SQLServerStatementVisitor.java
+++
b/parser/sql/dialect/sqlserver/src/main/java/org/apache/shardingsphere/sql/parser/sqlserver/visitor/statement/SQLServerStatementVisitor.java
@@ -1341,6 +1341,9 @@ public abstract class SQLServerStatementVisitor extends
SQLServerStatementBaseVi
if (null != ctx.identifier()) {
return new AliasSegment(ctx.start.getStartIndex(),
ctx.stop.getStopIndex(), (IdentifierValue) visit(ctx.identifier()));
}
+ if (null != ctx.NCHAR_TEXT()) {
+ return new AliasSegment(ctx.start.getStartIndex(),
ctx.stop.getStopIndex(), new
IdentifierValue(ctx.NCHAR_TEXT().getText().substring(1)));
+ }
return new AliasSegment(ctx.start.getStartIndex(),
ctx.stop.getStopIndex(), new IdentifierValue(ctx.STRING_().getText()));
}
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 f2b33ed0d0b..59e61ebecf1 100644
--- a/test/it/parser/src/main/resources/case/dml/insert.xml
+++ b/test/it/parser/src/main/resources/case/dml/insert.xml
@@ -3219,4 +3219,68 @@
</from>
</select>
</insert>
+
+ <insert sql-case-id="insert_sales_with_nchar">
+ <table name="sales" start-index="12" stop-index="16" />
+ <columns start-index="17" stop-index="17" />
+ <values>
+ <value>
+ <assignment-value>
+ <literal-expression value="Canada" start-index="26"
stop-index="34" />
+ </assignment-value>
+ <assignment-value>
+ <literal-expression value="British Columbia"
start-index="37" stop-index="55" />
+ </assignment-value>
+ <assignment-value>
+ <literal-expression value="300" start-index="58"
stop-index="60" />
+ </assignment-value>
+ </value>
+ </values>
+ </insert>
+
+ <insert sql-case-id="insert_sales_with_nchar_2">
+ <table name="sales" start-index="12" stop-index="16" />
+ <columns start-index="17" stop-index="17" />
+ <values>
+ <value>
+ <assignment-value>
+ <literal-expression value="United States" start-index="26"
stop-index="41" />
+ </assignment-value>
+ <assignment-value>
+ <literal-expression value="Montana" start-index="44"
stop-index="53" />
+ </assignment-value>
+ <assignment-value>
+ <literal-expression value="100" start-index="56"
stop-index="58" />
+ </assignment-value>
+ </value>
+ </values>
+ </insert>
+
+ <insert sql-case-id="insert_with_currency_value">
+ <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="795-73-9838" start-index="79"
stop-index="91" />
+ </assignment-value>
+ <assignment-value>
+ <literal-expression value="Catherine" start-index="94"
stop-index="105" />
+ </assignment-value>
+ <assignment-value>
+ <literal-expression value="Abel" start-index="108"
stop-index="114" />
+ </assignment-value>
+ <assignment-value>
+ <common-expression text="$31692" start-index="117"
stop-index="122" />
+ </assignment-value>
+ </value>
+ </values>
+ </insert>
</sql-parser-test-cases>
diff --git a/test/it/parser/src/main/resources/case/dml/select-group-by.xml
b/test/it/parser/src/main/resources/case/dml/select-group-by.xml
index 03b922bad9c..b3702d8f894 100644
--- a/test/it/parser/src/main/resources/case/dml/select-group-by.xml
+++ b/test/it/parser/src/main/resources/case/dml/select-group-by.xml
@@ -824,4 +824,90 @@
</column-item>
</order-by>
</select>
+
+ <select sql-case-id="select_with_datepart_group_by_with_order_by">
+ <projections start-index="7" stop-index="81">
+ <expression-projection text="DATEPART(yyyy,OrderDate)"
alias="Year" start-index="7" stop-index="41">
+ <expr>
+ <function function-name="DATEPART"
text="DATEPART(yyyy,OrderDate)" start-index="7" stop-index="30">
+ <parameter>
+ <column name="yyyy" start-index="16"
stop-index="19" />
+ </parameter>
+ <parameter>
+ <column name="OrderDate" start-index="21"
stop-index="29" />
+ </parameter>
+ </function>
+ </expr>
+ </expression-projection>
+ <aggregation-projection type="SUM" expression="SUM(TotalDue)"
alias="Total Order Amount" start-index="44" stop-index="56">
+ <paramters>
+ <column name="TotalDue" start-index="48" stop-index="55" />
+ </paramters>
+ </aggregation-projection>
+ </projections>
+ <from>
+ <simple-table name="SalesOrderHeader" start-index="88"
stop-index="109">
+ <owner name="Sales" start-index="88" stop-index="92"/>
+ </simple-table>
+ </from>
+ <group-by>
+ <expression-item expression="DATEPART(yyyy,OrderDate)"
start-index="120" stop-index="143" />
+ </group-by>
+ <order-by>
+ <expression-item expression="DATEPART(yyyy,OrderDate)"
order-direction="ASC" start-index="154" stop-index="177" />
+ </order-by>
+ </select>
+
+ <select sql-case-id="select_with_datepart_group_by_with_having_order_by">
+ <projections start-index="7" stop-index="81">
+ <expression-projection text="DATEPART(yyyy,OrderDate)"
alias="Year" start-index="7" stop-index="41">
+ <expr>
+ <function function-name="DATEPART"
text="DATEPART(yyyy,OrderDate)" start-index="7" stop-index="30">
+ <parameter>
+ <column name="yyyy" start-index="16"
stop-index="19" />
+ </parameter>
+ <parameter>
+ <column name="OrderDate" start-index="21"
stop-index="29" />
+ </parameter>
+ </function>
+ </expr>
+ </expression-projection>
+ <aggregation-projection type="SUM" expression="SUM(TotalDue)"
alias="Total Order Amount" start-index="44" stop-index="56">
+ <paramters>
+ <column name="TotalDue" start-index="48" stop-index="55" />
+ </paramters>
+ </aggregation-projection>
+ </projections>
+ <from>
+ <simple-table name="SalesOrderHeader" start-index="88"
stop-index="109">
+ <owner name="Sales" start-index="88" stop-index="92"/>
+ </simple-table>
+ </from>
+ <group-by>
+ <expression-item expression="DATEPART(yyyy,OrderDate)"
start-index="120" stop-index="143" />
+ </group-by>
+ <having start-index="145" stop-index="186">
+ <expr>
+ <binary-operation-expression text="DATEPART(yyyy,OrderDate)
>= N'2003'" start-index="152" stop-index="186">
+ <left>
+ <function function-name="DATEPART"
text="DATEPART(yyyy,OrderDate)" start-index="152" stop-index="175">
+ <parameter>
+ <column name="yyyy" start-index="161"
stop-index="164" />
+ </parameter>
+ <parameter>
+ <column name="OrderDate" start-index="166"
stop-index="174" />
+ </parameter>
+ </function>
+ </left>
+ <right>
+ <literal-expression value="2003" start-index="180"
stop-index="186" />
+ </right>
+ <operator>>=</operator>
+ </binary-operation-expression>
+ </expr>
+ </having>
+ <order-by>
+ <expression-item expression="DATEPART(yyyy,OrderDate)"
order-direction="ASC" start-index="197" stop-index="220" />
+ </order-by>
+ </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 9b3f5949bb4..f6f6102dd00 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
@@ -118,4 +118,7 @@
<sql-case id="insert_with_exec_mtcars" value="INSERT INTO dbo.MTCars EXEC
sp_execute_external_script @language = N'R', @script = N'MTCars <- mtcars' ,
@input_data_1 = N'', @output_data_1_name = N'MTCars'" db-types="SQLServer"/>
<sql-case id="insert_with_exec_model" value="INSERT INTO GLM_models(model)
EXEC generate_GLM" db-types="SQLServer"/>
<sql-case id="insert_with_table_hint" value="INSERT INTO cci_target WITH
(TABLOCK) SELECT TOP 300000 * FROM staging" db-types="SQLServer"/>
+ <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-cases>
diff --git
a/test/it/parser/src/main/resources/sql/supported/dml/select-group-by.xml
b/test/it/parser/src/main/resources/sql/supported/dml/select-group-by.xml
index 68b50c5d9ab..4ff1bfc6d5d 100644
--- a/test/it/parser/src/main/resources/sql/supported/dml/select-group-by.xml
+++ b/test/it/parser/src/main/resources/sql/supported/dml/select-group-by.xml
@@ -41,4 +41,6 @@
<sql-case id="select_group_by_with_having_with_subquery" value="SELECT
department_id, manager_id FROM employees GROUP BY department_id, manager_id
HAVING (department_id, manager_id) IN (SELECT department_id, manager_id FROM
employees x WHERE x.department_id = employees.department_id) ORDER BY
department_id" db-types="Oracle" />
<sql-case
id="select_with_case_when_then_in_group_by_item_and_order_by_item"
value="SELECT order_id FROM t_order GROUP BY CASE WHEN order_id > 0 AND
order_id <= 10 THEN '(0,10]' WHEN order_id > 10 THEN '(10,+∞)' ELSE '' END
ORDER BY CASE WHEN order_id > 0 AND order_id <= 10 THEN '(0,10]' WHEN
order_id > 10 THEN '(10,+∞)' ELSE '' END"
db-types="MySQL,PostgreSQL,openGauss,SQLServer,Oracle" />
<sql-case id="select_with_event_group_by_with_having_order_by"
value="SELECT c.name, Count(*) AS [Count-Per-Column-Repeated-Name] FROM
sys.syscolumns AS c JOIN sys.sysobjects AS o ON o.id = c.id WHERE o.type =
'V' AND c.name like '%event%' GROUP BY c.name HAVING Count(*) >= 3 ORDER BY
c.name" db-types="SQLServer" />
+ <sql-case id="select_with_datepart_group_by_with_order_by" value="SELECT
DATEPART(yyyy,OrderDate) AS N'Year', SUM(TotalDue) AS N'Total Order Amount'
FROM Sales.SalesOrderHeader GROUP BY DATEPART(yyyy,OrderDate) ORDER BY
DATEPART(yyyy,OrderDate)" db-types="SQLServer"/>
+ <sql-case id="select_with_datepart_group_by_with_having_order_by"
value="SELECT DATEPART(yyyy,OrderDate) AS N'Year', SUM(TotalDue) AS N'Total
Order Amount' FROM Sales.SalesOrderHeader GROUP BY DATEPART(yyyy,OrderDate)
HAVING DATEPART(yyyy,OrderDate) >= N'2003' ORDER BY
DATEPART(yyyy,OrderDate)" db-types="SQLServer"/>
</sql-cases>
