Hi all,
until now I thought I understood how LEAD and LAG work, but after adding both IGNORE NULLS and an offset I'm confused
I created a SQL Fiddle which is running an 11g R2, v$version returns Oracle Database 11g Express Edition Release 11.2.0.2.0
CREATE TABLE vt (part INT, ord int, val INT);
INSERT INTO vt VALUES(1, 1, 8);
INSERT INTO vt VALUES(1, 2, 10);
INSERT INTO vt VALUES(1, 3, 3);
INSERT INTO vt VALUES(1, 4, NULL);
INSERT INTO vt VALUES(1, 5, NULL);
INSERT INTO vt VALUES(1, 6, NULL);
INSERT INTO vt VALUES(1, 7, 2);
INSERT INTO vt VALUES(1, 8, NULL);
INSERT INTO vt VALUES(1, 9, 5);
SELECT part, ord, val,
LEAD(val,1)
OVER (PARTITION BY part ORDER BY ord) as lead1,
LEAD(val,1) IGNORE NULLS
OVER (PARTITION BY part ORDER BY ord) as ignore1,
LEAD(val,2)
OVER (PARTITION BY part ORDER BY ord) as lead2,
LEAD(val,2) IGNORE NULLS
OVER (PARTITION BY part ORDER BY ord) as ignore2
FROM vt;
The result for the last column, LEAD plus offset 2 and IGNORE NULLS is IMHO wrong, I marked the suspicious results in red and show what I would expect in green
| PART | ORD | VAL | LEAD1 | IGNORE1 | LEAD2 | IGNORE2 |
|------|-----|--------|--------|---------|--------|---------|
| 1 | 1 | 8 | 10 | 10 | 3 | 3 |
| 1 | 2 | 10 | 3 | 3 | (null) | 2 |
| 1 | 3 | 3 | (null) | 2 | (null) | 5 | 2
| 1 | 4 | (null) | (null) | 2 | (null) | 5 | 2
| 1 | 5 | (null) | (null) | 2 | 2 | 5 | 2
| 1 | 6 | (null) | 2 | 2 | (null) | 5 |
| 1 | 7 | 2 | (null) | 5 | 5 | (null) | 5
| 1 | 8 | (null) | 5 | 5 | (null) | (null) |
| 1 | 9 | 5 | (null) | (null) | (null) | (null) |
LAG returns a similar result. Am I totally wrong or is this a known bug which is fixed in a later release?
Thanks
Dieter
Modified the INSERTs to match the result.