Unparsing of filter depending on projection generates invalid SQL

[nuno-faria]

Describe the bug

When a filter is based on a projected expression, the unparsing of the optimized plan can generate invalid SQL.

To Reproduce

The filter in the query below depends on a newly generated column

select *
from (
    select random() as x
    from t
)
where x = 1

which is optimized into this plan

Filter: x = Float64(1)
  Projection: random() AS x
    TableScan: t projection=[]

The unparsing of this plan puts the filter and the projection at the same level, which is invalid:

SELECT random() AS x FROM t WHERE (x = 1.0) -- Schema error: No field named x. Valid fields are t.a.

Expected behavior

No response

Additional context

No response

[SanthoshRaaj-KR]

I traced the issue to derived_input_scope() in
datafusion/sql/src/unparser/plan.rs.

The function decides whether a Projection under a Filter needs to be
rendered as a nested derived subquery instead of being flattened into a
single SELECT.

At the moment, the Projection branch only forces this split when the
projection is "qualified", for example when it references a table-qualified
column such as t1.a. That handles the usual ambiguous-column case.

The missing case is when the projection creates an alias for a computed
expression, such as random() AS x, and the Filter above it refers to that
alias.

Because random() has no column references, qualified_projection evaluates
to false, causing derived_input_scope() to return None. The filter is
then flattened into the same SELECT that contains random() AS x, resulting
in invalid SQL:

SELECT random() AS x FROM t WHERE (x = 1.0)

The WHERE clause cannot reference a SELECT-list alias at the same query
level.

#25549 already addresses this case correctly. It adds
filter_depends_on_input_alias() to detect when a filter predicate depends on
an alias defined by the projection immediately below it. It then uses the same
derive()-based subquery split that is already used for qualified projections.

It also includes a regression test,
optimized_filter_after_projection, covering pushable, non-pushable, and mixed
cases.

Since #25549 already contains the relevant fix and regression coverage, I think
it would be better to review and merge that change rather than re-implementing
the fix separately.