cambyzju commented on code in PR #15745:
URL: https://github.com/apache/doris/pull/15745#discussion_r1065296964
##########
fe/fe-core/src/main/java/org/apache/doris/analysis/SelectStmt.java:
##########
@@ -1022,35 +1022,37 @@ private void analyzeAggregation(Analyzer analyzer)
throws AnalysisException {
* 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);
+ /* according to mysql
(https://dev.mysql.com/doc/refman/8.0/en/select.html)
+ * "For GROUP BY or HAVING clauses, it searches the FROM clause
before searching in the
+ * select_expr values. (For GROUP BY and HAVING, this differs from
the pre-MySQL 5.0 behavior
+ * that used the same rules as for ORDER BY.)"
+ * case1: having clause use column name table.v1, because it
searches the FROM clause firstly
+ * select id, sum(v1) v1 from table group by id,v1
having(v1>1);
+ * case2: having clause used in aggregate functions, such as
sum(v2) here
+ * select id, sum(v1) v1, sum(v2) v2 from table group by id,v1
having(v1>1 AND sum(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);
+ * case4: having clause use alias name vsum, because table do not
have column name vsum
+ * select id, floor(v1) v, sum(v2) vsum from table group by
id,v having(v>1 AND vsum>1);
+ */
if (groupByClause != null) {
- 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) {
+ ExprSubstitutionMap excludeAliasSMap = aliasSMap.clone();
+ List<Expr> havingSlots = Lists.newArrayList();
+ havingClause.collect(SlotRef.class, havingSlots);
+ for (Expr expr : havingSlots) {
+ if (excludeAliasSMap.get(expr) == null) {
Review Comment:
This behavior is the same as before pr15143, because here we use column name
firstly, so it report an error.
1. Before 15143, we use column name firstly; (So this SQL failed)
2. After 15143, we use column name firstly for columns inside group by; for
other columns, we use alias name firstly;
(So the SQL success)
3. Now we revert 15143 to keep the logic simple: always use column name
firstly. (So the SQL failed)
If we change `having(v2>1)` to `having(sum(v2)>1)`, the SQL will success.
Later we will add a config to choose alias name firstly or column name
firstly: https://github.com/apache/doris/pull/15748
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: [email protected]
For queries about this service, please contact Infrastructure at:
[email protected]
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
