alexandrusoare commented on code in PR #44650:
URL: https://github.com/apache/superset/pull/44650#discussion_r4120512774
##########
superset/sql/parse.py:
##########
@@ -2802,6 +2830,87 @@ def resolve(scope: Scope, seen: frozenset[int]) ->
list[exp.Table]:
)
+def _find_subquery_scopes(scopes: list[Scope]) -> set[int]:
+ """
+ Find the scopes whose rows only reach a statement through a sub-query.
+
+ That is every uncorrelated ``SUBQUERY`` scope (a scalar, ``IN`` or
``EXISTS``
+ sub-query), every scope nested inside one, and every CTE one of them reads
from,
+ including the scopes nested inside that CTE. Only a CTE named in the
sub-query's
+ own ``FROM`` or joins counts, not every CTE in lexical scope (which
+ ``Scope.sources`` holds), so a CTE only joined in the main ``FROM`` keeps
the
+ outer query's rules. A CTE read both from a sub-query and
+ from the statement's ``FROM`` counts as a sub-query, so its reads get the
stricter
+ rules. The body of a ``LATERAL`` or ``CROSS APPLY`` feeds the output like
a join,
+ so it is left out. A correlated sub-query is left out too: it is typically
a
+ lookup keyed to the enclosing rows, often over a table without the rule's
+ columns, which the rules would break the same way they would break a join.
The
+ outer query doesn't scope such a sub-query's tables either (UPDATING.md).
+
+ :param scopes: The scopes of the statement, as returned by
``traverse_scope``
+ :returns: The ``id`` of each scope found
+ """
+ found: set[int] = set()
+ pending = [
+ scope
+ for scope in scopes
+ if scope.scope_type == ScopeType.SUBQUERY
+ and not (scope.parent and scope.parent.scope_type == ScopeType.UDTF)
+ and not _is_correlated(scope)
+ ]
+ while pending:
+ scope = pending.pop()
+ if id(scope) in found:
+ continue
+ found.add(id(scope))
+ pending.extend(child for child in scopes if child.parent is scope)
+ pending.extend(
+ source
+ for _, source in scope.selected_sources.values()
+ if isinstance(source, Scope) and source.scope_type == ScopeType.CTE
+ )
+ return found
+
+
+def _is_correlated(scope: Scope) -> bool:
+ """
+ Does a sub-query reference a table of an enclosing query?
+
+ Only a column qualified with an enclosing table's name or alias counts,
when the
+ sub-query has no table of its own under that name. An unqualified column
can't
+ be told apart from one of the sub-query's own, so it is treated as local,
which
+ errs toward the sub-query getting the stricter rules. (``Scope``'s own
+ ``is_correlated_subquery`` treats every unqualified column as external.)
+
+ Only the sub-query's own columns count, not those of a sub-query nested in
it
+ (which ``Scope.columns`` includes): a nested correlated sub-query doesn't
key the
+ wrapping sub-query's tables to the enclosing rows.
+
+ Names are the ones each query reads in its ``FROM`` and joins
+ (``Scope.selected_sources``), not every CTE in lexical scope, so a
reference to
+ a CTE the enclosing query reads counts as external. They are compared
ignoring
+ letter-case, since most engines fold unquoted names. On one that doesn't, a
+ qualifier matching only when case is ignored either names one of the
+ sub-query's own tables, which errs toward the stricter rules, or names no
table
+ at all and the engine rejects the query.
+
+ :param scope: A ``SUBQUERY`` scope
+ :returns: True if the sub-query is correlated
+ """
+ enclosing: set[str] = set()
+ parent = scope.parent
+ while parent:
+ enclosing.update(name.lower() for name in parent.selected_sources)
+ parent = parent.parent
+ local = {name.lower() for name in scope.selected_sources}
+ return any(
+ isinstance(node, exp.Column)
+ and node.table.lower() in enclosing
Review Comment:
An unaliased source in the outer query makes any sub-query with an
unqualified column look correlated — `node.table` and the source's key are both
`''` — so it loses the rules.
--
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]
