[jira] [Commented] (FLINK-29873) CannotPlanException raised after "CROSS JOIN UNNEST"

["dalongliu (Jira)" <ji...@apache.org>]

    [ 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)