sepuri sai krishna created FLINK-40738:
------------------------------------------
Summary: LAG/LEAD with a non-null default value returns the
default instead of NULL
Key: FLINK-40738
URL: https://issues.apache.org/jira/browse/FLINK-40738
Project: Flink
Issue Type: Bug
Components: Table SQL / Planner
Affects Versions: 2.1.3, 2.2.1, 2.3.0, 1.20.4, 2.0.0
Reporter: sepuri sai krishna
Attachments: LagDefaultRepro.java, pom.xml
When {{LAG}} or {{LEAD}} is given a non-null default value, a genuine NULL at
the offset row
is replaced instead of being returned. The default is only meant to appear when
the offset
falls outside the partition.
{code:sql}
CREATE VIEW n AS SELECT * FROM (VALUES
('a',1,7),
('a',2,CAST(NULL AS INT)),
('a',3,CAST(NULL AS INT)),
('a',4,9)) AS n(k,ord,v);
SELECT ord, LAG(v,1) OVER (PARTITION BY k ORDER BY ord) FROM n;
SELECT ord, LAG(v,1,-1) OVER (PARTITION BY k ORDER BY ord) FROM n;
{code}
{noformat}
LAG(v,1) -> 1=>NULL 2=>7 3=>NULL 4=>NULL (expected)
LAG(v,1,-1) -> 1=>-1 2=>7 3=>-1 4=>-1 (expected: 1=>-1 2=>7
3=>NULL 4=>NULL)
{noformat}
At ord=3 and ord=4 the offset row exists and its value is NULL, so the result
should be
NULL.
With a STRING column the substituted value is not even the default:
{noformat}
LAG(s,1,'D') -> 1=>'D' 2=>'p' 3=>'' 4=>'' (expected: 1=>'D'
2=>'p' 3=>NULL 4=>NULL)
{noformat}
Reproduced in batch and streaming on 1.16.3, 1.17.2, 1.18.1, 1.19.3, 1.20.4,
2.0.0, 2.1.3,
2.2.1 and 2.3.0 -- every release I tested.
For comparison, on the same data:
{noformat}
Apache Spark 4.2.0 LAG(v,1,-1) -> -1, 7, NULL, NULL
DuckDB 1.5.5 LAG(v,1,-1) -> -1, 7, NULL, NULL
{noformat}
One open question for whoever picks this up: the same query also fails against
plain
calcite-core with no Flink involved, on 1.36.0, 1.41.0 and 1.42.0, so the fix
may belong
upstream rather than here. I could not find a Calcite issue covering it. Happy
to raise it
there instead, or in both places.
Reproducer attached.
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
