Rex Johnston 060012b3b7 MDEV-41225 Parse the ORDER BY tail of a wrapped parenthesized query in the wrapping select ...

A parenthesized query expression that already has its own ORDER BY or
LIMIT and is followed by another ORDER BY, e.g.

  (SELECT c1 FROM t1 ORDER BY c1 LIMIT 2) ORDER BY <expr> LIMIT 1

is wrapped into a derived table, and the outer ORDER BY/LIMIT is attached
to the wrapping select.  The grammar pushed the inner select before
parsing the tail, and add_tail_to_query_expression_body_ext_parens()
created the wrapper only after the tail had been parsed.  Anything that
is attached to the current select while the tail is being parsed was
therefore attached to the inner select, while the ORDER BY list itself
was moved to the wrapper:

- Window functions and their window specs were added to the inner
  select's window_funcs/window_specs.  The same Item_window_func was then
  in the wrapper's ORDER BY and in the inner select's window function
  list, which triggers the original assertion reported in MDEV-41225.

  (SELECT 1 FROM t1 LIMIT 1)
    ORDER BY PERCENTILE_DISC(1) WITHIN GROUP(ORDER BY TIME'0') OVER();

  The function is computed by the inner select over the rows before its
  LIMIT, so the outer ORDER BY sorts on wrong values
  (e.g. COUNT(*) OVER () returned 3 for a 2-row result).

- Subqueries were registered as units of the inner select.  An
  uncorrelated subquery in the tail crashed the server with SIGSEGV when
  it was executed by the outer filesort.  A correlated one resolved its
  outer references in the inner select (t1.c1) instead of the derived
  table.

The decision to wrap depends only on whether the inner select already
has a tail and on whether the outer tail has ORDER BY.  LIMIT and locking
clauses cannot contain window functions or subqueries.
We replace query_expression_tail with
query_expression_tail_with_order and query_expression_tail_no_order
so the mid-rule action knows whether an ORDER BY follows.
The new LEX::push_select_for_ext_parens_tail() creates the wrapper before
an ORDER BY tail is parsed when wrapping is required, and pushes it instead
of the inner select.  The tail's items are then created in the wrapper's
context, and window functions and subqueries are attached to the wrapper.
add_tail_to_query_expression_body_ext_parens() takes the pushed select and
skips wrapping when it has already happened; the other cases keep the
previous logic.  The grammar has the same number of conflicts as before.

A subquery in such a tail can no longer refer to the tables of the wrapped
query expression (e.g. t1.c1 above) and gets ER_BAD_FIELD_ERROR,
as those tables are not visible outside the derived table.

This commit was prepared with Claude Code (Opus 5.5). It traced the
wrong-result and crash cases to the parse-time registration of window
functions and subqueries in the inner select, tested the grammar split
with bison, wrote the code change and the brackets test.

2026-10-07 13:30:32 +13:00

364 KiB

Raw Permalink Blame History

View Raw