Clickhouse 24.10.1 INVALID_JOIN_ON_EXPRESSION: Cannot determine join keys in ALL INNER JOIN

[x0st]

Describe the issue I recently upgraded to 24.10.1 from 23.11.3 and some of my queries stopped working.

How to reproduce

CREATE TABLE address
(
    `email_address` String,
    `domain` LowCardinality(String),
    `first_name` String,
    `last_name` String,
    `country` LowCardinality(String),
    `esp` UInt8,
    `list_import_sid` LowCardinality(String)
)
ENGINE = MergeTree
PRIMARY KEY (list_import_sid, country, esp, domain, email_address)
ORDER BY (list_import_sid, country, esp, domain, email_address)
SETTINGS index_granularity = 8192;

CREATE TABLE fact_click
(
    `sid` String,
    `campaign_sid` LowCardinality(String),
    `campaign_batch_sid` LowCardinality(String)
)
ENGINE = ReplacingMergeTree
ORDER BY (campaign_sid, campaign_batch_sid, sid)
SETTINGS index_granularity = 8192;

WITH
    records AS (SELECT address.email_address FROM address GROUP BY address.email_address),
    stats AS (
        SELECT (SELECT COUNT(*) FROM fact_click INNER JOIN records ON records.email_address = fact_click.email_address) AS num_clicks,
               (SELECT COUNT(*) FROM records) AS num_records
    )
SELECT * FROM stats;

Error message and/or stacktrace

Query id: be3c1328-9610-4d95-a02d-c892462349ea

Elapsed: 0.004 sec.

Received exception from server (version 24.10.1):
Code: 403. DB::Exception: Received from localhost:9000. DB::Exception: Cannot determine join keys in  ALL INNER JOIN ... ON email_address = postman.fact_click.email_address. (INVALID_JOIN_ON_EXPRESSION)

Additional context

• It works fine in 23.11.3
• The attached query is not static, it can be much bigger since it's constructed programmatically. So, I kinda need it to stay in the same shape...

[den-crane]

it works with a disabled analyzer set enable_analyzer=0;

https://fiddle.clickhouse.com/fd5fd116-0e6e-4d8c-8619-6ae3eff3ade4

try

cat /etc/clickhouse-server/users.d/enable_analyzer.xml
<?xml version="1.0" ?>
<clickhouse>
    <profiles>
        <default>
            <enable_analyzer>0</enable_analyzer>
        </default>
    </profiles>
</clickhouse>

[x0st]

@den-crane True, it works. So, do you know if it's a bug or not really? Is this the right page https://clickhouse.com/docs/en/operations/analyzer to read about analyzer? Or is the best way to read the actual code? Out of curiosity, I would like to understand why it's behaving like that.

[den-crane]

It's just a bug in the new analyzer in alias resolution. There are plenty of them https://github.com/ClickHouse/ClickHouse/issues?q=is%3Aopen+is%3Aissue+label%3Aanalyzer

And your query works if you use a table alias https://fiddle.clickhouse.com/9efdaaf2-d47b-4c67-98bb-e121fc10ee16

some info about the new analyzer https://youtu.be/FGhdXXXTuTg?t=2523

[x0st]

Understood, thank you for quick help!