SanthoshRaaj-KR commented on issue #25548:
URL: https://github.com/apache/datafusion/issues/25548#issuecomment-5751332448
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.
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: [email protected]
For queries about this service, please contact Infrastructure at:
[email protected]
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
