Repository: duckdb/duckdb (language: python)

Issue:
window "lead" incorrectly yields null data in some groups
### What happens?

A 'LEAD(colname, 2)' operation over a simple partition produces incorrect results for one partition

[Example using DuckDB live here](https://shell.duckdb.org/#queries=v0,CREATE-TABLE-testdata-(date-DATE%2C-%22group%22-INT%2C-count-INT%2C-status-STRING)~%0AINSERT-INTO-testdata-VALUES%0A('2024%2001%2001'%2C-1%2C-1000%2C-'ordered')%2C%0A('2024%2002%2001'%2C-1%2C-1000%2C-'dispatched')%2C%0A('2024%2003%2001'%2C-1%2C-1000%2C-'dispatched')%2C%0A('2024%2001%2001'%2C-2%2C-2000%2C-'ordered')%2C%0A('2024%2002%2001'%2C-2%2C-2000%2C-'ordered')%2C%0A('2024%2003%2001'%2C-2%2C-2000%2C-'ordered')%2C%0A('2024%2001%2001'%2C-3%2C-3000%2C-'ordered')%2C%0A('2024%2002%2001'%2C-3%2C-3000%2C-'ordered')%2C%0A('2024%2003%2001'%2C-3%2C-3000%2C-'late')%2C%0A('2024%2001%2001'%2C-4%2C-4000%2C-'ordered')%2C%0A('2024%2002%2001'%2C-4%2C-4000%2C-'ordered')%2C%0A('2024%2003%2001'%2C-4%2C-4000%2C-'ordered')%2C%0A('2024%2001%2001'%2C-5%2C-5000%2C-'ordered')%2C%0A('2024%2002%2001'%2C-5%2C-5000%2C-'late')%2C%0A('2024%2003%2001'%2C-5%2C-5000%2C-'ordered')%2C%0A('2024%2001%2001'%2C-6%2C-1000%2C-'dispatched')%2C%0A('2024%2002%2001'%2C-6%2C-1000%2C-'dispatched')%2C%0A('2024%2003%2001'%2C-6%2C-1000%2C-'dispatched')%2C%0A('2024%2001%2001'%2C-7%2C-1000%2C-'late')%2C%0A('2024%2002%2001'%2C-7%2C-1000%2C-'dispatched')%2C%0A('2024%2003%2001'%2C-7%2C-1000%2C-'dispatched')~%0A,SELECT%0A--%22t0%22.%22date%22%2C%0A--%22t0%22.%22group%22%2C%0A--%22t0%22.%22count%22%2C%0A--%22t0%22.%22status%22%2C%0A--LEAD(%22t0%22.%22date%22%2C-2)-OVER-(PARTITION-BY-%22t0%22.%22group%22-ORDER-BY-%22t0%22.%22date%22-ASC-ROWS-BETWEEN-UNBOUNDED-PRECEDING-AND-UNBOUNDED-FOLLOWING)-AS-%22end_date%22%2C%0A--LEAD(%22t0%22.%22status%22%2C-2)-OVER-(PARTITION-BY-%22t0%22.%22group%22-ORDER-BY-%22t0%22.%22date%22-ASC-ROWS-BETWEEN-UNBOUNDED-PRECEDING-AND-UNBOUNDED-FOLLOWING)-AS-%22end_status%22%0AFROM-%22testdata%22-AS-%22t0%22~,DROP-TABLE-testdata~,CREATE-TABLE-testdata-(date-DATE%2C-%22group%22-INT%2C-count-INT%2C-status-STRING)~%0AINSERT-INTO-testdata-VALUES%0A('2024%2001%2001'%2C-6%2C-1000%2C-'dispatched')%2C%0A('2024%2002%2001'%2C-6%2C-1000%2C-'dispatched')%2C%0A('2024%2003%2001'%2C-6%2C-1000%2C-'dispatched')~,SELECT%0A--%22t0%22.%22date%22%2C%0A--%22t0%22.%22group%22%2C%0A--%22t0%22.%22count%22%2C%0A--%22t0%22.%22status%22%2C%0A--LEAD(%22t0%22.%22date%22%2C-2)-OVER-(PARTITION-BY-%22t0%22.%22group%22-ORDER-BY-%22t0%22.%22date%22-ASC-ROWS-BETWEEN-UNBOUNDED-PRECEDING-AND-UNBOUNDED-FOLLOWING)-AS-%22end_date%22%2C%0A--LEAD(%22t0%22.%22status%22%2C-2)-OVER-(PARTITION-BY-%22t0%22.%22group%22-ORDER-BY-%22t0%22.%22date%22-ASC-ROWS-BETWEEN-UNBOUNDED-PRECEDING-AND-UNBOUNDED-FOLLOWING)-AS-%22end_status%22%0AFROM-%22testdata%22-AS-%22t0%22~)

Tested on ubuntu and WASM
The output of the code below produces incorrect output for group 6 on date 2024-01-01 (row 4).  DuckDb produces:
```
┌────────────┬───────┬───────┬────────────┬────────────┬────────────┐
│ date       ┆ group ┆ count ┆ status     ┆ end_date   ┆ end_status │
╞════════════╪═══════╪═══════╪════════════╪════════════╪════════════╡
│ 2024-01-01 ┆     3 ┆  3000 ┆ ordered    ┆ 2024-03-01 ┆ late       │
│ 2024-02-01 ┆     3 ┆  3000 ┆ ordered    ┆            ┆            │
│ 2024-03-01 ┆     3 ┆  3000 ┆ late       ┆            ┆            │
│ 2024-01-01 ┆     6 ┆  1000 ┆ dispatched ┆            ┆            │
│ 2024-02-01 ┆     6 ┆  1000 ┆ dispatched ┆            ┆            │
│ 2024-03-01 ┆     6 ┆  1000 ┆ dispatched ┆            ┆            │
│ 2024-01-01 ┆     4 ┆  4000 ┆ ordered    ┆ 2024-03-01 ┆ ordered    │
│ 2024-02-01 ┆     4 ┆  4000 ┆ ordered    ┆            ┆            │
│ 2024-03-01 ┆     4 ┆  4000 ┆ ordered    ┆            ┆            │
│ 2024-01-01 ┆     1 ┆  1000 ┆ ordered    ┆ 2024-03-01 ┆ dispatched │
│ 2024-02-01 ┆     1 ┆  1000 ┆ dispatched ┆            ┆            │
│ 2024-03-01 ┆     1 ┆  1000 ┆ dispatched ┆            ┆            │
│ 2024-01-01 ┆     7 ┆  1000 ┆ late       ┆ 2024-03-01 ┆ dispatched │
│ 2024-02-01 ┆     7 ┆  1000 ┆ dispatched ┆            ┆            │
│ 2024-03-01 ┆     7 ┆  1000 ┆ dispatched ┆            ┆            │
│ 2024-01-01 ┆     2 ┆  2000 ┆ ordered    ┆ 2024-03-01 ┆ ordered    │
│ 2024-02-01 ┆     2 ┆  2000 ┆ ordered    ┆            ┆            │
│ 2024-03-01 ┆     2 ┆  2000 ┆ ordered    ┆            ┆            │
│ 2024-01-01 ┆     5 ┆  5000 ┆ ordered    ┆ 2024-03-01 ┆ ordered    │
│ 2024-02-01 ┆     5 ┆  5000 ┆ late       ┆            ┆            │
│ 2024-03-01 ┆     5 ┆  5000 ┆ ordered    ┆            ┆            │
└────────────┴───────┴───────┴────────────┴────────────┴────────────┘    
```

Correct output for that row is:
```
2024-01-01 ┆  6 ┆    1000 ┆ dispatched   ┆ 2024-03-01 ┆ dispatched  
```

### To Reproduce

following code produces the error:

```
CREATE TABLE testdata (date DATE, "group" INT, count INT, status STRING);
INSERT INTO testdata VALUES
('2024-01-01', 1, 1000, 'ordered'),
('2024-02-01', 1, 1000, 'dispatched'),
('2024-03-01', 1, 1000, 'dispatched'),
('2024-01-01', 2, 2000, 'ordered'),
('2024-02-01', 2, 2000, 'ordered'),
('2024-03-01', 2, 2000, 'ordered'),
('2024-01-01', 3, 3000, 'ordered'),
('2024-02-01', 3, 3000, 'ordered'),
('2024-03-01', 3, 3000, 'late'),
('2024-01-01', 4, 4000, 'ordered'),
('2024-02-01', 4, 4000, 'ordered'),
('2024-03-01', 4, 4000, 'ordered'),
('2024-01-01', 5, 5000, 'ordered'),
('2024-02-01', 5, 5000, 'late'),
('2024-03-01', 5, 5000, 'ordered'),
('2024-01-01', 6, 1000, 'dispatched'),
('2024-02-01', 6, 1000, 'dispatched'),
('2024-03-01', 6, 1000, 'dispatched'),
('2024-01-01', 7, 1000, 'late'),
('2024-02-01', 7, 1000, 'dispatched'),
('2024-03-01', 7, 1000, 'dispatched');


SELECT
  "t0"."date",
  "t0"."group",
  "t0"."count",
  "t0"."status",
  LEAD("t0"."date", 2) OVER (PARTITION BY "t0"."group" ORDER BY "t0"."date" ASC ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS "end_date",
  LEAD("t0"."status", 2) OVER (PARTITION BY "t0"."group" ORDER BY "t0"."date" ASC ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS "end_status"
FROM "testdata" AS "t0";
```

If only data for group 6 is inserted into the table, the correct output is produced.

```
DROP TABLE testdata;

CREATE TABLE testdata (date DATE, "group" INT, count INT, status STRING);
INSERT INTO testdata VALUES
('2024-01-01', 6, 1000, 'dispatched'),
('2024-02-01', 6, 1000, 'dispatched'),
('2024-03-01', 6, 1000, 'dispatched');

SELECT
  "t0"."date",
  "t0"."group",
  "t0"."count",
  "t0"."status",
  LEAD("t0"."date", 2) OVER (PARTITION BY "t0"."group" ORDER BY "t0"."date" ASC ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS "end_date",
  LEAD("t0"."status", 2) OVER (PARTITION BY "t0"."group" ORDER BY "t0"."date" ASC ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS "end_status"
FROM "testdata" AS "t0";

```

Output:

```
┌────────────┬───────┬───────┬────────────┬────────────┬────────────┐
│ date       ┆ group ┆ count ┆ status     ┆ end_date   ┆ end_status │
╞════════════╪═══════╪═══════╪════════════╪════════════╪════════════╡
│ 2024-01-01 ┆     6 ┆  1000 ┆ dispatched ┆ 2024-03-01 ┆ dispatched │
│ 2024-02-01 ┆     6 ┆  1000 ┆ dispatched ┆            ┆            │
│ 2024-03-01 ┆     6 ┆  1000 ┆ dispatched ┆            ┆            │
└────────────┴───────┴───────┴────────────┴────────────┴────────────┘
```

### OS:

ubuntu 24.04, x86_64

### DuckDB Version:

1.1.2

### DuckDB Client:

python

### Hardware:

_No response_

### Full Name:

Jonathan Clarke

### Affiliation:

FiveSigma

### 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?

No - Other reason (please specify in the issue body)

### Did you include all code required to reproduce the issue?

- [X] Yes, I have

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

- [X] Yes, I have


Discussion / hints:

Fix patch:
diff --git a/src/execution/window_executor.cpp b/src/execution/window_executor.cpp
index 56397f7a7a5b..9377410584f1 100644
--- a/src/execution/window_executor.cpp
+++ b/src/execution/window_executor.cpp
@@ -1671,23 +1671,23 @@ void WindowLeadLagExecutor::EvaluateInternal(WindowExecutorGlobalState &gstate,
 		// else offset is zero, so don't move.
 
 		if (can_shift) {
+			const auto target_limit = MinValue(partition_end[i], row_end) - row_idx;
 			if (!delta) {
 				//	Copy source[index:index+width] => result[i:]
 				const auto index = NumericCast<idx_t>(val_idx);
 				const auto source_limit = partition_end[i] - index;
-				const auto target_limit = MinValue(partition_end[i], row_end) - row_idx;
 				const auto width = MinValue(source_limit, target_limit);
 				auto &source = payload_collection.data[0];
 				VectorOperations::Copy(source, result, index + width, index, i);
 				i += width;
 				row_idx += width;
 			} else if (wexpr.default_expr) {
-				const auto width = MinValue(delta, count - i);
+				const auto width = MinValue(delta, target_limit);
 				llstate.leadlag_default.CopyCell(result, i, width);
 				i += width;
 				row_idx += width;
 			} else {
-				for (idx_t nulls = MinValue(delta, count - i); nulls--; ++i, ++row_idx) {
+				for (idx_t nulls = MinValue(delta, target_limit); nulls--; ++i, ++row_idx) {
 					FlatVector::SetNull(result, i, true);
 				}
 			}


Test patch:
diff --git a/test/sql/window/test_lead_lag.test b/test/sql/window/test_lead_lag.test
index 6ab328b6476e..506cb5507a48 100644
--- a/test/sql/window/test_lead_lag.test
+++ b/test/sql/window/test_lead_lag.test
@@ -44,3 +44,64 @@ order by id, t
 3	5	-2	NULL
 3	-1	0	NULL
 3	NULL	10	5
+
+# Shifted lead optimisation with hash collisions
+statement ok
+CREATE TABLE issue14398 (date DATE, "group" INT, count INT, status STRING);
+
+statement ok
+INSERT INTO issue14398 VALUES
+('2024-01-01', 1, 1000, 'ordered'),
+('2024-02-01', 1, 1000, 'dispatched'),
+('2024-03-01', 1, 1000, 'dispatched'),
+('2024-01-01', 2, 2000, 'ordered'),
+('2024-02-01', 2, 2000, 'ordered'),
+('2024-03-01', 2, 2000, 'ordered'),
+('2024-01-01', 3, 3000, 'ordered'),
+('2024-02-01', 3, 3000, 'ordered'),
+('2024-03-01', 3, 3000, 'late'),
+('2024-01-01', 4, 4000, 'ordered'),
+('2024-02-01', 4, 4000, 'ordered'),
+('2024-03-01', 4, 4000, 'ordered'),
+('2024-01-01', 5, 5000, 'ordered'),
+('2024-02-01', 5, 5000, 'late'),
+('2024-03-01', 5, 5000, 'ordered'),
+('2024-01-01', 6, 1000, 'dispatched'),
+('2024-02-01', 6, 1000, 'dispatched'),
+('2024-03-01', 6, 1000, 'dispatched'),
+('2024-01-01', 7, 1000, 'late'),
+('2024-02-01', 7, 1000, 'dispatched'),
+('2024-03-01', 7, 1000, 'dispatched');
+
+query IIIIII
+SELECT
+  "t0"."date",
+  "t0"."group",
+  "t0"."count",
+  "t0"."status",
+  LEAD("t0"."date", 2) OVER (PARTITION BY "t0"."group" ORDER BY "t0"."date" ASC ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS "end_date",
+  LEAD("t0"."status", 2) OVER (PARTITION BY "t0"."group" ORDER BY "t0"."date" ASC ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS "end_status"
+FROM "issue14398" AS "t0"
+ORDER BY 2, 1
+----
+2024-01-01	1	1000	ordered	2024-03-01	dispatched
+2024-02-01	1	1000	dispatched	NULL	NULL
+2024-03-01	1	1000	dispatched	NULL	NULL
+2024-01-01	2	2000	ordered	2024-03-01	ordered
+2024-02-01	2	2000	ordered	NULL	NULL
+2024-03-01	2	2000	ordered	NULL	NULL
+2024-01-01	3	3000	ordered	2024-03-01	late
+2024-02-01	3	3000	ordered	NULL	NULL
+2024-03-01	3	3000	late	NULL	NULL
+2024-01-01	4	4000	ordered	2024-03-01	ordered
+2024-02-01	4	4000	ordered	NULL	NULL
+2024-03-01	4	4000	ordered	NULL	NULL
+2024-01-01	5	5000	ordered	2024-03-01	ordered
+2024-02-01	5	5000	late	NULL	NULL
+2024-03-01	5	5000	ordered	NULL	NULL
+2024-01-01	6	1000	dispatched	2024-03-01	dispatched
+2024-02-01	6	1000	dispatched	NULL	NULL
+2024-03-01	6	1000	dispatched	NULL	NULL
+2024-01-01	7	1000	late	2024-03-01	dispatched
+2024-02-01	7	1000	dispatched	NULL	NULL
+2024-03-01	7	1000	dispatched	NULL	NULL