This is an automated email from the ASF dual-hosted git repository.
morningman pushed a commit to branch branch-1.2-lts
in repository https://gitbox.apache.org/repos/asf/doris.git
commit 26c8b663ddb343a7a7e3064b55494a657268ec24
Author: camby <104178...@qq.com>
AuthorDate: Thu Jan 5 10:15:15 2023 +0800
[fix](having-clause) having clause do not works correct with same alias
name (#15143)
---
.../apache/doris/analysis/ExprSubstitutionMap.java | 10 ++++++
.../java/org/apache/doris/analysis/SelectStmt.java | 38 ++++++++++++++++------
.../correctness_p0/test_group_having_alias.out | 10 ++++++
.../correctness_p0/test_group_having_alias.groovy | 25 +++++++++++++-
4 files changed, 72 insertions(+), 11 deletions(-)
diff --git
a/fe/fe-core/src/main/java/org/apache/doris/analysis/ExprSubstitutionMap.java
b/fe/fe-core/src/main/java/org/apache/doris/analysis/ExprSubstitutionMap.java
index b1806f3dbf..5f60d15e1d 100644
---
a/fe/fe-core/src/main/java/org/apache/doris/analysis/ExprSubstitutionMap.java
+++
b/fe/fe-core/src/main/java/org/apache/doris/analysis/ExprSubstitutionMap.java
@@ -103,6 +103,16 @@ public final class ExprSubstitutionMap {
return null;
}
+ public void removeByLhsExpr(Expr lhsExpr) {
+ for (int i = 0; i < lhs.size(); ++i) {
+ if (lhs.get(i).equals(lhsExpr)) {
+ lhs.remove(i);
+ rhs.remove(i);
+ break;
+ }
+ }
+ }
+
public void removeByRhsExpr(Expr rhsExpr) {
for (int i = 0; i < rhs.size(); ++i) {
if (rhs.get(i).equals(rhsExpr)) {
diff --git a/fe/fe-core/src/main/java/org/apache/doris/analysis/SelectStmt.java
b/fe/fe-core/src/main/java/org/apache/doris/analysis/SelectStmt.java
index 0950b3d839..eac2cd4c26 100644
--- a/fe/fe-core/src/main/java/org/apache/doris/analysis/SelectStmt.java
+++ b/fe/fe-core/src/main/java/org/apache/doris/analysis/SelectStmt.java
@@ -995,18 +995,36 @@ public class SelectStmt extends QueryStmt {
* (select min(k1) from table b where
a.key=b.k2);
* TODO: the a.key should be replaced by a.k1 instead of unknown
column 'key' in 'a'
*/
+
+ // according to mysql
+ // having clause should use column name inside group by clause,
prior to alias.
+ // case1: having clause use column name table.v1, because v1
inside group by clause
+ // select id, sum(v1) v1 from table group by id,v1
having(v1>1);
+ // case2: having clause use alias name v2, because v2 is not
inside group by clause
+ // select id, sum(v1) v1, sum(v2) v2 from table group by id,v1
having(v1>1 AND v2>1);
+ // case3: having clause use alias name v, because table do not
have column name v
+ // select id, floor(v1) v, sum(v2) v2 from table group by id,v
having(v>1 AND v2>1);
if (groupByClause != null) {
- // according to mysql
- // if there is a group by clause, the having clause should use
column name not alias
- // this is the same as group by clause
- try {
- // use col name from tableRefs first
- havingClauseAfterAnaylzed = havingClause.clone();
- havingClauseAfterAnaylzed.analyze(analyzer);
- } catch (AnalysisException ex) {
- // then consider alias name
- havingClauseAfterAnaylzed =
havingClause.substitute(aliasSMap, analyzer, false);
+ ExprSubstitutionMap excludeGroupByaliasSMap =
aliasSMap.clone();
+ // according to case2, maybe some having slots inside group by
clause, some do not
+ List<Expr> groupBySlots = Lists.newArrayList();
+ for (Expr expr : groupByClause.getGroupingExprs()) {
+ expr.collect(SlotRef.class, groupBySlots);
+ }
+ for (Expr expr : groupBySlots) {
+ if (excludeGroupByaliasSMap.get(expr) == null) {
+ continue;
+ }
+ try {
+ // try to use column name firstly
+ expr.clone().analyze(analyzer);
+ // analyze success means column name exist, do not use
alias name
+ excludeGroupByaliasSMap.removeByLhsExpr(expr);
+ } catch (AnalysisException ex) {
+ // according to case3, column name do not exist, keep
alias name inside alias map
+ }
}
+ havingClauseAfterAnaylzed =
havingClause.substitute(excludeGroupByaliasSMap, analyzer, false);
} else {
// according to mysql
// if there is no group by clause, the having clause should
use alias
diff --git a/regression-test/data/correctness_p0/test_group_having_alias.out
b/regression-test/data/correctness_p0/test_group_having_alias.out
index fa1e177399..8d3629b5f5 100644
--- a/regression-test/data/correctness_p0/test_group_having_alias.out
+++ b/regression-test/data/correctness_p0/test_group_having_alias.out
@@ -10,3 +10,13 @@
202245
202245
+-- !case1 --
+2 2
+2 3
+
+-- !case2 --
+2 3 3
+
+-- !case3 --
+2 1 3
+
diff --git
a/regression-test/suites/correctness_p0/test_group_having_alias.groovy
b/regression-test/suites/correctness_p0/test_group_having_alias.groovy
index 1ff8dbbad8..45350026e2 100644
--- a/regression-test/suites/correctness_p0/test_group_having_alias.groovy
+++ b/regression-test/suites/correctness_p0/test_group_having_alias.groovy
@@ -74,4 +74,27 @@
sql """
DROP TABLE IF EXISTS `tb_holiday`;
"""
- }
\ No newline at end of file
+
+ sql """ DROP TABLE IF EXISTS `test_having_alias_tb`; """
+ sql """
+ CREATE TABLE `test_having_alias_tb` (
+ `id` int(11) NULL,
+ `v1` bigint(20) NULL,
+ `v2` bigint(20) NULL
+ ) ENGINE=OLAP
+ DUPLICATE KEY(`id`)
+ COMMENT 'OLAP'
+ DISTRIBUTED BY HASH(`id`) BUCKETS 1
+ PROPERTIES (
+ "replication_allocation" = "tag.location.default: 1",
+ "in_memory" = "false",
+ "storage_format" = "V2",
+ "disable_auto_compaction" = "false"
+ );
+ """
+ sql """ INSERT INTO test_having_alias_tb values(1,1,1),(2,2,2),(2,3,3); """
+ qt_case1 """ SELECT id, sum(v1) v1 FROM test_having_alias_tb GROUP BY
id,v1 having(v1>1) ORDER BY id,v1; """
+ qt_case2 """ SELECT id, sum(v1) v1, sum(v2) v2 FROM test_having_alias_tb
GROUP BY id,v1 having(v1!=2 AND v2>1) ORDER BY id,v1; """
+ qt_case3 """ SELECT id, v1-2 as v, sum(v2) v2 FROM test_having_alias_tb
GROUP BY id,v having(v>0 AND v2>1) ORDER BY id,v; """
+ sql """ DROP TABLE IF EXISTS `test_having_alias_tb`; """
+ }
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscr...@doris.apache.org
For additional commands, e-mail: commits-h...@doris.apache.org
