[ https://issues.apache.org/jira/browse/FLINK-29873?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17628625#comment-17628625 ]
dalongliu commented on FLINK-29873: ----------------------------------- CC [~godfreyhe] > 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 > Priority: Major > 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)