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 f17f89ea1de Support GroupConcat sql for aggregating multiple
shards(#33797) (#33808)
f17f89ea1de is described below
commit f17f89ea1de8d03bb2adeb6db23d2d2fd98b8e96
Author: YaoFly <[email protected]>
AuthorDate: Fri Dec 6 09:17:54 2024 +0800
Support GroupConcat sql for aggregating multiple shards(#33797) (#33808)
* 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)
---------
Co-authored-by: yaofly <[email protected]>
---
RELEASE-NOTES.md | 1 +
.../dql/groupby/GroupByMemoryMergedResult.java | 3 +-
.../dql/groupby/GroupByStreamMergedResult.java | 3 +-
.../aggregation/AggregationUnitFactory.java | 5 +-
.../DistinctGroupConcatAggregationUnit.java | 51 +++++++++++++++++
.../aggregation/GroupConcatAggregationUnit.java | 51 +++++++++++++++++
.../aggregation/AggregationUnitFactoryTest.java | 30 +++++++---
.../GroupConcatAggregationUnitTest.java | 51 +++++++++++++++++
.../select/projection/engine/ProjectionEngine.java | 6 +-
.../impl/AggregationDistinctProjection.java | 8 +++
.../projection/impl/AggregationProjection.java | 14 +++++
.../projection/ProjectionsSegmentBinder.java | 6 +-
.../impl/AggregationProjectionConverter.java | 12 +++-
.../src/main/antlr4/imports/mysql/BaseRule.g4 | 10 +++-
.../visitor/statement/MySQLStatementVisitor.java | 24 +++++---
.../item/AggregationDistinctProjectionSegment.java | 6 ++
.../dml/item/AggregationProjectionSegment.java | 15 +++++
.../cases/dql/e2e-dql-select-aggregate.xml | 66 ++++++++++++----------
.../segment/projection/ProjectionAssert.java | 1 +
.../aggregation/ExpectedAggregationProjection.java | 3 +
.../main/resources/case/dml/select-aggregate.xml | 25 ++++++++
.../resources/case/dml/select-special-function.xml | 12 ++--
.../sql/supported/dml/select-aggregate.xml | 2 +
.../sql/supported/dml/select-special-function.xml | 2 +-
24 files changed, 343 insertions(+), 64 deletions(-)
diff --git a/RELEASE-NOTES.md b/RELEASE-NOTES.md
index ee11ef4c411..b880b94df86 100644
--- a/RELEASE-NOTES.md
+++ b/RELEASE-NOTES.md
@@ -28,6 +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. 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/features/sharding/core/src/main/java/org/apache/shardingsphere/sharding/merge/dql/groupby/GroupByMemoryMergedResult.java
b/features/sharding/core/src/main/java/org/apache/shardingsphere/sharding/merge/dql/groupby/GroupByMemoryMergedResult.java
index 56505af0f44..a0a2871e069 100644
---
a/features/sharding/core/src/main/java/org/apache/shardingsphere/sharding/merge/dql/groupby/GroupByMemoryMergedResult.java
+++
b/features/sharding/core/src/main/java/org/apache/shardingsphere/sharding/merge/dql/groupby/GroupByMemoryMergedResult.java
@@ -81,7 +81,8 @@ public final class GroupByMemoryMergedResult extends
MemoryMergedResult<Sharding
dataMap.put(groupByValue, new MemoryQueryResultRow(queryResult));
}
aggregationMap.computeIfAbsent(groupByValue, unused ->
selectStatementContext.getProjectionsContext().getAggregationProjections().stream()
- .collect(Collectors.toMap(Function.identity(), input ->
AggregationUnitFactory.create(input.getType(), input instanceof
AggregationDistinctProjection))));
+ .collect(Collectors.toMap(Function.identity(),
+ input ->
AggregationUnitFactory.create(input.getType(), input instanceof
AggregationDistinctProjection, input.getSeparator().orElse(null)))));
}
private void aggregate(final SelectStatementContext
selectStatementContext, final QueryResult queryResult,
diff --git
a/features/sharding/core/src/main/java/org/apache/shardingsphere/sharding/merge/dql/groupby/GroupByStreamMergedResult.java
b/features/sharding/core/src/main/java/org/apache/shardingsphere/sharding/merge/dql/groupby/GroupByStreamMergedResult.java
index 550c982af4b..a26782b3be7 100644
---
a/features/sharding/core/src/main/java/org/apache/shardingsphere/sharding/merge/dql/groupby/GroupByStreamMergedResult.java
+++
b/features/sharding/core/src/main/java/org/apache/shardingsphere/sharding/merge/dql/groupby/GroupByStreamMergedResult.java
@@ -77,7 +77,8 @@ public final class GroupByStreamMergedResult extends
OrderByStreamMergedResult {
boolean result = false;
boolean cachedRow = false;
Map<AggregationProjection, AggregationUnit> aggregationUnitMap =
Maps.toMap(
-
selectStatementContext.getProjectionsContext().getAggregationProjections(),
input -> AggregationUnitFactory.create(input.getType(), input instanceof
AggregationDistinctProjection));
+
selectStatementContext.getProjectionsContext().getAggregationProjections(),
+ input -> AggregationUnitFactory.create(input.getType(), input
instanceof AggregationDistinctProjection, input.getSeparator().orElse(null)));
while (currentGroupByValues.equals(new
GroupByValue(getCurrentQueryResult(),
selectStatementContext.getGroupByContext().getItems()).getGroupValues())) {
aggregate(aggregationUnitMap);
if (!cachedRow) {
diff --git
a/features/sharding/core/src/main/java/org/apache/shardingsphere/sharding/merge/dql/groupby/aggregation/AggregationUnitFactory.java
b/features/sharding/core/src/main/java/org/apache/shardingsphere/sharding/merge/dql/groupby/aggregation/AggregationUnitFactory.java
index c6709cbb4ed..02caeecf6c1 100644
---
a/features/sharding/core/src/main/java/org/apache/shardingsphere/sharding/merge/dql/groupby/aggregation/AggregationUnitFactory.java
+++
b/features/sharding/core/src/main/java/org/apache/shardingsphere/sharding/merge/dql/groupby/aggregation/AggregationUnitFactory.java
@@ -33,10 +33,11 @@ public final class AggregationUnitFactory {
*
* @param type aggregation function type
* @param isDistinct is distinct
+ * @param separator is separator for group_concat
* @return aggregation unit instance
* @throws UnsupportedSQLOperationException unsupported SQL operation
exception
*/
- public static AggregationUnit create(final AggregationType type, final
boolean isDistinct) {
+ public static AggregationUnit create(final AggregationType type, final
boolean isDistinct, final String separator) {
switch (type) {
case MAX:
return new ComparableAggregationUnit(false);
@@ -50,6 +51,8 @@ public final class AggregationUnitFactory {
return isDistinct ? new DistinctAverageAggregationUnit() : new
AverageAggregationUnit();
case BIT_XOR:
return new BitXorAggregationUnit();
+ case GROUP_CONCAT:
+ return isDistinct ? new
DistinctGroupConcatAggregationUnit(separator) : new
GroupConcatAggregationUnit(separator);
default:
throw new UnsupportedSQLOperationException(type.name());
}
diff --git
a/features/sharding/core/src/main/java/org/apache/shardingsphere/sharding/merge/dql/groupby/aggregation/DistinctGroupConcatAggregationUnit.java
b/features/sharding/core/src/main/java/org/apache/shardingsphere/sharding/merge/dql/groupby/aggregation/DistinctGroupConcatAggregationUnit.java
new file mode 100644
index 00000000000..93a19c21d69
--- /dev/null
+++
b/features/sharding/core/src/main/java/org/apache/shardingsphere/sharding/merge/dql/groupby/aggregation/DistinctGroupConcatAggregationUnit.java
@@ -0,0 +1,51 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one or more
+ * contributor license agreements. See the NOTICE file distributed with
+ * this work for additional information regarding copyright ownership.
+ * The ASF licenses this file to You under the Apache License, Version 2.0
+ * (the "License"); you may not use this file except in compliance with
+ * the License. You may obtain a copy of the License at
+ *
+ * http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing, software
+ * distributed under the License is distributed on an "AS IS" BASIS,
+ * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ * See the License for the specific language governing permissions and
+ * limitations under the License.
+ */
+
+package org.apache.shardingsphere.sharding.merge.dql.groupby.aggregation;
+
+import java.util.Collection;
+import java.util.LinkedHashSet;
+import java.util.List;
+
+/**
+ * Distinct group concat aggregation unit.
+ */
+public final class DistinctGroupConcatAggregationUnit implements
AggregationUnit {
+
+ private static final String DEFAULT_SEPARATOR = ",";
+
+ private final Collection<String> values = new LinkedHashSet<>();
+
+ private final String separator;
+
+ public DistinctGroupConcatAggregationUnit(final String separator) {
+ this.separator = null == separator ? DEFAULT_SEPARATOR : separator;
+ }
+
+ @Override
+ public void merge(final List<Comparable<?>> values) {
+ if (null == values || null == values.get(0)) {
+ return;
+ }
+ this.values.add(String.valueOf(values.get(0)));
+ }
+
+ @Override
+ public Comparable<?> getResult() {
+ return String.join(separator, values);
+ }
+}
diff --git
a/features/sharding/core/src/main/java/org/apache/shardingsphere/sharding/merge/dql/groupby/aggregation/GroupConcatAggregationUnit.java
b/features/sharding/core/src/main/java/org/apache/shardingsphere/sharding/merge/dql/groupby/aggregation/GroupConcatAggregationUnit.java
new file mode 100644
index 00000000000..c3e1796106d
--- /dev/null
+++
b/features/sharding/core/src/main/java/org/apache/shardingsphere/sharding/merge/dql/groupby/aggregation/GroupConcatAggregationUnit.java
@@ -0,0 +1,51 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one or more
+ * contributor license agreements. See the NOTICE file distributed with
+ * this work for additional information regarding copyright ownership.
+ * The ASF licenses this file to You under the Apache License, Version 2.0
+ * (the "License"); you may not use this file except in compliance with
+ * the License. You may obtain a copy of the License at
+ *
+ * http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing, software
+ * distributed under the License is distributed on an "AS IS" BASIS,
+ * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ * See the License for the specific language governing permissions and
+ * limitations under the License.
+ */
+
+package org.apache.shardingsphere.sharding.merge.dql.groupby.aggregation;
+
+import java.util.ArrayList;
+import java.util.Collection;
+import java.util.List;
+
+/**
+ * Group concat aggregation unit.
+ */
+public final class GroupConcatAggregationUnit implements AggregationUnit {
+
+ private static final String DEFAULT_SEPARATOR = ",";
+
+ private final Collection<String> values = new ArrayList<>();
+
+ private final String separator;
+
+ public GroupConcatAggregationUnit(final String separator) {
+ this.separator = null == separator ? DEFAULT_SEPARATOR : separator;
+ }
+
+ @Override
+ public void merge(final List<Comparable<?>> values) {
+ if (null == values || null == values.get(0)) {
+ return;
+ }
+ this.values.add(String.valueOf(values.get(0)));
+ }
+
+ @Override
+ public Comparable<?> getResult() {
+ return String.join(separator, values);
+ }
+}
diff --git
a/features/sharding/core/src/test/java/org/apache/shardingsphere/sharding/merge/dql/groupby/aggregation/AggregationUnitFactoryTest.java
b/features/sharding/core/src/test/java/org/apache/shardingsphere/sharding/merge/dql/groupby/aggregation/AggregationUnitFactoryTest.java
index 6e14d251409..92bc71a5fa3 100644
---
a/features/sharding/core/src/test/java/org/apache/shardingsphere/sharding/merge/dql/groupby/aggregation/AggregationUnitFactoryTest.java
+++
b/features/sharding/core/src/test/java/org/apache/shardingsphere/sharding/merge/dql/groupby/aggregation/AggregationUnitFactoryTest.java
@@ -27,38 +27,50 @@ class AggregationUnitFactoryTest {
@Test
void assertCreateComparableAggregationUnit() {
- assertThat(AggregationUnitFactory.create(AggregationType.MIN, false),
instanceOf(ComparableAggregationUnit.class));
- assertThat(AggregationUnitFactory.create(AggregationType.MAX, false),
instanceOf(ComparableAggregationUnit.class));
+ assertThat(AggregationUnitFactory.create(AggregationType.MIN, false,
null), instanceOf(ComparableAggregationUnit.class));
+ assertThat(AggregationUnitFactory.create(AggregationType.MAX, false,
null), instanceOf(ComparableAggregationUnit.class));
}
@Test
void assertCreateAccumulationAggregationUnit() {
- assertThat(AggregationUnitFactory.create(AggregationType.SUM, false),
instanceOf(AccumulationAggregationUnit.class));
- assertThat(AggregationUnitFactory.create(AggregationType.COUNT,
false), instanceOf(AccumulationAggregationUnit.class));
+ assertThat(AggregationUnitFactory.create(AggregationType.SUM, false,
null), instanceOf(AccumulationAggregationUnit.class));
+ assertThat(AggregationUnitFactory.create(AggregationType.COUNT, false,
null), instanceOf(AccumulationAggregationUnit.class));
}
@Test
void assertCreateAverageAggregationUnit() {
- assertThat(AggregationUnitFactory.create(AggregationType.AVG, false),
instanceOf(AverageAggregationUnit.class));
+ assertThat(AggregationUnitFactory.create(AggregationType.AVG, false,
null), instanceOf(AverageAggregationUnit.class));
}
@Test
void assertCreateDistinctSumAggregationUnit() {
- assertThat(AggregationUnitFactory.create(AggregationType.SUM, true),
instanceOf(DistinctSumAggregationUnit.class));
+ assertThat(AggregationUnitFactory.create(AggregationType.SUM, true,
null), instanceOf(DistinctSumAggregationUnit.class));
}
@Test
void assertCreateDistinctCountAggregationUnit() {
- assertThat(AggregationUnitFactory.create(AggregationType.COUNT, true),
instanceOf(DistinctCountAggregationUnit.class));
+ assertThat(AggregationUnitFactory.create(AggregationType.COUNT, true,
null), instanceOf(DistinctCountAggregationUnit.class));
}
@Test
void assertCreateDistinctAverageAggregationUnit() {
- assertThat(AggregationUnitFactory.create(AggregationType.AVG, true),
instanceOf(DistinctAverageAggregationUnit.class));
+ assertThat(AggregationUnitFactory.create(AggregationType.AVG, true,
null), instanceOf(DistinctAverageAggregationUnit.class));
}
@Test
void assertCreateBitXorAggregationUnit() {
- assertThat(AggregationUnitFactory.create(AggregationType.BIT_XOR,
false), instanceOf(BitXorAggregationUnit.class));
+ assertThat(AggregationUnitFactory.create(AggregationType.BIT_XOR,
false, null), instanceOf(BitXorAggregationUnit.class));
+ }
+
+ @Test
+ void assertGroupConcatAggregationUnit() {
+ assertThat(AggregationUnitFactory.create(AggregationType.GROUP_CONCAT,
false, null), instanceOf(GroupConcatAggregationUnit.class));
+ assertThat(AggregationUnitFactory.create(AggregationType.GROUP_CONCAT,
false, " "), instanceOf(GroupConcatAggregationUnit.class));
+ }
+
+ @Test
+ void assertDistinctGroupConcatAggregationUnit() {
+ assertThat(AggregationUnitFactory.create(AggregationType.GROUP_CONCAT,
true, null), instanceOf(DistinctGroupConcatAggregationUnit.class));
+ assertThat(AggregationUnitFactory.create(AggregationType.GROUP_CONCAT,
true, " "), instanceOf(DistinctGroupConcatAggregationUnit.class));
}
}
diff --git
a/features/sharding/core/src/test/java/org/apache/shardingsphere/sharding/merge/dql/groupby/aggregation/GroupConcatAggregationUnitTest.java
b/features/sharding/core/src/test/java/org/apache/shardingsphere/sharding/merge/dql/groupby/aggregation/GroupConcatAggregationUnitTest.java
new file mode 100644
index 00000000000..24b6e085500
--- /dev/null
+++
b/features/sharding/core/src/test/java/org/apache/shardingsphere/sharding/merge/dql/groupby/aggregation/GroupConcatAggregationUnitTest.java
@@ -0,0 +1,51 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one or more
+ * contributor license agreements. See the NOTICE file distributed with
+ * this work for additional information regarding copyright ownership.
+ * The ASF licenses this file to You under the Apache License, Version 2.0
+ * (the "License"); you may not use this file except in compliance with
+ * the License. You may obtain a copy of the License at
+ *
+ * http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing, software
+ * distributed under the License is distributed on an "AS IS" BASIS,
+ * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ * See the License for the specific language governing permissions and
+ * limitations under the License.
+ */
+
+package org.apache.shardingsphere.sharding.merge.dql.groupby.aggregation;
+
+import org.junit.jupiter.api.Test;
+
+import java.util.Collections;
+
+import static org.hamcrest.CoreMatchers.is;
+import static org.hamcrest.MatcherAssert.assertThat;
+
+class GroupConcatAggregationUnitTest {
+
+ @Test
+ void assertGroupConcatAggregation() {
+ GroupConcatAggregationUnit groupConcatAggregationUnit = new
GroupConcatAggregationUnit(" ");
+ groupConcatAggregationUnit.merge(null);
+ groupConcatAggregationUnit.merge(Collections.singletonList(null));
+ groupConcatAggregationUnit.merge(Collections.singletonList("001"));
+ groupConcatAggregationUnit.merge(Collections.singletonList("002"));
+ groupConcatAggregationUnit.merge(Collections.singletonList("002 003"));
+ assertThat(groupConcatAggregationUnit.getResult(), is("001 002 002
003"));
+ }
+
+ @Test
+ void assertDistinctGroupConcatAggregation() {
+ DistinctGroupConcatAggregationUnit distinctGroupConcatAggregationUnit
= new DistinctGroupConcatAggregationUnit(" ");
+ distinctGroupConcatAggregationUnit.merge(null);
+
distinctGroupConcatAggregationUnit.merge(Collections.singletonList(null));
+
distinctGroupConcatAggregationUnit.merge(Collections.singletonList(""));
+
distinctGroupConcatAggregationUnit.merge(Collections.singletonList("001"));
+
distinctGroupConcatAggregationUnit.merge(Collections.singletonList("001"));
+
distinctGroupConcatAggregationUnit.merge(Collections.singletonList("003"));
+ assertThat(distinctGroupConcatAggregationUnit.getResult(), is(" 001
003"));
+ }
+}
diff --git
a/infra/binder/src/main/java/org/apache/shardingsphere/infra/binder/context/segment/select/projection/engine/ProjectionEngine.java
b/infra/binder/src/main/java/org/apache/shardingsphere/infra/binder/context/segment/select/projection/engine/ProjectionEngine.java
index 7edd0674707..3fd824f99a4 100644
---
a/infra/binder/src/main/java/org/apache/shardingsphere/infra/binder/context/segment/select/projection/engine/ProjectionEngine.java
+++
b/infra/binder/src/main/java/org/apache/shardingsphere/infra/binder/context/segment/select/projection/engine/ProjectionEngine.java
@@ -121,7 +121,7 @@ public final class ProjectionEngine {
projectionSegment.getAlias().orElseGet(() -> new
IdentifierValue(DerivedColumn.AGGREGATION_DISTINCT_DERIVED.getDerivedColumnAlias(aggregationDistinctDerivedColumnCount++)));
AggregationDistinctProjection result = new
AggregationDistinctProjection(
projectionSegment.getStartIndex(),
projectionSegment.getStopIndex(), projectionSegment.getType(),
projectionSegment.getExpression(), alias,
- projectionSegment.getDistinctInnerExpression(), databaseType);
+ projectionSegment.getDistinctInnerExpression(), databaseType,
projectionSegment.getSeparator().orElse(null));
if (AggregationType.AVG == result.getType()) {
appendAverageDistinctDerivedProjection(result);
}
@@ -129,7 +129,9 @@ public final class ProjectionEngine {
}
private AggregationProjection createProjection(final
AggregationProjectionSegment projectionSegment) {
- AggregationProjection result = new
AggregationProjection(projectionSegment.getType(),
projectionSegment.getExpression(), projectionSegment.getAlias().orElse(null),
databaseType);
+ AggregationProjection result =
+ new AggregationProjection(projectionSegment.getType(),
projectionSegment.getExpression(), projectionSegment.getAlias().orElse(null),
databaseType,
+ projectionSegment.getSeparator().orElse(null));
if (AggregationType.AVG == result.getType()) {
appendAverageDerivedProjection(result);
// TODO replace avg to constant, avoid calculate useless avg
diff --git
a/infra/binder/src/main/java/org/apache/shardingsphere/infra/binder/context/segment/select/projection/impl/AggregationDistinctProjection.java
b/infra/binder/src/main/java/org/apache/shardingsphere/infra/binder/context/segment/select/projection/impl/AggregationDistinctProjection.java
index 6bb6241b00b..e047645fa1e 100644
---
a/infra/binder/src/main/java/org/apache/shardingsphere/infra/binder/context/segment/select/projection/impl/AggregationDistinctProjection.java
+++
b/infra/binder/src/main/java/org/apache/shardingsphere/infra/binder/context/segment/select/projection/impl/AggregationDistinctProjection.java
@@ -43,4 +43,12 @@ public final class AggregationDistinctProjection extends
AggregationProjection {
this.stopIndex = stopIndex;
this.distinctInnerExpression = distinctInnerExpression;
}
+
+ public AggregationDistinctProjection(final int startIndex, final int
stopIndex, final AggregationType type, final String expression,
+ final IdentifierValue alias, final
String distinctInnerExpression, final DatabaseType databaseType, final String
separator) {
+ super(type, expression, alias, databaseType, separator);
+ this.startIndex = startIndex;
+ this.stopIndex = stopIndex;
+ this.distinctInnerExpression = distinctInnerExpression;
+ }
}
diff --git
a/infra/binder/src/main/java/org/apache/shardingsphere/infra/binder/context/segment/select/projection/impl/AggregationProjection.java
b/infra/binder/src/main/java/org/apache/shardingsphere/infra/binder/context/segment/select/projection/impl/AggregationProjection.java
index 3034779f829..0493904fd8b 100644
---
a/infra/binder/src/main/java/org/apache/shardingsphere/infra/binder/context/segment/select/projection/impl/AggregationProjection.java
+++
b/infra/binder/src/main/java/org/apache/shardingsphere/infra/binder/context/segment/select/projection/impl/AggregationProjection.java
@@ -50,11 +50,21 @@ public class AggregationProjection implements Projection {
private final DatabaseType databaseType;
+ private final String separator;
+
private final List<AggregationProjection> derivedAggregationProjections =
new ArrayList<>(2);
@Setter
private int index = -1;
+ public AggregationProjection(final AggregationType type, final String
expression, final IdentifierValue alias, final DatabaseType databaseType) {
+ this.type = type;
+ this.expression = expression;
+ this.alias = alias;
+ this.databaseType = databaseType;
+ this.separator = null;
+ }
+
@Override
public String getColumnName() {
return getColumnLabel();
@@ -72,4 +82,8 @@ public class AggregationProjection implements Projection {
public final Optional<IdentifierValue> getAlias() {
return Optional.ofNullable(alias);
}
+
+ public Optional<String> getSeparator() {
+ return Optional.ofNullable(separator);
+ }
}
diff --git
a/infra/binder/src/main/java/org/apache/shardingsphere/infra/binder/engine/segment/projection/ProjectionsSegmentBinder.java
b/infra/binder/src/main/java/org/apache/shardingsphere/infra/binder/engine/segment/projection/ProjectionsSegmentBinder.java
index a520cc4142f..83557beb1f9 100644
---
a/infra/binder/src/main/java/org/apache/shardingsphere/infra/binder/engine/segment/projection/ProjectionsSegmentBinder.java
+++
b/infra/binder/src/main/java/org/apache/shardingsphere/infra/binder/engine/segment/projection/ProjectionsSegmentBinder.java
@@ -106,7 +106,8 @@ public final class ProjectionsSegmentBinder {
final Multimap<CaseInsensitiveString, TableSegmentBinderContext>
tableBinderContexts,
final Multimap<CaseInsensitiveString, TableSegmentBinderContext>
outerTableBinderContexts) {
AggregationDistinctProjectionSegment result = new
AggregationDistinctProjectionSegment(aggregationDistinctSegment.getStartIndex(),
aggregationDistinctSegment.getStopIndex(),
- aggregationDistinctSegment.getType(),
aggregationDistinctSegment.getExpression(),
aggregationDistinctSegment.getDistinctInnerExpression());
+ aggregationDistinctSegment.getType(),
aggregationDistinctSegment.getExpression(),
aggregationDistinctSegment.getDistinctInnerExpression(),
+ aggregationDistinctSegment.getSeparator().orElse(null));
aggregationDistinctSegment.getParameters()
.forEach(each ->
result.getParameters().add(ExpressionSegmentBinder.bind(each,
SegmentType.PROJECTION, binderContext, tableBinderContexts,
outerTableBinderContexts)));
aggregationDistinctSegment.getAliasSegment().ifPresent(result::setAlias);
@@ -117,7 +118,8 @@ public final class ProjectionsSegmentBinder {
final Multimap<CaseInsensitiveString, TableSegmentBinderContext>
tableBinderContexts,
final Multimap<CaseInsensitiveString, TableSegmentBinderContext>
outerTableBinderContexts) {
AggregationProjectionSegment result =
- new
AggregationProjectionSegment(aggregationSegment.getStartIndex(),
aggregationSegment.getStopIndex(), aggregationSegment.getType(),
aggregationSegment.getExpression());
+ new
AggregationProjectionSegment(aggregationSegment.getStartIndex(),
aggregationSegment.getStopIndex(), aggregationSegment.getType(),
aggregationSegment.getExpression(),
+ aggregationSegment.getSeparator().orElse(null));
aggregationSegment.getParameters()
.forEach(each ->
result.getParameters().add(ExpressionSegmentBinder.bind(each,
SegmentType.PROJECTION, binderContext, tableBinderContexts,
outerTableBinderContexts)));
aggregationSegment.getAliasSegment().ifPresent(result::setAlias);
diff --git
a/kernel/sql-federation/optimizer/src/main/java/org/apache/shardingsphere/sqlfederation/optimizer/converter/segment/projection/impl/AggregationProjectionConverter.java
b/kernel/sql-federation/optimizer/src/main/java/org/apache/shardingsphere/sqlfederation/optimizer/converter/segment/projection/impl/AggregationProjectionConverter.java
index dd62b33e6b2..8cb1e65af35 100644
---
a/kernel/sql-federation/optimizer/src/main/java/org/apache/shardingsphere/sqlfederation/optimizer/converter/segment/projection/impl/AggregationProjectionConverter.java
+++
b/kernel/sql-federation/optimizer/src/main/java/org/apache/shardingsphere/sqlfederation/optimizer/converter/segment/projection/impl/AggregationProjectionConverter.java
@@ -57,12 +57,17 @@ public final class AggregationProjectionConverter {
register(SqlStdOperatorTable.COUNT);
register(SqlStdOperatorTable.AVG);
register(SqlStdOperatorTable.BIT_XOR);
+ register(SqlStdOperatorTable.LISTAGG, "GROUP_CONCAT");
}
private static void register(final SqlAggFunction sqlAggFunction) {
REGISTRY.put(sqlAggFunction.getName(), sqlAggFunction);
}
+ private static void register(final SqlAggFunction sqlAggFunction, final
String alias) {
+ REGISTRY.put(alias, sqlAggFunction);
+ }
+
/**
* Convert aggregation projection segment to sql node.
*
@@ -75,7 +80,7 @@ public final class AggregationProjectionConverter {
}
SqlLiteral functionQuantifier = segment instanceof
AggregationDistinctProjectionSegment ?
SqlLiteral.createSymbol(SqlSelectKeyword.DISTINCT, SqlParserPos.ZERO) : null;
SqlAggFunction operator = convertOperator(segment.getType().name());
- List<SqlNode> params = convertParameters(segment.getParameters(),
segment.getExpression());
+ List<SqlNode> params = convertParameters(segment.getParameters(),
segment.getExpression(), segment.getSeparator().orElse(null));
SqlBasicCall sqlBasicCall = new SqlBasicCall(operator, params,
SqlParserPos.ZERO, functionQuantifier);
if (segment.getAliasName().isPresent()) {
return Optional.of(new SqlBasicCall(SqlStdOperatorTable.AS,
Arrays.asList(sqlBasicCall,
@@ -89,7 +94,7 @@ public final class AggregationProjectionConverter {
return REGISTRY.get(operator);
}
- private static List<SqlNode> convertParameters(final
Collection<ExpressionSegment> params, final String expression) {
+ private static List<SqlNode> convertParameters(final
Collection<ExpressionSegment> params, final String expression, final String
separator) {
if (expression.contains("*")) {
return
Collections.singletonList(SqlIdentifier.star(SqlParserPos.ZERO));
}
@@ -97,6 +102,9 @@ public final class AggregationProjectionConverter {
for (ExpressionSegment each : params) {
ExpressionConverter.convert(each).ifPresent(result::add);
}
+ if (null != separator) {
+ result.add(SqlLiteral.createCharString(separator,
SqlParserPos.ZERO));
+ }
return result;
}
}
diff --git a/parser/sql/dialect/mysql/src/main/antlr4/imports/mysql/BaseRule.g4
b/parser/sql/dialect/mysql/src/main/antlr4/imports/mysql/BaseRule.g4
index 77d80dbd1d6..ae356733ce3 100644
--- a/parser/sql/dialect/mysql/src/main/antlr4/imports/mysql/BaseRule.g4
+++ b/parser/sql/dialect/mysql/src/main/antlr4/imports/mysql/BaseRule.g4
@@ -986,8 +986,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?
;
jsonFunction
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 d058633429a..6ab433e59a1 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
@@ -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.MySQLStatementBaseVisitor;
+import
org.apache.shardingsphere.sql.parser.autogen.MySQLStatementParser.SeparatorNameContext;
import
org.apache.shardingsphere.sql.parser.autogen.MySQLStatementParser.AggregationFunctionContext;
import
org.apache.shardingsphere.sql.parser.autogen.MySQLStatementParser.AliasContext;
import
org.apache.shardingsphere.sql.parser.autogen.MySQLStatementParser.AssignmentContext;
@@ -916,6 +917,11 @@ public abstract class MySQLStatementVisitor extends
MySQLStatementBaseVisitor<AS
: new
ExpressionProjectionSegment(ctx.getStart().getStartIndex(),
ctx.getStop().getStopIndex(), getOriginalText(ctx));
}
+ @Override
+ public ASTNode visitSeparatorName(final SeparatorNameContext ctx) {
+ return new StringLiteralValue(ctx.string_().getText());
+ }
+
@Override
public final ASTNode visitJsonFunction(final JsonFunctionContext ctx) {
JsonFunctionNameContext functionNameContext = ctx.jsonFunctionName();
@@ -957,14 +963,18 @@ public abstract class MySQLStatementVisitor extends
MySQLStatementBaseVisitor<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;
}
@@ -980,11 +990,7 @@ public abstract class MySQLStatementVisitor extends
MySQLStatementBaseVisitor<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
diff --git
a/parser/sql/statement/core/src/main/java/org/apache/shardingsphere/sql/parser/statement/core/segment/dml/item/AggregationDistinctProjectionSegment.java
b/parser/sql/statement/core/src/main/java/org/apache/shardingsphere/sql/parser/statement/core/segment/dml/item/AggregationDistinctProjectionSegment.java
index e01efd80f4a..2b82bd12096 100644
---
a/parser/sql/statement/core/src/main/java/org/apache/shardingsphere/sql/parser/statement/core/segment/dml/item/AggregationDistinctProjectionSegment.java
+++
b/parser/sql/statement/core/src/main/java/org/apache/shardingsphere/sql/parser/statement/core/segment/dml/item/AggregationDistinctProjectionSegment.java
@@ -33,4 +33,10 @@ public final class AggregationDistinctProjectionSegment
extends AggregationProje
super(startIndex, stopIndex, type, expression);
distinctInnerExpression =
SQLUtils.getExpressionWithoutOutsideParentheses(distinctExpression);
}
+
+ public AggregationDistinctProjectionSegment(final int startIndex, final
int stopIndex, final AggregationType type, final String expression, final
String distinctExpression,
+ final String separator) {
+ super(startIndex, stopIndex, type, expression, separator);
+ distinctInnerExpression =
SQLUtils.getExpressionWithoutOutsideParentheses(distinctExpression);
+ }
}
diff --git
a/parser/sql/statement/core/src/main/java/org/apache/shardingsphere/sql/parser/statement/core/segment/dml/item/AggregationProjectionSegment.java
b/parser/sql/statement/core/src/main/java/org/apache/shardingsphere/sql/parser/statement/core/segment/dml/item/AggregationProjectionSegment.java
index ab8eed3a2dc..667a5ef89db 100644
---
a/parser/sql/statement/core/src/main/java/org/apache/shardingsphere/sql/parser/statement/core/segment/dml/item/AggregationProjectionSegment.java
+++
b/parser/sql/statement/core/src/main/java/org/apache/shardingsphere/sql/parser/statement/core/segment/dml/item/AggregationProjectionSegment.java
@@ -43,6 +43,8 @@ public class AggregationProjectionSegment implements
ProjectionSegment, AliasAva
private final String expression;
+ private final String separator;
+
private final Collection<ExpressionSegment> parameters = new
LinkedList<>();
@Setter
@@ -53,6 +55,15 @@ public class AggregationProjectionSegment implements
ProjectionSegment, AliasAva
this.stopIndex = stopIndex;
this.type = type;
this.expression = expression;
+ this.separator = null;
+ }
+
+ public AggregationProjectionSegment(final int startIndex, final int
stopIndex, final AggregationType type, final String expression, final String
separator) {
+ this.startIndex = startIndex;
+ this.stopIndex = stopIndex;
+ this.type = type;
+ this.expression = expression;
+ this.separator = separator;
}
@Override
@@ -83,4 +94,8 @@ public class AggregationProjectionSegment implements
ProjectionSegment, AliasAva
public String getText() {
return expression;
}
+
+ public Optional<String> getSeparator() {
+ return Optional.ofNullable(separator);
+ }
}
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 f8c39535f1f..f4bd5b48092 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
@@ -20,123 +20,123 @@
<test-case sql="SELECT count(0) as orders_count FROM t_order o WHERE
o.status LIKE CONCAT('%%', ?, '%%') AND o.user_id IN (?, ?) AND o.order_id
BETWEEN ? AND ?"
scenario-types="db,tbl,dbtbl_with_readwrite_splitting,readwrite_splitting">
<assertion parameters="init:String, 10:int, 11:int, 1000:int,
2901:int" expected-data-source-name="read_dataset" />
</test-case>
-
+
<test-case sql="SELECT count(0) as orders_count FROM t_order o WHERE
o.status ~~ CONCAT('%%', ?, '%%') AND o.user_id IN (?, ?) AND o.order_id
BETWEEN ? AND ?" db-types="PostgreSQL"
scenario-types="db,tbl,dbtbl_with_readwrite_splitting,readwrite_splitting">
<assertion parameters="init:String, 10:int, 11:int, 1000:int,
2901:int" expected-data-source-name="read_dataset" />
</test-case>
-
+
<test-case sql="SELECT SUM(user_id) AS user_id_sum FROM t_order"
scenario-types="db,tbl,dbtbl_with_readwrite_splitting,readwrite_splitting">
<assertion expected-data-source-name="read_dataset" />
</test-case>
-
+
<test-case sql="SELECT COUNT(*) AS orders_count FROM t_order"
scenario-types="db,tbl,dbtbl_with_readwrite_splitting,readwrite_splitting">
<assertion expected-data-source-name="read_dataset" />
</test-case>
-
+
<test-case sql="SELECT COUNT(*) AS orders_count FROM t_order WHERE
order_id > 1-1"
scenario-types="db,tbl,dbtbl_with_readwrite_splitting,readwrite_splitting">
<assertion expected-data-source-name="read_dataset" />
</test-case>
-
+
<test-case sql="SELECT COUNT(*) AS orders_count FROM t_order WHERE
order_id > 1 - 1"
scenario-types="db,tbl,dbtbl_with_readwrite_splitting,readwrite_splitting">
<assertion expected-data-source-name="read_dataset" />
</test-case>
-
+
<test-case sql="SELECT MAX(user_id) AS max_user_id FROM t_order"
scenario-types="db,tbl,dbtbl_with_readwrite_splitting,readwrite_splitting">
<assertion expected-data-source-name="read_dataset" />
</test-case>
-
+
<test-case sql="SELECT MIN(user_id) AS min_user_id FROM t_order"
scenario-types="db,tbl,dbtbl_with_readwrite_splitting,readwrite_splitting">
<assertion expected-data-source-name="read_dataset" />
</test-case>
-
+
<!-- FIXME #15593 Expected: is "19", but: was "19.5000" in db scenario -->
<test-case sql="SELECT AVG(user_id) AS user_id_avg FROM t_order"
scenario-types="db,tbl,dbtbl_with_readwrite_splitting,readwrite_splitting">
<assertion expected-data-source-name="read_dataset" />
</test-case>
-
+
<test-case sql="SELECT COUNT(*) AS items_count FROM t_order o,
t_order_item i WHERE o.user_id = i.user_id AND o.order_id = i.order_id AND
o.user_id IN (?, ?) AND o.order_id BETWEEN ? AND ?"
scenario-types="db,tbl,dbtbl_with_readwrite_splitting,readwrite_splitting">
<assertion parameters="10:int, 11:int, 1000:int, 1909:int"
expected-data-source-name="read_dataset" />
</test-case>
-
+
<test-case sql="SELECT COUNT(*) AS items_count FROM t_order o JOIN
t_order_item i ON o.user_id = i.user_id AND o.order_id = i.order_id WHERE
o.user_id IN (?, ?) AND o.order_id BETWEEN ? AND ?"
scenario-types="db,tbl,dbtbl_with_readwrite_splitting,readwrite_splitting">
<assertion parameters="10:int, 11:int, 1000:int, 1909:int"
expected-data-source-name="read_dataset" />
</test-case>
-
+
<test-case sql="SELECT COUNT(`order_id`) AS orders_count FROM t_order"
db-types="MySQL"
scenario-types="db,tbl,dbtbl_with_readwrite_splitting,readwrite_splitting">
<assertion expected-data-source-name="read_dataset" />
</test-case>
-
+
<test-case sql="SELECT SUM(order_id) AS orders_sum, user_id FROM t_order
GROUP BY user_id ORDER BY user_id"
scenario-types="db,tbl,dbtbl_with_readwrite_splitting,readwrite_splitting">
<assertion expected-data-source-name="read_dataset" />
</test-case>
-
+
<test-case sql="SELECT COUNT(order_id) AS orders_count, user_id FROM
t_order GROUP BY user_id ORDER BY user_id"
scenario-types="db,tbl,dbtbl_with_readwrite_splitting,readwrite_splitting">
<assertion expected-data-source-name="read_dataset" />
</test-case>
-
+
<test-case sql="SELECT MAX(order_id) AS max_order_id, user_id FROM t_order
GROUP BY user_id ORDER BY user_id"
scenario-types="db,tbl,dbtbl_with_readwrite_splitting,readwrite_splitting">
<assertion expected-data-source-name="read_dataset" />
</test-case>
-
+
<test-case sql="SELECT MIN(order_id) AS min_order_id, user_id FROM t_order
GROUP BY user_id ORDER BY user_id"
scenario-types="db,tbl,dbtbl_with_readwrite_splitting,readwrite_splitting">
<assertion expected-data-source-name="read_dataset" />
</test-case>
-
+
<test-case sql="SELECT AVG(order_id) AS orders_avg, user_id FROM t_order
GROUP BY user_id ORDER BY user_id"
scenario-types="db,tbl,dbtbl_with_readwrite_splitting,readwrite_splitting">
<assertion expected-data-source-name="read_dataset" />
</test-case>
-
+
<test-case sql="SELECT SUM(order_id) AS orders_sum, user_id FROM t_order
GROUP BY user_id ORDER BY orders_sum DESC"
scenario-types="db,tbl,dbtbl_with_readwrite_splitting,readwrite_splitting">
<assertion expected-data-source-name="read_dataset" />
</test-case>
-
+
<test-case sql="SELECT count(*) as items_count FROM t_order o JOIN
t_order_item i ON o.user_id = i.user_id AND o.order_id = i.order_id WHERE
o.user_id IN (?, ?) AND o.order_id BETWEEN ? AND ? GROUP BY o.user_id"
scenario-types="db,tbl,dbtbl_with_readwrite_splitting,readwrite_splitting">
<assertion parameters="10:int, 11:int, 1000:int, 1109:int"
expected-data-source-name="read_dataset" />
</test-case>
-
+
<test-case sql="SELECT sum(if(status=0, 1, 0)) func_status FROM t_order
WHERE user_id = ? AND order_id = ?" db-types="MySQL"
scenario-types="db,tbl,dbtbl_with_readwrite_splitting,readwrite_splitting">
<assertion parameters="12:int, 1000:int"
expected-data-source-name="read_dataset" />
</test-case>
-
+
<!-- TODO Replace with standard table structure -->
<!--<test-case sql="SELECT (SELECT MAX(user_id) FROM
t_order_federate_sharding) max_user_id, order_id_sharding, status FROM
t_order_federate_sharding WHERE order_id_sharding > ?"
db-types="MySQL,PostgreSQL" scenario-types="tbl">
<assertion parameters="1100:int" />
</test-case>-->
-
+
<!-- TODO Replace with standard table structure -->
<!--<test-case sql="SELECT user_id, SUM(order_id_sharding) FROM
t_order_federate_sharding GROUP BY user_id HAVING SUM(order_id_sharding) > ?
ORDER BY user_id" db-types="MySQL,PostgreSQL" scenario-types="tbl">
<assertion parameters="1000:int" />
</test-case>-->
-
+
<test-case sql="SELECT COUNT(1) FROM t_order WHERE order_id < ?"
db-types="PostgreSQL" scenario-types="db,tbl">
<assertion parameters="2000:int" />
</test-case>
-
+
<test-case sql="SELECT SUM(CRC32(`order_id`)) FROM t_order WHERE order_id
= ?" db-types="MySQL" scenario-types="db,tbl">
<assertion parameters="1000:int" />
</test-case>
-
+
<!-- TODO Replace with standard table structure -->
<!--<test-case sql="SELECT AVG(order_id_sharding) AS order_id_sharding_avg
FROM (SELECT order_id_sharding, user_id FROM t_order_federate_sharding WHERE
order_id_sharding = 1010) AS TEMP"
scenario-types="db,dbtbl_with_readwrite_splitting_and_encrypt,sharding_and_encrypt,encrypt_and_readwrite_splitting"
db-types="MySQL">
<assertion expected-data-source-name="read_dataset" />
</test-case>-->
-
+
<test-case sql="SELECT MIN(o.order_id), MIN(o.merchant_id), i.product_id
FROM t_order o INNER JOIN t_order_item i ON o.order_id = i.order_id WHERE
o.user_id = ? GROUP BY i.product_id ORDER BY i.product_id"
db-types="MySQL,PostgreSQL,openGauss" scenario-types="db_tbl_sql_federation">
<assertion parameters="10:int"
expected-data-source-name="read_dataset" />
</test-case>
-
+
<test-case sql="SELECT MIN(o.order_id), MIN(o.merchant_id),
MIN(m.merchant_name) FROM t_order o INNER JOIN t_merchant m ON o.merchant_id =
m.merchant_id WHERE o.user_id = ? GROUP BY m.merchant_id ORDER BY
m.merchant_id" db-types="MySQL,PostgreSQL,openGauss"
scenario-types="db_tbl_sql_federation">
<assertion parameters="10:int"
expected-data-source-name="read_dataset" />
</test-case>
-
+
<test-case sql="SELECT MIN(d.detail_id), MIN(p.category_id), p.product_id
FROM t_product p INNER JOIN t_product_detail d ON p.product_id = d.product_id
WHERE p.product_id = ? GROUP BY p.product_id"
db-types="MySQL,PostgreSQL,openGauss" scenario-types="db_tbl_sql_federation">
<assertion parameters="10:int"
expected-data-source-name="read_dataset" />
</test-case>
-
+
<test-case sql="SELECT MAX(p.price) AS max_price, MIN(p.price) AS
min_price, SUM(p.price) AS sum_price, AVG(p.price) AS avg_price, COUNT(1) AS
count FROM t_order o INNER JOIN t_order_item i ON o.order_id = i.order_id INNER
JOIN t_product p ON i.product_id = p.product_id GROUP BY o.order_id HAVING
SUM(p.price) > ? ORDER BY max_price" db-types="MySQL,PostgreSQL,openGauss"
scenario-types="db_tbl_sql_federation">
<assertion parameters="10000:int"
expected-data-source-name="read_dataset" />
</test-case>
-
+
<test-case sql="SELECT * FROM t_merchant WHERE business_code LIKE
CONCAT('%', ?, '%')" db-types="MySQL,PostgreSQL,openGauss"
scenario-types="encrypt">
<assertion parameters="abc:String"
expected-data-source-name="read_dataset" />
</test-case>
@@ -144,4 +144,12 @@
<test-case sql="SELECT * FROM t_merchant WHERE business_code LIKE
CONCAT('%', CONCAT(?, '%'))" db-types="MySQL,PostgreSQL,openGauss"
scenario-types="encrypt">
<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">
+ <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">
+ <assertion expected-data-source-name="read_dataset" />
+ </test-case>
</e2e-test-cases>
diff --git
a/test/it/parser/src/main/java/org/apache/shardingsphere/test/it/sql/parser/internal/asserts/segment/projection/ProjectionAssert.java
b/test/it/parser/src/main/java/org/apache/shardingsphere/test/it/sql/parser/internal/asserts/segment/projection/ProjectionAssert.java
index 7a8fbe2a079..ecaa805fd2c 100644
---
a/test/it/parser/src/main/java/org/apache/shardingsphere/test/it/sql/parser/internal/asserts/segment/projection/ProjectionAssert.java
+++
b/test/it/parser/src/main/java/org/apache/shardingsphere/test/it/sql/parser/internal/asserts/segment/projection/ProjectionAssert.java
@@ -169,6 +169,7 @@ public final class ProjectionAssert {
assertThat(assertContext.getText("Aggregation projection type
assertion error: "), actual.getType().name(), is(expected.getType()));
assertThat(assertContext.getText("Aggregation projection inner
expression assertion error: "), actual.getExpression(),
is(expected.getExpression()));
assertThat(assertContext.getText("Aggregation projection alias
assertion error: "), actual.getAliasName().orElse(null),
is(expected.getAlias()));
+ assertThat(assertContext.getText("Aggregation projection separator
assertion error: "), actual.getSeparator().orElse(null),
is(expected.getSeparator()));
if (actual instanceof AggregationDistinctProjectionSegment) {
assertThat(assertContext.getText("Projection type assertion error:
"), expected, instanceOf(ExpectedAggregationDistinctProjection.class));
assertThat(assertContext.getText("Aggregation projection distinct
inner expression assertion error: "),
diff --git
a/test/it/parser/src/main/java/org/apache/shardingsphere/test/it/sql/parser/internal/cases/parser/jaxb/segment/impl/projection/impl/aggregation/ExpectedAggregationProjection.java
b/test/it/parser/src/main/java/org/apache/shardingsphere/test/it/sql/parser/internal/cases/parser/jaxb/segment/impl/projection/impl/aggregation/ExpectedAggregationProjection.java
index 2cf8da6f422..384048fa74c 100644
---
a/test/it/parser/src/main/java/org/apache/shardingsphere/test/it/sql/parser/internal/cases/parser/jaxb/segment/impl/projection/impl/aggregation/ExpectedAggregationProjection.java
+++
b/test/it/parser/src/main/java/org/apache/shardingsphere/test/it/sql/parser/internal/cases/parser/jaxb/segment/impl/projection/impl/aggregation/ExpectedAggregationProjection.java
@@ -44,6 +44,9 @@ public class ExpectedAggregationProjection extends
AbstractExpectedSQLSegment im
@XmlAttribute
private String alias;
+ @XmlAttribute
+ private String separator;
+
@XmlElement(name = "parameters")
private final List<ExpectedExpression> parameters = new LinkedList<>();
}
diff --git a/test/it/parser/src/main/resources/case/dml/select-aggregate.xml
b/test/it/parser/src/main/resources/case/dml/select-aggregate.xml
index 50d7b5bd00f..08dd5ab8685 100644
--- a/test/it/parser/src/main/resources/case/dml/select-aggregate.xml
+++ b/test/it/parser/src/main/resources/case/dml/select-aggregate.xml
@@ -518,4 +518,29 @@
<index-item index="1" start-index="199" stop-index="199"/>
</order-by>
</select>
+ <select sql-case-id="select_group_concat">
+ <projections start-index="7" stop-index="51">
+ <aggregation-projection type="GROUP_CONCAT"
expression="GROUP_CONCAT(user_id)" alias="user_id_group_concat" start-index="7"
stop-index="27">
+ <parameter>
+ <column name="user_id" start-index="20" stop-index="26" />
+ </parameter>
+ </aggregation-projection>
+ </projections>
+ <from>
+ <simple-table name="t_order" start-index="58" stop-index="64"/>
+ </from>
+ </select>
+ <select sql-case-id="select_group_concat_with_distinct_with_separator">
+ <projections start-index="7" stop-index="74">
+ <aggregation-distinct-projection type="GROUP_CONCAT"
expression="GROUP_CONCAT(distinct user_id SEPARATOR ' ')" separator=" "
+ distinct-inner-expression="user_id"
alias="user_id_group_concat" start-index="7" stop-index="50">
+ <parameter>
+ <column name="user_id" start-index="29" stop-index="35" />
+ </parameter>
+ </aggregation-distinct-projection>
+ </projections>
+ <from>
+ <simple-table name="t_order" start-index="81" stop-index="87"/>
+ </from>
+ </select>
</sql-parser-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 ac2e2441214..102a9540647 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
@@ -17,20 +17,20 @@
-->
<sql-parser-test-cases>
- <select sql-case-id="select_group_concat">
+ <select sql-case-id="select_group_concat_with_order_by">
<from>
- <simple-table name="t_order" start-index="33" stop-index="39" />
+ <simple-table name="t_order" start-index="49" stop-index="55" />
</from>
- <projections start-index="7" stop-index="26">
- <aggregation-projection type="GROUP_CONCAT"
expression="GROUP_CONCAT(status)" text="GROUP_CONCAT(status)" start-index="7"
stop-index="26">
+ <projections start-index="7" stop-index="42">
+ <expression-projection text="GROUP_CONCAT(status ORDER BY status)"
start-index="7" stop-index="42">
<expr>
- <function function-name="GROUP_CONCAT" start-index="7"
stop-index="26" text="GROUP_CONCAT(status)">
+ <function function-name="GROUP_CONCAT" start-index="7"
stop-index="42" text="GROUP_CONCAT(status ORDER BY status)">
<parameter>
<column name="status" start-index="20"
stop-index="25" />
</parameter>
</function>
</expr>
- </aggregation-projection>
+ </expression-projection>
</projections>
</select>
<select sql-case-id="select_window_function">
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 583ad048847..a4196127606 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,4 +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-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 8d8dcd4a0e8..99727b077bc 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" value="SELECT GROUP_CONCAT(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" />
<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" />
