Long query run time when using SSB benchmark

[KinderRiven]

When using the SSB (scale 100) benchmark, the following query will cause deadlock. We have tried several versions of this problem. This may be a potential bug.

select D_YEARMONTHNUM, S_CITY, P_BRAND, sum(LO_REVENUE - LO_SUPPLYCOST) as profit
from date, customer, supplier, part, lineorder
where LO_CUSTKEY = C_CUSTKEY
  and LO_SUPPKEY = S_SUPPKEY
  and LO_PARTKEY = P_PARTKEY
  and LO_ORDERDATE = D_DATEKEY
  and S_NATION = 'UNITED KINGDOM'
  and P_CATEGORY = 'MFGR#21'
  and (LO_QUANTITY between 34 and 44)
  and (LO_ORDERDATE between toDate('1996-01-01') and toDate('1996-12-31'))
group by D_YEARMONTHNUM, S_CITY, P_BRAND
order by D_YEARMONTHNUM, S_CITY, P_BRAND;

We are currently locating the call stack with deadlock

2022.08.24 14:26:33.349320 [ 442655 ] {8e8a7787-fd0a-40b9-adb5-3cf963407900} <Information> IColumn: insertFrom - 0. StackTrace::StackTrace() @ 0xa932c4c in /mnt/ebs/ClickHouse/build/programs/clickhouse
1. DB::ColumnString::insertFrom(DB::IColumn const&, unsigned long) @ 0x14521069 in /mnt/ebs/ClickHouse/build/programs/clickhouse
2. DB::IColumn::insertManyFrom(DB::IColumn const&, unsigned long, unsigned long) @ 0x133d7ab3 in /mnt/ebs/ClickHouse/build/programs/clickhouse
3. DB::HashJoin::joinBlockImplCross(DB::Block&, std::__1::shared_ptr<DB::ExtraBlock>&) const @ 0x13cdeddc in /mnt/ebs/ClickHouse/build/programs/clickhouse
4. DB::HashJoin::joinBlock(DB::Block&, std::__1::shared_ptr<DB::ExtraBlock>&) @ 0x13ce0d29 in /mnt/ebs/ClickHouse/build/programs/clickhouse
5. DB::JoiningTransform::readExecute(DB::Chunk&) @ 0x14fda65d in /mnt/ebs/ClickHouse/build/programs/clickhouse
6. DB::JoiningTransform::transform(DB::Chunk&) @ 0x14fd9364 in /mnt/ebs/ClickHouse/build/programs/clickhouse
7. DB::JoiningTransform::work() @ 0x14fd8e0b in /mnt/ebs/ClickHouse/build/programs/clickhouse
8. DB::ExecutionThreadContext::executeTask() @ 0x14e48086 in /mnt/ebs/ClickHouse/build/programs/clickhouse
9. DB::PipelineExecutor::executeStepImpl(unsigned long, std::__1::atomic<bool>*) @ 0x14e3e8d0 in /mnt/ebs/ClickHouse/build/programs/clickhouse
10. void std::__1::__function::__policy_invoker<void ()>::__call_impl<std::__1::__function::__default_alloc_func<ThreadFromGlobalPool::ThreadFromGlobalPool<DB::PipelineExecutor::spawnThreads()::$_0>(DB::PipelineExecutor::spawnThreads()::$_0&&)::'lambda'(), void ()> >(std::__1::__function::__policy_storage const*) @ 0x14e3fb33 in /mnt/ebs/ClickHouse/build/programs/clickhouse
11. ThreadPoolImpl<std::__1::thread>::worker(std::__1::__list_iterator<std::__1::thread, void*>) @ 0xa9a80fb in /mnt/ebs/ClickHouse/build/programs/clickhouse
12. void* std::__1::__thread_proxy<std::__1::tuple<std::__1::unique_ptr<std::__1::__thread_struct, std::__1::default_delete<std::__1::__thread_struct> >, void ThreadPoolImpl<std::__1::thread>::scheduleImpl<void>(std::__1::function<void ()>, int, std::__1::optional<unsigned long>)::'lambda0'()> >(void*) @ 0xa9aa3ae in /mnt/ebs/ClickHouse/build/programs/clickhouse
13. ? @ 0x7fc9c7d9a609 in ?
14. clone @ 0x7fc9c7cbf133 in ?

[alexey-milovidov]

@KinderRiven according to the stack trace, the query is using CROSS JOIN, which takes a long time.

[KinderRiven]

It's not like a performance problem. The same SQL can be completed in a few seconds, and Clickhouse takes two hours (not yet finished)

[qoega]

BTW can you kill this query while it is in possible deadlock state? Sometimes deadlocks can occur inside executor task and if so this query can not be killed.