JOIN + GROUP BY doesn't respect timeouts or KILL requests #26554
Description
Describe the bug
Reduced query:
SELECT * FROM
(
SELECT a.name as n
FROM
(
SELECT 'aaaa' as name, number FROM system.numbers LIMIT 2000000
) AS a,
(
SELECT 'aaaa' as name, number FROM system.numbers LIMIT 2000000
) as b
GROUP BY n
)
LIMIT 20
FORMAT JSONIf you run this query it is not possible to KILL it or limit its timeout, only to cancel it via Ctrl+C in the client.
set max_execution_time = 1;
${QUERY}
Doesn't work, the process continues after 1 second.
kill query where query_id = '03721910-9db1-4bf0-91e2-b565f373b30b';
KILL QUERY WHERE query_id = '03721910-9db1-4bf0-91e2-b565f373b30b' ASYNC
Query id: 23fb949b-ff87-4382-bbb6-98f0b60b3eaa
┌─kill_status─┬─query_id─────────────────────────────┬─user────┬─query──────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┐
│ waiting │ 03721910-9db1-4bf0-91e2-b565f373b30b │ default │ SELECT * FROM
(
SELECT a.name as n
FROM
(
SELECT 'aaaa' as name, number FROM system.numbers LIMIT 2000000
) AS a,
(
SELECT 'aaaa' as name, number FROM system.numbers LIMIT 2000000
) as b
GROUP BY n
)
LIMIT 20
FORMAT JSON │
└─────────────┴──────────────────────────────────────┴─────────┴────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘
1 rows in set. Elapsed: 0.002 sec.
Doesn't stop either.
If you request a SYNC kill it doesn't stop either (I stopped the sync KILL request after ~40 seconds):
kill query where query_id = '03721910-9db1-4bf0-91e2-b565f373b30b' sync;
KILL QUERY WHERE query_id = '03721910-9db1-4bf0-91e2-b565f373b30b' SYNC
Query id: 61e87749-02bd-4833-a4e9-8e9311036adc
Cancelling query.
Ok.
Query was cancelled.
0 rows in set. Elapsed: 40.252 sec.
The only thing that does work is to cancel the request from the client. In this case, since I was using clickhouse-client, Ctrl+C stopped the process almost immediately:
Query id: 03721910-9db1-4bf0-91e2-b565f373b30b
Cancelling query.
Query was cancelled.
0 rows in set. Elapsed: 58.908 sec. Processed 2.10 million rows, 16.77 MB (35.58 thousand rows/s., 284.67 KB/s.)
Received exception from server (version 21.9.1):
Code: 394. DB::Exception: Received from localhost:9000. DB::Exception: Query was cancelled. (QUERY_WAS_CANCELLED)
Does it reproduce on recent release?
Yes, tested in master and 21.6.5.37.
Expected behavior
Since Ctrl+C works, I would expect KILL to also stop the query. Ideally max_execution_time should work in a similar way too.
Additional context
I'm planning on looking into it myself, but any pointers about why it doesn't currently work or how should it behave are appreciated.