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 8806813bf9a Support SQLServer convert function parse (#29138)
8806813bf9a is described below
commit 8806813bf9a649e3ea44e7b606a808784b22b5c0
Author: niu niu <[email protected]>
AuthorDate: Thu Nov 23 17:53:25 2023 +0800
Support SQLServer convert function parse (#29138)
---
.../src/main/antlr4/imports/sqlserver/BaseRule.g4 | 6 +-
.../src/main/antlr4/imports/sqlserver/Literals.g4 | 2 +-
.../statement/SQLServerStatementVisitor.java | 15 ++
.../parser/src/main/resources/case/dml/select.xml | 295 ++++++++++++++++++++-
.../main/resources/sql/supported/dml/select.xml | 13 +-
5 files changed, 327 insertions(+), 4 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 487acd22e19..c6837bc7874 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
@@ -300,13 +300,17 @@ distinct
;
specialFunction
- : castFunction | charFunction
+ : castFunction | charFunction | convertFunction
;
castFunction
: CAST LP_ expr AS dataType RP_
;
+convertFunction
+ : CONVERT LP_ dataType COMMA_ expr (COMMA_ NUMBER_)? RP_
+ ;
+
charFunction
: CHAR LP_ expr (COMMA_ expr)* (USING ignoredIdentifier)? RP_
;
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 099b735c8af..3f259ff7491 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_) [a-zA-Z0-9@$#_,.\\/\u0080-\uFFFF ]+ (DQ_ | RBT_)
;
STRING_
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 4147471516b..fed3241059e 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
@@ -42,6 +42,7 @@ import
org.apache.shardingsphere.sql.parser.autogen.SQLServerStatementParser.Col
import
org.apache.shardingsphere.sql.parser.autogen.SQLServerStatementParser.ColumnNamesContext;
import
org.apache.shardingsphere.sql.parser.autogen.SQLServerStatementParser.ColumnNamesWithSortContext;
import
org.apache.shardingsphere.sql.parser.autogen.SQLServerStatementParser.ConstraintNameContext;
+import
org.apache.shardingsphere.sql.parser.autogen.SQLServerStatementParser.ConvertFunctionContext;
import
org.apache.shardingsphere.sql.parser.autogen.SQLServerStatementParser.CreateTableAsSelectClauseContext;
import
org.apache.shardingsphere.sql.parser.autogen.SQLServerStatementParser.CteClauseContext;
import
org.apache.shardingsphere.sql.parser.autogen.SQLServerStatementParser.CteClauseSetContext;
@@ -598,6 +599,9 @@ public abstract class SQLServerStatementVisitor extends
SQLServerStatementBaseVi
if (null != ctx.castFunction()) {
return visit(ctx.castFunction());
}
+ if (null != ctx.convertFunction()) {
+ return visit(ctx.convertFunction());
+ }
if (null != ctx.charFunction()) {
return visit(ctx.charFunction());
}
@@ -618,6 +622,17 @@ public abstract class SQLServerStatementVisitor extends
SQLServerStatementBaseVi
return result;
}
+ @Override
+ public ASTNode visitConvertFunction(final ConvertFunctionContext ctx) {
+ FunctionSegment result = new
FunctionSegment(ctx.getStart().getStartIndex(), ctx.getStop().getStopIndex(),
ctx.CONVERT().getText(), getOriginalText(ctx));
+ result.getParameters().add((DataTypeSegment) visit(ctx.dataType()));
+ result.getParameters().add((ExpressionSegment) visit(ctx.expr()));
+ if (null != ctx.NUMBER_()) {
+ result.getParameters().add(new
LiteralExpressionSegment(ctx.getStart().getStartIndex(),
ctx.getStop().getStopIndex(), ctx.NUMBER_().getText()));
+ }
+ return result;
+ }
+
@Override
public final ASTNode visitCharFunction(final CharFunctionContext ctx) {
calculateParameterCount(ctx.expr());
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 fa588d4f47a..2e4c1d956f7 100644
--- a/test/it/parser/src/main/resources/case/dml/select.xml
+++ b/test/it/parser/src/main/resources/case/dml/select.xml
@@ -3771,7 +3771,7 @@
</where>
</select>
- <select sql-case-id="select_with_convert_function">
+ <select sql-case-id="select_with_convert_function1">
<from>
<simple-table name="t_order" start-index="70" stop-index="76" />
</from>
@@ -3797,6 +3797,299 @@
</where>
</select>
+ <select sql-case-id="select_with_convert_function2">
+ <projections start-index="7" stop-index="246">
+ <column-projection name="sequence_name" start-index="7"
stop-index="19" />
+ <column-projection name="sequence_catalog" start-index="22"
stop-index="37" />
+ <column-projection name="sequence_schema" start-index="40"
stop-index="54" />
+ <expression-projection alias="start_value" text="convert( bigint,
start_value )" start-index="57" stop-index="101">
+ <expr>
+ <function function-name="convert" text="convert( bigint,
start_value )" start-index="57" stop-index="86">
+ <parameter>
+ <data-type value="bigint" start-index="66"
stop-index="71" />
+ </parameter>
+ <parameter>
+ <column name="start_value" start-index="74"
stop-index="84" />
+ </parameter>
+ </function>
+ </expr>
+ </expression-projection>
+ <expression-projection alias="minimum_value" text="convert(
bigint, minimum_value )" start-index="104" stop-index="152">
+ <expr>
+ <function function-name="convert" text="convert( bigint,
minimum_value )" start-index="104" stop-index="135">
+ <parameter>
+ <data-type value="bigint" start-index="113"
stop-index="118" />
+ </parameter>
+ <parameter>
+ <column name="minimum_value" start-index="121"
stop-index="133" />
+ </parameter>
+ </function>
+ </expr>
+ </expression-projection>
+ <expression-projection alias="maximum_value" text="convert(
bigint, maximum_value )" start-index="155" stop-index="203">
+ <expr>
+ <function function-name="convert" text="convert( bigint,
maximum_value )" start-index="155" stop-index="186">
+ <parameter>
+ <data-type value="bigint" start-index="164"
stop-index="169" />
+ </parameter>
+ <parameter>
+ <column name="maximum_value" start-index="172"
stop-index="184" />
+ </parameter>
+ </function>
+ </expr>
+ </expression-projection>
+ <expression-projection alias="increment" text="convert( bigint,
increment )" start-index="206" stop-index="246">
+ <expr>
+ <function function-name="convert" text="convert( bigint,
increment )" start-index="206" stop-index="233">
+ <parameter>
+ <data-type value="bigint" start-index="215"
stop-index="220" />
+ </parameter>
+ <parameter>
+ <column name="increment" start-index="223"
stop-index="231" />
+ </parameter>
+ </function>
+ </expr>
+ </expression-projection>
+ </projections>
+ <from>
+ <simple-table name="SEQUENCES" start-index="253" stop-index="280">
+ <owner name="INFORMATION_SCHEMA" start-index="253"
stop-index="270" />
+ </simple-table>
+ </from>
+ </select>
+
+ <select sql-case-id="select_with_convert_function3">
+ <projections start-index="7" stop-index="37">
+ <expression-projection text="CONVERT(NVARCHAR, GETDATE(), 0)"
start-index="7" stop-index="37">
+ <expr>
+ <function function-name="CONVERT" text="CONVERT(NVARCHAR,
GETDATE(), 0)" start-index="7" stop-index="37">
+ <parameter>
+ <data-type value="NVARCHAR" start-index="15"
stop-index="22" />
+ </parameter>
+ <parameter>
+ <function function-name="GETDATE" text="GETDATE()"
start-index="25" stop-index="33" />
+ </parameter>
+ <parameter>
+ <literal-expression value="0" start-index="7"
stop-index="37" />
+ </parameter>
+ </function>
+ </expr>
+ </expression-projection>
+ </projections>
+ </select>
+
+ <select sql-case-id="select_with_convert_function4">
+ <projections start-index="7" stop-index="61">
+ <expression-projection text="CONVERT(DECIMAL(10, 5),
CONVERT(VARBINARY(20), @myval))" start-index="7" stop-index="61">
+ <expr>
+ <function function-name="CONVERT"
text="CONVERT(DECIMAL(10, 5), CONVERT(VARBINARY(20), @myval))" start-index="7"
stop-index="61">
+ <parameter>
+ <data-type value="DECIMAL" start-index="15"
stop-index="28" />
+ </parameter>
+ <parameter>
+ <function function-name="CONVERT"
text="CONVERT(VARBINARY(20), @myval)" start-index="31" stop-index="60">
+ <parameter>
+ <data-type value="VARBINARY"
start-index="39" stop-index="51" />
+ </parameter>
+ <parameter>
+ <column name="@myval" start-index="54"
stop-index="59" />
+ </parameter>
+ </function>
+ </parameter>
+ </function>
+ </expr>
+ </expression-projection>
+ </projections>
+ </select>
+
+ <select sql-case-id="select_with_convert_function5">
+ <projections start-index="7" stop-index="69">
+ <expression-projection alias="Style 0, character to binary"
text="CONVERT(BINARY(8), 'Name', 0)" start-index="7" stop-index="69">
+ <expr>
+ <function function-name="CONVERT" text="CONVERT(BINARY(8),
'Name', 0)" start-index="7" stop-index="35">
+ <parameter>
+ <data-type value="BINARY" start-index="15"
stop-index="23" />
+ </parameter>
+ <parameter>
+ <literal-expression value="Name" start-index="26"
stop-index="31" />
+ </parameter>
+ <parameter>
+ <literal-expression value="0" start-index="7"
stop-index="35" />
+ </parameter>
+ </function>
+ </expr>
+ </expression-projection>
+ </projections>
+ </select>
+
+ <select sql-case-id="select_with_convert_xml1">
+ <projections start-index="7" stop-index="43">
+ <expression-projection text="CONVERT(XML,
'<root><child/></root>')" start-index="7" stop-index="43">
+ <expr>
+ <function function-name="CONVERT" text="CONVERT(XML,
'<root><child/></root>')" start-index="7" stop-index="43">
+ <parameter>
+ <data-type value="XML" start-index="15"
stop-index="17" />
+ </parameter>
+ <parameter>
+ <literal-expression
value="<root><child/></root>" start-index="20"
stop-index="42" />
+ </parameter>
+ </function>
+ </expr>
+ </expression-projection>
+ </projections>
+ </select>
+
+ <select sql-case-id="select_with_convert_xml2">
+ <projections start-index="7" stop-index="65">
+ <expression-projection text="CONVERT(XML, '<root>
<child/> </root>', 1)" start-index="7" stop-index="65">
+ <expr>
+ <function function-name="CONVERT" text="CONVERT(XML,
'<root> <child/> </root>', 1)"
start-index="7" stop-index="65">
+ <parameter>
+ <data-type value="XML" start-index="15"
stop-index="17" />
+ </parameter>
+ <parameter>
+ <literal-expression value="<root>
<child/> </root>" start-index="20" stop-index="61" />
+ </parameter>
+ <parameter>
+ <literal-expression value="1" start-index="7"
stop-index="65" />
+ </parameter>
+ </function>
+ </expr>
+ </expression-projection>
+ </projections>
+ </select>
+
+ <select sql-case-id="select_cast_convert_function">
+ <projections start-index="7" stop-index="149">
+ <expression-projection alias="UnconvertedDateTime"
text="GETDATE()" start-index="7" stop-index="38">
+ <expr>
+ <function function-name="GETDATE" text="GETDATE()"
start-index="7" stop-index="15" />
+ </expr>
+ </expression-projection>
+ <expression-projection alias="UsingCast" text="CAST(GETDATE() AS
NVARCHAR(30))" start-index="41" stop-index="84">
+ <expr>
+ <function function-name="CAST" text="CAST(GETDATE() AS
NVARCHAR(30))" start-index="41" stop-index="71">
+ <parameter>
+ <data-type value="NVARCHAR" start-index="59"
stop-index="70" />
+ </parameter>
+ </function>
+ </expr>
+ </expression-projection>
+ <expression-projection alias="UsingConvertTo_ISO8601"
text="CONVERT(NVARCHAR(30), GETDATE(), 126)" start-index="87" stop-index="149">
+ <expr>
+ <function function-name="CONVERT"
text="CONVERT(NVARCHAR(30), GETDATE(), 126)" start-index="87" stop-index="123">
+ <parameter>
+ <data-type value="NVARCHAR" start-index="95"
stop-index="106" />
+ </parameter>
+ <parameter>
+ <function function-name="GETDATE" text="GETDATE()"
start-index="109" stop-index="117" />
+ </parameter>
+ <parameter>
+ <literal-expression value="126" start-index="87"
stop-index="123" />
+ </parameter>
+ </function>
+ </expr>
+ </expression-projection>
+ </projections>
+ </select>
+
+ <select sql-case-id="select_cast_convert_datetime">
+ <projections start-index="7" stop-index="187">
+ <expression-projection alias="UnconvertedText"
text="2006-04-25T15:50:59.997" start-index="7" stop-index="50">
+ <expr>
+ <literal-expression value="2006-04-25T15:50:59.997"
start-index="7" stop-index="31" />
+ </expr>
+ </expression-projection>
+ <expression-projection alias="UsingCast"
text="CAST('2006-04-25T15:50:59.997' AS DATETIME)" start-index="53"
stop-index="108">
+ <expr>
+ <function function-name="CAST"
text="CAST('2006-04-25T15:50:59.997' AS DATETIME)" start-index="53"
stop-index="95">
+ <parameter>
+ <literal-expression
value="2006-04-25T15:50:59.997" start-index="58" stop-index="82" />
+ </parameter>
+ <parameter>
+ <data-type value="DATETIME" start-index="87"
stop-index="94" />
+ </parameter>
+ </function>
+ </expr>
+ </expression-projection>
+ <expression-projection alias="UsingConvertFrom_ISO8601"
text="CONVERT(DATETIME, '2006-04-25T15:50:59.997', 126)" start-index="111"
stop-index="187">
+ <expr>
+ <function function-name="CONVERT" text="CONVERT(DATETIME,
'2006-04-25T15:50:59.997', 126)" start-index="111" stop-index="159">
+ <parameter>
+ <data-type value="DATETIME" start-index="119"
stop-index="126" />
+ </parameter>
+ <parameter>
+ <literal-expression
value="2006-04-25T15:50:59.997" start-index="129" stop-index="153" />
+ </parameter>
+ <parameter>
+ <literal-expression value="126" start-index="111"
stop-index="159" />
+ </parameter>
+ </function>
+ </expr>
+ </expression-projection>
+ </projections>
+ </select>
+
+ <select sql-case-id="select_with_convert_hex1">
+ <projections start-index="7" stop-index="71">
+ <expression-projection alias="Style 0, binary to character"
text="CONVERT(CHAR(8), 0x4E616d65, 0)" start-index="7" stop-index="71">
+ <expr>
+ <function function-name="CONVERT" text="CONVERT(CHAR(8),
0x4E616d65, 0)" start-index="7" stop-index="37">
+ <parameter>
+ <data-type value="CHAR" start-index="15"
stop-index="21" />
+ </parameter>
+ <parameter>
+ <common-expression literal-text="0x4E616d65"
start-index="24" stop-index="33" />
+ </parameter>
+ <parameter>
+ <literal-expression value="0" start-index="7"
stop-index="37" />
+ </parameter>
+ </function>
+ </expr>
+ </expression-projection>
+ </projections>
+ </select>
+
+ <select sql-case-id="select_with_convert_hex2">
+ <projections start-index="7" stop-index="71">
+ <expression-projection alias="Style 1, binary to character"
text="CONVERT(CHAR(8), 0x4E616d65, 1)" start-index="7" stop-index="71">
+ <expr>
+ <function function-name="CONVERT" text="CONVERT(CHAR(8),
0x4E616d65, 1)" start-index="7" stop-index="37">
+ <parameter>
+ <data-type value="CHAR" start-index="15"
stop-index="21" />
+ </parameter>
+ <parameter>
+ <common-expression literal-text="0x4E616d65"
start-index="24" stop-index="33" />
+ </parameter>
+ <parameter>
+ <literal-expression value="1" start-index="7"
stop-index="37" />
+ </parameter>
+ </function>
+ </expr>
+ </expression-projection>
+ </projections>
+ </select>
+
+ <select sql-case-id="select_with_convert_hex3">
+ <projections start-index="7" stop-index="75">
+ <expression-projection alias="Style 1, character to binary"
text="CONVERT(BINARY(4), '0x4E616D65', 1)" start-index="7" stop-index="75">
+ <expr>
+ <function function-name="CONVERT" text="CONVERT(BINARY(4),
'0x4E616D65', 1)" start-index="7" stop-index="41">
+ <parameter>
+ <data-type value="BINARY" start-index="15"
stop-index="23" />
+ </parameter>
+ <parameter>
+ <literal-expression value="0x4E616D65"
start-index="26" stop-index="37" />
+ </parameter>
+ <parameter>
+ <literal-expression value="1" start-index="7"
stop-index="41" />
+ </parameter>
+ </function>
+ </expr>
+ </expression-projection>
+ </projections>
+ </select>
+
<select sql-case-id="select_with_json_extract">
<from>
<simple-table name="tb_content_json" start-index="70"
stop-index="84" />
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 cff56d26a3f..a7927b8ecd1 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
@@ -90,7 +90,18 @@
<sql-case id="select_with_match_against" value="SELECT * FROM t_order_item
WHERE MATCH(t_order_item.description) AGAINST (? IN NATURAL LANGUAGE MODE) AND
user_id = ?" db-types="MySQL" />
<sql-case id="select_with_json_separator" value="select
content_json->>'$.nation' as nation,content_json->>'$.title' as title from
tb_content_json b where b.content_id=1" db-types="MySQL" />
<sql-case id="select_with_json_value_return_type" value="SELECT * FROM
t_order WHERE JSON_VALUE(items, '$.name' RETURNING VARCHAR(100)) = 'jack'"
db-types="MySQL" />
- <sql-case id="select_with_convert_function" value="SELECT
CONVERT(SUBSTRING(content, 5) , SIGNED) AS signed_content FROM t_order WHERE
order_id = 1" db-types="MySQL" />
+ <sql-case id="select_with_convert_function1" value="SELECT
CONVERT(SUBSTRING(content, 5) , SIGNED) AS signed_content FROM t_order WHERE
order_id = 1" db-types="MySQL" />
+ <sql-case id="select_with_convert_function2" value="select sequence_name,
sequence_catalog, sequence_schema, convert( bigint, start_value ) as
start_value, convert( bigint, minimum_value ) as minimum_value, convert(
bigint, maximum_value ) as maximum_value, convert( bigint, increment ) as
increment from INFORMATION_SCHEMA.SEQUENCES" db-types="SQLServer" />
+ <sql-case id="select_with_convert_function3" value="SELECT
CONVERT(NVARCHAR, GETDATE(), 0)" db-types="SQLServer" />
+ <sql-case id="select_with_convert_function4" value="SELECT
CONVERT(DECIMAL(10, 5), CONVERT(VARBINARY(20), @myval))" db-types="SQLServer" />
+ <sql-case id="select_with_convert_function5" value="SELECT
CONVERT(BINARY(8), 'Name', 0) AS [Style 0, character to binary]"
db-types="SQLServer" />
+ <sql-case id="select_with_convert_xml1" value="SELECT CONVERT(XML,
'<root><child/></root>')" db-types="SQLServer" />
+ <sql-case id="select_with_convert_xml2" value="SELECT CONVERT(XML,
'<root> <child/> </root>', 1)"
db-types="SQLServer" />
+ <sql-case id="select_cast_convert_function" value="SELECT GETDATE() AS
UnconvertedDateTime, CAST(GETDATE() AS NVARCHAR(30)) AS UsingCast,
CONVERT(NVARCHAR(30), GETDATE(), 126) AS UsingConvertTo_ISO8601"
db-types="SQLServer" />
+ <sql-case id="select_cast_convert_datetime" value="SELECT
'2006-04-25T15:50:59.997' AS UnconvertedText, CAST('2006-04-25T15:50:59.997' AS
DATETIME) AS UsingCast, CONVERT(DATETIME, '2006-04-25T15:50:59.997', 126) AS
UsingConvertFrom_ISO8601" db-types="SQLServer" />
+ <sql-case id="select_with_convert_hex1" value="SELECT CONVERT(CHAR(8),
0x4E616d65, 0) AS [Style 0, binary to character]" db-types="SQLServer" />
+ <sql-case id="select_with_convert_hex2" value="SELECT CONVERT(CHAR(8),
0x4E616d65, 1) AS [Style 1, binary to character]" db-types="SQLServer" />
+ <sql-case id="select_with_convert_hex3" value="SELECT CONVERT(BINARY(4),
'0x4E616D65', 1) AS [Style 1, character to binary]" db-types="SQLServer" />
<sql-case id="select_with_json_extract" value="SELECT
content_json::json->'title', content_json::json->'nation' FROM tb_content_json
WHERE content_id = 1" db-types="PostgreSQL,openGauss" />
<sql-case id="select_with_json_extract_text" value="SELECT * FROM
tb_content_json WHERE content_json::json->>'nation'='CHINA'"
db-types="PostgreSQL,openGauss" />
<sql-case id="select_with_json_path_extract" value="SELECT
content_json::json#>'{keyword,1}' FROM tb_content_json"
db-types="PostgreSQL,openGauss" />
