DB::Exception: Unsupported JOIN ON conditions

[beijishiqidu]

When I run the following SQL statement on the ClickHouse of the 22.8.11.15 version, the result is returned.

SELECT count(1) as num, hostName() as hostName from system.query_log as a inner join system.processes as b on a.query_id = b.query_id and type = 'QueryStart' and dateDiff('second', event_time, now()) > 5 and hostName ilike 'clickhouse'

SELECT
    count(1) AS num,
    hostName() AS hostName
FROM system.query_log AS a
INNER JOIN system.processes AS b ON (a.query_id = b.query_id) AND (type = 'QueryStart') AND (dateDiff('second', event_time, now()) > 5) AND (hostName ILIKE 'clickhouse')

Query id: 269ce0f3-37f9-479f-b1f7-83fc6fb32272

┌─num─┬─hostName─────┐
│   0 │ clickhouse-0 │
└─────┴──────────────┘

1 row in set. Elapsed: 0.082 sec. Processed 11.71 million rows, 367.91 MB (142.51 million rows/s., 4.48 GB/s.)

However, after the version of the ClickHouse is upgraded to 23.8.2.7, the SQL statement cannot be executed normally.

SELECT
    count(1) AS num,
    hostName() AS hostName
FROM system.query_log AS a
INNER JOIN system.processes AS b ON (a.query_id = b.query_id) AND (type = 'QueryStart') AND (dateDiff('second', event_time, now()) > 5) AND (hostName ILIKE 'clickhouse')

Query id: 57a613e1-d3bf-4321-8b63-43efd7ea571c


0 rows in set. Elapsed: 0.004 sec.

Received exception from server (version 23.8.2):
Code: 403. DB::Exception: Received from clickhouse-0.clickhouse-headless.kube-system.svc.cluster.local:26730. DB::Exception: Unsupported JOIN ON conditions. Unexpected '(hostName() AS hostName) ILIKE 'clickhouse'': While processing (hostName() AS hostName) ILIKE 'clickhouse'. (INVALID_JOIN_ON_EXPRESSION)

I've checked the changlog for changes in this area, but unfortunately, I can't find a corresponding description.

I see a similar question, #43814, does the latest version of ClickHouse no longer support such queries?

If that's true, that's terrible. Isn't there any parameter configuration to support history?

[beijishiqidu]

@alexey-milovidov hello, my friend, Does this really change in the new version? No longer compatible with history?

[alexey-milovidov]

I don't think it was intentional.

Nevertheless, it works after enabling

SET allow_experimental_analyzer = 1;

[nikitamikhaylov]

Confirming, it is perfectly working with analyzer: https://fiddle.clickhouse.com/e966a46d-7c87-46bf-ba74-70af8e181998