You are viewing a plain text version of this content. The canonical link for it is here.
Posted to issues@flink.apache.org by "Jianhui Dong (Jira)" <ji...@apache.org> on 2022/12/13 08:42:00 UTC
[jira] [Updated] (FLINK-30396) sql hint 'LOOKUP' which is defined in outer query block may take effect in inner query block
[ 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' can ,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 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' can ,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 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' can ,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 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)