[SPARK-60873][SQL] Evaluate HAVING before nested window functions - #60090

Blocking (P1): Once foundWindow is true, this fallback accepts any stop point, including a lower Window of the same chain whose child lacks the condition's references. That breaks the all-or-none contract in the method doc above ("Returns None unless the condition can be evaluated below every Window in the chain").

It is reachable from SQL. In a generator query, HAVING is resolved against the post-generator Project, so it can read a SELECT-list window alias:

SELECT explode(array(k)) AS g,
       RANK() OVER (ORDER BY k) AS r,
       COUNT(*) OVER () AS c
FROM VALUES ('a', 1), ('b', 2) AS t(k, v)
GROUP BY k
HAVING r = 1

addWindow stacks Window[c] over Window[r]. The walk passes Window[c], fails the guard at Window[r] (r is not in the Aggregate output), and this case returns Filter(r = 1, Window[r]). COUNT(*) OVER () therefore counts only the surviving row, and the query returns (a, 1, 1). Before this change, and with the legacy flag set to true, the filter stays above both windows and the query returns (a, 1, 2). Listing c before r brings back c = 2, so the value now depends on SELECT-list order. Neither the migration note nor the config doc describes this, and no test covers it.

Suggested fix: return None whenever the walk stops at a Window, or at a Project or Generate that still has a Window below it, so the filter is inserted only once every Window of the chain is above the stop point.

This extends an existing discussion.

Verification:

• Regression: A generator query whose HAVING references the alias of a window computed below another window spec keeps the HAVING filter above both windows and returns the base-branch values, and the same query with the two window expressions listed in the opposite order returns the same values.
• Behavior: Generator queries with nested windows and HAVING on a grouping key still place the HAVING filter directly above the Aggregate and return rank_sum 1.
• Compatibility: With spark.sql.legacy.evaluateHavingAfterInnerWindow set to true, generator-query plans and results are unchanged.
• Inspection: The default-path walk returns no placement whenever a Window would remain below the inserted filter, matching the documented contract that HAVING goes below every Window in the chain or not at all.

Fixed the all-or-none placement. insertFilterBeforeAllWindows now refuses a stop point while any Window remains below the candidate filter. Added generator regressions where HAVING references the lower window alias with both SELECT-list orders, under both config values. In every case the filter remains above the full window chain and group_count is 2.

Non-blocking (P2): This placement runs only when HAVING reaches ExtractWindowExpressions still attached to its Aggregate, which grouping analytics does only for conditions that need type coercion. BaseGroupingSets.resolved is always false, so the UnresolvedHaving case that calls this method cannot match while the Aggregate still has ROLLUP, CUBE or GROUPING SETS. ResolveGroupingAnalytics, which runs earlier in the batch, calls the unchanged tryResolveHavingCondition instead. When the condition resolves without type coercion, that method returns Filter(cond, constructAggregate(...)) while the window expressions are still in the aggregate list. Every window layer is then extracted below that Filter, whatever spark.sql.legacy.evaluateHavingAfterInnerWindow is set to. For example:

SELECT k, SUM(RANK() OVER (ORDER BY SUM(v) DESC)) OVER () AS rank_sum
FROM VALUES ('drop', 2), ('keep', 1) AS input(k, v)
GROUP BY ROLLUP(k)
HAVING k = 'keep'

The windows run over the groups (NULL, 3), ('drop', 2) and ('keep', 1), so the query returns (keep, 6). Evaluating HAVING first gives (keep, 1), which is what the same query with GROUP BY k now returns. The existing GROUPING_ID() = 1 golden lands below its window only because that condition needs coercion and is deferred to this method. The migration note and the config doc state the fix unconditionally, and no test covers this form. A HAVING condition that reads a SELECT-list window alias also resolves on this route and is evaluated after every window; that case is outside this gap.

Recommended change: In ResolveGroupingAnalytics, when spark.sql.legacy.evaluateHavingAfterInnerWindow is false, the aggregate list still contains window expressions, and the resolved HAVING condition reads no window output, keep the resolved HAVING attached to the constructed Aggregate as an unresolved HAVING, instead of turning it into a Filter. The nested-window placement then extracts every window layer first and puts the Filter directly on the Aggregate. A condition that reads a SELECT-list window alias keeps the current Filter above every window. Add window-resolution SQL golden tests for ROLLUP or GROUPING SETS with nested and single-level windows and a HAVING that needs no coercion, plus one whose HAVING reads a window alias, under both flag values, and update the migration note and the config doc to cover grouping analytics.

Why this works: The condition is already resolved and remapped to the constructed Aggregate's output, and that Aggregate's own expressions are resolved. ExtractWindowExpressions' HAVING case therefore matches it in the same analyzer batch, and the nested-window placement builds the window layers over a Filter on the Aggregate. The existing grouping-function and aggregate rewrites still see a Filter directly over an Aggregate. When extra aggregates were pushed for the condition, the existing projection that drops them stays on top. Because this rule resolves HAVING against the Aggregate output, which includes the SELECT-list window aliases, the remapped condition's references are checked against the outputs of the window-bearing aggregate expressions: a condition that reads one keeps the current Filter, since the nested-window placement would put it below the Window that produces that value and analysis would fail with a missing attribute.

Scope: Route grouping-analytics HAVING that reads no window output through the change's nested-window placement, add goldens for that route, and align the documentation.

Compatibility: Grouping-analytics HAVING without window functions in the SELECT list keeps its current plans. A grouping-analytics HAVING that reads a window alias keeps its Filter above every window, and grouping-analytics queries with a generator in the SELECT list never reach this rule, so both keep their current plans.

Risks: Single-level window queries over ROLLUP, CUBE or GROUPING SETS with a HAVING that needs no coercion also change plans and results. HAVING now runs before their window, which corrects the base-branch behavior. The legacy flag rolls this back, and the migration note should mention it.

Constraints: Gate the new routing on spark.sql.legacy.evaluateHavingAfterInnerWindow, so that true keeps the current placement on this route. Keep the HAVING condition resolved against the constructed Aggregate, including the existing attribute remapping and any aggregate expressions pushed for the condition. Do not defer a condition that reads a window output; keep it as a Filter above every window, as today.

Exclusions:

• The single-pass resolver already declines HAVING with grouping analytics and falls back to the fixed-point analyzer, and it stays unchanged.
• Whether a HAVING condition that reads a window alias should be rejected on this route, as it already is for plain GROUP BY, is a separate decision that this repair does not make.

Success: With the legacy flag false, ROLLUP, CUBE and GROUPING SETS queries with window functions and a HAVING condition that resolves without coercion and reads no window output evaluate HAVING below every window layer. With the legacy flag true, those queries keep the base-branch plans and results. A grouping-analytics HAVING condition that reads a SELECT-list window alias keeps its current plan and result under both flag values. The migration note and the config doc accurately describe grouping-analytics queries.

Verification:

• Regression: A ROLLUP or GROUPING SETS query with nested window functions and a HAVING on a grouping key that needs no coercion places the HAVING filter directly above the Aggregate and returns rank_sum 1 by default.
• Compatibility: The same query with spark.sql.legacy.evaluateHavingAfterInnerWindow set to true keeps the base-branch plan and result.
• Behavior: Grouping-analytics HAVING conditions that use grouping functions, aggregates absent from the SELECT list, or a subquery still resolve and keep the right groups when window functions are present.
• Behavior: A single-level window over ROLLUP, CUBE or GROUPING SETS with a HAVING that needs no coercion evaluates HAVING before the window by default.
• Behavior: A ROLLUP or GROUPING SETS query with window functions whose HAVING reads a SELECT-list window alias without needing coercion keeps the HAVING filter above every window and returns the same result as before the change, under both flag values.

Fixed the grouping-analytics path. With spark.sql.legacy.evaluateHavingAfterInnerWindow=false, tryResolveHavingCondition keeps a resolved UnresolvedHaving only when the aggregate contains window outputs and the condition does not reference one of them; ExtractWindowExpressions then places the filter directly above the constructed aggregate. Conditions referencing a window output keep the previous analysis behavior. Added ROLLUP nested-window, GROUPING SETS single-window, window-alias negative, grouping-function, HAVING-only aggregate, and scalar-subquery coverage, plus legacy-true cases. Default results are rank_sum = 1 and group_count = 1; legacy results remain 6 and 3. The config doc and migration guide now cover grouping analytics.

Thanks, the deferral fixes the main cases: by default these conditions now sit directly above the constructed aggregate, below every window layer, and the legacy goldens keep the old plans. This review raises two related gaps: a HAVING condition with a nondeterministic expression still runs after the windows, and the check for window outputs also counts window functions inside SELECT-list subqueries.

Nit (P3): With LEGACY_EVALUATE_HAVING_AFTER_INNER_WINDOW set to true, the recursion guard below fails. The method then extracts only the outermost layer and returns addWindow over Filter(condition, Aggregate(...)), whose aggregate list still holds the inner window expressions. Those are later extracted below the filter, as the new legacy golden shows (Window [sum] +- Filter (k = keep) +- Project +- Project +- Window [rank] +- Aggregate). The doc states the default-branch guarantee unconditionally; it could cover both branches:

Verification:

• Inspection: The addWindowWithHaving Scaladoc states that HAVING goes below every nested window layer only when spark.sql.legacy.evaluateHavingAfterInnerWindow is false, and that with true only the outermost layer is extracted and HAVING can end up above the inner windows.

Updated the Scaladoc to describe both branches: the default extracts every nested window layer before placing HAVING, while the legacy setting extracts only the outermost layer here and allows inner windows to be extracted below the filter later.

Blocking (P1): In default mode this guard checks the stop node's whole subtree, not just the SELECT-list window chain. Tree-pattern bits are unioned over children, expressions, and subquery plans, so any Window below the grouping Aggregate makes the walk return None. That includes a FROM-clause derived table or view with a window function, a windowed join input, and a WHERE subquery that uses one. ResolveUnresolvedHaving then keeps HAVING above every SELECT-list window, and those windows count the groups that HAVING removes. For example:

SELECT explode(array(k)) AS g, COUNT(*) OVER () AS c, k
FROM (SELECT k, v, ROW_NUMBER() OVER (ORDER BY v) AS rn
      FROM VALUES ('drop', 2), ('keep', 1) AS t(k, v)) s
GROUP BY k
HAVING k = 'keep'

This returns (keep, 2, keep) by default. The base branch, and this PR with spark.sql.legacy.evaluateHavingAfterInnerWindow=true, return (keep, 1, keep), as the Spark 4.3 migration note promises for generator queries. With nested SELECT-list windows over such an input, every layer now sees the removed groups. No generator golden reads from a windowed input.

Suggested fix: bound the check to the chain the walk descends through. Accept the stop node unless a Window is reachable from it through Project, Generate, and Window nodes. That still rejects a stop at or above a lower window of the chain, and it no longer looks inside the aggregate's input.

This extends an existing discussion.

Verification:

• Regression: A generator query with COUNT(*) OVER () and a HAVING on the grouping key evaluates HAVING before the window whenever the aggregate's input contains a window function: when it reads from a derived table that uses ROW_NUMBER(), and when its WHERE clause has a subquery that uses a window function. For the ('drop', 2), ('keep', 1) input, with both rows kept by the WHERE clause, the window counts 1, not 2.
• Regression: The same windowed-input shape with nested SELECT-list windows places HAVING below every window layer and returns the corrected nested result.
• Behavior: Generator queries whose HAVING reads a lower window's alias keep HAVING above the whole window chain in both SELECT-list orders.
• Compatibility: With the legacy flag set to true, the windowed-input generator queries keep their base-branch plans and results.

Addressed at the current head. The stop check now follows only the SELECT-list Project/Generate/Window spine, so a window in a derived-table input or WHERE subquery no longer blocks HAVING placement. I added default and legacy goldens for windowed inputs, windowed subqueries, and nested SELECT-list windows over a windowed input.

Non-blocking (P2): This new UnresolvedHaving breaks grouping-analytics queries that use a lateral column alias. ResolveLateralColumnAliasReference deliberately leaves an Aggregate under UnresolvedHaving untouched, and a LateralColumnAliasReference counts as resolved. Later in the same batch, the HAVING case of ExtractWindowExpressions matches this aggregate and throws UNSUPPORTED_FEATURE.LATERAL_COLUMN_ALIAS_IN_AGGREGATE_WITH_WINDOW_AND_HAVING. On the base branch, the Filter parent let the lateral-alias rule lift the aggregate into a Project, and the query analyzed. For example, this query returns the a and b rows on the base branch and now fails by default:

SELECT k, SUM(v) AS s, s * 2 AS s2, SUM(SUM(v)) OVER (PARTITION BY k) AS ps
FROM VALUES ('a', 1), ('b', 2) AS t(k, v)
GROUP BY ROLLUP(k)
HAVING k IS NOT NULL

Its base-branch rows were already correct, because dropping the grand-total group cannot change another partition. The migration note does not mention the new error, and no golden uses a lateral alias on this route.

See Shared repair plan 1 in the review body.

Addressed at the current head. Grouping-analytics deferral now resolves lateral column aliases to a fixed point before wrapping the plan in DeferredHaving. If the lift cannot complete, or the condition uses a grouping function, it retains the existing Filter path. Default and legacy lateral-alias goldens cover the corrected behavior.

Nit (P3): group_count = 3 compares a bigint with an int literal, so it never reaches the new branch in tryResolveHavingCondition. Type coercion skips an operator whose child is the still-unresolved GROUPING SETS aggregate. The condition is therefore unresolved when tryResolveHavingCondition checks it, the method returns early, and the condition takes the older deferred route. That is why this case fails with the same MISSING_ATTRIBUTES error under both flag values. GROUPING_ID() = 1 and SUM(v) = 1 take the same route. The only grouping-analytics cases that reach the branch are k = 'keep' and k = (SELECT 'keep'). No golden would fail if the branch's Filter for a window-output reference, its Project that drops HAVING-only aggregates, or its lateral-alias handling regressed.

This extends existing discussions 1, 2.

See Shared repair plan 1 in the review body.

Addressed. The grouping-analytics cases now use type-matched 3L/1L literals so they reach the new branch in tryResolveHavingCondition. The goldens cover the window-output path, HAVING-only aggregate projection, grouping-function handling, and lateral aliases under both default and legacy settings.

Thanks, the type-matched literals helped: the window-output case now analyzes, the grouping-function filter stays above the window, and the lateral-alias plans change with the flag, so those cases reach the new branch. The HAVING-only aggregate case still does not: HAVING SUM(v) = 1L has the same plan by default and with the legacy flag, so no golden reaches the projection that drops HAVING-only aggregates above the deferred HAVING. This review raises that remaining gap, with its cause and a case that does reach the branch.

Nit (P3): The Scaladoc above this method is unchanged from the base branch. It still describes a single search through Project and Generate nodes for "a Window chain". The method now dispatches on spark.sql.legacy.evaluateHavingAfterInnerWindow. By default, insertFilterBeforeAllWindows also descends through Window nodes and the Projects between window layers, and either places HAVING below every SELECT-list window or leaves it above all of them. With the flag set to true, insertFilterBeforeOuterWindow places it only below the first contiguous run of Window operators, which can put it between two layers. For a nested generator chain, a reader cannot tell which of those two plans the method produces. The Scaladoc could describe both branches, as the addWindowWithHaving Scaladoc now does.

Verification:

• Inspection: The insertFilterBeforeWindow Scaladoc names the legacy flag, describes the placement each value produces, and agrees with the stop check being limited to the window chain.

Addressed. The Scaladoc now documents both dispatch paths: the default path places HAVING below the complete SELECT-list window chain or leaves it above all of it, while the legacy path moves it below only the first contiguous Window run.

Blocking (P1): This case runs in the last analyzer batch, after HandleSpecialCommand. That rule calls checkAnalysis on every resolved analysis-only command and marks its query analyzed, which turns the query into an inner child. ResolveUnresolvedHaving reaches neither inner children nor analyzed subtrees, so a DeferredHaving inside a command's query is never converted, and no optimizer rule or planner strategy handles it.

By the review's reading of the code, with default settings and a v2 catalog testcat:

CREATE TABLE testcat.ns.t AS
SELECT k, COUNT(*) OVER () AS c
FROM VALUES ('drop', 2), ('keep', 1) AS t(k, v)
GROUP BY ROLLUP(k)
HAVING k = 'keep'

CreateTableAsSelectExec creates the table, then writeToTable runs the frozen query through a new QueryExecution whose analysis skips it again, so planning fails with No plan for DeferredHaving, reported as an internal error, and the table is dropped. REPLACE TABLE AS SELECT takes the same path. On the base branch the HAVING is already a Filter when the command is checked, and the statement writes its row.

The same early check also validates the marker as a subquery host: CREATE VIEW, ALTER VIEW ... AS and CACHE TABLE ... AS SELECT of such a query whose HAVING has an IN, EXISTS or correlated scalar subquery fail at creation with UNSUPPORTED_SUBQUERY_EXPRESSION_CATEGORY, although the bare SELECT runs. With spark.sql.legacy.storeAnalyzedPlanForView=true, temporary views and CACHE TABLE ... AS SELECT store the unconverted plan, which fails when read. v1 data source and Hive CTAS are not affected, because they re-analyze their query when they write. No test runs a command over a deferred HAVING.

See Shared repair plan 1 in the review body.

Fixed in b970dec. DeferredHaving now remains unresolved and is placed immediately after ExtractWindowExpressions in the main resolution batch, before HandleSpecialCommand can freeze the query. Added v2 CTAS/RTAS coverage, including a lateral-alias CTAS, plus CREATE/ALTER VIEW and CACHE TABLE cases with HAVING subqueries.

Blocking (P1): The parser puts QUALIFY directly over HAVING, and ResolveQualify runs in the main batch right after window extraction, while the DeferredHaving created here stays until the last batch. Once window extraction resolves the marker's child, ResolveQualify fires: resolveQualifyCondition treats the marker as a leaf, and hasWindowInPlan recognizes only Window, Project and Filter, so it does not see the windows below. A QUALIFY condition without its own window function then throws QUALIFY_REQUIRES_WINDOW_FUNCTION, whose message wrongly says the query has no window function.

By the review's reading of the code, with default settings this query fails analysis:

SELECT k, RANK() OVER (PARTITION BY k ORDER BY SUM(v)) AS rnk
FROM VALUES ('a', 1), ('b', 2) AS input(k, v)
GROUP BY ROLLUP(k)
HAVING k IS NOT NULL
QUALIFY rnk = 1

On the base branch the child is a Filter over the window chain, so it would return (a, 1) and (b, 1), which is also the correct result because each partition holds one group. Queries this PR sets out to fix fail the same way, such as one with COUNT(*) OVER () AS group_count, HAVING k = 'keep' and QUALIFY group_count > 0. Because the leaf case also stops at the marker, a QUALIFY that reads a grouping column outside the SELECT list, which the base branch adds to the aggregate, now stays unresolved. This holds with or without lateral column aliases, and no golden combines grouping analytics, a deferrable HAVING, a window and QUALIFY.

See Shared repair plan 1 in the review body.

Fixed in b970dec. ResolveDeferredHaving now runs immediately after ExtractWindowExpressions and before ResolveQualify, so QUALIFY sees the placed Filter and window chain. Added regressions both without lateral aliases and with lateral aliases plus a grouping column outside the SELECT list.

Non-blocking (P2): Because this marker is a resolved unary node whose output is its child's, PullOutNondeterministic, which runs before the last batch, rewrites it as it rewrites any such node: it moves the nondeterministic expression into a new Project below the marker and makes the condition read the new _nondeterministic attribute. insertFilterBeforeAllWindows then cannot descend past that Project, because the attribute is not in its child's output, and keeps the Filter above every window. By the review's reading of the code, this query would still return group_count = 3, as on the base branch:

SELECT k, COUNT(*) OVER () AS group_count
FROM VALUES ('drop', 2), ('keep', 1) AS input(k, v)
GROUP BY ROLLUP(k)
HAVING k = 'keep' AND rand() < 2D

Without rand(), or with GROUP BY k, it would return 1. The migration note and the config doc say such conditions now run before the windows, and the PR defers only grouping functions. Plain GROUP BY and generator queries are not affected, because PullOutNondeterministic leaves a Filter and an unresolved node alone. No test covers a nondeterministic HAVING on this route.

This extends an existing discussion.

See Shared repair plan 1 in the review body.

Fixed in b970dec. The deferred marker is converted to a Filter in the main resolution batch before PullOutNondeterministic runs, so the seeded RAND predicate remains below the SELECT-list window chain. Added default and legacy golden coverage.

Blocking (P1): containsPattern(WINDOW_EXPRESSION) reads tree-pattern bits, and a subquery expression's bits include its plan's, so a SELECT-list subquery that uses a window function counts as a window output here. Window extraction and Aggregate.resolved walk only the expression tree and treat that item as an ordinary expression. By the review's reading of the code, two effects follow.

When such a subquery is the only window-bearing item, HAVING is deferred over an aggregate that no window extraction touches, so DeferredHaving sits directly on the resolved Aggregate until the last batch. ResolveReferencesInSort and ResolveAggregateFunctions resolve ORDER BY aggregates only under an Aggregate or a Filter over one, so this query, which analyzed on the base branch, now fails with an unresolved column:

SELECT k,
  (SELECT MAX(rn) FROM (SELECT ROW_NUMBER() OVER (ORDER BY x) AS rn
                        FROM VALUES (1) AS q(x))) AS sq
FROM VALUES ('drop', 2), ('keep', 1) AS input(k, v)
GROUP BY ROLLUP(k)
HAVING k = 'keep'
ORDER BY SUM(v)

Such queries also lose the UpdateOuterReferences rewrite of outer aggregate references in HAVING subqueries, and they reach the command failure this review raises separately.

When the HAVING reads such an alias, for example HAVING sq = 1 AND k = 'keep' next to COUNT(*) OVER () AS group_count, the condition counts as reading a window output and stays above every real window, so group_count would still be 3 instead of the 1 that GROUP BY k gives, although the migration note and the config doc say such conditions now run first.

This extends an existing discussion.

Verification:

• Regression: By default, the ROLLUP query whose only window is inside a SELECT-list subquery, with HAVING k = 'keep' and ORDER BY SUM(v), analyzes and returns the keep row with the filter directly over the aggregate.
• Regression: By default, the ROLLUP query with COUNT(*) OVER () AS group_count and HAVING sq = 1 AND k = 'keep', where sq is a windowed subquery alias, returns group_count 1, as GROUP BY k does, and with the legacy flag it returns the base-branch value 3.
• Compatibility: A grouping-analytics HAVING that reads a real SELECT-list window alias keeps the filter above every window under both flag values.

Fixed in b970dec. Window-output classification now uses expression-only traversal (expr.exists(_.isInstanceOf[WindowExpression])), which does not descend into subquery plans. Added both requested regressions: a subquery-only window with ORDER BY SUM(v), and a subquery alias alongside a real window with default/legacy results.

Nit (P3): With 1L this golden still has the same analyzed plan by default and with the legacy flag: Window over Project [k] over Filter (sum(v) = 1) over the Aggregate, which is the older route. When tryResolveHavingCondition first sees this condition, v only becomes a TempResolvedColumn inside that method and sum is resolved later in the same pass, so the condition is unresolved and the method returns early; by then the ROLLUP aggregate has been built, and the HAVING goes through ExtractWindowExpressions and ResolveAggregateFunctions instead. No other golden adds a HAVING-only aggregate on this route, so the Project that drops HAVING-only aggregates above DeferredHaving never runs in a test, and a regression there would go unnoticed. A HAVING-only aggregate whose arguments are literals, such as HAVING COUNT(*) = 1L, is already resolved at that point and would reach it.

This extends an existing discussion.

Verification:

• Regression: A grouping-analytics query whose HAVING reads only an aggregate that is absent from the SELECT list and already resolved before the aggregate is built has different default and legacy plans and results: by default the window counts only the surviving groups and the output omits the HAVING-only aggregate.

Fixed in b970dec. The HAVING-only aggregate case now uses HAVING COUNT(*) = 1L with data that distinguishes the surviving groups, so it reaches the deferred branch and exercises the projection that drops the extra aggregate. Default and legacy goldens differ as expected.

Nit (P3): These cases use one alias level (SUM(v) AS s, s * 2 AS s2) and a HAVING on k. resolveLateralColumnAliases converges for them after two applications of ResolveLateralColumnAliasReference, while a chained alias such as s2 + 1 AS s3 needs a third, because the first unwrap leaves the reference to s2 for a later round. And because the condition reads k, the placement walk always ends at the Aggregate, so its terminal case on a lifted-alias Project, and the Project and Generate branches of hasWindowInWindowChain, never run. By the review's reading of the code, a lift capped at two rounds, or a terminal accepted only on an Aggregate, would leave every new golden unchanged, yet a query that adds s2 + 1 AS s3 with HAVING k = 'keep', or one with HAVING s = 1L, would fall back to HAVING above the window and count the removed groups (group_count 3 instead of 1). The literal must be 1L: with an int literal the condition needs a cast and takes the older route.

Verification:

• Regression: A grouping-analytics query whose lateral aliases chain through three levels evaluates a grouping-key HAVING below the window by default and counts only the surviving groups.
• Regression: A grouping-analytics query with lateral aliases whose HAVING reads a lifted aggregate alias places the filter directly above the Project that defines the alias, below the window, by default, and keeps it above the window with the legacy flag.

Fixed in b970dec. Added a third lateral-alias level (s3) to exercise fixed-point lifting and a separate HAVING s = 1L case to exercise placement on the lifted-alias Project. Both default and legacy plans/results are covered.

Nit (P3): This block starts between the two queries of the existing section headed -- Nested windows over an aggregate, and in QUALIFY together with HAVING. at line 100. The QUALIFY-with-HAVING query that comment describes, which directly followed the section's first query on the base branch, now sits after RESET spark.sql.legacy.evaluateHavingAfterInnerWindow at line 323 with no comment of its own, and the regenerated analyzer and result goldens carry the same order. Moving the new tests after that query, in their own section, and regenerating the goldens restores it.

Verification:

• Inspection: The section comment about nested windows and QUALIFY with HAVING is followed by both of its queries, and the regenerated golden files differ from the current ones only in order.

Fixed in b970dec. Restored the existing QUALIFY-with-HAVING query directly under its section header, moved the new HAVING cases after that section, and regenerated the execution and analyzer goldens.