[
https://issues.apache.org/jira/browse/FLINK-40738?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=18117899#comment-18117899
]
sepuri sai krishna commented on FLINK-40738:
--------------------------------------------
I have a reproducer and would like to work on this. Could I be assigned?
Before writing anything I would like a second opinion on where the fix belongs,
given it also reproduces against calcite-core on its own, see the description.
> 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.0.0, 1.20.4, 2.3.0, 2.2.1, 2.1.3
> Reporter: sepuri sai krishna
> Priority: Major
> 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)
