[ https://issues.apache.org/jira/browse/FLINK-30396?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17693587#comment-17693587 ]
lincoln lee edited comment on FLINK-30396 at 2/27/23 6:47 AM: -------------------------------------------------------------- fixed in master: a72f88f36505e1ce45f2280584a11385c2c2bc14 fixed in release-1.17: 5ee98b544d936e8cde2d4429425da46185031bb3 was (Author: lincoln.86xy): fixed in master: a72f88f36505e1ce45f2280584a11385c2c2bc14 > 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 > Assignee: Jianhui Dong > Priority: Major > Labels: pull-request-available > > 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: > {noformat} > { > [LOOKUP inheritPath:[0] options:{table=inner}], > [LOOKUP inheritPath:[0, 0, 0] options:{table=outer}] > }{noformat} > 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)