[ https://issues.apache.org/jira/browse/FLINK-34583?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Xingcan Cui updated FLINK-34583: -------------------------------- Description: The table options hints don't work well with multiple WITH clauses referring to the same table. Please see the following example. The following query with hints works well. {code:java} SELECT * FROM T1 /*+ OPTIONS('foo' = 'bar') */ WHERE...;{code} The following query with multiple WITH clauses also works well. {code:java} WITH T2 AS (SELECT * FROM T1 /*+ OPTIONS('foo' = 'bar') */ WHERE...), T3 AS (SELECT ... FROM T2 WHERE...) SELECT * FROM T3;{code} The following query with multiple WITH clauses referring to the same original table failed to recognize the hints. {code:java} WITH T2 AS (SELECT * FROM T1 /*+ OPTIONS('foo' = 'bar') */ WHERE...), T3 AS (SELECT ... FROM T2 WHERE...), T4 AS (SELECT ... FROM T2 WHERE...), T5 AS (SELECT ... FROM T3 JOIN T4 ON...) SELECT * FROM T5;{code} was: The table options hints don't work well with multiple WITH clauses referring to the same table. Please see the following example. The following query with hints works well. {code:java} SELECT * FROM T1 /*+ OPTIONS('foo' = 'bar') */ WHERE...;{code} The following query with multiple WITH clauses also works well. {code:java} WITH T2 AS (SELECT * FROM T1 /*+ OPTIONS('foo' = 'bar') */ WHERE...) T3 AS (SELECT ... FROM T2 WHERE...) SELECT * FROM T3;{code} The following query with multiple WITH clauses referring to the same original table failed to recognize the hints. {code:java} WITH T2 AS (SELECT * FROM T1 /*+ OPTIONS('foo' = 'bar') */ WHERE...), T3 AS (SELECT ... FROM T2 WHERE...), T4 AS (SELECT ... FROM T2 WHERE...), T5 AS (SELECT ... FROM T3 JOIN T4 ON...) SELECT * FROM T5;{code} > Bug for dynamic table option hints with multiple CTEs > ----------------------------------------------------- > > Key: FLINK-34583 > URL: https://issues.apache.org/jira/browse/FLINK-34583 > Project: Flink > Issue Type: Bug > Components: Table SQL / Planner > Affects Versions: 1.18.1 > Reporter: Xingcan Cui > Priority: Major > > The table options hints don't work well with multiple WITH clauses referring > to the same table. Please see the following example. > > The following query with hints works well. > {code:java} > SELECT * FROM T1 /*+ OPTIONS('foo' = 'bar') */ WHERE...;{code} > The following query with multiple WITH clauses also works well. > {code:java} > WITH T2 AS (SELECT * FROM T1 /*+ OPTIONS('foo' = 'bar') */ WHERE...), > T3 AS (SELECT ... FROM T2 WHERE...) > SELECT * FROM T3;{code} > The following query with multiple WITH clauses referring to the same original > table failed to recognize the hints. > {code:java} > WITH T2 AS (SELECT * FROM T1 /*+ OPTIONS('foo' = 'bar') */ WHERE...), > T3 AS (SELECT ... FROM T2 WHERE...), > T4 AS (SELECT ... FROM T2 WHERE...), > T5 AS (SELECT ... FROM T3 JOIN T4 ON...) > SELECT * FROM T5;{code} -- This message was sent by Atlassian Jira (v8.20.10#820010)