[GitHub] [doris] cambyzju commented on a diff in pull request #15745: [fix](having) revert 15143 and fix having clause with multi-conditions

[GitBox <gi...@apache.org>]

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: commits-unsubscribe@doris.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For additional commands, e-mail: commits-help@doris.apache.org