Daeyeon Bae created FLINK-29873: ----------------------------------- Summary: CannotPlanException raised after "CROSS JOIN UNNEST" Key: FLINK-29873 URL: https://issues.apache.org/jira/browse/FLINK-29873 Project: Flink Issue Type: Bug Components: Table SQL / Planner Affects Versions: 1.15.2 Reporter: Daeyeon Bae Attachments: error-log.txt
When I create a VIEW as a result of "CROSS JOIN UNNEST" and then use the condition in the WHERE clause of the VIEW, it throws an exception "org.apache.calcite.plan.RelOptPlanner$CannotPlanException". Why am I getting this exception and how should I handle it the right way? The following is the test code in which an error occurs. {code:java} 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() } {code} If I remove the condition object_key > '' in the "filtered_s3_objects" VIEW, and do it in the "s3_objects" VIEW, no exception is thrown.However, my actual query is complicated, so it is not easy to move the condition of the WHERE clause like this. It's hard to use especially if I need to separate the output stream. I have attached the error log. -- This message was sent by Atlassian Jira (v8.20.10#820010)