[FLINK-35037] Optimize uniqueKeys and upsertKeys inference of windows with ROW_NUMBER

• Type: Improvement
• Status: Open
• Priority: Major
• Resolution: Unresolved
• Affects Version/s: 1.20.0
• Component/s: Table SQL / Planner
• Labels: pull-request-available
• Assignee: yisha zhou
• Reporter: yisha zhou
• Created: 07/Apr/24 12:21
• Updated: 21/May/24 04:02

Description

In current Implementation, relNodes with Window type will only deliver upsert/unique keys of their inputs.

However windows with ROW_NUMBER can also produce upsert/unique keys.

For example:

select id, name, score, age, class,
    row_number() over(partition by class order by name) as rn,
    rank() over (partition by class order by score) as rk,
    dense_rank() over (partition by class order by score) as drk,
    avg(score) over (partition by class order by score) as avg_score,
    max(score) over (partition by age) as max_score,
    count(id) over (partition by age) as cnt
from student 

(class, rn) is a valid upsert/unique keys candidate. 

Issue Links

• links to: GitHub Pull Request #24638