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
