Hi, thank you for the tip - but it’s the same problem: Calc(select=[version AS L_Version, version0 AS R_Version, COALESCE(window_start, window_start0) AS window_start_inner, COALESCE(window_end, window_end0) AS window_end_inner, IF(CAST(window_time AS TIMESTAMP_WITH_LOCAL_TIME_ZONE(3)) IS NULL, CAST(window_time0 AS TIMESTAMP_WITH_LOCAL_TIME_ZONE(3)), CAST(window_time AS TIMESTAMP_WITH_LOCAL_TIME_ZONE(3))) AS window_time_inner]) +- WindowJoin(leftWindow=[TUMBLE(win_start=[window_start], win_end=[window_end], size=[5 s])], rightWindow=[TUMBLE(win_start=[window_start], win_end=[window_end], size=[5 s])], joinType=[FullOuterJoin], where=[((version = version0) AND (window_time = window_time0))], select=[version, window_start, window_end, window_time, version0, window_start0, window_end0, window_time0])
I think the underlying issue is mentioned in https://nightlies.apache.org/flink/flink-docs-master/docs/dev/table/concepts/time_attributes/#introduction-to-time-attributes where it says: * Time attributes behave like regular timestamps, and are accessible for calculations. When used in calculations, time attributes are materialized and act as standard timestamps. However, ordinary timestamps cannot be used in place of, or be converted to, time attributes. If the answer is simply “window outer joins will remove time attributes” then I am happy to raise a PR against the docs for window join (https://nightlies.apache.org/flink/flink-docs-master/docs/dev/table/sql/queries/window-join/#limitation) to make that clearer. -- Nic Townsend IBM Event Processing Architect / Senior Engineer Slack: @nictownsend Bluesky: @nict0wnsend.bsky.social From: Xuyang <xyzhong...@163.com> Date: Tuesday, 29 July 2025 at 07:34 To: user@flink.apache.org <user@flink.apache.org> Subject: [EXTERNAL] Re:Maintaining time attribute after a Window Outer Join 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" <nictownsend@ uk. ibm. com> wrote: Hi all, is there valid Flink SQL for 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" <nictowns...@uk.ibm.com> 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 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
