Indentical subqueries (or CTE) execute only once. (condition pushdown) #21992
Description
After #2301 fix, clickhouse execute sub queries from single level of query only once. But it doesn't work in case we are using that sub query in WHERE condition and that conditions is being pushed to the inner query.
Use case
WITH x AS
(
SELECT *
FROM numbers(10000)
WHERE NOT sleep(1)
)
SELECT count()
FROM
(
SELECT *
FROM numbers(100)
)
WHERE number IN (x)
Query id: 6b8ed07a-1513-49f1-9f5e-ef3c32375fda
┌─count()─┐
│ 100 │
└─────────┘
1 rows in set. Elapsed: 2.009 sec. Processed 20.10 thousand rows, 160.80 KB (10.00 thousand rows/s., 80.04 KB/s.)
SELECT count()
FROM
(
SELECT *
FROM numbers(100)
)
WHERE number IN
(
SELECT *
FROM numbers(10000)
WHERE NOT sleep(1)
)
Query id: 6d1f8c64-118f-4a44-a0c3-30c9bf31bca3
┌─count()─┐
│ 100 │
└─────────┘
1 rows in set. Elapsed: 2.005 sec. Processed 20.10 thousand rows, 160.80 KB (10.02 thousand rows/s., 80.19 KB/s.)
WITH
(
SELECT groupArray(number)
FROM numbers(10000)
WHERE NOT sleep(1)
) AS x
SELECT count()
FROM
(
SELECT *
FROM numbers(100)
)
WHERE number IN
(
SELECT arrayJoin(x)
)
Query id: 4bc49312-9e82-4dca-883d-316734c76ba2
┌─count()─┐
│ 100 │
└─────────┘
1 rows in set. Elapsed: 1.004 sec.
Describe the solution you'd like
Clickhouse wouldn't push that kind of conditions or would execute them only once.
Describe alternatives you've considered
Disable predicate optimization by hand:
set set enable_optimize_predicate_expression =0;