[Enhancement] Add a switch for users to force using alias name in group by and having clause

[xy720]

Search before asking

• I had searched in the issues 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?

• Yes I am willing to submit a PR!

Code of Conduct

• I agree to follow this project's Code of Conduct