[SQL] Confusion Encountered When Using arrayJoin Function and GROUP BY Clause in ClickHouse

the first sql

SELECT rtx
, arrayJoin(['Istanbul', 'Berlin', 'Bobruisk']) AS city
FROM (
SELECT 'hi' as rtx
)
where city = 'Istanbul'
group by rtx, city;


the result is

rtx city
hi Istanbul

the next sql

SELECT rtx
, arrayJoin(['Istanbul', 'Berlin', 'Bobruisk']) AS city
FROM (
SELECT 'hi' as rtx
)
where city = 'Istanbul'
group by rtx;


the result is

| rtx | city |
| -------- | -------- |
| hi | Istanbul |
| hi | Berlin |
| hi | Bobruisk |


I don't know why the result of second sql is so confusing. It's just like arrayJoin function is executed twice, before group by and after group by.

when the column that arrayJoin func generates is not in the group by clause, what is the sql execution order

Continue reading...