Hi, have you tried using the `if` function? For example `if(window_start is
null, window_start0, window_start)`
--
Best！
Xuyang
At 2025-07-29 00:31:47, "Nic Townsend" <[email protected]> wrote:
Hi all, is there valid Flink SQL for emitting a time attribute from an outer
window join?
I have two tables where I have used a TVF to window each into the same window
interval .
As such, I have two columns containing a window_time time attribute.
When I perform an outer join on the two tables (L.id = R.id AND L.window_start
= R.window_start AND L.window_end = R.window_end AND L.window_time =
R.window_time), I would like a single attribute output for window_time.
I appreciate that if I use COALESCE(left, right) then I lose the time attribute
and any downstream time based operation like window aggregate will not compile
– because the optimized plan has cast to TIMESTMAP_LTZ:
Calc(select=[version AS L_Version, version0 AS R_Version,
COALESCE(window_start, window_start0) AS window_start, COALESCE(window_end,
window_end0) AS window_end, COALESCE(CAST(window_time AS
TIMESTAMP_WITH_LOCAL_TIME_ZONE(3)), CAST(window_time0 AS
TIMESTAMP_WITH_LOCAL_TIME_ZONE(3))) AS window_time])
If I don’t use COALESCE and I pick either left/right – then for some rows the
time attribute is NULL which causes an exception at runtime.
Is there a way to perform a special form of COALESE on time attributes for a
join – something that knows that if the join condition contained window_time
then it’s safe to avoid the CAST?
--
Nic Townsend
IBM Event Processing
Architect / Senior Engineer
Slack: @nictownsend
Bluesky: @nict0wnsend.bsky.social
Unless otherwise stated above:
IBM United Kingdom Limited
Registered in England and Wales with number 741598
Registered office: Building C, IBM Hursley Office, Hursley Park Road,
Winchester, Hampshire SO21 2JN
