[FLINK-33395] The join hint doesn't work when appears in subquery

• Type: Bug
• Status: Closed
• Priority: Major
• Resolution: Fixed
• Affects Version/s: 1.16.0, 1.17.0, 1.18.0
• Fix Version/s: 1.19.0, 1.18.1, 1.17.3
• Component/s: Table SQL / Planner
• Labels: pull-request-available
• Assignee: xuyang
• Reporter: xuyang
• Created: 30/Oct/23 09:12
• Updated: 29/Nov/23 03:43
• Resolved: 17/Nov/23 02:15

Description

See the existent test 'NestLoopJoinHintTest#testJoinHintWithJoinHintInCorrelateAndWithAgg', the test plan is 

HashJoin(joinType=[LeftSemiJoin], where=[=(a1, EXPR$0)], select=[a1, b1], build=[right], tryDistinctBuildRow=[true])
:- Exchange(distribution=[hash[a1]])
:  +- TableSourceScan(table=[[default_catalog, default_database, T1]], fields=[a1, b1])
+- Exchange(distribution=[hash[EXPR$0]])
   +- LocalHashAggregate(groupBy=[EXPR$0], select=[EXPR$0])
      +- Calc(select=[EXPR$0])
         +- HashAggregate(isMerge=[true], groupBy=[a1], select=[a1, Final_COUNT(count$0) AS EXPR$0])
            +- Exchange(distribution=[hash[a1]])
               +- LocalHashAggregate(groupBy=[a1], select=[a1, Partial_COUNT(a2) AS count$0])
                  +- NestedLoopJoin(joinType=[InnerJoin], where=[=(a2, a1)], select=[a2, a1], build=[right])
                     :- TableSourceScan(table=[[default_catalog, default_database, T2, project=[a2], metadata=[]]], fields=[a2], hints=[[[ALIAS options:[T2]]]])
                     +- Exchange(distribution=[broadcast])
                        +- TableSourceScan(table=[[default_catalog, default_database, T1, project=[a1], metadata=[]]], fields=[a1], hints=[[[ALIAS options:[T1]]]]) 

but the NestedLoopJoin should broadcase left side.

Issue Links

• relates to: FLINK-33397 FLIP-373: Support Configuring Different State TTLs using SQL Hint
• links to: GitHub Pull Request #23620
• links to: GitHub Pull Request #23716
• links to: GitHub Pull Request #23720