SQL interval join 问题

Mon, 19 Oct 2020 00:04:20 -0700 

现有 SQL 语句如下：
create table source1(
  id varchar PRIMARY KEY,
  a varchar,
  proctime AS PROCTIME()
) with (
    'connector' = 'kafka'
    ...
);
create table source2(
  id varchar PRIMARY KEY,
  a varchar,
  proctime AS PROCTIME()
) with (
    'connector' = 'kafka'
    ...
);
select
  case
    when s1.id is not null then s1.id
    else s2.id
  end as ids,
  s1.a, s2.b
from source1 as s1 full outer join source2 as s2 on s1.id = s2.id where 
s1.proctime between s2.proctime - INTERVAL '5' SECOND and s2.proctime + 
INTERVAL '5' SECOND;


最后的 join 语句预期是 如果两个source的消息<id, a>, <id, b>先后到达时间超过 10 秒，则输出<id, a>, <id, b> 
两条消息。


目前的观察结果是，如果两条消息<id, a>, <id, b>先后到达时间超过10 秒，输出为：<id, a>, <id, a, b> 
为何超过 10 秒后，仍然会输出 <id, a, b> ？

回复