Window function range between 1 following and 1 following returns a result when it should not #16098
Description
What happens?
The window function:
first (year_total) over (
partition by
sale_customer__id
order by
sale_date__year range between 1 following
and 1 following
)
returns values for some records when it should return NULL.
Further, running the query repeatedly returns different results each time.
To Reproduce
Load the attached file.
Run the query:
select * from
(
select
sale_customer__id,
year_total,
next_year,
(
first (year_total) over (
partition by
sale_customer__id
order by
sale_date__year range between 1 following
and 1 following
)
) as recompute_next_year,
sale_date__year
from
without_where_small
)
where next_year is distinct from recompute_next_year;
In DuckDB 1.1.3 this always returns no records.
In DuckDB 1.2.0 this returns a various set of records.
You can recreate the without_where_small table if necessary with the following query:
create table without_where_small as
(
select
sale_customer__id,
year_total,
(
first (year_total) over (
partition by
sale_customer__id
order by
sale_date__year range between 1 following
and 1 following
)
) as next_year,
sale_date__year
from
year_total_small
);
OS:
Windows 11 x86_64
DuckDB Version:
1.2.0
DuckDB Client:
CLI
Hardware:
No response
Full Name:
Dan Cory
Affiliation:
self
What is the latest build you tested with? If possible, we recommend testing with the latest nightly build.
I have tested with a stable release
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