bug: spill wrong result and hang

[Dousir9]

Summary

How to reproduce

set join_spilling_memory_ratio = 1;
set join_spilling_bytes_threshold_per_proc = 1;
set join_spilling_buffer_threshold_per_proc_mb = 1;
set sort_spilling_memory_ratio = 1;
set sort_spilling_bytes_threshold_per_proc = 1;
set aggregate_spilling_memory_ratio = 1;
set aggregate_spilling_bytes_threshold_per_proc = 1;
set window_partition_spilling_memory_ratio = 1;
set window_partition_spilling_bytes_threshold_per_proc = 1;

Wrong result

1. Load ci TPC-DS SF1.
2. Execute TPC-DS Q39.

Hang

1. Start the Databend cluster.
2. Comment out lines 9 and 10 in ci-run-sqllogic-tests.sh.
3. Create a file named spill.test in the tests/sqllogictests/suites/tpch directory.
4. ./scripts/ci/ci-run-sqllogic-tests.sh tpch

spill.test

# TPC-H spill test
statement ok
set sandbox_tenant = 'test_tenant';

statement ok
use tpch_test;

statement ok
set join_spilling_memory_ratio = 1;

statement ok
set join_spilling_bytes_threshold_per_proc = 1;

statement ok
set join_spilling_buffer_threshold_per_proc_mb = 1;

statement ok
set sort_spilling_memory_ratio = 1;

statement ok
set sort_spilling_bytes_threshold_per_proc = 1;

statement ok
set aggregate_spilling_memory_ratio = 1;

statement ok
set aggregate_spilling_bytes_threshold_per_proc = 1;

statement ok
set window_partition_spilling_memory_ratio = 1;

statement ok
set window_partition_spilling_bytes_threshold_per_proc = 1;

# TPC-H TEST
include ./queries.test

statement ok
set join_spilling_memory_ratio = 60;

statement ok
set join_spilling_bytes_threshold_per_proc = 0;

statement ok
set join_spilling_buffer_threshold_per_proc_mb = 512;

statement ok
set sort_spilling_memory_ratio = 60;

statement ok
set sort_spilling_bytes_threshold_per_proc = 0;

statement ok
set aggregate_spilling_memory_ratio = 60;

statement ok
set aggregate_spilling_bytes_threshold_per_proc = 0;

statement ok
set window_partition_spilling_memory_ratio = 60;

statement ok
set window_partition_spilling_bytes_threshold_per_proc = 0;