From 22.3.2.2 while using aggregate function in WITH clause, with rows inserted, Cannot find column % in source stream, there are only columns: []. (THERE_IS_NO_COLUMN)

[NickStepanov]

You have to provide the following information whenever possible.

When inserting rows into table and after that using WITH max(column) as columnAlias, getting an error:

Received exception from server (version 22.3.3):
Code: 8. DB::Exception: Received from localhost:9000. DB::Exception: Cannot find column columnAlias in source stream, there are only columns: []. (THERE_IS_NO_COLUMN)

Does it reproduce on recent release?

Same error observed on docker images of these versions:
docker pull clickhouse/clickhouse-server:22.3.3.44
docker pull clickhouse/clickhouse-server:22.3.2.2

How to reproduce

• Use 22.3.2.2 or 22.3.3.44 versions running in docker container:
  docker run --name test_ch -it --rm clickhouse/clickhouse-server:22.3.3.44
• Connect to container, use clickhouse-client
• Create table:

CREATE TABLE test
                (
                  `dt` DateTime,
                  `text` String
                )
                ENGINE = MergeTree
                ORDER BY dt

• Insert a single row into the table:

insert into test values ('2020-01-01', 'text')

• Queries to run that lead to unexpected result:

WITH max(dt) AS maxDt
SELECT maxDt
FROM test

Expected behavior

Should return maximum value for column dt, which in our case is:
┌───────────────maxDt─┐
│ 2020-01-01 00:00:00 │
└─────────────────────┘

Error message and/or stacktrace

Received exception from server (version 22.3.3):
Code: 8. DB::Exception: Received from localhost:9000. DB::Exception: Cannot find column maxDt in source stream, there are only columns: []. (THERE_IS_NO_COLUMN)

Additional context

Please note that it works fine if we don't use aggregate function like max:

WITH dt AS maxDt
SELECT maxDt
FROM test

[kitaisreal]

Will be fixed after #23194 will be merged. Currently aggregate declared in WITH part is not visible inside other SELECT query parts.

[NickStepanov]

@kitaisreal Do you know ETA on that? I am reading through #23194 but can't understand how they are related. Looks like a long-standing issue.

[alexey-milovidov]

@NickStepanov Analyzer will be available for evaluation in October and planned for production readiness in December.

[alexey-milovidov]

Update: analyzer has been available for evaluation since October 2022 and I confirm that it solved the issue:

$ clickhouse-local 
ClickHouse local version 23.3.1.2537.

milovidov-desktop :) CREATE TABLE test
                                     (
                                       `dt` DateTime,
                                       `text` String
                                     )
                                     ENGINE = MergeTree
                                     ORDER BY dt

CREATE TABLE test
(
    `dt` DateTime,
    `text` String
)
ENGINE = MergeTree
ORDER BY dt

Query id: 1c6698e9-8e1e-4176-90e6-da442d5d5be5

Ok.

0 rows in set. Elapsed: 0.021 sec. 

milovidov-desktop :) insert into test values ('2020-01-01', 'text')

INSERT INTO test FORMAT Values

Query id: d17d7dd3-5543-44f1-b37a-debb67031f96

Ok.

1 row in set. Elapsed: 0.002 sec. 

milovidov-desktop :) WITH max(dt) AS maxDt
                     SELECT maxDt
                     FROM test

WITH max(dt) AS maxDt
SELECT maxDt
FROM test

Query id: 9f39238d-5701-4a99-a44e-8d4110d62848


0 rows in set. Elapsed: 0.174 sec. 

Received exception:
Code: 10. DB::Exception: Not found column maxDt in block max(dt) DateTime UInt32(size = 0). (NOT_FOUND_COLUMN_IN_BLOCK)

milovidov-desktop :) SET allow_experimental_analyzer = 1

SET allow_experimental_analyzer = 1

Query id: 894c191a-46d2-4c24-b0f0-0294732e2454

Ok.

0 rows in set. Elapsed: 0.000 sec. 

milovidov-desktop :) WITH max(dt) AS maxDt
                     SELECT maxDt
                     FROM test

WITH max(dt) AS maxDt
SELECT maxDt
FROM test

Query id: 357ca845-6952-434c-8301-59d201c4e0d2

┌───────────────maxDt─┐
│ 2020-01-01 00:00:00 │
└─────────────────────┘

1 row in set. Elapsed: 0.008 sec. 

milovidov-desktop :)

But we don't expect production readiness before April.