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 fac2bb62e26 Support GroupConcat sql for aggregating multiple shards in
opengauss and doris database(#33992) (#33991)
fac2bb62e26 is described below
commit fac2bb62e26d7db2b214b12dd4152f3f073385f7
Author: YaoFly <[email protected]>
AuthorDate: Tue Dec 10 19:25:36 2024 +0800
Support GroupConcat sql for aggregating multiple shards in opengauss and
doris database(#33992) (#33991)
* Support GroupConcat sql for aggregating multiple shards(#33797)
* Check Style fix(#33797)
* Check Style fix(#33797)
* spotless fix (#33797)
* unit test fix (#33797)
* spotless fix (#33797)
* group_concat distinct compatible (#33797)
* group_concat distinct compatible (#33797)
* unit test fix for distinct group_concat (#33797)
* e2e test for group_concat (#33797)
* e2e test for group_concat (#33797)
* code format (#33797)
* e2e test (#33797)
* e2e test (#33797)
* e2e test (#33797)
* remove useless code(#33797)
* code optimization (#33797)
* sql parse unit test (#33797)
* RELEASE-NOTES.md updated(#33797)
* Code Optimization (#33797)
* Support GroupConcat sql for aggregating multiple shards in opengauss and
doris database(#33797)
* doris parse unit test fix (#33797)
* spotless fix (#33797)
* Update RELEASE-NOTES.md
---------
Co-authored-by: yaofly <[email protected]>
Co-authored-by: Zhengqiang Duan <[email protected]>
---
RELEASE-NOTES.md | 2 +-
.../src/main/antlr4/imports/doris/BaseRule.g4 | 10 ++++++-
.../visitor/statement/DorisStatementVisitor.java | 34 +++++++++++++++-------
.../visitor/statement/MySQLStatementVisitor.java | 16 +++++++++-
.../src/main/antlr4/imports/opengauss/BaseRule.g4 | 7 +++--
.../src/main/antlr4/imports/opengauss/Keyword.g4 | 3 ++
.../statement/OpenGaussStatementVisitor.java | 10 +++++--
.../cases/dql/e2e-dql-select-aggregate.xml | 4 +--
.../resources/case/dml/select-special-function.xml | 3 ++
.../sql/supported/dml/select-aggregate.xml | 4 +--
.../sql/supported/dml/select-special-function.xml | 2 +-
11 files changed, 72 insertions(+), 23 deletions(-)
diff --git a/RELEASE-NOTES.md b/RELEASE-NOTES.md
index 0f8f89e0dc5..a80e8f1c66f 100644
--- a/RELEASE-NOTES.md
+++ b/RELEASE-NOTES.md
@@ -28,7 +28,7 @@
1. Proxy Native: Change the Base Docker Image of ShardingSphere Proxy Native -
[#33263](https://github.com/apache/shardingsphere/issues/33263)
1. Proxy Native: Support connecting to HiveServer2 with ZooKeeper Service
Discovery enabled in GraalVM Native Image -
[#33768](https://github.com/apache/shardingsphere/pull/33768)
1. Proxy Native: Support local transactions of ClickHouse under GraalVM Native
Image - [#33801](https://github.com/apache/shardingsphere/pull/33801)
-1. Sharding: Support MYSQL GroupConcat function for aggregating multiple
shards - [#33808](https://github.com/apache/shardingsphere/pull/33808)
+1. Sharding: Support GroupConcat function for aggregating multiple shards in
MySQL, OpenGauss, Doris -
[#33808](https://github.com/apache/shardingsphere/pull/33808)
1. Proxy Native: Support Seata AT integration under Proxy Native in GraalVM
Native Image - [#33889](https://github.com/apache/shardingsphere/pull/33889)
1. Agent: Simplify the use of Agent's Docker Image -
[#33356](https://github.com/apache/shardingsphere/pull/33356)
diff --git a/parser/sql/dialect/doris/src/main/antlr4/imports/doris/BaseRule.g4
b/parser/sql/dialect/doris/src/main/antlr4/imports/doris/BaseRule.g4
index 8cf25e65a63..6a560ccbc42 100644
--- a/parser/sql/dialect/doris/src/main/antlr4/imports/doris/BaseRule.g4
+++ b/parser/sql/dialect/doris/src/main/antlr4/imports/doris/BaseRule.g4
@@ -965,8 +965,16 @@ udfFunction
: functionName LP_ (expr? | expr (COMMA_ expr)*) RP_
;
+separatorName
+ : SEPARATOR string_
+ ;
+
+aggregationExpression
+ : expr (COMMA_ expr)* | ASTERISK_
+ ;
+
aggregationFunction
- : aggregationFunctionName LP_ distinct? (expr (COMMA_ expr)* | ASTERISK_)?
collateClause? RP_ overClause?
+ : aggregationFunctionName LP_ distinct? aggregationExpression?
collateClause? separatorName? RP_ overClause?
;
// DORIS ADDED BEGIN
diff --git
a/parser/sql/dialect/doris/src/main/java/org/apache/shardingsphere/sql/parser/doris/visitor/statement/DorisStatementVisitor.java
b/parser/sql/dialect/doris/src/main/java/org/apache/shardingsphere/sql/parser/doris/visitor/statement/DorisStatementVisitor.java
index 1fc3967231b..ef186494f82 100644
---
a/parser/sql/dialect/doris/src/main/java/org/apache/shardingsphere/sql/parser/doris/visitor/statement/DorisStatementVisitor.java
+++
b/parser/sql/dialect/doris/src/main/java/org/apache/shardingsphere/sql/parser/doris/visitor/statement/DorisStatementVisitor.java
@@ -22,6 +22,7 @@ import lombok.Getter;
import org.antlr.v4.runtime.ParserRuleContext;
import org.antlr.v4.runtime.Token;
import org.antlr.v4.runtime.misc.Interval;
+import org.antlr.v4.runtime.tree.ParseTree;
import org.antlr.v4.runtime.tree.TerminalNode;
import org.apache.shardingsphere.sql.parser.api.ASTNode;
import org.apache.shardingsphere.sql.parser.autogen.DorisStatementBaseVisitor;
@@ -958,14 +959,18 @@ public abstract class DorisStatementVisitor extends
DorisStatementBaseVisitor<AS
private ASTNode createAggregationSegment(final AggregationFunctionContext
ctx, final String aggregationType) {
AggregationType type =
AggregationType.valueOf(aggregationType.toUpperCase());
+ String separator = null;
+ if (null != ctx.separatorName()) {
+ separator = new
StringLiteralValue(ctx.separatorName().string_().getText()).getValue();
+ }
if (null != ctx.distinct()) {
AggregationDistinctProjectionSegment result =
- new
AggregationDistinctProjectionSegment(ctx.getStart().getStartIndex(),
ctx.getStop().getStopIndex(), type, getOriginalText(ctx),
getDistinctExpression(ctx));
- result.getParameters().addAll(getExpressions(ctx.expr()));
+ new
AggregationDistinctProjectionSegment(ctx.getStart().getStartIndex(),
ctx.getStop().getStopIndex(), type, getOriginalText(ctx),
getDistinctExpression(ctx), separator);
+
result.getParameters().addAll(getExpressions(ctx.aggregationExpression().expr()));
return result;
}
- AggregationProjectionSegment result = new
AggregationProjectionSegment(ctx.getStart().getStartIndex(),
ctx.getStop().getStopIndex(), type, getOriginalText(ctx));
- result.getParameters().addAll(getExpressions(ctx.expr()));
+ AggregationProjectionSegment result = new
AggregationProjectionSegment(ctx.getStart().getStartIndex(),
ctx.getStop().getStopIndex(), type, getOriginalText(ctx), separator);
+
result.getParameters().addAll(getExpressions(ctx.aggregationExpression().expr()));
return result;
}
@@ -981,11 +986,7 @@ public abstract class DorisStatementVisitor extends
DorisStatementBaseVisitor<AS
}
private String getDistinctExpression(final AggregationFunctionContext ctx)
{
- StringBuilder result = new StringBuilder();
- for (int i = 3; i < ctx.getChildCount() - 1; i++) {
- result.append(ctx.getChild(i).getText());
- }
- return result.toString();
+ return ctx.aggregationExpression().getText();
}
@Override
@@ -1046,12 +1047,25 @@ public abstract class DorisStatementVisitor extends
DorisStatementBaseVisitor<AS
public final ASTNode visitGroupConcatFunction(final
GroupConcatFunctionContext ctx) {
calculateParameterCount(ctx.expr());
FunctionSegment result = new
FunctionSegment(ctx.getStart().getStartIndex(), ctx.getStop().getStopIndex(),
ctx.GROUP_CONCAT().getText(), getOriginalText(ctx));
- for (ExprContext each : ctx.expr()) {
+ for (ExprContext each : getTargetRuleContextFromParseTree(ctx,
ExprContext.class)) {
result.getParameters().add((ExpressionSegment) visit(each));
}
return result;
}
+ private <T extends ParseTree> Collection<T>
getTargetRuleContextFromParseTree(final ParseTree parseTree, final Class<?
extends T> clazz) {
+ Collection<T> result = new LinkedList<>();
+ for (int index = 0; index < parseTree.getChildCount(); index++) {
+ ParseTree child = parseTree.getChild(index);
+ if (clazz.isInstance(child)) {
+ result.add(clazz.cast(child));
+ } else {
+ result.addAll(getTargetRuleContextFromParseTree(child, clazz));
+ }
+ }
+ return result;
+ }
+
// DORIS ADDED BEGIN
@Override
public final ASTNode visitBitwiseFunction(final BitwiseFunctionContext
ctx) {
diff --git
a/parser/sql/dialect/mysql/src/main/java/org/apache/shardingsphere/sql/parser/mysql/visitor/statement/MySQLStatementVisitor.java
b/parser/sql/dialect/mysql/src/main/java/org/apache/shardingsphere/sql/parser/mysql/visitor/statement/MySQLStatementVisitor.java
index 6ab433e59a1..28fb3b1022b 100644
---
a/parser/sql/dialect/mysql/src/main/java/org/apache/shardingsphere/sql/parser/mysql/visitor/statement/MySQLStatementVisitor.java
+++
b/parser/sql/dialect/mysql/src/main/java/org/apache/shardingsphere/sql/parser/mysql/visitor/statement/MySQLStatementVisitor.java
@@ -22,6 +22,7 @@ import lombok.Getter;
import org.antlr.v4.runtime.ParserRuleContext;
import org.antlr.v4.runtime.Token;
import org.antlr.v4.runtime.misc.Interval;
+import org.antlr.v4.runtime.tree.ParseTree;
import org.antlr.v4.runtime.tree.TerminalNode;
import org.apache.shardingsphere.sql.parser.api.ASTNode;
import org.apache.shardingsphere.sql.parser.autogen.MySQLStatementBaseVisitor;
@@ -1041,12 +1042,25 @@ public abstract class MySQLStatementVisitor extends
MySQLStatementBaseVisitor<AS
public final ASTNode visitGroupConcatFunction(final
GroupConcatFunctionContext ctx) {
calculateParameterCount(ctx.expr());
FunctionSegment result = new
FunctionSegment(ctx.getStart().getStartIndex(), ctx.getStop().getStopIndex(),
ctx.GROUP_CONCAT().getText(), getOriginalText(ctx));
- for (ExprContext each : ctx.expr()) {
+ for (ExprContext each : getTargetRuleContextFromParseTree(ctx,
ExprContext.class)) {
result.getParameters().add((ExpressionSegment) visit(each));
}
return result;
}
+ private <T extends ParseTree> Collection<T>
getTargetRuleContextFromParseTree(final ParseTree parseTree, final Class<?
extends T> clazz) {
+ Collection<T> result = new LinkedList<>();
+ for (int index = 0; index < parseTree.getChildCount(); index++) {
+ ParseTree child = parseTree.getChild(index);
+ if (clazz.isInstance(child)) {
+ result.add(clazz.cast(child));
+ } else {
+ result.addAll(getTargetRuleContextFromParseTree(child, clazz));
+ }
+ }
+ return result;
+ }
+
@Override
public final ASTNode visitCastFunction(final CastFunctionContext ctx) {
FunctionSegment result = new
FunctionSegment(ctx.getStart().getStartIndex(), ctx.getStop().getStopIndex(),
ctx.CAST().getText(), getOriginalText(ctx));
diff --git
a/parser/sql/dialect/opengauss/src/main/antlr4/imports/opengauss/BaseRule.g4
b/parser/sql/dialect/opengauss/src/main/antlr4/imports/opengauss/BaseRule.g4
index 610ebdda0be..80817068f1e 100644
--- a/parser/sql/dialect/opengauss/src/main/antlr4/imports/opengauss/BaseRule.g4
+++ b/parser/sql/dialect/opengauss/src/main/antlr4/imports/opengauss/BaseRule.g4
@@ -954,13 +954,16 @@ typeList
: typeName (COMMA_ typeName)*
;
+separatorName
+ : SEPARATOR STRING_
+ ;
+
funcApplication
: funcName LP_ RP_
- | funcName LP_ funcArgList sortClause? RP_
+ | funcName LP_ DISTINCT? funcArgList sortClause? separatorName? RP_
| funcName LP_ VARIADIC funcArgExpr sortClause? RP_
| funcName LP_ funcArgList COMMA_ VARIADIC funcArgExpr sortClause? RP_
| funcName LP_ ALL funcArgList sortClause? RP_
- | funcName LP_ DISTINCT funcArgList sortClause? RP_
| funcName LP_ ASTERISK_ RP_
;
diff --git
a/parser/sql/dialect/opengauss/src/main/antlr4/imports/opengauss/Keyword.g4
b/parser/sql/dialect/opengauss/src/main/antlr4/imports/opengauss/Keyword.g4
index 2a69c54c9e5..1f1bd273741 100644
--- a/parser/sql/dialect/opengauss/src/main/antlr4/imports/opengauss/Keyword.g4
+++ b/parser/sql/dialect/opengauss/src/main/antlr4/imports/opengauss/Keyword.g4
@@ -664,3 +664,6 @@ MAXVALUE
: M A X V A L U E
;
+SEPARATOR
+ : S E P A R A T O R
+ ;
diff --git
a/parser/sql/dialect/opengauss/src/main/java/org/apache/shardingsphere/sql/parser/opengauss/visitor/statement/OpenGaussStatementVisitor.java
b/parser/sql/dialect/opengauss/src/main/java/org/apache/shardingsphere/sql/parser/opengauss/visitor/statement/OpenGaussStatementVisitor.java
index 266d7d1fca7..00d0758eb98 100644
---
a/parser/sql/dialect/opengauss/src/main/java/org/apache/shardingsphere/sql/parser/opengauss/visitor/statement/OpenGaussStatementVisitor.java
+++
b/parser/sql/dialect/opengauss/src/main/java/org/apache/shardingsphere/sql/parser/opengauss/visitor/statement/OpenGaussStatementVisitor.java
@@ -449,7 +449,7 @@ public abstract class OpenGaussStatementVisitor extends
OpenGaussStatementBaseVi
Collection<ExpressionSegment> expressionSegments =
getExpressionSegments(getTargetRuleContextFromParseTree(ctx,
AExprContext.class));
// TODO replace aggregation segment
String aggregationType = ctx.funcApplication().funcName().getText();
- if (AggregationType.isAggregationType(aggregationType)) {
+ if (AggregationType.isAggregationType(aggregationType) && null ==
ctx.funcApplication().sortClause()) {
return createAggregationSegment(ctx.funcApplication(),
aggregationType, expressionSegments);
}
FunctionSegment result = new
FunctionSegment(ctx.getStart().getStartIndex(), ctx.getStop().getStopIndex(),
ctx.funcApplication().funcName().getText(), getOriginalText(ctx));
@@ -594,13 +594,17 @@ public abstract class OpenGaussStatementVisitor extends
OpenGaussStatementBaseVi
private ProjectionSegment createAggregationSegment(final
FuncApplicationContext ctx, final String aggregationType, final
Collection<ExpressionSegment> expressionSegments) {
AggregationType type =
AggregationType.valueOf(aggregationType.toUpperCase());
+ String separator = null;
+ if (null != ctx.separatorName()) {
+ separator = new
StringLiteralValue(ctx.separatorName().STRING_().getText()).getValue();
+ }
if (null == ctx.DISTINCT()) {
- AggregationProjectionSegment result = new
AggregationProjectionSegment(ctx.getStart().getStartIndex(),
ctx.getStop().getStopIndex(), type, getOriginalText(ctx));
+ AggregationProjectionSegment result = new
AggregationProjectionSegment(ctx.getStart().getStartIndex(),
ctx.getStop().getStopIndex(), type, getOriginalText(ctx), separator);
result.getParameters().addAll(expressionSegments);
return result;
}
AggregationDistinctProjectionSegment result =
- new
AggregationDistinctProjectionSegment(ctx.getStart().getStartIndex(),
ctx.getStop().getStopIndex(), type, getOriginalText(ctx),
getDistinctExpression(ctx));
+ new
AggregationDistinctProjectionSegment(ctx.getStart().getStartIndex(),
ctx.getStop().getStopIndex(), type, getOriginalText(ctx),
getDistinctExpression(ctx), separator);
result.getParameters().addAll(expressionSegments);
return result;
}
diff --git
a/test/e2e/sql/src/test/resources/cases/dql/e2e-dql-select-aggregate.xml
b/test/e2e/sql/src/test/resources/cases/dql/e2e-dql-select-aggregate.xml
index f4bd5b48092..643f8d69e48 100644
--- a/test/e2e/sql/src/test/resources/cases/dql/e2e-dql-select-aggregate.xml
+++ b/test/e2e/sql/src/test/resources/cases/dql/e2e-dql-select-aggregate.xml
@@ -145,11 +145,11 @@
<assertion parameters="abc:String"
expected-data-source-name="read_dataset" />
</test-case>
- <test-case sql="SELECT GROUP_CONCAT(o.remark) as order_id_group_concat
FROM t_order o where o.order_id > 1 - 1" db-types="MySQL"
scenario-types="db,tbl,dbtbl_with_readwrite_splitting,readwrite_splitting,db_tbl_sql_federation">
+ <test-case sql="SELECT GROUP_CONCAT(o.remark) as order_id_group_concat
FROM t_order o where o.order_id > 1 - 1" db-types="MySQL,openGauss"
scenario-types="db,tbl,dbtbl_with_readwrite_splitting,readwrite_splitting,db_tbl_sql_federation">
<assertion expected-data-source-name="read_dataset" />
</test-case>
- <test-case sql="SELECT GROUP_CONCAT(distinct o.remark SEPARATOR ' ') as
order_id_group_concat FROM t_order o where o.order_id > 1 - 1" db-types="MySQL"
scenario-types="db,tbl,dbtbl_with_readwrite_splitting,readwrite_splitting">
+ <test-case sql="SELECT GROUP_CONCAT(distinct o.remark SEPARATOR ' ') as
order_id_group_concat FROM t_order o where o.order_id > 1 - 1"
db-types="MySQL,openGauss"
scenario-types="db,tbl,dbtbl_with_readwrite_splitting,readwrite_splitting">
<assertion expected-data-source-name="read_dataset" />
</test-case>
</e2e-test-cases>
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 102a9540647..2a297614d7e 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
@@ -28,6 +28,9 @@
<parameter>
<column name="status" start-index="20"
stop-index="25" />
</parameter>
+ <parameter>
+ <column name="status" start-index="36"
stop-index="41" />
+ </parameter>
</function>
</expr>
</expression-projection>
diff --git
a/test/it/parser/src/main/resources/sql/supported/dml/select-aggregate.xml
b/test/it/parser/src/main/resources/sql/supported/dml/select-aggregate.xml
index a4196127606..0cae6e4c9af 100644
--- a/test/it/parser/src/main/resources/sql/supported/dml/select-aggregate.xml
+++ b/test/it/parser/src/main/resources/sql/supported/dml/select-aggregate.xml
@@ -38,6 +38,6 @@
<sql-case id="select_approx_count"
value="select owner, approx_count(*) , approx_rank(partition by
owner order by approx_count(*) desc) from t group by owner having
approx_rank(partition by owner order by approx_count(*) desc) <= 1 order by
1"
db-types="Oracle"/>
- <sql-case id="select_group_concat" value="SELECT GROUP_CONCAT(user_id) AS
user_id_group_concat FROM t_order" db-types="MySQL"/>
- <sql-case id="select_group_concat_with_distinct_with_separator"
value="SELECT GROUP_CONCAT(distinct user_id SEPARATOR ' ') AS
user_id_group_concat FROM t_order" db-types="MySQL"/>
+ <sql-case id="select_group_concat" value="SELECT GROUP_CONCAT(user_id) AS
user_id_group_concat FROM t_order" db-types="MySQL,Doris,openGauss"/>
+ <sql-case id="select_group_concat_with_distinct_with_separator"
value="SELECT GROUP_CONCAT(distinct user_id SEPARATOR ' ') AS
user_id_group_concat FROM t_order" db-types="MySQL,Doris,openGauss"/>
</sql-cases>
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 99727b077bc..178b5e7ef9c 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
@@ -17,7 +17,7 @@
-->
<sql-cases>
- <sql-case id="select_group_concat_with_order_by" value="SELECT
GROUP_CONCAT(status ORDER BY status) FROM t_order" db-types="MySQL" />
+ <sql-case id="select_group_concat_with_order_by" value="SELECT
GROUP_CONCAT(status ORDER BY status) FROM t_order"
db-types="MySQL,Doris,openGauss" />
<sql-case id="select_window_function" value="SELECT order_id, ROW_NUMBER()
OVER() FROM t_order" db-types="MySQL" />
<sql-case id="select_cast_function" value="SELECT CAST('1' AS UNSIGNED)"
db-types="MySQL" />
<sql-case id="select_cast" value="SELECT CAST(c AT TIME ZONE 'UTC' AS
DATETIME)" db-types="MySQL" />
