"CROSS JOIN UNNEST"后不能计划异常

Question

当我创建一个VIEW作为"；CROSS JOIN UNNEST"；然后使用VIEW的WHERE子句中的条件；org.apache.calcate.plan.RelOptPlaner$CannotPlanException"。

为什么我会遇到这个异常，我应该如何正确处理它？

以下是发生错误的测试代码。

it should "filter with object_key" in {
tEnv.executeSql(
s"""CREATE TABLE s3_put_event (
|  Records ARRAY<
|    ROW<
|      s3 ROW<
|        bucket ROW<name STRING>,
|        object ROW<key STRING, size BIGINT>
|      >
|    >
|  >
|) WITH (
|  'connector' = 'datagen',
|  'number-of-rows' = '3',
|  'rows-per-second' = '1',
|  'fields.Records.element.s3.bucket.name.length' = '8',
|  'fields.Records.element.s3.object.key.length' = '15',
|  'fields.Records.element.s3.object.size.min' = '1',
|  'fields.Records.element.s3.object.size.max' = '1000'
|)
|""".stripMargin
)
tEnv.executeSql(
s"""CREATE TEMPORARY VIEW s3_objects AS
|SELECT object_key, bucket_name
|FROM (
|  SELECT
|    r.s3.bucket.name AS bucket_name,
|    r.s3.object.key AS object_key,
|    r.s3.object.size AS object_size
|  FROM s3_put_event
|  CROSS JOIN UNNEST(s3_put_event.Records) AS r(s3)
|) rs
|WHERE object_size > 0
|""".stripMargin
)
tEnv.executeSql(
s"""CREATE TEMPORARY VIEW filtered_s3_objects AS
|SELECT bucket_name, object_key
|FROM s3_objects
|WHERE object_key > ''
|""".stripMargin)
val result = tEnv.sqlQuery("SELECT * FROM filtered_s3_objects")
tEnv.toChangelogStream(result).print()
env.execute()
}

如果删除条件object_key>''在"；filtered_s3_objects"；VIEW，并在"；s3_objects"；VIEW，没有抛出异常。

然而，我的实际查询很复杂，所以像这样移动WHERE子句的条件并不容易。它很难使用，尤其是当我需要分离输出流时。

Answer 1

我不确定您是否可以在具有嵌套层次结构的数组上使用CROSS JOIN UNNEST(假设您的array中有ROW(。不管怎样，你能为此申请一张Jira票吗？https://issues.apache.org/jira/projects/FLINK/issues/