This is an automated email from the ASF dual-hosted git repository.
morrysnow pushed a commit to branch branch-2.0
in repository https://gitbox.apache.org/repos/asf/doris.git
The following commit(s) were added to refs/heads/branch-2.0 by this push:
new 7262a6734d [Fix](Planner) fix parse error of view with group_concat
order by (#22530)
7262a6734d is described below
commit 7262a6734def83534813773cdf5ee22e80b1f4e0
Author: LiBinfeng <[email protected]>
AuthorDate: Thu Aug 3 11:47:12 2023 +0800
[Fix](Planner) fix parse error of view with group_concat order by (#22530)
cherry-pick from master
master pr：https://github.com/apache/doris/pull/22196
commit id： 3a1d678ca97f6d7e69a89fbe05c6f701515bb67c
Problem:
When create view with projection group_concat(xxx, xxx order by
orderkey). It will failed during second parse of inline view
For example:
it works when doing
"SELECT id, group_concat(`name`, "," ORDER BY id) AS test_group_column
FROM test GROUP BY id"
but when create view it does not work
"create view test_view as SELECT id, group_concat(`name`, "," ORDER BY
id) AS test_group_column FROM test GROUP BY id"
Reason:
when creating view, we will doing parse again of view.toSql() to check
whether it has some syntax error. And when doing toSql() to group_concat with
order by, it add seperate ', ' between second parameter and order by. So when
parsing again, it
would failed because it is different semantic with original statement.
group_concat(`name`, "," ORDER BY id) ==> group_concat(`name`, "," ,
ORDER BY id)
Solved:
Change toSql of group_concat and add order by statement analyze() of
group_concat in Planner cause it would work if we get order by from view
statement and do not analyze and binding slot reference to it
---
.../java/org/apache/doris/analysis/FunctionCallExpr.java | 12 +++++++++++-
.../data/nereids_p0/group_concat/test_group_concat.out | 4 ++++
.../data/query_p0/group_concat/test_group_concat.out | 5 ++++-
.../suites/nereids_p0/group_concat/test_group_concat.groovy | 6 ++++++
.../suites/query_p0/group_concat/test_group_concat.groovy | 7 ++++++-
5 files changed, 31 insertions(+), 3 deletions(-)
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 f1ea909d6e..7101401bb3 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
@@ -547,7 +547,12 @@ public class FunctionCallExpr extends Expr {
for (int i = 0; i < len; ++i) {
if (i != 0) {
- sb.append(", ");
+ if (fnName.getFunction().equalsIgnoreCase("group_concat")
+ && orderByElements.size() > 0 && i == len -
orderByElements.size()) {
+ sb.append(" ");
+ } else {
+ sb.append(", ");
+ }
}
if (ConnectContext.get() != null &&
ConnectContext.get().getState().isQuery() && i == 1
&& (fnName.getFunction().equalsIgnoreCase("aes_decrypt")
@@ -1778,6 +1783,11 @@ public class FunctionCallExpr extends Expr {
}
// rewrite return type if is nested type function
analyzeNestedFunction();
+ for (OrderByElement o : orderByElements) {
+ if (!o.getExpr().isAnalyzed) {
+ o.getExpr().analyzeImpl(analyzer);
+ }
+ }
}
// if return type is nested type, need to be determined the sub-element
type
diff --git a/regression-test/data/nereids_p0/group_concat/test_group_concat.out
b/regression-test/data/nereids_p0/group_concat/test_group_concat.out
index 16467f6be7..2d97d50122 100644
--- a/regression-test/data/nereids_p0/group_concat/test_group_concat.out
+++ b/regression-test/data/nereids_p0/group_concat/test_group_concat.out
@@ -59,3 +59,7 @@ false
1 3,21,2,11,1
2 23,222,22,211,21
+-- !select_group_concat_order_by_desc4 --
+1 3,21,2,11,1
+2 23,222,22,211,21
+
diff --git a/regression-test/data/query_p0/group_concat/test_group_concat.out
b/regression-test/data/query_p0/group_concat/test_group_concat.out
index 7153ce1be3..d01900ef88 100644
--- a/regression-test/data/query_p0/group_concat/test_group_concat.out
+++ b/regression-test/data/query_p0/group_concat/test_group_concat.out
@@ -63,6 +63,9 @@ false
1 3,21,2,11,1
2 23,222,22,211,21
--- !select_group_concat_order_by --
+-- !select_group_concat_order_by1 --
+1,11,2,21,21,211,22,222,23,3 3,23,222,22,211,21,21,2,11,1
+
+-- !select_group_concat_order_by2 --
1,11,2,21,21,211,22,222,23,3 3,23,222,22,211,21,21,2,11,1
diff --git
a/regression-test/suites/nereids_p0/group_concat/test_group_concat.groovy
b/regression-test/suites/nereids_p0/group_concat/test_group_concat.groovy
index b9896f5cf8..a570ac3da1 100644
--- a/regression-test/suites/nereids_p0/group_concat/test_group_concat.groovy
+++ b/regression-test/suites/nereids_p0/group_concat/test_group_concat.groovy
@@ -113,4 +113,10 @@ suite("test_group_concat") {
qt_select_group_concat_order_by_desc3 """
SELECT b1, group_concat(cast(abs(b3) as varchar) order by
abs(b2) desc, b3 desc) FROM table_group_concat group by b1 order by b1
"""
+
+ sql """create view if not exists test_view as SELECT b1,
group_concat(cast(abs(b3) as varchar) order by abs(b2) desc, b3 desc) FROM
table_group_concat group by b1 order by b1;"""
+ qt_select_group_concat_order_by_desc4 """
+ select * from test_view;
+ """
+ sql """drop view if exists test_view"""
}
diff --git
a/regression-test/suites/query_p0/group_concat/test_group_concat.groovy
b/regression-test/suites/query_p0/group_concat/test_group_concat.groovy
index b37c28d01b..2247cc4e5f 100644
--- a/regression-test/suites/query_p0/group_concat/test_group_concat.groovy
+++ b/regression-test/suites/query_p0/group_concat/test_group_concat.groovy
@@ -119,8 +119,13 @@ suite("test_group_concat") {
qt_select_group_concat_order_by_desc3 """
SELECT b1, group_concat(cast(abs(b3) as varchar) order by
abs(b2) desc, b3 desc) FROM table_group_concat group by b1 order by b1
"""
- qt_select_group_concat_order_by """
+ qt_select_group_concat_order_by1 """
select group_concat(b3,',' order by b3
asc),group_concat(b3,',' order by b3 desc) from table_group_concat;
"""
+ sql """create view if not exists test_view as select group_concat(b3,','
order by b3 asc),group_concat(b3,',' order by b3 desc) from
table_group_concat;"""
+ qt_select_group_concat_order_by2 """
+ select * from test_view;
+ """
+ sql """drop view if exists test_view"""
}
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
