[SPARK-36191] Support ORDER BY and LIMIT to be on the correlation path

• Type: Sub-task
• Status: Resolved
• Priority: Major
• Resolution: Fixed
• Affects Version/s: 3.2.0
• Fix Version/s: 4.0.0
• Component/s: SQL
• Labels: pull-request-available
• Assignee: Allison Wang
• Reporter: Allison Wang
• Created: 16/Jul/21 23:00
• Updated: 21/Mar/24 06:29
• Resolved: 14/Sep/23 01:32

Description

A correlation path is defined as the sub-tree of all the operators that are on the path from the operator hosting the correlated expressions up to the operator producing the correlated values. 

We want to support ORDER BY (Sort) and LIMT operators to be on the correlation path to achieve better feature parity with Postgres. Here is an example query in `postgreSQL/join.sql`:

select * from
  text_tbl t1
  left join int8_tbl i8
  on i8.q2 = 123,
  lateral (select i8.q1, t2.f1 from text_tbl t2 limit 1) as ss
where t1.f1 = ss.f1;

Issue Links

• links to: GitHub Pull Request #42705