Flink相关子查询结果与预期不符，咨询是否为正常行为

表定义与数据 #

left_table建表语句及数据 #

CREATE TABLE
  left_table (
    `experiment_id` BIGINT,
    `f_sequence` BIGINT,
    `line_string` STRING,
    `log_time` TIMESTAMP(3),
    WATERMARK FOR log_time AS log_time - INTERVAL '30' SECONDS,
    PRIMARY KEY (f_sequence) NOT ENFORCED
  )
  WITH (
    'connector' = 'filesystem',
    'path' = '<left_table_csv_path>',
    'format' = 'csv',
    'csv.ignore-parse-errors' = 'true'
    );

CSV数据：

1,0,60ee1985-a8a5-44ce-b2c3-416477c92150,2022-08-11 14:55:59.350333
1,1,a69bf93e-a097-42f0-93db-02772eb71181,2022-08-11 14:55:59.359333
1,2,3dbaf345-956e-49f0-af20-41eda50c97fa,2022-08-11 14:55:59.364333

right_table建表语句及数据 #

CREATE TABLE
  right_table (
    `experiment_id` BIGINT,
    `model_id` STRING,
    `model_time` TIMESTAMP(3),
    WATERMARK FOR model_time AS model_time - INTERVAL '30' SECONDS,
    PRIMARY KEY (model_id) NOT ENFORCED
  )
  WITH (
    'connector' = 'filesystem',
    'path' = '<right_table_csv_path>',
    'format' = 'csv',
    'csv.ignore-parse-errors' = 'true'
    );

CSV数据：

1,1b7af6e8-149a-4602-abf3-e3fc243d3840,2022-08-11 14:55:59.349333
1,17ca2ce2-f0e6-4a8b-92e8-9cd5a391c104,2022-08-11 14:55:59.358333
1,8f74ef65-f619-4c37-a2f4-04c31a9c6469,2022-08-11 14:55:59.365333

执行的查询语句 #

SELECT l.experiment_id, 
    l.f_sequence,
    l.log_time, (
    SELECT r.model_time
    FROM right_table as r
    WHERE l.log_time>=r.model_time
    ORDER BY r.model_time DESC
    LIMIT 1
) AS model_time FROM left_table as l;

预期结果 #

experiment_id   f_sequence  log_time                    model_time
1               1           2022-08-11T14:55:59.35Z     2022-08-11T14:55:59.349Z
1               2           2022-08-11T14:55:59.359Z    2022-08-11T14:55:59.349Z
1               3           2022-08-11T14:55:59.364Z    2022-08-11T14:55:59.349Z

实际结果 #

experiment_id   f_sequence   log_time                 model_time
1               2            2022-08-11 14:55:59.364  <NULL>
1               0            2022-08-11 14:55:59.350  <NULL>
1               1            2022-08-11 14:55:59.359  2022-08-11 14:55:59.358

疑问 #

无法定位问题根源，认为该行为与SQL标准不符，想咨询这是Flink的预期行为还是我操作有误？

内容的提问来源于stack exchange，提问作者akurmustafa