Pushdown projection below NestedLoopJoinExec

[jayzhan211]

          A solid example of what I am looking for is, these plans will project a&c (first CsvExec), and b only (second CsvExec). Embedding the projection into CsvExec is already done, we just need to pushdown the projection below NestedLoopJoinExec

Originally posted by @berkaysynnada in #14120 (comment)

Current optimization is like

        let expected = [
            "NestedLoopJoinExec: join_type=Inner, filter=a@0 < b@1, projection=[c@2]",
            "  CsvExec: file_groups={1 group: [[x]]}, projection=[a, b, c, d, e], has_header=false",
            "  CsvExec: file_groups={1 group: [[x]]}, projection=[a, b, c, d, e], has_header=false",
        ];

where we could further pushdown projection to CscExec

        let expected = [
            "NestedLoopJoinExec: join_type=Inner, filter=a@0 < b@1, projection=[c@2]",
            "  CsvExec: file_groups={1 group: [[x]]}, projection=[a, c], has_header=false",
            "  CsvExec: file_groups={1 group: [[x]]}, projection=[b], has_header=false",
        ];

Since we only requires a, c from left side and b from right side

[berkaysynnada]

It's root cause is actually not CsvExec, it is NestedLoopJoinExec (projections can be embedded into CsvExec seamlessly). Moreover, the issue can be generalized as "improve projection pushdown logic on operators having built-in projection.

Giving more details, when a projection has an input having a built-in projection, it is either pushed-down over it through the children, or refine its built-in projection. However, these can happen at the same time, and the example in the issue description is a good reproducer of it.

[Rachelint]

take

[alamb]

Now that we have DataSouceExec is this still an issue?

[Dandandan]

I took the liberty to edit the title according what @berkaysynnada shared.
It seems it could use a test to show the embedded projection from NestedLoopJoinExec isn't pushed down.