Hi everyone! I might have an interesting question for you that not even ChatGPT can figure out 🙂 Imagine you have to streams that you want to join on a common ID, and the goal is to end up with “unioned” events, combining the event with the latest event from the other stream. So in the end you’d have something like

{
    'A_event_ts`: '2023-07-25T00:00:00',
    'B_event_ts': '2023-07-24T00:00:00',
    'A_value': 12,
    'B_value': 13
}

when A arrives after B, or something like:

{
    'A_event_ts`: '2023-07-25T00:00:00',
    'B_event_ts': '2023-07-26T00:00:00',
    'A_value': 12,
    'B_value': 14
}

when B arrives after A. In effect, it’s a union of two asof joins, but that doesn’t work because the schemas become incompatible due to the definition of the system time. Any hints? To make the question a bit more challenging, I’d like to achieve it with Flink SQL only, if possible :)

Yes, use a type like

Wrapper(Option[A], Option[B])

Is that possible with SQL?

Sure, you can use ROW as a wrapper type, with a and b be nullable fields in that (also ROWs).

ok, I’m not 100% sure how I would apply that here, can you give a little more context? 🙂