AND (cond OR cond) prevent CROSS JOIN to INNER JOIN rewrite #19856
Description
Describe the bug
It's possible to rewrite CROSS JOIN TO INNER JOIN in certain conditions, but clickhouse doesn't do that when we have AND (cond OR cond) in WHERE clause.
Does it reproduce on recent release?
Yes
How to reproduce
Clickhouse version 21.2
EXPLAIN SYNTAX
WITH tbl AS
(
SELECT *
FROM numbers(1000)
)
SELECT count(x.number)
FROM tbl AS x
, tbl AS z
WHERE x.number = z.number
Query id: ea50c6a4-618d-4f87-a089-01ef2ba956fe
┌─explain──────────────────────────────────────┐
│ WITH tbl AS │
│ ( │
│ SELECT * │
│ FROM numbers(1000) │
│ ) │
│ SELECT count(number) │
│ FROM tbl AS x │
│ ALL INNER JOIN tbl AS z ON number = z.number │
│ WHERE number = z.number │
└──────────────────────────────────────────────┘
EXPLAIN SYNTAX
WITH tbl AS
(
SELECT *
FROM numbers(1000)
)
SELECT count(x.number)
FROM tbl AS x
, tbl AS z
WHERE (x.number = z.number) AND ((x.number = 1) OR (x.number = 2))
Query id: 4f3f6e5a-f7fc-41c0-a2f2-675525e5d63e
┌─explain──────────────────────────────────────────────────────┐
│ WITH tbl AS │
│ ( │
│ SELECT * │
│ FROM numbers(1000) │
│ ) │
│ SELECT count(number) │
│ FROM tbl AS x │
│ CROSS JOIN tbl AS z │
│ WHERE (number = z.number) AND ((number = 1) OR (number = 2)) │
└──────────────────────────────────────────────────────────────┘
EXPLAIN SYNTAX
WITH tbl AS
(
SELECT *
FROM numbers(1000)
)
SELECT count(x.number)
FROM tbl AS x
, tbl AS z
WHERE (x.number = z.number) AND 1
Query id: 3ba0c863-8432-441a-b04c-e06047436cbc
┌─explain─────────────────────────┐
│ WITH tbl AS │
│ ( │
│ SELECT * │
│ FROM numbers(1000) │
│ ) │
│ SELECT count(number) │
│ FROM tbl AS x │
│ CROSS JOIN tbl AS z │
│ WHERE (number = z.number) AND 1 │
└─────────────────────────────────┘