You are viewing a plain text version of this content. The canonical link for it is here.
Posted to issues@flink.apache.org by "lincoln lee (Jira)" <ji...@apache.org> on 2023/02/27 06:48:00 UTC
[jira] [Comment Edited] (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: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)