UNION ALL matches branch columns BY NAME, not by position: column 2 comes back NULL when the branches name their columns differently

[fupelaqu]

Summary

A UNION ALL matches its branches' columns by name instead of by position, on every
execution route. SQL-92 7.10 says the result's column NAMES come from the first branch and the
branches are matched positionally; the engine instead looks each of the first branch's names up
in the leg's row, null-fills what it does not find, and appends the rest.

So a branch that projects the right shape with different names loses its values. HTTP 200
throughout. Same family as #205 / #209 / #253: the query runs, returns rows, and the rows are wrong.

Mechanism: multiSearch hands requests.head's output names to parseResponseTree, which applies
ElasticConversion.rowNormalizer - a by-name lookup. SearchApi.unionAllRowNormalizer applies the
same function on the per-leg and licensed-cap routes, so all three routes share the behaviour.

Reproduction (measured on real Elasticsearch 8.18)

Two indices, mapping { id: keyword, category: keyword, tag: keyword, amount: integer }.

1. Same column names, different positions - the guards ADMIT it and column 2 is NULL

SELECT a, b FROM x UNION ALL SELECT a, a FROM y

Branch arity matches and the types match, so neither parse-time guard rejects it. Branch 2's rows
come back (category, R_CAT_n), (tag, null) - column 2 is null where SQL says it holds a's value.
Reproduced on all three routes (one-shot _msearch, per-leg, licensed cap).

2. An alias per branch - the most visible instance, and the first branch is affected too

SELECT id AS x FROM rc_left LIMIT 5 UNION ALL SELECT id AS y FROM rc_right LIMIT 5

Fixture: 1 shard / 0 replicas, two documents each, L_id_1, L_id_2, R_id_1, R_id_2.
Measured at b7f40955 (the merge of #352, before story 22.6):

spelling	rows
LIMIT 5 on both legs (one-shot _msearch)	{x -> null, y -> L_id_1} {x -> null, y -> L_id_2} {x -> null, y -> R_id_1} {x -> null, y -> R_id_2}
no LIMIT (also one-shot at b7f40955)	identical

Note branch 1's own rows: the column the analyst asked for is null, and its value appears under
the other branch's alias.

3. A duplicate output name lets a leg's extra columns through

SELECT amount, amount FROM bt_a UNION ALL SELECT amount, MAX(amount) AS m FROM bt_b

A duplicate requested name sends rowNormalizer down its legacy per-row path with a requested-name
SET, so the leg's other columns - an internal aggregate key max#m included - survive into
user-visible rows, and the routes disagree:

cap route      List(amount) ; List(amount, m, max#m)
per-leg route  List(amount) ; List(amount, m, max#m)
one-shot       List(amount) ; List(amount, max#m)

Status and scope

• Pre-existing - case 2 is reproduced at b7f40955 above, and nothing in story 22.6 changed the
  one-shot path.
• Where the branches agree on column names, which is the overwhelmingly common case and every
  shape any captured BI workload emits, every route agrees and is correct.
• Story 22.6 gave the per-leg and licensed-cap routes the same normaliser the one-shot route already
  used, so the three routes now agree except on case 2's first branch (the one-shot route is the
  wrong one there) and case 3. Making them agree by copying the one-shot behaviour would have meant
  shipping a wrong answer on purpose, so it was deliberately not done.

Suggested fix

Match branches POSITIONALLY, as SQL-92 does, with the first branch supplying the names - i.e. apply
each leg's own output-name mapping first and then zip positionally against the first branch's names,
rather than looking the first branch's names up in a leg that never used them. The duplicate-name
case falls out of the same change.

This touches the ES-native UNION ALL fast path (epic 22 AD-6: "one _msearch when every leg is
bounded, byte-for-byte today's request") and the shared row contract all three routes go through, so
it wants its own change with its own real-Elasticsearch coverage across the three routes.

[fupelaqu]

The standard confirms positional matching — and names the trap for whoever implements it

Verified against the SQL standard and the major engines, because the fix direction depends on it.

SQL-92 defines set operators as UNION [ALL] [CORRESPONDING [BY (col, …)]]. That optional
CORRESPONDING clause is the name-based matching mode, and its existence in the grammar is the
proof that positional is the default — nobody adds an opt-in for the behaviour they already have.
Without CORRESPONDING the rules are:

• both operands must have the same degree (column count);
• the i-th column of one must be type-compatible with the i-th column of the other;
• column names play no part in the matching at all.

Result naming is the looser part: where the i-th names agree, that name is used; where they differ
the standard is vague, and in practice every engine takes the first branch's names. That is
exactly what this issue already proposes, so the framing here is correct.

Almost nobody implements CORRESPONDING — PostgreSQL and MySQL do not advertise it. DuckDB, our own
engine underneath, went the other way and added a non-standard UNION BY NAME as an explicit
opt-in. Again: opt-in, because positional is the baseline.

🔴 The trap: "positional" must mean the DECLARED projection, never the row map's order

Elasticsearch returns documents as JSON objects — name→value maps, not ordered tuples. The
engine's natural unit is therefore a map, which is precisely why by-name matching crept in: it is the
path of least resistance from the wire format.

Story 22.6 hit this and it cost a review round. Its first attempt at a positional re-key derived the
position from the row's own key enumeration (row.keys.toSeq) rather than from the branch's declared
select list. Measured on real ES 8.18 with self-identifying values:

SELECT category, tag FROM l UNION ALL SELECT tag, category FROM r

  one-shot route   {category -> R_CAT_23, tag -> R_TAG_23}   correct
  the positional
  re-key as built  {category -> R_TAG_23, tag -> R_CAT_23}   values under the WRONG names

HTTP 200, no error, and strictly harder to detect than the raggedness it replaced — because the
column names looked right. A second shape was worse still: with SELECT * as one branch,
extractOutputFieldNames returns empty, the leg's rows arrive in _source order, and a positional
zip put the id under category and silently dropped two columns.

So the rule for whoever takes this issue:

Position means "index into the branch's declared projection", not "index into whatever order the
row map happens to enumerate". Same word, two mechanisms, only one of them correct.

An opaque SELECT * branch has no declared projection at all, so it cannot be matched positionally
without first resolving its columns from the mapping or from data — that case needs its own decision,
not a zip.

Cases this issue covers, restated against the standard

statement	standard says	engine returns today
SELECT a, b FROM x UNION ALL SELECT a, a FROM y	col 2 = branch 2's second a	col 2 = NULL (branch 2 has no b)
SELECT id AS x FROM l UNION ALL SELECT id AS y FROM r	one column x, both branches' ids	{x -> null, y -> …} — wrong even for branch 1's own rows

The second is the more serious: branch 1 is the branch that supplies the names, and its own rows come
back under the wrong key with a NULL beside them.

References

• SQL-92 BNF, <corresponding spec>: https://ronsavage.github.io/SQL/sql-92.bnf.html
• PostgreSQL, Combining Queries: https://www.postgresql.org/docs/current/queries-union.html
• MySQL, Set Operations with UNION/INTERSECT/EXCEPT: https://dev.mysql.com/doc/refman/8.0/en/set-operations.html