You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@doris.apache.org by yi...@apache.org on 2022/05/17 11:29:53 UTC
[incubator-doris] branch master updated: [feature] group_concat support distinct (#9576)
This is an automated email from the ASF dual-hosted git repository.
yiguolei pushed a commit to branch master
in repository https://gitbox.apache.org/repos/asf/incubator-doris.git
The following commit(s) were added to refs/heads/master by this push:
new d95fe08458 [feature] group_concat support distinct (#9576)
d95fe08458 is described below
commit d95fe0845827a2ec6220ac92229659f26d0bde30
Author: Stalary <st...@163.com>
AuthorDate: Tue May 17 19:29:47 2022 +0800
[feature] group_concat support distinct (#9576)
---
.../aggregate-functions/group_concat.md | 14 +++++++--
.../aggregate-functions/group_concat.md | 12 ++++++--
.../apache/doris/analysis/FunctionCallExpr.java | 4 ---
.../org/apache/doris/common/util/SqlUtils.java | 2 +-
.../org/apache/doris/analysis/SelectStmtTest.java | 5 +++-
.../data/query/group_concat/test_group_concat.out | 7 +++++
.../query/group_concat/test_group_concat.groovy | 35 +++++-----------------
7 files changed, 40 insertions(+), 39 deletions(-)
diff --git a/docs/en/sql-manual/sql-functions/aggregate-functions/group_concat.md b/docs/en/sql-manual/sql-functions/aggregate-functions/group_concat.md
index 7f7ec160e9..7e0095cafd 100644
--- a/docs/en/sql-manual/sql-functions/aggregate-functions/group_concat.md
+++ b/docs/en/sql-manual/sql-functions/aggregate-functions/group_concat.md
@@ -28,7 +28,7 @@ under the License.
### description
#### Syntax
-`VARCHAR GROUP_CONCAT(VARCHAR str[, VARCHAR sep])`
+`VARCHAR GROUP_CONCAT([DISTINCT] VARCHAR str[, VARCHAR sep])`
This function is an aggregation function similar to sum (), and group_concat links multiple rows of results in the result set to a string. The second parameter, sep, is a connection symbol between strings, which can be omitted. This function usually needs to be used with group by statements.
@@ -43,22 +43,30 @@ mysql> select value from test;
| a |
| b |
| c |
+| c |
+-------+
mysql> select GROUP_CONCAT(value) from test;
+-----------------------+
| GROUP_CONCAT(`value`) |
+-----------------------+
-| a, b, c |
+| a, b, c, c |
+-----------------------+
mysql> select GROUP_CONCAT(value, " ") from test;
+----------------------------+
| GROUP_CONCAT(`value`, ' ') |
+----------------------------+
-| a b c |
+| a b c c |
+----------------------------+
+mysql> select GROUP_CONCAT(DISTINCT value) from test;
++-----------------------+
+| GROUP_CONCAT(`value`) |
++-----------------------+
+| a, b, c |
++-----------------------+
+
mysql> select GROUP_CONCAT(value, NULL) from test;
+----------------------------+
| GROUP_CONCAT(`value`, NULL)|
diff --git a/docs/zh-CN/sql-manual/sql-functions/aggregate-functions/group_concat.md b/docs/zh-CN/sql-manual/sql-functions/aggregate-functions/group_concat.md
index 3a92c31f23..ef6189483f 100644
--- a/docs/zh-CN/sql-manual/sql-functions/aggregate-functions/group_concat.md
+++ b/docs/zh-CN/sql-manual/sql-functions/aggregate-functions/group_concat.md
@@ -28,7 +28,7 @@ under the License.
### description
#### Syntax
-`VARCHAR GROUP_CONCAT(VARCHAR str[, VARCHAR sep])`
+`VARCHAR GROUP_CONCAT([DISTINCT] VARCHAR str[, VARCHAR sep])`
该函数是类似于 sum() 的聚合函数,group_concat 将结果集中的多行结果连接成一个字符串。第二个参数 sep 为字符串之间的连接符号,该参数可以省略。该函数通常需要和 group by 语句一起使用。
@@ -43,12 +43,20 @@ mysql> select value from test;
| a |
| b |
| c |
+| c |
+-------+
mysql> select GROUP_CONCAT(value) from test;
+-----------------------+
| GROUP_CONCAT(`value`) |
+-----------------------+
+| a, b, c, c |
++-----------------------+
+
+mysql> select GROUP_CONCAT(DISTINCT value) from test;
++-----------------------+
+| GROUP_CONCAT(`value`) |
++-----------------------+
| a, b, c |
+-----------------------+
@@ -56,7 +64,7 @@ mysql> select GROUP_CONCAT(value, " ") from test;
+----------------------------+
| GROUP_CONCAT(`value`, ' ') |
+----------------------------+
-| a b c |
+| a b c c |
+----------------------------+
mysql> select GROUP_CONCAT(value, NULL) from test;
diff --git a/fe/fe-core/src/main/java/org/apache/doris/analysis/FunctionCallExpr.java b/fe/fe-core/src/main/java/org/apache/doris/analysis/FunctionCallExpr.java
index 40387673bd..7f55d59fe4 100644
--- a/fe/fe-core/src/main/java/org/apache/doris/analysis/FunctionCallExpr.java
+++ b/fe/fe-core/src/main/java/org/apache/doris/analysis/FunctionCallExpr.java
@@ -481,10 +481,6 @@ public class FunctionCallExpr extends Expr {
"group_concat requires one or two parameters: " + this.toSql());
}
- if (fnParams.isDistinct()) {
- throw new AnalysisException("group_concat does not support DISTINCT");
- }
-
Expr arg0 = getChild(0);
if (!arg0.type.isStringType() && !arg0.type.isNull()) {
throw new AnalysisException(
diff --git a/fe/fe-core/src/main/java/org/apache/doris/common/util/SqlUtils.java b/fe/fe-core/src/main/java/org/apache/doris/common/util/SqlUtils.java
index 1fc04fc108..6690883209 100644
--- a/fe/fe-core/src/main/java/org/apache/doris/common/util/SqlUtils.java
+++ b/fe/fe-core/src/main/java/org/apache/doris/common/util/SqlUtils.java
@@ -17,7 +17,7 @@
package org.apache.doris.common.util;
-import org.apache.parquet.Strings;
+import com.google.common.base.Strings;
public class SqlUtils {
public static String escapeUnquote(String ident) {
diff --git a/fe/fe-core/src/test/java/org/apache/doris/analysis/SelectStmtTest.java b/fe/fe-core/src/test/java/org/apache/doris/analysis/SelectStmtTest.java
index 4699a85792..0c82aed559 100755
--- a/fe/fe-core/src/test/java/org/apache/doris/analysis/SelectStmtTest.java
+++ b/fe/fe-core/src/test/java/org/apache/doris/analysis/SelectStmtTest.java
@@ -787,9 +787,12 @@ public class SelectStmtTest {
@Test
public void testSelectOuterJoinSql() throws Exception {
ConnectContext ctx = UtFrameUtils.createDefaultCtx();
- String sql1 = "select l.citycode, group_concat(r.username) from db1.table1 l left join db1.table2 r on l.citycode=r.citycode group by l.citycode";
+ String sql1 = "select l.citycode, group_concat(distinct r.username) from db1.table1 l "
+ + "left join db1.table2 r on l.citycode=r.citycode group by l.citycode";
SelectStmt stmt1 = (SelectStmt) UtFrameUtils.parseAndAnalyzeStmt(sql1, ctx);
Assert.assertTrue(stmt1.getAnalyzer().getSlotDesc(new SlotId(2)).getIsNullable());
Assert.assertTrue(stmt1.getAnalyzer().getSlotDescriptor("r.username").getIsNullable());
+ FunctionCallExpr expr = (FunctionCallExpr) stmt1.getSelectList().getItems().get(1).getExpr();
+ Assert.assertTrue(expr.getFnParams().isDistinct());
}
}
diff --git a/regression-test/data/query/group_concat/test_group_concat.out b/regression-test/data/query/group_concat/test_group_concat.out
new file mode 100644
index 0000000000..94f73cc536
--- /dev/null
+++ b/regression-test/data/query/group_concat/test_group_concat.out
@@ -0,0 +1,7 @@
+-- This file is automatically generated. You should know what you did if you want to edit this
+-- !select --
+false, false
+
+-- !select --
+false
+
diff --git a/fe/fe-core/src/main/java/org/apache/doris/common/util/SqlUtils.java b/regression-test/suites/query/group_concat/test_group_concat.groovy
similarity index 51%
copy from fe/fe-core/src/main/java/org/apache/doris/common/util/SqlUtils.java
copy to regression-test/suites/query/group_concat/test_group_concat.groovy
index 1fc04fc108..6bb57dea44 100644
--- a/fe/fe-core/src/main/java/org/apache/doris/common/util/SqlUtils.java
+++ b/regression-test/suites/query/group_concat/test_group_concat.groovy
@@ -15,33 +15,12 @@
// specific language governing permissions and limitations
// under the License.
-package org.apache.doris.common.util;
+suite("test_group_concat", "query") {
+ qt_select """
+ SELECT group_concat(k6) FROM test_query_db.test where k6='false'
+ """
-import org.apache.parquet.Strings;
-
-public class SqlUtils {
- public static String escapeUnquote(String ident) {
- return ident.replaceAll("``", "`");
- }
-
- public static String getIdentSql(String ident) {
- StringBuilder sb = new StringBuilder();
- sb.append('`');
- for (char ch : ident.toCharArray()) {
- if (ch == '`') {
- sb.append("``");
- } else {
- sb.append(ch);
- }
- }
- sb.append('`');
- return sb.toString();
- }
-
- public static String escapeQuota(String str) {
- if (Strings.isNullOrEmpty(str)) {
- return str;
- }
- return str.replaceAll("\"", "\\\\\"");
- }
+ qt_select """
+ SELECT group_concat(DISTINCT k6) FROM test_query_db.test where k6='false'
+ """
}
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For additional commands, e-mail: commits-help@doris.apache.org