[SPARK-48416][SQL] Support related nested WITH expression

[zml1206]

What changes were proposed in this pull request?

Expression definitions should apply to the entire With child contains internally nested WITH, so refactor RewriteWithExpression logic. Rewrite With consists of two steps:

1. Convert defs into Project of inputPlans
2. Replace the references in inputPlans and child then output the new child

With support nestd, defs and child can all contain sub-With, the Alias generated by the current With acts on the child and the child's internal With, so generate Project order is：

1. internally nested with of main expression definitions
2. main expression
3. internally nested with of main expression

Why are the changes needed?

Optimize WITH nested to apply to more scenarios, such as push down predicate.

Does this PR introduce any user-facing change?

No.

How was this patch tested?

Unit test.

Was this patch authored or co-authored using generative AI tooling?

No.

[cloud-fan]

Can we solve this issue by recursion? Which is easier to understand.

[zml1206]

Can we solve this issue by recursion? Which is easier to understand.

rewriteWithExprAndInputPlans was originally by recursion, so I optimized the code and comments for easy to understand, please take a look again, thank you.

[holdenk]

CC @cloud-fan

[github-actions[bot]]

We're closing this PR because it hasn't been updated in a while. This isn't a judgement on the merit of the PR in any way. It's just a way of keeping the PR queue manageable. If you'd like to revive this PR, please reopen it and ask a committer to remove the Stale tag!

[cloud-fan]

Sorry for the late review. I think the implementation can be much simpler, please see my attempt at https://github.com/apache/spark/pull/49093