Recently, I came into contact with clickhouse. I have to say that the performance is really powerful. Even the simplest use without any optimization is much faster than a conventional database. Therefore, it has always been used as an ordinary database and supports sql statements. It is easy to get started. However, the case when statement is used when writing requirements today, which leads to some usages of clickhouse field alias.
First, the alias refers to the use of select column a as column B.
(all the conclusions are based on your own exploration and no relevant information is found. If there are any errors, please correct them.)

Biggest difference: the alias of the previous field can be used directly in the following fields

This is the most unaccustomed problem when contacting ck, because the database contacted before has not used this usage, so many errors are encountered when using ck. give an example:
mysql database

mysql> select 1 as a ,a +1 as b;
ERROR 1054 (42S22): Unknown column 'a' in 'field list'

A simple query, but an error will be reported, that is, the alias of the previous field cannot be recognized next.
clickhouse:

SELECT
    1 AS a,
    a + 1 AS b

┌─a─┬─b─┐
│ 1 │ 2 │
└───┴───┘

1 rows in set. Elapsed: 0.004 sec.

But in ck, it can be recognized!!! However, it will also cause the following fields to be forcibly replaced. This means that if the name of a field in the table is "num", after sum, the same alias is taken, that is, sum(num) as num
The following fields can't recognize the original num. it's a little wordy. Test it directly:
Build test table:

CREATE TABLE test123
ENGINE = TinyLog() AS
SELECT
    'a' AS name,
    1 AS num
UNION ALL
SELECT
    'a' AS name,
    2 AS num
UNION ALL
SELECT
    'a' AS name,
    3 AS num
UNION ALL
SELECT
    'b' AS name,
    4 AS num

:) select * from test123 ;

┌─name─┬─num─┐
│ a    │   3 │
│ b    │   4 │
│ a    │   1 │
│ a    │   2 │
└──────┴─────┘

4 rows in set. Elapsed: 0.004 sec.

Test statement:

SELECT
    sum(num) AS num,
    sum(num + 1) AS num_1
FROM test123;

Received exception from server (version 20.12.3):
Code: 184. DB::Exception: Received from 10.***.***.***:9000. DB::Exception: Aggregate function sum(num) is found inside another aggregate function in query: While processing sum(num) AS num.

0 rows in set. Elapsed: 0.004 sec.

It can be seen that the error Aggregate function sum(num) is found inside another aggregate will be reported in the ck. However, the traditional database supports this writing method and did not know this feature at the beginning,
I thought it was a problem with aggregate functions. I read the documentation of aggregate functions for a long time.
But once you find this feature, you will find it very practical! For example, the calculated results can be directly used when calculating the proportion:

And although it is different from the traditional database, the way to avoid it is also very simple, as long as the alias is not the same as the original field name.

SELECT
    sum(num) AS num1,
    sum(num + 1) AS num_1
FROM test123

┌─num1─┬─num_1─┐
│   10 │    14 │
└──────┴───────┘

1 rows in set. Elapsed: 0.004 sec.

However, most of the time, for the sake of beauty (laziness), I don't want to take a new name. Most of the time, it doesn't have any impact. However, I have encountered a problem today, that is, the identification of this alias is not only used in select statements, but also

It also acts in the where statement!

SELECT if(num > 1, 'Greater than 1', 'Less than 1') AS num
FROM test123
WHERE num > 1

Received exception from server (version 20.12.3):
Code: 386. DB::Exception: Received from 10.***.***.***:9000. DB::Exception: There is no supertype for types String, UInt8 because some of them are String/FixedString and some of them are not: while executing 'FUNCTION greater(if(greater(num, 1), 'Greater than 1', 'Less than 1') : 5, 1 : 1) -> greater(if(greater(num, 1), 'Greater than 1', 'Less than 1'), 1) UInt8 : 6'.

0 rows in set. Elapsed: 0.004 sec.

There is no supertype for types String, UInt8 because some of them are String/FixedString and some of them are not: while executing (of course, the wrong sentence is more complex than this, involving the time judgment of case when. Even when I wrote this blog, I thought it was the problem of case when...)
This is equivalent to directly replacing the usage of having in the traditional database. Therefore, if you also encounter the above errors, you can pay attention to the problem of alias. Is there a similar usage.