[jira] [Commented] (FLINK-34583) Bug for dynamic table option hints with multiple CTEs
[
https://issues.apache.org/jira/browse/FLINK-34583?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17838379#comment-17838379
]
Xingcan Cui commented on FLINK-34583:
-
Hi [~xuyangzhong], thanks for looking into this. I hit the issue when using the
Paimon table source. The execution plan looks good. However, the options don't
work. It could be a runtime issue or Paimon source implementation bug. I can't
remember clearly if Flink generates multiple table sources and then merges them
at runtime. If it does, the options may not be merged properly.
!image-2024-04-17-16-48-49-073.png!
> 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
> Attachments: image-2024-04-17-16-35-06-153.png,
> image-2024-04-17-16-48-49-073.png
>
>
> 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)
[jira] [Commented] (FLINK-34583) Bug for dynamic table option hints with multiple CTEs
[
https://issues.apache.org/jira/browse/FLINK-34583?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17838130#comment-17838130
]
xuyang commented on FLINK-34583:
Hi, [~xccui] can you provide more details about this bug? I try to run this
test in my local env with Flink 1.18-SNAPSHOT, but could not re-produce it.
{code:java}
// run it in org.apache.flink.table.planner.plan.stream.sql.CalcTest
@Test
def test(): Unit = {
util.tableEnv.executeSql(s"""
|create temporary table T1 (
| a int,
| b int,
| c int)
| with ( 'connector' = 'values' )
|""".stripMargin)
util.verifyExecPlan(
"with q1 as (SELECT * FROM T1 /*+ OPTIONS('changelog-mode' = 'I,D') */
WHERE a > 10)," +
"q2 as (SELECT a, b, c FROM q1 where b > 10)," +
"q3 as (select a,b,c from q1 where c > 20)," +
"q4 as (select * from q2 join q3 on q2.a = q3.a) SELECT * FROM q4");
}
// result
Join(joinType=[InnerJoin], where=[(a = a0)], select=[a, b, c, a0, b0, c0],
leftInputSpec=[NoUniqueKey], rightInputSpec=[NoUniqueKey])
:- Exchange(distribution=[hash[a]])
: +- Calc(select=[a, b, c], where=[((a > 10) AND (b > 10))])
: +- TableSourceScan(table=[[default_catalog, default_database, T1,
filter=[]]], fields=[a, b, c], hints=[[[OPTIONS
options:{changelog-mode=I,D}]]])(reuse_id=[1])
+- Exchange(distribution=[hash[a]])
+- Calc(select=[a, b, c], where=[((a > 10) AND (c > 20))])
+- Reused(reference_id=[1]){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)
[jira] [Commented] (FLINK-34583) Bug for dynamic table option hints with multiple CTEs
[
https://issues.apache.org/jira/browse/FLINK-34583?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17823973#comment-17823973
]
xuyang commented on FLINK-34583:
[~lincoln.86xy] Sure.
> 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)
[jira] [Commented] (FLINK-34583) Bug for dynamic table option hints with multiple CTEs
[
https://issues.apache.org/jira/browse/FLINK-34583?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17823959#comment-17823959
]
lincoln lee commented on FLINK-34583:
-
cc
[xuyang|https://issues.apache.org/jira/secure/ViewProfile.jspa?name=xuyangzhong]
can you take a look when you have time since you're familiar with this part？
> 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)
