You are viewing a plain text version of this content. The canonical link for it is here.
Posted to notifications@shardingsphere.apache.org by du...@apache.org on 2023/04/17 08:13:39 UTC
[shardingsphere] branch master updated: Trim functions support (#25189)
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 be806e8f2fb Trim functions support (#25189)
be806e8f2fb is described below
commit be806e8f2fb89f2c9dce88de45cdbfa98188e2f2
Author: kanha gupta <92...@users.noreply.github.com>
AuthorDate: Mon Apr 17 13:43:32 2023 +0530
Trim functions support (#25189)
* support for Char function
* support for Weight string SQL
* Support for Char & weight string Function
* Support for Substring function
* Support for substring,dual,spatial functions
* support for Char function
* support for Weight string SQL
* Support for Char & weight string Function
* Support for Substring function
* Support for trim functions
* support for Char function
* support for Weight string SQL
* Support for Char & weight string Function
* null
* Revert "Support for Substring function"
This reverts commit 9af038be0f4be54affd801c9ac2f6750db883377.
* Support for Trim functions
---
.../impl/LiteralExpressionConverter.java | 18 ++++++++
.../statement/impl/MySQLStatementSQLVisitor.java | 21 ++++++++-
.../test/it/optimize/SQLNodeConverterEngineIT.java | 3 ++
.../resources/case/dml/select-special-function.xml | 52 +++++++++++++++++-----
.../sql/supported/dml/select-special-function.xml | 6 +--
5 files changed, 84 insertions(+), 16 deletions(-)
diff --git a/kernel/sql-federation/optimizer/src/main/java/org/apache/shardingsphere/sqlfederation/optimizer/converter/segment/expression/impl/LiteralExpressionConverter.java b/kernel/sql-federation/optimizer/src/main/java/org/apache/shardingsphere/sqlfederation/optimizer/converter/segment/expression/impl/LiteralExpressionConverter.java
index dc8543b22a2..624adaaa0c9 100644
--- a/kernel/sql-federation/optimizer/src/main/java/org/apache/shardingsphere/sqlfederation/optimizer/converter/segment/expression/impl/LiteralExpressionConverter.java
+++ b/kernel/sql-federation/optimizer/src/main/java/org/apache/shardingsphere/sqlfederation/optimizer/converter/segment/expression/impl/LiteralExpressionConverter.java
@@ -20,6 +20,7 @@ package org.apache.shardingsphere.sqlfederation.optimizer.converter.segment.expr
import org.apache.calcite.sql.SqlLiteral;
import org.apache.calcite.sql.SqlNode;
import org.apache.calcite.sql.parser.SqlParserPos;
+import org.apache.calcite.sql.fun.SqlTrimFunction;
import org.apache.shardingsphere.sql.parser.sql.common.segment.dml.expr.simple.LiteralExpressionSegment;
import org.apache.shardingsphere.sqlfederation.optimizer.converter.segment.SQLSegmentConverter;
@@ -38,6 +39,23 @@ public final class LiteralExpressionConverter implements SQLSegmentConverter<Lit
if (segment.getLiterals() instanceof Integer) {
return Optional.of(SqlLiteral.createExactNumeric(String.valueOf(segment.getLiterals()), SqlParserPos.ZERO));
}
+ if (segment.getLiterals().equals("BOTH") || segment.getLiterals().equals("LEADING") || segment.getLiterals().equals("TRAILING")) {
+ SqlTrimFunction.Flag flag;
+ switch (segment.getLiterals().toString()) {
+ case "BOTH":
+ flag = SqlTrimFunction.Flag.BOTH;
+ break;
+ case "LEADING":
+ flag = SqlTrimFunction.Flag.LEADING;
+ break;
+ case "TRAILING":
+ flag = SqlTrimFunction.Flag.TRAILING;
+ break;
+ default:
+ throw new IllegalArgumentException("Invalid literal for flag: " + segment.getLiterals());
+ }
+ return Optional.of(SqlLiteral.createSymbol(flag, SqlParserPos.ZERO));
+ }
if (segment.getLiterals() instanceof String) {
return Optional.of(SqlLiteral.createCharString(String.valueOf(segment.getLiterals()), SqlParserPos.ZERO));
}
diff --git a/sql-parser/dialect/mysql/src/main/java/org/apache/shardingsphere/sql/parser/mysql/visitor/statement/impl/MySQLStatementSQLVisitor.java b/sql-parser/dialect/mysql/src/main/java/org/apache/shardingsphere/sql/parser/mysql/visitor/statement/impl/MySQLStatementSQLVisitor.java
index 656ed7f831f..f71f31196aa 100644
--- a/sql-parser/dialect/mysql/src/main/java/org/apache/shardingsphere/sql/parser/mysql/visitor/statement/impl/MySQLStatementSQLVisitor.java
+++ b/sql-parser/dialect/mysql/src/main/java/org/apache/shardingsphere/sql/parser/mysql/visitor/statement/impl/MySQLStatementSQLVisitor.java
@@ -961,11 +961,28 @@ public abstract class MySQLStatementSQLVisitor extends MySQLStatementBaseVisitor
}
return result;
}
-
+
@Override
public final ASTNode visitTrimFunction(final TrimFunctionContext ctx) {
calculateParameterCount(ctx.expr());
- return new FunctionSegment(ctx.getStart().getStartIndex(), ctx.getStop().getStopIndex(), ctx.TRIM().getText(), getOriginalText(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()) {
+ ASTNode expr = visit(each);
+ result.getParameters().add((ExpressionSegment) expr);
+ }
+ return result;
}
@Override
diff --git a/test/it/optimizer/src/test/java/org/apache/shardingsphere/test/it/optimize/SQLNodeConverterEngineIT.java b/test/it/optimizer/src/test/java/org/apache/shardingsphere/test/it/optimize/SQLNodeConverterEngineIT.java
index e3ec5422aa5..09ec95ab03b 100644
--- a/test/it/optimizer/src/test/java/org/apache/shardingsphere/test/it/optimize/SQLNodeConverterEngineIT.java
+++ b/test/it/optimizer/src/test/java/org/apache/shardingsphere/test/it/optimize/SQLNodeConverterEngineIT.java
@@ -193,6 +193,9 @@ class SQLNodeConverterEngineIT {
result.add("select_order_by_for_nulls_last");
result.add("select_char");
result.add("select_weight_string");
+ result.add("select_trim");
+ result.add("select_with_trim_expr");
+ result.add("select_with_trim_expr_from_expr");
return result;
}
// CHECKSTYLE:ON
diff --git a/test/it/parser/src/main/resources/case/dml/select-special-function.xml b/test/it/parser/src/main/resources/case/dml/select-special-function.xml
index 172d31f4e62..7a1a1f06477 100644
--- a/test/it/parser/src/main/resources/case/dml/select-special-function.xml
+++ b/test/it/parser/src/main/resources/case/dml/select-special-function.xml
@@ -150,36 +150,66 @@
</projections>
</select>
<select sql-case-id="select_trim">
- <projections start-index="7" stop-index="22">
- <expression-projection text="TRIM(' bar ')" start-index="7" stop-index="22">
+ <projections start-index="7" stop-index="33">
+ <expression-projection text="TRIM(BOTH ' ' from ' bar ')" start-index="7" stop-index="33">
<expr>
- <function function-name="TRIM" start-index="7" stop-index="22" text="TRIM(' bar ')" />
+ <function function-name="TRIM" start-index="7" stop-index="33" text="TRIM(BOTH ' ' from ' bar ')" >
+ <parameter>
+ <literal-expression value="BOTH" start-index="12" stop-index="15" />
+ </parameter>
+ <parameter>
+ <literal-expression value=" " start-index="17" stop-index="19" />
+ </parameter>
+ <parameter>
+ <literal-expression value=" bar " start-index="26" stop-index="32" />
+ </parameter>
+ </function>
</expr>
</expression-projection>
</projections>
</select>
<select sql-case-id="select_with_trim_expr">
- <projections start-index="7" stop-index="27">
- <expression-projection text="trim('#' from `name`)" start-index="7" stop-index="27">
+ <projections start-index="7" stop-index="32">
+ <expression-projection text="TRIM(BOTH '#' FROM `name`)" start-index="7" stop-index="32">
<expr>
- <function function-name="trim" start-index="7" stop-index="27" text="trim('#' from `name`)" />
+ <function function-name="TRIM" start-index="7" stop-index="32" text="TRIM(BOTH '#' FROM `name`)" >
+ <parameter>
+ <literal-expression value="BOTH" start-index="12" stop-index="15" />
+ </parameter>
+ <parameter>
+ <literal-expression value="#" start-index="17" stop-index="19" />
+ </parameter>
+ <parameter>
+ <column name="name" start-delimiter="`" end-delimiter="`" start-index="26" stop-index="31" />
+ </parameter>
+ </function>
</expr>
</expression-projection>
</projections>
<from>
- <simple-table name="t_order" start-index="34" stop-index="40" />
+ <simple-table name="t_order" start-delimiter="`" end-delimiter="`" start-index="39" stop-index="47" />
</from>
</select>
<select sql-case-id="select_with_trim_expr_from_expr">
- <projections start-index="7" stop-index="33">
- <expression-projection text="trim(remove_name from name)" start-index="7" stop-index="33">
+ <projections start-index="7" stop-index="42">
+ <expression-projection text="TRIM(BOTH `remove_name` FROM `name`)" start-index="7" stop-index="42">
<expr>
- <function function-name="trim" start-index="7" stop-index="33" text="trim(remove_name from name)" />
+ <function function-name="TRIM" start-index="7" stop-index="42" text="TRIM(BOTH `remove_name` FROM `name`)" >
+ <parameter>
+ <literal-expression value="BOTH" start-index="12" stop-index="15" />
+ </parameter>
+ <parameter>
+ <column name="remove_name" start-delimiter="`" end-delimiter="`" start-index="17" stop-index="29" />
+ </parameter>
+ <parameter>
+ <column name="name" start-delimiter="`" end-delimiter="`" start-index="36" stop-index="41" />
+ </parameter>
+ </function>
</expr>
</expression-projection>
</projections>
<from>
- <simple-table name="t_order" start-index="40" stop-index="46" />
+ <simple-table name="t_order" start-delimiter="`" end-delimiter="`" start-index="49" stop-index="57" />
</from>
</select>
<select sql-case-id="select_weight_string">
diff --git a/test/it/parser/src/main/resources/sql/supported/dml/select-special-function.xml b/test/it/parser/src/main/resources/sql/supported/dml/select-special-function.xml
index 13793d00095..b01cbc076ed 100644
--- a/test/it/parser/src/main/resources/sql/supported/dml/select-special-function.xml
+++ b/test/it/parser/src/main/resources/sql/supported/dml/select-special-function.xml
@@ -26,9 +26,9 @@
<sql-case id="select_substring" value="SELECT SUBSTRING('foobarbar' from 4)" db-types="MySQL" />
<sql-case id="select_extract" value="SELECT EXTRACT(YEAR FROM '2019-07-02')" db-types="MySQL" />
<sql-case id="select_char" value="SELECT CHAR(77,121,83,81,'76')" db-types="MySQL" />
- <sql-case id="select_trim" value="SELECT TRIM(' bar ')" db-types="MySQL" />
- <sql-case id="select_with_trim_expr" value="select trim('#' from `name`) from t_order" db-types="MySQL" />
- <sql-case id="select_with_trim_expr_from_expr" value="select trim(remove_name from name) from t_order" db-types="MySQL" />
+ <sql-case id="select_trim" value="SELECT TRIM(BOTH ' ' from ' bar ')" db-types="MySQL" />
+ <sql-case id="select_with_trim_expr" value="SELECT TRIM(BOTH '#' FROM `name`) FROM `t_order`" db-types="MySQL" />
+ <sql-case id="select_with_trim_expr_from_expr" value="SELECT TRIM(BOTH `remove_name` FROM `name`) FROM `t_order`" db-types="MySQL" />
<sql-case id="select_weight_string" value="SELECT WEIGHT_STRING('bar')" db-types="MySQL" />
<sql-case id="select_values" value="SELECT VALUES(order_id) FROM t_order" db-types="MySQL" />
<sql-case id="select_current_user_brackets" value="SELECT CURRENT_USER()" db-types="MySQL" />