`LEAD` advances a sequence too many times

What happens?

A legal LEAD(nextval(...)) over 5000 rows returns sequence values as high as 9096 and advances the sequence to 9097 instead of advancing it to 5001.

To Reproduce

Code

SET threads=1;
SET enable_optimizer=true;
DROP SEQUENCE IF EXISTS wopt004_seq;
CREATE SEQUENCE wopt004_seq START 1;

SELECT
  min(x) AS minimum,
  max(x) AS maximum,
  sum(x) AS total,
  count(*) AS rows
FROM (
  SELECT LEAD(nextval('wopt004_seq'), 1, -1) OVER () AS x
  FROM range(5000)
);

SELECT nextval('wopt004_seq') AS after;
DROP SEQUENCE wopt004_seq;

Expected

Actual

Notes From Minimization

• SET enable_optimizer=false changes all five observed values to the expected values.
• The output cardinality remains 5000 rows.

OS:

macOS 15.7.4, arm64

DuckDB Version:

current main (8bc48eb3fe36f13b5e57e5e136c3ba85cc7d74d4, v2.1.0-dev84598)

DuckDB Client:

CLI

Hardware:

No response

Full Name:

Yibo Dong

Affiliation:

National University of Singapore

Did you include all relevant configuration (e.g., CPU architecture, Linux distribution) to reproduce the issue?

• Yes, I have

Did you include all code required to reproduce the issue?

• Yes, I have

Did you include all relevant data sets for reproducing the issue?

Yes