Window functions randomly returning null values when using unbounded following with a uuid partition #16617
Description
What happens?
When using a window function with rows between unbounded preceding and unbounded following window functions will sometimes return nulls instead of the aggregate response.
I have narrowed this issue down to these scenarios:
- The window is pertaining by the primary table and aggregating a joined table
- The id used in the join and in the partition is a uuid, strings work fine
- The window is set to unbounded following
- This works fine in 1.2.0 but not in 1.2.1
To Reproduce
If you run the following queries several times you will sometimes see the test_array with for id fb30cf47-6f6b-42ef-dec2-3f984479a2aa with 12 value in an array, and sometimes with a null value.
with table_1 AS (
SELECT
'fb30cf47-6f6b-42ef-dec2-3f984479a2aa'::uuid AS id,
unnest(generate_series(
'2024-04-01'::date,
'2025-03-01'::date,
interval '1 month'
)) AS date
UNION ALL BY NAME
SELECT
'7d1cc557-2d45-6900-a1ed-b2c64f5d9200'::uuid AS id,
unnest(generate_series(
'2024-02-01'::date,
'2025-01-01'::date,
interval '1 month'
)) AS date
), table_2 AS (
SELECT
'fb30cf47-6f6b-42ef-dec2-3f984479a2aa'::uuid AS id,
unnest(generate_series(
'2024-04-01'::date,
'2025-03-01'::date,
interval '1 month'
)) AS date,
1 AS value
UNION ALL BY NAME
SELECT
'7d1cc557-2d45-6900-a1ed-b2c64f5d9200'::uuid AS id,
unnest(generate_series(
'2022-12-01'::date,
'2023-12-01'::date,
interval '1 month'
)) AS date,
1 AS value
), output AS (
SELECT
table_1.id,
table_1.date,
-- Should show values for all periods (Does not work as expected, will sometimes return null)
array_agg(table_2.value) over (
PARTITION BY table_1.id
ORDER BY table_1.date ASC
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
) AS test_array,
-- Should show values for previous periods (Works as expected)
array_agg(table_2.value) over (
PARTITION BY table_1.id
ORDER BY table_1.date ASC
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
) AS test_array_2,
FROM table_1
LEFT JOIN table_2
ON table_1.id = table_2.id
AND table_1.date = table_2.date
)
SELECT * FROM output
ORDER BY id DESC, date DESC;
OS:
arm64
DuckDB Version:
1.2.1
DuckDB Client:
python and jdbc
Hardware:
No response
Full Name:
Thomas Boles
Affiliation:
Finqore
What is the latest build you tested with? If possible, we recommend testing with the latest nightly build.
I have not tested with any build
Did you include all relevant data sets for reproducing the issue?
Yes
Did you include all code required to reproduce the issue?
- Yes, I have
Did you include all relevant configuration (e.g., CPU architecture, Python version, Linux distribution) to reproduce the issue?
- Yes, I have