Unexpected SELECT value8 from WITH query result

[bamoo456]

Hi I got a issue as below:

Describe the unexpected behaviour
The provided query should work without exceptions.

How to reproduce

• Which ClickHouse server version to use
  ClickHouse server version 22.11.2.30 (official build).

• Which interface to use, if matters
  ClickHouse client version 22.11.2.30 (official build).

• How to reproduce:

CREATE TABLE parent(
    a_id Int64,
    b_id Int64,
    c_id Int64,
    created_at Int64
)
ENGINE=MergeTree()
ORDER BY (a_id, b_id, c_id, created_at)

CREATE TABLE join_table_1(
    a_id Int64,
    b_id Int64
)
ENGINE=MergeTree()
ORDER BY (a_id, b_id)

CREATE TABLE join_table_2(
    c_id Int64,
    created_at Int64
)
ENGINE=MergeTree()
ORDER BY (c_id, created_at)


WITH with_table as (
    SELECT p.a_id, p.b_id, p.c_id FROM parent p
    LEFT JOIN join_table_1 jt1 ON jt1.a_id = p.a_id AND jt1.b_id = p.b_id
    LEFT JOIN join_table_2 jt2 ON jt2.c_id = p.c_id
    WHERE
        p.a_id = 0 AND (jt2.c_id = 0 OR p.created_at = 0)
)
SELECT a_id, b_id, COUNT(*) as f_count FROM with_table
GROUP BY a_id, b_id

Expected behavior
The above query should work without exceptions, but I got the following errors

Received exception from server (version 22.11.2):
Code: 47. DB::Exception: Received from localhost:9000. DB::Exception: Missing columns: 'b_id' 'a_id' while processing query: 'WITH with_table AS (SELECT p.a_id, p.b_id, p.c_id FROM parent AS p LEFT JOIN join_table_1 AS jt1 ON (jt1.a_id = p.a_id) AND (jt1.b_id = p.b_id) LEFT JOIN join_table_2 AS jt2 ON jt2.c_id = p.c_id WHERE (p.a_id = 0) AND ((jt2.c_id = 0) OR (p.created_at = 0))) SELECT a_id, b_id, count() AS f_count FROM with_table GROUP BY a_id, b_id', required columns: 'a_id' 'b_id' 'a_id' 'b_id'. (UNKNOWN_IDENTIFIER)

Additional context
If I switch to use the following query, it would be OK without issues...

WITH with_table as (
    SELECT p.a_id, p.b_id, p.c_id FROM parent p
    LEFT JOIN join_table_1 jt1 ON jt1.a_id = p.a_id AND jt1.b_id = p.b_id
    LEFT JOIN join_table_2 jt2 ON jt2.c_id = p.c_id
    WHERE
        p.a_id = 0 AND (jt2.c_id = 0 OR p.created_at = 0)
)
SELECT p.a_id, p.b_id, COUNT(*) as f_count FROM with_table
GROUP BY p.a_id, b_id

[den-crane]

@kitaisreal

https://fiddle.clickhouse.com/087dc0ef-e73f-46ac-936b-d05e19c1fe55

allow_experimental_analyzer=1 Aggregate function COUNT requires zero or one argument.