
**How to reproduce**
clickhouse server 21.1.4.46

```
CREATE TABLE table_case (number UInt32) ENGINE=Memory();

 INSERT INTO table_case SELECT * FROM numbers(100000000);
 
  set max_threads=1;
 SELECT
    multiIf(
    (
        SELECT count(*)
        FROM table_case
        WHERE (number % 20) = 2
    ) > 4094370,
    (
        SELECT avg(number)
        FROM table_case
        WHERE (number % 20) = 2
    ),
    (
        SELECT avg(rand())
        FROM table_case
        WHERE (number % 20) = 2
    )) AS bucket1,
    multiIf(
    (
        SELECT count(*)
        FROM table_case
        WHERE (number % 20) = 7
    ) > 5000000,
    (
        SELECT avg(number)
        FROM table_case
        WHERE (number % 20) = 7
    ),
    (
        SELECT avg(rand())
        FROM table_case
        WHERE (number % 20) = 7
    )) AS bucket2,
    multiIf(
    (
        SELECT count(*)
        FROM table_case
        WHERE (number % 10) = 3
    ) > 13000000,
    (
        SELECT avg(number)
        FROM table_case
        WHERE (number % 10) = 3
    ),
    (
        SELECT avg(rand())
        FROM table_case
        WHERE (number % 10) = 3
    )) AS bucket3,
    multiIf(
    (
        SELECT count(*)
        FROM table_case
        WHERE (number % 10) = 3
    ) > 23000000,
    (
        SELECT avg(number)
        FROM table_case
        WHERE (number % 10) = 3
    ),
    (
        SELECT avg(rand())
        FROM table_case
        WHERE (number % 10) = 3
    )) AS bucket4,
    multiIf(
    (
        SELECT count(*)
        FROM table_case
        WHERE (number % 17) = 4
    ) > 15000000,
    (
        SELECT avg(number)
        FROM table_case
        WHERE (number % 17) = 4
    ),
    (
        SELECT avg(rand())
        FROM table_case
        WHERE (number % 17) = 4
    )) AS bucket5

Query id: c03edf92-53d1-48f6-b4f6-3153599d07ae

┌──bucket1─┬───────────bucket2─┬────────────bucket3─┬────────────bucket4─┬──────────bucket5─┐
│ 49999992 │ 2147267165.746676 │ 2147653473.0356455 │ 2147653473.0356455 │ 2146430096.56865 │
└──────────┴───────────────────┴────────────────────┴────────────────────┴──────────────────┘

1 rows in set. Elapsed: 7.707 sec.

set max_threads=16;


┌──bucket1─┬───────────bucket2─┬───────────bucket3─┬───────────bucket4─┬───────────bucket5─┐
│ 49999992 │ 2147628607.640714 │ 2146571628.422384 │ 2146571628.422384 │ 2147553185.879362 │
└──────────┴───────────────────┴───────────────────┴───────────────────┴───────────────────┘

1 rows in set. Elapsed: 1.165 sec.
 
```

clickhouse-server  21.2.3.15

```
set max_threads=1;

┌──bucket1─┬──────────bucket2─┬─────────bucket3─┬─────────bucket4─┬────────────bucket5─┐
│ 49999992 │ 2147530694.57038 │ 2148494441.6794 │ 2148494441.6794 │ 2146861224.9913669 │
└──────────┴──────────────────┴─────────────────┴─────────────────┴────────────────────┘

1 rows in set. Elapsed: 7.824 sec.

 set max_threads=16;
 
 ┌──bucket1─┬────────────bucket2─┬────────────bucket3─┬────────────bucket4─┬────────────bucket5─┐
│ 49999992 │ 2145953907.4246264 │ 2147932668.7410493 │ 2147932668.7410493 │ 2147690139.9780807 │
└──────────┴────────────────────┴────────────────────┴────────────────────┴────────────────────┘

1 rows in set. Elapsed: 7.726 sec.

```


{
    
    auto join_hash = join_element.getTreeHash();
    String join_subquery_id = toString(join_hash.first) + "_" + toString(join_hash.second);
    SubqueryForSet & subquery_for_join = subqueries_for_sets[join_subquery_id];
    
    if (!subquery_for_join.join)
        subquery_for_join.join = tryGetStorageJoin(syntax->analyzed_join);
    if (!subquery_for_join.join)
    {
        
        ExpressionActionsPtr joined_block_actions = createJoinedBlockActions(context, analyzedJoin());
        Names original_right_columns;
        if (!subquery_for_join.source)
        {
            NamesWithAliases required_columns_with_aliases = analyzedJoin().getRequiredColumns(
                joined_block_actions->getSampleBlock(), joined_block_actions->getRequiredColumns());
            for (auto & pr : required_columns_with_aliases)
                original_right_columns.push_back(pr.first);
            /** For GLOBAL JOINs (in the case, for example, of the push method for executing GLOBAL subqueries), the following occurs
                * - in the addExternalStorage function, the JOIN (SELECT ...) subquery is replaced with JOIN _data1,
                *   in the subquery_for_set object this subquery is exposed as source and the temporary table _data1 as the `table`.
                * - this function shows the expression JOIN _data1.
                */
            auto interpreter = interpretSubquery(join_element.table_expression, context, original_right_columns, query_options);
            subquery_for_join.makeSource(interpreter, std::move(required_columns_with_aliases));
        }