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
The following commit(s) were added to refs/heads/branch-1.2-lts by this push:
new 1f716ec753 [cherry-pick](grouping) Add a switch for users to force
using alias name in group by and having clause (#16176)
1f716ec753 is described below
commit 1f716ec7532588166e8076cc674fafba15d41a5d
Author: xy720 <[email protected]>
AuthorDate: Tue Jan 31 23:46:51 2023 +0800
[cherry-pick](grouping) Add a switch for users to force using alias name in
group by and having clause (#16176)
cherry-pic #15748 to 1.2-lts
---
docs/en/docs/advanced/variables.md | 3 ++
docs/zh-CN/docs/advanced/variables.md | 5 ++-
.../java/org/apache/doris/analysis/SelectStmt.java | 51 ++++++++++++++--------
.../java/org/apache/doris/qe/SessionVariable.java | 11 +++++
.../correctness_p0/test_group_having_alias.out | 10 +++++
.../correctness_p0/test_group_having_alias.groovy | 35 +++++++++++++++
6 files changed, 97 insertions(+), 18 deletions(-)
diff --git a/docs/en/docs/advanced/variables.md
b/docs/en/docs/advanced/variables.md
index adbaddc51e..0da3bb5ede 100644
--- a/docs/en/docs/advanced/variables.md
+++ b/docs/en/docs/advanced/variables.md
@@ -558,3 +558,6 @@ Translated with www.DeepL.com/Translator (free version)
For compatible purpose. This variable has no effect, just enable some BI
tools can query or set this session variable sucessfully.
+* `group_by_and_having_use_alias_first`
+
+ Specifies whether group by and having clauses use column aliases rather
than searching for column name in From clause. The default value is false.
diff --git a/docs/zh-CN/docs/advanced/variables.md
b/docs/zh-CN/docs/advanced/variables.md
index 2ca025f825..19e585eae7 100644
--- a/docs/zh-CN/docs/advanced/variables.md
+++ b/docs/zh-CN/docs/advanced/variables.md
@@ -545,4 +545,7 @@ SELECT /*+ SET_VAR(query_timeout = 1,
enable_partition_cache=true) */ sleep(3);
* `group_concat_max_len`
为了兼容某些BI工具能正确获取和设置该变量，变量值实际并没有作用。
-
+
+* `group_by_and_having_use_alias_first`
+
+ 指定group by和having语句是否优先使用列的别名，而非从From语句里寻找列的名字。默认为false。
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 2548136966..1aae7c324f 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
@@ -1006,23 +1006,32 @@ public class SelectStmt extends QueryStmt {
* select id, floor(v1) v, sum(v2) vsum from table group by
id,v having(v>1 AND vsum>1);
*/
if (groupByClause != null) {
- ExprSubstitutionMap excludeAliasSMap = aliasSMap.clone();
- List<Expr> havingSlots = Lists.newArrayList();
- havingClause.collect(SlotRef.class, havingSlots);
- for (Expr expr : havingSlots) {
- if (excludeAliasSMap.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
- excludeAliasSMap.removeByLhsExpr(expr);
- } catch (AnalysisException ex) {
- // according to case3, column name do not exist, keep
alias name inside alias map
+ boolean aliasFirst = false;
+ if (analyzer.getContext() != null) {
+ aliasFirst =
analyzer.getContext().getSessionVariable().isGroupByAndHavingUseAliasFirst();
+ }
+ if (!aliasFirst) {
+ ExprSubstitutionMap excludeAliasSMap = aliasSMap.clone();
+ List<Expr> havingSlots = Lists.newArrayList();
+ havingClause.collect(SlotRef.class, havingSlots);
+ for (Expr expr : havingSlots) {
+ if (excludeAliasSMap.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
+ excludeAliasSMap.removeByLhsExpr(expr);
+ } catch (AnalysisException ex) {
+ // according to case3, column name do not exist,
keep alias name inside alias map
+ }
}
+ havingClauseAfterAnaylzed =
havingClause.substitute(excludeAliasSMap, analyzer, false);
+ } else {
+ // If user set force using alias, then having clauses
prefer using alias rather than column name
+ havingClauseAfterAnaylzed =
havingClause.substitute(aliasSMap, analyzer, false);
}
- havingClauseAfterAnaylzed =
havingClause.substitute(excludeAliasSMap, analyzer, false);
} else {
// according to mysql
// if there is no group by clause, the having clause should
use alias
@@ -1139,7 +1148,11 @@ public class SelectStmt extends QueryStmt {
groupingInfo.buildRepeat(groupingExprs,
groupByClause.getGroupingSetList());
}
- substituteOrdinalsAliases(groupingExprs, "GROUP BY", analyzer,
false);
+ boolean aliasFirst = false;
+ if (analyzer.getContext() != null) {
+ aliasFirst =
analyzer.getContext().getSessionVariable().isGroupByAndHavingUseAliasFirst();
+ }
+ substituteOrdinalsAliases(groupingExprs, "GROUP BY", analyzer,
aliasFirst);
if (!groupByClause.isGroupByExtension() &&
!groupingExprs.isEmpty()) {
ArrayList<Expr> tempExprs = new ArrayList<>(groupingExprs);
@@ -2090,7 +2103,11 @@ public class SelectStmt extends QueryStmt {
}
// substitute group by
if (groupByClause != null) {
- substituteOrdinalsAliases(groupByClause.getGroupingExprs(), "GROUP
BY", analyzer, false);
+ boolean aliasFirst = false;
+ if (analyzer.getContext() != null) {
+ aliasFirst =
analyzer.getContext().getSessionVariable().isGroupByAndHavingUseAliasFirst();
+ }
+ substituteOrdinalsAliases(groupByClause.getGroupingExprs(), "GROUP
BY", analyzer, aliasFirst);
}
// substitute having
if (havingClause != null) {
diff --git a/fe/fe-core/src/main/java/org/apache/doris/qe/SessionVariable.java
b/fe/fe-core/src/main/java/org/apache/doris/qe/SessionVariable.java
index 74c5412705..bfaefd8ac5 100644
--- a/fe/fe-core/src/main/java/org/apache/doris/qe/SessionVariable.java
+++ b/fe/fe-core/src/main/java/org/apache/doris/qe/SessionVariable.java
@@ -248,6 +248,8 @@ public class SessionVariable implements Serializable,
Writable {
public static final String GROUP_CONCAT_MAX_LEN = "group_concat_max_len";
+ public static final String GROUP_BY_AND_HAVING_USE_ALIAS_FIRST =
"group_by_and_having_use_alias_first";
+
// session origin value
public Map<Field, String> sessionOriginValue = new HashMap<Field,
String>();
// check stmt is or not [select /*+ SET_VAR(...)*/ ...]
@@ -651,6 +653,11 @@ public class SessionVariable implements Serializable,
Writable {
@VariableMgr.VarAttr(name = GROUP_CONCAT_MAX_LEN)
public long groupConcatMaxLen = 2147483646;
+ // Default value is false, which means the group by and having clause
+ // should first use column name not alias. According to mysql.
+ @VariableMgr.VarAttr(name = GROUP_BY_AND_HAVING_USE_ALIAS_FIRST)
+ public boolean groupByAndHavingUseAliasFirst = false;
+
// If this fe is in fuzzy mode, then will use initFuzzyModeVariables to
generate some variables,
// not the default value set in the code.
public void initFuzzyModeVariables() {
@@ -1163,6 +1170,10 @@ public class SessionVariable implements Serializable,
Writable {
return partitionPruneAlgorithmVersion;
}
+ public boolean isGroupByAndHavingUseAliasFirst() {
+ return groupByAndHavingUseAliasFirst;
+ }
+
public int getCpuResourceLimit() {
return cpuResourceLimit;
}
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 7a87076e28..1c4feeeecb 100644
--- a/regression-test/data/correctness_p0/test_group_having_alias.out
+++ b/regression-test/data/correctness_p0/test_group_having_alias.out
@@ -10,6 +10,16 @@
202245
202245
+-- !sql --
+
+-- !sql --
+202245 3
+
+-- !sql --
+202245
+202245
+202245
+
-- !case1 --
2 2
2 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 80187b9113..8429be8920 100644
--- a/regression-test/suites/correctness_p0/test_group_having_alias.groovy
+++ b/regression-test/suites/correctness_p0/test_group_having_alias.groovy
@@ -71,6 +71,41 @@
ORDER BY date;
"""
+ sql """set group_by_and_having_use_alias_first=true"""
+
+ qt_sql """
+ SELECT
+ date_format(date, '%x%v') AS `date`,
+ count(date) AS `diff_days`
+ FROM `tb_holiday`
+ WHERE `date` between 20221111 AND 20221116
+ GROUP BY date
+ HAVING date = 20221111
+ ORDER BY date;
+ """
+
+ qt_sql """
+ SELECT
+ date_format(date, '%x%v') AS `date2`,
+ count(date) AS `diff_days`
+ FROM `tb_holiday`
+ WHERE `date` between 20221111 AND 20221116
+ GROUP BY date2
+ HAVING date2 = 202245
+ ORDER BY date2;
+ """
+
+ qt_sql """
+ SELECT
+ date_format(date, '%x%v') AS `date`
+ FROM `tb_holiday`
+ WHERE `date` between 20221111 AND 20221116
+ HAVING date = 202245
+ ORDER BY date;
+ """
+
+ sql """set group_by_and_having_use_alias_first=false"""
+
sql """
DROP TABLE IF EXISTS `tb_holiday`;
"""
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
