This is an automated email from the ASF dual-hosted git repository.
niuzihao 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 bedd9ca2a3d Support parsing SQL Server INSERT INTO sql #29152 (#29329)
bedd9ca2a3d is described below
commit bedd9ca2a3df184bad14981e9845dbce7e0a36ce
Author: LotusMoon <[email protected]>
AuthorDate: Tue Dec 12 11:23:37 2023 +0800
Support parsing SQL Server INSERT INTO sql #29152 (#29329)
* Support parsing SQL Server INSERT INTO sql(#29152)
* Fix the oracle parser error test
* solve this nchar problem by adjusting Oracle's lexical or grammatical
rules
* pass the spotless:apply check
* Fix PostgreSQL E2E error
---
.../src/main/antlr4/imports/oracle/BaseRule.g4 | 2 +
.../src/main/antlr4/imports/oracle/Literals.g4 | 10 +++-
.../visitor/statement/OracleStatementVisitor.java | 6 +-
.../src/main/antlr4/imports/sqlserver/BaseRule.g4 | 9 ++-
.../src/main/antlr4/imports/sqlserver/Symbol.g4 | 1 +
.../statement/SQLServerStatementVisitor.java | 29 +++++++++-
.../parser/src/main/resources/case/dml/insert.xml | 67 +++++++++++++++++++++-
.../parser/src/main/resources/case/dml/select.xml | 39 +++++++++++--
.../main/resources/sql/supported/dml/insert.xml | 4 ++
.../main/resources/sql/supported/dml/select.xml | 1 +
10 files changed, 154 insertions(+), 14 deletions(-)
diff --git
a/parser/sql/dialect/oracle/src/main/antlr4/imports/oracle/BaseRule.g4
b/parser/sql/dialect/oracle/src/main/antlr4/imports/oracle/BaseRule.g4
index e11ae2d0bc6..8472d58648d 100644
--- a/parser/sql/dialect/oracle/src/main/antlr4/imports/oracle/BaseRule.g4
+++ b/parser/sql/dialect/oracle/src/main/antlr4/imports/oracle/BaseRule.g4
@@ -48,6 +48,8 @@ intervalUnit
stringLiterals
: STRING_
+ | NCHAR_TEXT
+ | UCHAR_TEXT
;
numberLiterals
diff --git
a/parser/sql/dialect/oracle/src/main/antlr4/imports/oracle/Literals.g4
b/parser/sql/dialect/oracle/src/main/antlr4/imports/oracle/Literals.g4
index 9027672dcd3..03936901a58 100644
--- a/parser/sql/dialect/oracle/src/main/antlr4/imports/oracle/Literals.g4
+++ b/parser/sql/dialect/oracle/src/main/antlr4/imports/oracle/Literals.g4
@@ -28,7 +28,7 @@ IDENTIFIER_
;
STRING_
- : (N | U)? SINGLE_QUOTED_TEXT
+ : SINGLE_QUOTED_TEXT
;
SINGLE_QUOTED_TEXT
@@ -55,6 +55,14 @@ BIT_NUM_
: '0b' ('0' | '1')+ | B SQ_ ('0' | '1')+ SQ_
;
+NCHAR_TEXT
+ : N STRING_
+ ;
+
+UCHAR_TEXT
+ : U STRING_
+ ;
+
fragment INT_
: [0-9]+
;
diff --git
a/parser/sql/dialect/oracle/src/main/java/org/apache/shardingsphere/sql/parser/oracle/visitor/statement/OracleStatementVisitor.java
b/parser/sql/dialect/oracle/src/main/java/org/apache/shardingsphere/sql/parser/oracle/visitor/statement/OracleStatementVisitor.java
index 5f9c266aa2a..e13066d1237 100644
---
a/parser/sql/dialect/oracle/src/main/java/org/apache/shardingsphere/sql/parser/oracle/visitor/statement/OracleStatementVisitor.java
+++
b/parser/sql/dialect/oracle/src/main/java/org/apache/shardingsphere/sql/parser/oracle/visitor/statement/OracleStatementVisitor.java
@@ -238,7 +238,11 @@ public abstract class OracleStatementVisitor extends
OracleStatementBaseVisitor<
@Override
public final ASTNode visitStringLiterals(final StringLiteralsContext ctx) {
- return new StringLiteralValue(ctx.getText());
+ if (null != ctx.STRING_()) {
+ return new StringLiteralValue(ctx.getText());
+ } else {
+ return new StringLiteralValue(ctx.getText().substring(1));
+ }
}
@Override
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 f8f3bfb59f0..e493579b65e 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
@@ -35,6 +35,7 @@ literals
stringLiterals
: STRING_
+ | NCHAR_TEXT
;
numberLiterals
@@ -151,7 +152,7 @@ sequenceName
;
tableName
- : (owner DOT_)? name
+ : (databaseName DOT_ (owner DOT_) ? | (owner DOT_) ?) name
;
queueName
@@ -167,7 +168,11 @@ serviceName
;
columnName
- : (owner DOT_)? name
+ : (owner DOT_)? (name | scriptVariableName)
+ ;
+
+scriptVariableName
+ : DOLLAR_ LP_ name RP_
;
owner
diff --git
a/parser/sql/dialect/sqlserver/src/main/antlr4/imports/sqlserver/Symbol.g4
b/parser/sql/dialect/sqlserver/src/main/antlr4/imports/sqlserver/Symbol.g4
index da742daf655..5a0d8f39b4c 100644
--- a/parser/sql/dialect/sqlserver/src/main/antlr4/imports/sqlserver/Symbol.g4
+++ b/parser/sql/dialect/sqlserver/src/main/antlr4/imports/sqlserver/Symbol.g4
@@ -57,3 +57,4 @@ BQ_: '`';
QUESTION_: '?';
AT_: '@';
SEMI_: ';';
+DOLLAR_: '$';
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 fed3241059e..3fafe09b496 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
@@ -25,6 +25,7 @@ import org.antlr.v4.runtime.misc.Interval;
import org.antlr.v4.runtime.tree.TerminalNode;
import org.apache.shardingsphere.sql.parser.api.ASTNode;
import
org.apache.shardingsphere.sql.parser.autogen.SQLServerStatementBaseVisitor;
+import org.apache.shardingsphere.sql.parser.autogen.SQLServerStatementParser;
import
org.apache.shardingsphere.sql.parser.autogen.SQLServerStatementParser.AggregationClauseContext;
import
org.apache.shardingsphere.sql.parser.autogen.SQLServerStatementParser.AggregationFunctionContext;
import
org.apache.shardingsphere.sql.parser.autogen.SQLServerStatementParser.AliasContext;
@@ -228,7 +229,11 @@ public abstract class SQLServerStatementVisitor extends
SQLServerStatementBaseVi
@Override
public final ASTNode visitStringLiterals(final StringLiteralsContext ctx) {
- return new StringLiteralValue(ctx.getText());
+ if (null != ctx.STRING_()) {
+ return new StringLiteralValue(ctx.getText());
+ } else {
+ return new StringLiteralValue(ctx.getText().substring(1));
+ }
}
@Override
@@ -289,14 +294,27 @@ public abstract class SQLServerStatementVisitor extends
SQLServerStatementBaseVi
SimpleTableSegment result = new SimpleTableSegment(new
TableNameSegment(ctx.name().getStart().getStartIndex(),
ctx.name().getStop().getStopIndex(), (IdentifierValue) visit(ctx.name())));
OwnerContext owner = ctx.owner();
if (null != owner) {
- result.setOwner(new OwnerSegment(owner.getStart().getStartIndex(),
owner.getStop().getStopIndex(), (IdentifierValue) visit(owner.identifier())));
+ OwnerSegment ownerSegment = new
OwnerSegment(owner.getStart().getStartIndex(), owner.getStop().getStopIndex(),
(IdentifierValue) visit(owner.identifier()));
+ if (null != ctx.databaseName()) {
+ SQLServerStatementParser.DatabaseNameContext dbName =
ctx.databaseName();
+ ownerSegment.setOwner(new
OwnerSegment(dbName.getStart().getStartIndex(),
dbName.getStop().getStopIndex(), (IdentifierValue) visit(dbName.identifier())));
+ }
+ result.setOwner(ownerSegment);
+ } else if (null != ctx.databaseName()) {
+ SQLServerStatementParser.DatabaseNameContext dbName =
ctx.databaseName();
+ result.setOwner(new
OwnerSegment(dbName.getStart().getStartIndex(),
dbName.getStop().getStopIndex(), (IdentifierValue) visit(dbName.identifier())));
}
return result;
}
@Override
public final ASTNode visitColumnName(final ColumnNameContext ctx) {
- ColumnSegment result = new
ColumnSegment(ctx.getStart().getStartIndex(), ctx.getStop().getStopIndex(),
(IdentifierValue) visit(ctx.name()));
+ ColumnSegment result;
+ if (null != ctx.name()) {
+ result = new ColumnSegment(ctx.getStart().getStartIndex(),
ctx.getStop().getStopIndex(), (IdentifierValue) visit(ctx.name()));
+ } else {
+ result = new ColumnSegment(ctx.getStart().getStartIndex(),
ctx.getStop().getStopIndex(), (IdentifierValue)
visit(ctx.scriptVariableName()));
+ }
OwnerContext owner = ctx.owner();
if (null != owner) {
result.setOwner(new OwnerSegment(owner.getStart().getStartIndex(),
owner.getStop().getStopIndex(), (IdentifierValue) visit(owner.identifier())));
@@ -304,6 +322,11 @@ public abstract class SQLServerStatementVisitor extends
SQLServerStatementBaseVi
return result;
}
+ @Override
+ public ASTNode visitScriptVariableName(final
SQLServerStatementParser.ScriptVariableNameContext ctx) {
+ return new IdentifierValue(ctx.getText());
+ }
+
@Override
public final ASTNode visitIndexName(final IndexNameContext ctx) {
IndexNameSegment indexName = new
IndexNameSegment(ctx.start.getStartIndex(), ctx.stop.getStopIndex(),
(IdentifierValue) visit(ctx.identifier()));
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 f9bae458b1a..0969b479f28 100644
--- a/test/it/parser/src/main/resources/case/dml/insert.xml
+++ b/test/it/parser/src/main/resources/case/dml/insert.xml
@@ -2699,7 +2699,7 @@
</function>
</assignment-value>
<assignment-value>
- <literal-expression value="'500 Oracle Parkway"
start-index="59" stop-index="79" literal-start-index="59"
literal-stop-index="79" />
+ <literal-expression value="500 Oracle Parkway"
start-index="60" stop-index="79" literal-start-index="59"
literal-stop-index="79" />
</assignment-value>
<assignment-value>
<column name="sysdate" start-index="81" stop-index="87"
literal-start-index="81" literal-stop-index="87" />
@@ -2900,4 +2900,69 @@
</from>
</select>
</insert>
+ <insert sql-case-id="insert_with_nchar_1">
+ <table name="T1" start-index="12" stop-index="17">
+ <owner name="dbo" start-index="12" stop-index="14" />
+ </table>
+ <columns start-index="18" stop-index="18" />
+ <values>
+ <value>
+ <assignment-value>
+ <literal-expression value="1" start-index="27"
stop-index="27" />
+ </assignment-value>
+ <assignment-value>
+ <literal-expression value="Natalia" start-index="30"
stop-index="39" />
+ </assignment-value>
+ </value>
+ </values>
+ </insert>
+ <insert sql-case-id="insert_with_nchar_2">
+ <table name="T1" start-index="12" stop-index="17">
+ <owner name="dbo" start-index="12" stop-index="14" />
+ </table>
+ <columns start-index="18" stop-index="18" />
+ <values>
+ <value>
+ <assignment-value>
+ <literal-expression value="2" start-index="27"
stop-index="27" />
+ </assignment-value>
+ <assignment-value>
+ <literal-expression value="Mark" start-index="30"
stop-index="36" />
+ </assignment-value>
+ </value>
+ </values>
+ </insert>
+ <insert sql-case-id="insert_with_nchar_3">
+ <table name="T1" start-index="12" stop-index="17">
+ <owner name="dbo" start-index="12" stop-index="14" />
+ </table>
+ <columns start-index="18" stop-index="18" />
+ <values>
+ <value>
+ <assignment-value>
+ <literal-expression value="3" start-index="27"
stop-index="27" />
+ </assignment-value>
+ <assignment-value>
+ <literal-expression value="Randolph" start-index="30"
stop-index="40" />
+ </assignment-value>
+ </value>
+ </values>
+ </insert>
+ <insert sql-case-id="insert_with_dbName">
+ <table name="VariableTest" start-index="12" stop-index="46">
+ <owner name="dbo" start-index="31" stop-index="33">
+ <owner name="AdventureWorks2022" start-index="12"
stop-index="29"/>
+ </owner>
+ </table>
+ <columns start-index="47" stop-index="52">
+ <column name="Col1" start-index="48" stop-index="51" />
+ </columns>
+ <values>
+ <value>
+ <assignment-value>
+ <literal-expression value="$(tablename)" start-index="61"
stop-index="74" />
+ </assignment-value>
+ </value>
+ </values>
+ </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 4068b52514b..506e3db1f03 100644
--- a/test/it/parser/src/main/resources/case/dml/select.xml
+++ b/test/it/parser/src/main/resources/case/dml/select.xml
@@ -7307,19 +7307,19 @@
<select sql-case-id="select_with_unicode_string">
<projections start-index="7" stop-index="22" literal-start-index="7"
literal-stop-index="22">
- <expression-projection text="'test" start-index="7"
stop-index="13" literal-start-index="7"
+ <expression-projection text="test" start-index="7" stop-index="13"
literal-start-index="7"
literal-stop-index="13">
- <literalText>'test</literalText>
+ <literalText>test</literalText>
<expr>
- <literal-expression value="'test" start-index="7"
stop-index="13" literal-start-index="7"
+ <literal-expression value="test" start-index="7"
stop-index="13" literal-start-index="7"
literal-stop-index="13"/>
</expr>
</expression-projection>
- <expression-projection text="'test" start-index="16"
stop-index="22" literal-start-index="16"
+ <expression-projection text="test" start-index="16"
stop-index="22" literal-start-index="16"
literal-stop-index="22">
- <literalText>'test</literalText>
+ <literalText>test</literalText>
<expr>
- <literal-expression value="'test" start-index="16"
stop-index="22" literal-start-index="16"
+ <literal-expression value="test" start-index="16"
stop-index="22" literal-start-index="16"
literal-stop-index="22"/>
</expr>
</expression-projection>
@@ -7498,4 +7498,31 @@
<column-item name="updatable" order-direction="ASC"
start-index="116" stop-index="124" />
</order-by>
</select>
+ <select sql-case-id="select_with_script_variables">
+ <from>
+ <simple-table name="Person" alias="x" start-index="28"
stop-index="42">
+ <owner name="Person" start-index="28" stop-index="33"/>
+ </simple-table>
+ </from>
+ <projections start-index="7" stop-index="21">
+ <column-projection name="$(ColumnName)" start-index="7"
stop-index="21">
+ <owner name="x" start-index="7" stop-index="7" />
+ </column-projection>>
+ </projections>
+ <where start-index="44" stop-index="71">
+ <expr>
+ <binary-operation-expression start-index="50" stop-index="71">
+ <left>
+ <column name="BusinessEntityID" start-index="50"
stop-index="67">
+ <owner name="x" start-index="50" stop-index="50" />
+ </column>
+ </left>
+ <operator>></operator>
+ <right>
+ <literal-expression value="5" start-index="71"
stop-index="71" />
+ </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 a1a9d530623..7f83dbbb58a 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
@@ -103,4 +103,8 @@
<sql-case id="insert_with_national_character_set" value="INSERT INTO
customers VALUES (1000, TO_NCHAR('John Smith'),N'500 Oracle Parkway',sysdate);"
db-types="Oracle" />
<sql-case id="insert_with_oracle_datetime_type" value="INSERT INTO t_order
(create_date, create_timestamp, create_interval_year, create_interval_day)
VALUES (TO_DATE('2009', 'YYYY'), TO_DATE('2009', 'YYYY'), (TO_DATE('2009',
'YYYY') - TO_DATE('2009', 'YYYY')) year to MONTH, (TO_DATE('2009', 'YYYY') -
TO_DATE('2009', 'YYYY')) DAY TO SECOND);" db-types="Oracle" />
<sql-case id="insert_all_into" value="INSERT ALL INTO
T_MASK(ID,EMAIL,NAME,PHONE,ADDRESS) VALUES (1,'2','3','4','5') INTO
T_MASK(ID,EMAIL,NAME,PHONE,ADDRESS) VALUES (2,'2','3','4','5') INTO
T_MASK(ID,EMAIL,NAME,PHONE,ADDRESS) VALUES (3,'2','3','4','5') SELECT 1 FROM
DUAL" db-types="Oracle" />
+ <sql-case id="insert_with_nchar_1" value="INSERT INTO dbo.T1 VALUES (1,
N'Natalia')" db-types="SQLServer"/>
+ <sql-case id="insert_with_nchar_2" value="INSERT INTO dbo.T1 VALUES (2,
N'Mark')" db-types="SQLServer"/>
+ <sql-case id="insert_with_nchar_3" value="INSERT INTO dbo.T1 VALUES (3,
N'Randolph')" db-types="SQLServer"/>
+ <sql-case id="insert_with_dbName" value="INSERT INTO
AdventureWorks2022.dbo.VariableTest(Col1) VALUES('$(tablename)')"
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 a7927b8ecd1..7e7e5ef66b1 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
@@ -229,4 +229,5 @@
<sql-case id="select_wm_concat_function3" value="SELECT
REPLACE(WM_CONCAT(NAME),',','|') FROM TEST_TABLE" db-types="Oracle" />
<sql-case id="select_wm_concat_function4" value="SELECT
NAME,WM_CONCAT(DECODE(SUBSTR((TO_CHAR(NAME)),0,1),'.','0'||TO_CHAR(NAME),TO_CHAR(NAME)))
WM_NAME FROM TEST_TABLE WHERE NAME ='TEST' GROUP BY NAME" db-types="Oracle" />
<sql-case id="select_with_user_updatable_columns" value="SELECT
column_name, updatable FROM user_updatable_columns WHERE table_name =
'LOCATIONS_VIEW' ORDER BY column_name, updatable" db-types="Oracle" />
+ <sql-case id="select_with_script_variables" value="SELECT x.$(ColumnName)
FROM Person.Person x WHERE x.BusinessEntityID > 5" db-types="SQLServer" />
</sql-cases>
