[jira] [Created] (FLINK-30396) sql hint 'LOOKUP' which is defined in outer query block may take effect in inner query block

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

Jianhui Dong created FLINK-30396:
------------------------------------

             Summary: 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


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)