[
https://issues.apache.org/jira/browse/FLINK-30396?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Jianhui Dong updated FLINK-30396:
---------------------------------
Description:
As [flink
doc|https://nightlies.apache.org/flink/flink-docs-master/docs/dev/table/sql/queries/hints/#query-hints]
said:
> {{Query hints}} can be used to suggest the optimizer to affect query
> execution plan within a specified query scope. Their effective scope is
> current {{{}Query block{}}}([What are query blocks
> ?|https://nightlies.apache.org/flink/flink-docs-master/docs/dev/table/sql/queries/hints/#what-are-query-blocks-])
> which {{Query Hints}} are specified.
But the sql hint 'LOOKUP' behaves differently like the demo following:
{code:java}
-- DDL
CREATE TABLE left_table (
lid INTEGER,
lname VARCHAR,
pts AS PROCTIME()
) WITH (
'connector' = 'filesystem',
'format' = 'csv',
'path'='xxx'
)
CREATE TABLE dim_table (
id INTEGER,
name VARCHAR,
mentor VARCHAR,
gender VARCHAR
) WITH (
'connector' = 'jdbc',
'url' = 'xxx',
'table-name' = 'dim1',
'username' = 'xxx',
'password' = 'xxx',
'driver'= 'com.mysql.cj.jdbc.Driver'
)
-- DML
SELECT /*+ LOOKUP('table'='outer') */
ll.id AS lid,
ll.name,
r.mentor,
r.gender
FROM (
SELECT /*+ LOOKUP('table'='inner') */
l.lid AS id,
l.lname AS name,
r.mentor,
r.gender,
l.pts
FROM left_table AS l
JOIN dim_table FOR SYSTEM_TIME AS OF l.pts AS r
ON l.lname = r.name
) ll JOIN dim_table FOR SYSTEM_TIME AS OF ll.pts AS r ON ll.name=r.name{code}
The inner correlate will have two hints:
{[LOOKUP inheritPath:[0] options:\{table=inner}], [LOOKUP inheritPath:[0, 0, 0]
options:\{table=outer}]}, and IMO which maybe is a bug.
The first hint comes from the inner query block and the second hint comes from
the outer block, and ClearJoinHintWithInvalidPropagationShuttle will not clear
the second hint cause the correlate has no 'ALIAS' hint.
The reason for the above case is that the hint 'ALIAS' now only works for join
rel nodes and 'LOOKUP' works for correlate and join rel nodes.
I think maybe the better way would be to make 'ALIAS' support both correlate
and join rel nodes like 'LOOKUP'.
was:
As [flink
doc|https://nightlies.apache.org/flink/flink-docs-master/docs/dev/table/sql/queries/hints/#query-hints]
said:
> {{Query hints}} can be used to suggest the optimizer to affect query
> execution plan within a specified query scope. Their effective scope is
> current {{{}Query block{}}}([What are query blocks
> ?|https://nightlies.apache.org/flink/flink-docs-master/docs/dev/table/sql/queries/hints/#what-are-query-blocks-])
> which {{Query Hints}} are specified.
But the sql hint 'LOOKUP' behaves differently like the demo following:
{code:java}
-- DDL
CREATE TABLE left_table (
lid INTEGER,
lname VARCHAR,
pts AS PROCTIME()
) WITH (
'connector' = 'filesystem',
'format' = 'csv',
'path'='xxx'
)
CREATE TABLE dim_table (
id INTEGER,
name VARCHAR,
mentor VARCHAR,
gender VARCHAR
) WITH (
'connector' = 'jdbc',
'url' = 'xxx',
'table-name' = 'dim1',
'username' = 'xxx',
'password' = 'xxx',
'driver'= 'com.mysql.cj.jdbc.Driver'
)
-- DML
SELECT /*+ LOOKUP('table'='outer') */
ll.id AS lid,
ll.name,
r.mentor,
r.gender
FROM (
SELECT /*+ LOOKUP('table'='inner') */
l.lid AS id,
l.lname AS name,
r.mentor,
r.gender,
l.pts
FROM left_table AS l
JOIN dim_table FOR SYSTEM_TIME AS OF l.pts AS r
ON l.lname = r.name
) ll JOIN dim_table FOR SYSTEM_TIME AS OF ll.pts AS r ON ll.name=r.name{code}
The inner correlate will have two hints:
{[LOOKUP inheritPath:[0] options:\\{table=inner}
], [LOOKUP inheritPath:[0, 0, 0] options:\{table=outer}]}, and IMO which maybe
is a bug.
The first hint comes from the inner query block and the second hint comes from
the outer block, and ClearJoinHintWithInvalidPropagationShuttle will not clear
the second hint cause the correlate has no 'ALIAS' hint.
The reason for the above case is that the hint 'ALIAS' now only works for join
rel nodes and 'LOOKUP' works for correlate and join rel nodes.
I think maybe the better way would be to make 'ALIAS' support both correlate
and join rel nodes like 'LOOKUP'.
> sql hint 'LOOKUP' which is defined in outer query block may take effect in
> inner query block
> --------------------------------------------------------------------------------------------
>
> Key: FLINK-30396
> URL: https://issues.apache.org/jira/browse/FLINK-30396
> Project: Flink
> Issue Type: Bug
> Components: Table SQL / Planner
> Affects Versions: 1.16.0
> Reporter: Jianhui Dong
> Priority: Major
>
> As [flink
> doc|https://nightlies.apache.org/flink/flink-docs-master/docs/dev/table/sql/queries/hints/#query-hints]
> said:
> > {{Query hints}} can be used to suggest the optimizer to affect query
> > execution plan within a specified query scope. Their effective scope is
> > current {{{}Query block{}}}([What are query blocks
> > ?|https://nightlies.apache.org/flink/flink-docs-master/docs/dev/table/sql/queries/hints/#what-are-query-blocks-])
> > which {{Query Hints}} are specified.
> But the sql hint 'LOOKUP' behaves differently like the demo following:
> {code:java}
> -- DDL
> CREATE TABLE left_table (
> lid INTEGER,
> lname VARCHAR,
> pts AS PROCTIME()
> ) WITH (
> 'connector' = 'filesystem',
> 'format' = 'csv',
> 'path'='xxx'
> )
> CREATE TABLE dim_table (
> id INTEGER,
> name VARCHAR,
> mentor VARCHAR,
> gender VARCHAR
> ) WITH (
> 'connector' = 'jdbc',
> 'url' = 'xxx',
> 'table-name' = 'dim1',
> 'username' = 'xxx',
> 'password' = 'xxx',
> 'driver'= 'com.mysql.cj.jdbc.Driver'
> )
> -- DML
> SELECT /*+ LOOKUP('table'='outer') */
> ll.id AS lid,
> ll.name,
> r.mentor,
> r.gender
> FROM (
> SELECT /*+ LOOKUP('table'='inner') */
> l.lid AS id,
> l.lname AS name,
> r.mentor,
> r.gender,
> l.pts
> FROM left_table AS l
> JOIN dim_table FOR SYSTEM_TIME AS OF l.pts AS r
> ON l.lname = r.name
> ) ll JOIN dim_table FOR SYSTEM_TIME AS OF ll.pts AS r ON ll.name=r.name{code}
> The inner correlate will have two hints:
> {[LOOKUP inheritPath:[0] options:\{table=inner}], [LOOKUP inheritPath:[0, 0,
> 0] options:\{table=outer}]}, and IMO which maybe is a bug.
> The first hint comes from the inner query block and the second hint comes
> from the outer block, and ClearJoinHintWithInvalidPropagationShuttle will not
> clear the second hint cause the correlate has no 'ALIAS' hint.
> The reason for the above case is that the hint 'ALIAS' now only works for
> join rel nodes and 'LOOKUP' works for correlate and join rel nodes.
> I think maybe the better way would be to make 'ALIAS' support both correlate
> and join rel nodes like 'LOOKUP'.
>
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
