ClickHouse Column xxx is not under aggregate function and not in GROUP BY keys

I had an instance of ClickHouse 24.1.1.2408 and recently founded another instance 24.3.2.23 for transferring, found difference behavior processing column alias of aggregation, I was wondering if i made any mistake or is there any feature changed?

Here is the sample table:

create table t (code varchar(10), val int) engine=Memory;
insert into t values ('a', 10);
insert into t values ('a', 20);
insert into t values ('a', 30);
insert into t values ('b', 11);

In version 24.1.1.2048, column name and aggregate result alias is recognized and the out matches expected:

SELECT code, sum(val) as val, val / count(1) as avg from t group by code;

Another style may causes aggregation re-caculating, so it gave me an error, this seems meanful.

SELECT code, sum(val) as val, sum(val) / count(1) as avg from t group by code;

>Code: 184. DB::Exception: Aggregate function sum(val) is found inside another aggregate function in query: While processing sum(val) AS val. (ILLEGAL_AGGREGATION) (version 24.1.1.2048 (official build))

In version 24.3.2.23, things have changed, it seems alias for aggregating function result not recognized, it gave me a error like this:

SELECT code, sum(val) as val, val / count(1) as avg from t group by code;

>Code: 215. DB::Exception: Column dt.t.val is not under aggregate function and not in GROUP BY keys. In query SELECT code, sum(val) AS val, val / count(1) AS avg FROM t GROUP BY code. (NOT_AN_AGGREGATE) (version 24.3.2.23 (official build))

Using 2nd style works well:

SELECT code, sum(val) as val, sum(val) / count(1) as avg from t group by code;

Tested in 24.6.1.4423 got same result with version 24.3.2.23, so what is the problem?

Setting prefer_column_name_to_alias defines what is preferable to use column name or alias. Probably the default value of this one is different for different versions of CH.

Use this query to check the default value of setting:

SELECT *
FROM system.settings
WHERE name = 'prefer_column_name_to_alias'
  
/*
┌─name────────────────────────┬─value─┬─changed─┬─description───────────────────────────────────────────────┬─min──┬─max──┬─readonly─┬─type─┬─default─┬─alias_for─┬─is_obsolete─┐
│ prefer_column_name_to_alias │ 0     │       0 │ Prefer using column names instead of aliases if possible. │ ᴺᵁᴸᴸ │ ᴺᵁᴸᴸ │        0 │ Bool │ 0       │           │           0 │
└─────────────────────────────┴───────┴─────────┴───────────────────────────────────────────────────────────┴──────┴──────┴──────────┴──────┴─────────┴───────────┴─────────────┘
*/

All your examples will work fine if define this setting according to the query:

/* CH v.24.3.4.147 */

SELECT
    code,
    sum(val) AS val,
    val / count(1) AS avg
FROM t
GROUP BY code
SETTINGS prefer_column_name_to_alias = 0

/*
   ┌─code─┬─val─┬─avg─┐
1. │ b    │  11 │  11 │
2. │ a    │  60 │  20 │
   └──────┴─────┴─────┘
*/


SELECT
    code,
    sum(val) AS val,
    val / count(1) AS avg
FROM t
GROUP BY code
SETTINGS prefer_column_name_to_alias = 1

/*
Received exception from server (version 24.3.4):
Code: 215. DB::Exception: Received from localhost:9000. DB::Exception: Column t.val is not under aggregate function and not in GROUP BY keys.
*/


SELECT
    code,
    sum(val) AS val,
    sum(val) / count(1) AS avg
FROM t
GROUP BY code
SETTINGS prefer_column_name_to_alias = 0

/*
Received exception from server (version 24.3.4):
Code: 184. DB::Exception: Received from localhost:9000. DB::Exception: Aggregate function sum(val) AS val is found inside another aggregate function in query. (ILLEGAL_AGGREGATION)
*/


SELECT
    code,
    sum(val) AS val,
    sum(val) / count(1) AS avg
FROM t
GROUP BY code
SETTINGS prefer_column_name_to_alias = 1

/*
   ┌─code─┬─val─┬─avg─┐
1. │ b    │  11 │  11 │
2. │ a    │  60 │  20 │
   └──────┴─────┴─────┘
*/