xy720 opened a new issue, #15747: URL: https://github.com/apache/doris/issues/15747
### Search before asking - [X] I had searched in the [issues](https://github.com/apache/doris/issues?q=is%3Aissue) and found no similar issues. ### Description Reference to pr #14408 (issue #14372) and #14077 (issue #14043), Doris changed the behavior of group by and having clause to be consistent with MySql. The group by and having clause will use the column name first rather than alias name. However, users who have been used to the previous behavior react that they have to make a lot of sql changes for this behavior change. For example, with the following table: ``` mysql> desc table_car; +-------------+--------------+------+-------+---------+---------+ | Field | Type | Null | Key | Default | Extra | +-------------+--------------+------+-------+---------+---------+ | date | DATE | Yes | true | NULL | | | car_id | VARCHAR(64) | Yes | true | NULL | | | name | VARCHAR(200) | Yes | true | NULL | | +-------------+--------------+------+-------+---------+---------+ ``` The sql will report analysis error: ``` mysql> select CONCAT( car_id, ' ', name ) AS name, count(*) as cnt from table_car group by name; ERROR 1105 (HY000): errCode = 2, detailMessage = select list expression not produced by aggregation output (missing from GROUP BY clause?): concat(`car_id`, ' ', `name`) ``` After we add a switch to force using alias name in group by and having clause. It will become: ``` mysql> set group_by_and_having_use_alias_first = true; Query OK, 0 rows affected (0.00 sec) mysql> select CONCAT( car_id, ' ', name ) AS name, count(*) as cnt from table_car group by name; Empty set (0.03 sec) ``` ### Solution Add a switch for users to force using alias name in group by and having clause. ### Are you willing to submit PR? - [X] Yes I am willing to submit a PR! ### Code of Conduct - [X] I agree to follow this project's [Code of Conduct](https://www.apache.org/foundation/policies/conduct) -- 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]
