EXPLAIN SYNTAX produces query with ambiguous column

[UnamedRus]

How to reproduce
Clickhouse 21.4

EXPLAIN SYNTAX
WITH tbl AS
    (
        SELECT intDiv(number, 10) AS key_1
        FROM numbers(1000)
    )
SELECT count()
FROM tbl AS x
INNER JOIN tbl AS z ON x.key_1 = z.key_1
FORMAT TSVRaw

Query id: 5582fbcf-2134-4dc7-89e8-d37e9ae3b060

WITH tbl AS
    (
        SELECT intDiv(number, 10) AS key_1
        FROM numbers(1000)
    )
SELECT count()
FROM tbl AS x
ALL INNER JOIN tbl AS z ON key_1 = z.key_1


WITH tbl AS
    (
        SELECT intDiv(number, 10) AS key_1
        FROM numbers(1000)
    )
SELECT count()
FROM tbl AS x
ALL INNER JOIN tbl AS z ON key_1 = z.key_1

Query id: b09e46b8-bf78-4882-903a-04150bf85821


0 rows in set. Elapsed: 0.002 sec.

Received exception from server (version 21.4.1):
Code: 352. DB::Exception: Received from localhost:9000. DB::Exception: Column 'key_1' is ambiguous: While processing key_1 = z.key_1.

EXPLAIN SYNTAX
WITH tbl AS
    (
        SELECT intDiv(number, 10) AS key_1
        FROM numbers(1000)
    )
SELECT count()
FROM tbl AS x
CROSS JOIN tbl AS z
WHERE x.key_1 = z.key_1
FORMAT TSVRaw

Query id: 4e196c98-54f3-40d4-ae8c-11e5f0800031

WITH tbl AS
    (
        SELECT intDiv(number, 10) AS key_1
        FROM numbers(1000)
    )
SELECT count()
FROM tbl AS x
ALL INNER JOIN tbl AS z ON key_1 = z.key_1
WHERE key_1 = z.key_1

WITH tbl AS
    (
        SELECT intDiv(number, 10) AS key_1
        FROM numbers(1000)
    )
SELECT count()
FROM tbl AS x
ALL INNER JOIN tbl AS z ON key_1 = z.key_1
WHERE key_1 = z.key_1

Query id: 9b50bb1d-9b4e-4508-8401-2498caf2b051


0 rows in set. Elapsed: 0.004 sec.

Received exception from server (version 21.4.1):
Code: 352. DB::Exception: Received from localhost:9000. DB::Exception: Ambiguous column 'key_1': While processing WITH tbl AS (SELECT intDiv(number, 10) AS key_1 FROM numbers(1000)) SELECT count() FROM tbl AS x ALL INNER JOIN tbl AS z ON key_1 = z.key_1 WHERE key_1 = z.key_1.

[amosbird]

Could you try constructing a case with this PR #21414 ? It doesn't suffer from this issue by removing useless WITH elements...

[UnamedRus]

I can, but that issue not about WITH element's it's more about JOIN rewriting. it would reproduce even without with clause.

[amosbird]

I can, but that issue not about WITH element's it's more about JOIN rewriting. it would reproduce even without with clause.

Ok. I fail to reproduce with the above queries. Not sure why it's related to explain only.

[UnamedRus]

@amosbird

SELECT
    *,
    version()
FROM system.build_options
WHERE name = 'VERSION_GITHASH'

┌─name────────────┬─value────────────────────────────────────┬─version()───┐
│ VERSION_GITHASH │ 3b193363049590958e9c5b066f5f56ba392b51d7 │ 21.4.1.6210 │
└─────────────────┴──────────────────────────────────────────┴─────────────┘

EXPLAIN SYNTAX
WITH tbl AS
    (
        SELECT intDiv(number, 10) AS key_1
        FROM numbers(1000)
    )
SELECT count()
FROM tbl AS x
INNER JOIN tbl AS z ON x.key_1 = z.key_1
FORMAT TSVRaw

Query id: 13dab5df-14b1-4031-9091-1636190a14bc

SELECT count()
FROM tbl AS x
ALL INNER JOIN tbl AS z ON key_1 = z.key_1

I tried to run queries with that branch, but it completely removing WITH clause which is actually useful in that query :).

EXPLAIN SYNTAX
SELECT count()
FROM
(
    SELECT number AS key_1
    FROM numbers(15)
) AS x
INNER JOIN
(
    SELECT number AS key_1
    FROM numbers(10)
) AS z ON x.key_1 = z.key_1
FORMAT TSVRaw

Query id: 17ba789c-41d8-479e-91b3-5ae7decac7d9

SELECT count()
FROM
(
    SELECT number AS key_1
    FROM numbers(15)
) AS x
ALL INNER JOIN
(
    SELECT number AS key_1
    FROM numbers(10)
) AS z ON key_1 = z.key_1


SELECT count()
FROM
(
    SELECT number AS key_1
    FROM numbers(15)
) AS x
ALL INNER JOIN
(
    SELECT number AS key_1
    FROM numbers(10)
) AS z ON key_1 = z.key_1

Query id: 71d43dd3-b6fd-4737-8afc-e7839e023575


0 rows in set. Elapsed: 0.002 sec.

Received exception from server (version 21.4.1):
Code: 352. DB::Exception: Received from localhost:9000. DB::Exception: Column 'key_1' is ambiguous: While processing key_1 = z.key_1.

However problem is still here, clickhouse removing alias for left table in join, and query from explain syntax doesn't work.