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 2c3eb0ca0b7 Support parsing SQL Server SELECT TRIM (#30249)
2c3eb0ca0b7 is described below
commit 2c3eb0ca0b7741875b87cae6ede325fcfd515b5b
Author: zhengke zhou <[email protected]>
AuthorDate: Sat Feb 24 15:09:29 2024 +0800
Support parsing SQL Server SELECT TRIM (#30249)
* Support parsing SQL Server SELECT TRIM
* fix up
* fix code format
---
.../src/main/antlr4/imports/sqlserver/BaseRule.g4 | 11 ++-
.../src/main/antlr4/imports/sqlserver/Keyword.g4 | 12 ++++
.../statement/SQLServerStatementVisitor.java | 25 +++++++
.../parser/src/main/resources/case/dml/select.xml | 79 +++++++++++++++++++++-
.../main/resources/sql/supported/dml/select.xml | 6 +-
5 files changed, 128 insertions(+), 5 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 4ec987a43ea..380e20c58aa 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
@@ -54,7 +54,7 @@ hexadecimalLiterals
bitValueLiterals
: BIT_NUM_
;
-
+
booleanLiterals
: TRUE | FALSE
;
@@ -315,9 +315,14 @@ distinct
;
specialFunction
- : conversionFunction | charFunction | openJsonFunction | jsonFunction |
openRowSetFunction | windowFunction | approxFunction | openDatasourceFunction |
rowNumberFunction | graphFunction
+ : conversionFunction | charFunction | openJsonFunction | jsonFunction |
openRowSetFunction | windowFunction | approxFunction | openDatasourceFunction |
rowNumberFunction | graphFunction | trimFunction
;
+ trimFunction
+ : TRIM LP_ ((LEADING | BOTH | TRAILING) expr? FROM)? expr RP_
+ | TRIM LP_ (expr FROM)? expr RP_
+ ;
+
graphFunction
: graphAggFunction
;
@@ -478,7 +483,7 @@ partitionByClause
: PARTITION BY expr (COMMA_ expr)*
;
-rowRangeClause
+rowRangeClause
: (ROWS | RANGE) windowFrameExtent
;
diff --git
a/parser/sql/dialect/sqlserver/src/main/antlr4/imports/sqlserver/Keyword.g4
b/parser/sql/dialect/sqlserver/src/main/antlr4/imports/sqlserver/Keyword.g4
index 1bbf16738a6..90b39ae64e3 100644
--- a/parser/sql/dialect/sqlserver/src/main/antlr4/imports/sqlserver/Keyword.g4
+++ b/parser/sql/dialect/sqlserver/src/main/antlr4/imports/sqlserver/Keyword.g4
@@ -811,3 +811,15 @@ MATCHED
TARGET
: T A R G E T
;
+
+LEADING
+ : L E A D I N G
+ ;
+
+BOTH
+ : B O T H
+ ;
+
+TRAILING
+ : T R A I L I N G
+ ;
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 600709517d1..7ac2fd263e9 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
@@ -128,6 +128,7 @@ import
org.apache.shardingsphere.sql.parser.autogen.SQLServerStatementParser.Tab
import
org.apache.shardingsphere.sql.parser.autogen.SQLServerStatementParser.TableReferenceContext;
import
org.apache.shardingsphere.sql.parser.autogen.SQLServerStatementParser.TableReferencesContext;
import
org.apache.shardingsphere.sql.parser.autogen.SQLServerStatementParser.TopContext;
+import
org.apache.shardingsphere.sql.parser.autogen.SQLServerStatementParser.TrimFunctionContext;
import
org.apache.shardingsphere.sql.parser.autogen.SQLServerStatementParser.UnreservedWordContext;
import
org.apache.shardingsphere.sql.parser.autogen.SQLServerStatementParser.UpdateContext;
import
org.apache.shardingsphere.sql.parser.autogen.SQLServerStatementParser.UpdateStatisticsContext;
@@ -705,9 +706,33 @@ public abstract class SQLServerStatementVisitor extends
SQLServerStatementBaseVi
if (null != ctx.graphFunction()) {
return visit(ctx.graphFunction());
}
+ if (null != ctx.trimFunction()) {
+ return visit(ctx.trimFunction());
+ }
return new FunctionSegment(ctx.getStart().getStartIndex(),
ctx.getStop().getStopIndex(), ctx.getChild(0).getChild(0).getText(),
getOriginalText(ctx));
}
+ @Override
+ public ASTNode visitTrimFunction(final TrimFunctionContext ctx) {
+ FunctionSegment result = new
FunctionSegment(ctx.getStart().getStartIndex(), ctx.getStop().getStopIndex(),
ctx.TRIM().getText(), getOriginalText(ctx));
+ if (null != ctx.BOTH()) {
+ result.getParameters().add(new
LiteralExpressionSegment(ctx.BOTH().getSymbol().getStartIndex(),
ctx.BOTH().getSymbol().getStopIndex(),
+ new
OtherLiteralValue(ctx.BOTH().getSymbol().getText()).getValue()));
+ }
+ if (null != ctx.TRAILING()) {
+ result.getParameters().add(new
LiteralExpressionSegment(ctx.TRAILING().getSymbol().getStartIndex(),
ctx.TRAILING().getSymbol().getStopIndex(),
+ new
OtherLiteralValue(ctx.TRAILING().getSymbol().getText()).getValue()));
+ }
+ if (null != ctx.LEADING()) {
+ result.getParameters().add(new
LiteralExpressionSegment(ctx.LEADING().getSymbol().getStartIndex(),
ctx.LEADING().getSymbol().getStopIndex(),
+ new
OtherLiteralValue(ctx.LEADING().getSymbol().getText()).getValue()));
+ }
+ for (ExprContext each : ctx.expr()) {
+ result.getParameters().add((ExpressionSegment) visit(each));
+ }
+ return result;
+ }
+
@Override
public ASTNode visitGraphFunction(final GraphFunctionContext ctx) {
if (null != ctx.graphAggFunction()) {
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 2ad4839e311..4998f34fe5c 100644
--- a/test/it/parser/src/main/resources/case/dml/select.xml
+++ b/test/it/parser/src/main/resources/case/dml/select.xml
@@ -6436,7 +6436,7 @@
</order-by>
</select>
- <select sql-case-id="select_with_trim_function">
+ <select sql-case-id="select_with_trim_function_multi">
<projections start-index="7" stop-index="203" literal-start-index="7"
literal-stop-index="203">
<expression-projection text="TRIM(' derby ')" start-index="7"
stop-index="22" literal-start-index="7" literal-stop-index="22">
<literalText>TRIM(' derby ')</literalText>
@@ -6517,6 +6517,83 @@
<simple-table name="employees" start-index="210" stop-index="218"
literal-start-index="210" literal-stop-index="218" />
</from>
</select>
+
+ <select sql-case-id="select_with_trim_function_simple">
+ <projections start-index="7" stop-index="57">
+ <expression-projection text="TRIM( '.,! ' FROM ' # test
.')" start-index="7" stop-index="57" alias="Result">
+ <expr>
+ <function function-name="TRIM" start-index="7"
stop-index="47" text="TRIM( '.,! ' FROM ' # test .')">
+ <parameter>
+ <literal-expression value=".,! " start-index="13"
stop-index="18" />
+ </parameter>
+ <parameter>
+ <literal-expression value=" # test ."
start-index="25" stop-index="46" />
+ </parameter>
+ </function>
+ </expr>
+ </expression-projection>
+ </projections>
+ </select>
+
+ <select sql-case-id="select_with_trim_function_leading">
+ <projections start-index="7" stop-index="66">
+ <expression-projection text="TRIM(LEADING '.,! ' FROM ' .#
test .')" start-index="7" stop-index="66" alias="Result">
+ <expr>
+ <function function-name="TRIM" start-index="7"
stop-index="56" text="TRIM(LEADING '.,! ' FROM ' .# test .')">
+ <parameter>
+ <literal-expression value="LEADING"
start-index="12" stop-index="18" />
+ </parameter>
+ <parameter>
+ <literal-expression value=".,! " start-index="20"
stop-index="25" />
+ </parameter>
+ <parameter>
+ <literal-expression value=" .# test ."
start-index="33" stop-index="55" />
+ </parameter>
+ </function>
+ </expr>
+ </expression-projection>
+ </projections>
+ </select>
+
+ <select sql-case-id="select_with_trim_function_trailing">
+ <projections start-index="7" stop-index="66">
+ <expression-projection text="TRIM(TRAILING '.,! ' FROM ' .#
test .')" start-index="7" stop-index="66" alias="Result">
+ <expr>
+ <function function-name="TRIM" start-index="7"
stop-index="56" text="TRIM(TRAILING '.,! ' FROM ' .# test .')">
+ <parameter>
+ <literal-expression value="TRAILING"
start-index="12" stop-index="19" />
+ </parameter>
+ <parameter>
+ <literal-expression value=".,! " start-index="21"
stop-index="26" />
+ </parameter>
+ <parameter>
+ <literal-expression value=" .# test ."
start-index="33" stop-index="55" />
+ </parameter>
+ </function>
+ </expr>
+ </expression-projection>
+ </projections>
+ </select>
+
+ <select sql-case-id="select_with_trim_function_both">
+ <projections start-index="7" stop-index="49">
+ <expression-projection text="TRIM(BOTH '123' FROM '123abc123')"
start-index="7" stop-index="49" alias="Result">
+ <expr>
+ <function function-name="TRIM" start-index="7"
stop-index="39" text="TRIM(BOTH '123' FROM '123abc123')">
+ <parameter>
+ <literal-expression value="BOTH" start-index="12"
stop-index="15" />
+ </parameter>
+ <parameter>
+ <literal-expression value="123" start-index="17"
stop-index="21" />
+ </parameter>
+ <parameter>
+ <literal-expression value="123abc123"
start-index="28" stop-index="38" />
+ </parameter>
+ </function>
+ </expr>
+ </expression-projection>
+ </projections>
+ </select>
<select sql-case-id="select_with_ratio_to_report_function">
<projections start-index="7" stop-index="79" literal-start-index="7"
literal-stop-index="79">
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 6147314ac3f..add821df9fd 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
@@ -208,7 +208,11 @@
<sql-case id="select_with_percentile_functions" value="SELECT
department_id, PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY salary DESC) 'Median
cont', PERCENTILE_DISC(0.5) WITHIN GROUP (ORDER BY salary DESC) 'Median disc'
FROM employees GROUP BY department_id" db-types="Oracle" />
<sql-case id="select_with_keep_clause" value="SELECT salary,MIN(salary)
KEEP (DENSE_RANK FIRST ORDER BY commission_pct) OVER (PARTITION BY
department_id) 'Worst', MAX(salary) KEEP (DENSE_RANK LAST ORDER BY
commission_pct) OVER (PARTITION BY department_id) 'Best' FROM employees ORDER
BY department_id" db-types="Oracle" />
<sql-case id="select_with_corr_function" value="SELECT employee_id,
CORR(SYSDATE - hire_date, salary) FROM employees WHERE department_id in (50,
80) ORDER BY employee_id" db-types="Oracle" />
- <sql-case id="select_with_trim_function" value="select TRIM(' derby '),
TRIM(BOTH ' ' FROM ' derby '), TRIM(TRAILING ' ' FROM ' derby '), TRIM(cast
(null as char(1)) FROM ' derby '), TRIM(' ' FROM cast(null as varchar(30))),
TRIM('y' FROM ' derby') FROM employees" db-types="Oracle" />
+ <sql-case id="select_with_trim_function_multi" value="select TRIM(' derby
'), TRIM(BOTH ' ' FROM ' derby '), TRIM(TRAILING ' ' FROM ' derby '),
TRIM(cast (null as char(1)) FROM ' derby '), TRIM(' ' FROM cast(null as
varchar(30))), TRIM('y' FROM ' derby') FROM employees" db-types="Oracle" />
+ <sql-case id="select_with_trim_function_simple" value="SELECT TRIM( '.,! '
FROM ' # test .') AS Result" db-types="SQLServer" />
+ <sql-case id="select_with_trim_function_leading" value="SELECT
TRIM(LEADING '.,! ' FROM ' .# test .') AS Result"
db-types="SQLServer" />
+ <sql-case id="select_with_trim_function_trailing" value="SELECT
TRIM(TRAILING '.,! ' FROM ' .# test .') AS Result"
db-types="SQLServer" />
+ <sql-case id="select_with_trim_function_both" value="SELECT TRIM(BOTH
'123' FROM '123abc123') AS Result" db-types="SQLServer" />
<sql-case id="select_with_ratio_to_report_function" value="SELECT
TO_CHAR(RATIO_TO_REPORT(amount_sold) OVER (), '9.999') AS RATIO_TO_REPORT FROM
sales s GROUP BY s.channel_desc" db-types="Oracle" />
<sql-case id="select_with_sys_xmlagg_and_xmlgen" value="SELECT
SYS_XMLAGG(last_name) a, SYS_XMLGEN(last_name) b FROM employees WHERE last_name
LIKE 'R%' ORDER BY xmlagg;" db-types="Oracle" />
<sql-case id="select_with_cover_pop_and_covar_samp" value="SELECT
product_id, supplier_id, COVAR_POP(list_price, min_price) OVER (ORDER BY
product_id, supplier_id) AS CUM_COVP, COVAR_SAMP(list_price, min_price) OVER
(ORDER BY product_id, supplier_id) AS CUM_COVS FROM product_information p
WHERE category_id = 29 ORDER BY product_id, supplier_id" db-types="Oracle" />
