CalvinKirs opened a new pull request, #68260:
URL: https://github.com/apache/doris/pull/68260
Follow-up of #67438.
## The problem
A multipart name is either a relation-qualified column (`t.col`) or a nested
field reference (`col.field`). When a select output alias has the same name as
a relation alias, a legal relation-qualified reference fails:
```sql
SELECT q.v AS q FROM (SELECT 7 AS v) q ORDER BY q.v;
-- No such field 'v' in 'q'
SELECT q.v FROM (SELECT 7 AS v) q ORDER BY q.v; -- ok, 7
SELECT q.v AS q FROM (SELECT 7 AS v) q ORDER BY v; -- ok, 7
```
ORDER BY, HAVING and QUALIFY do not bind against one scope. They bind
against layered local scopes, for ORDER BY the select output first and then its
child output. #67438 added a pass that tries the relation-qualified reading in
every visible scope before any `col.field` reading, and threaded it through all
these layered binders, but the pass only ran when a correlated outer scope
existed. At the top level the nearer output alias `q` was taken as the first
part of `col.field`, and because `q` is a scalar the lookup threw before the
child output was ever tried. The same HAVING inside a subquery already worked,
which is what pointed at the gate.
It is worse when the alias is a struct, map or variant: nothing fails, `q.v`
silently binds to `element_at(alias q, 'v')` and the query returns wrong
results.
Shapes that were broken at the top level (all fixed here):
| Clause | Example | Before |
|---|---|---|
| ORDER BY | `select q.v as q from t q order by q.v` (also with an
expression, `DISTINCT`, `LIMIT`, a join, inside an `IN` subquery, `db.tbl.col`)
| No such field |
| ORDER BY over aggregate | `select max(q.v) as q from t q group by q.id
order by q.id` / `... order by max(q.v)` | No such field |
| HAVING without aggregate | `select q.v as q from t q having q.v > 0` | No
such field |
| HAVING / QUALIFY after `GROUP BY <expr>` | `select q.id + 1 as q from t q
group by q.id + 1 having q.id + 1 > 0` | No such field |
| struct / map / variant alias | `select q.s as q from t q order by q.v`
(`s` is `struct<v:int>`, `t` has a column `v`) | **wrong result**, sorted by
`s.v` |
While auditing the same family, one more bypass of the layered binders
showed up, and it needs no alias at all: a **lambda body** inside these clauses
resolved names against the default scope of the enclosing analyzer only, so it
could not see the child output.
```sql
SELECT id FROM t ORDER BY array_sum(array_map(x -> x + v, arr));
-- Unknown lambda slot 'v in lambda arguments[x] (the same expression
works in WHERE)
```
## The fix
Both changes are in `ExpressionAnalyzer`, no new mechanism is added.
1. `visitUnboundSlot`: run the relation-qualified pass over the local scopes
for **every** multipart name. Only the lookup in the outer scope stays
conditional on a correlated subquery. For an analyzer with a single scope the
result is identical to before, the pass is just the first stage of the normal
lookup.
2. Lambda analyzer: bind the lambda arguments first as the nearest lexical
scope, and delegate every other name to the enclosing analyzer, so a lambda
body resolves names exactly like the clause around it. A nested lambda
delegates to the lambda around it.
## Binding priority after this PR
For every name:
1. Multipart name only: try the **relation-qualified** reading (`t.col`,
`db.t.col`, `ctl.db.t.col`) through the local scope chain of the clause (table
below). If nothing matches, the clause is in a correlated subquery, and no
local relation has that name, try it in the outer scope.
2. Full reading through the same local scope chain. Inside one scope:
`ctl.db.t.col` → `db.t.col` → `t.col` → `col.field`.
3. Full reading in the outer scope (one level up), if the clause may bind it.
4. 0 matches → error, 1 → bound, more → the exact qualifier match if the
clause enables it, otherwise ambiguous.
Local scope chain per clause (near → far). Only step 1 is new for the top
level, the chains themselves are unchanged:
| Clause | Local scope chain | Outer scope |
|---|---|---|
| SELECT list, WHERE, JOIN ON, aggregate output, window, LATERAL VIEW |
child output | yes |
| GROUP BY | child output (exactly one match) → aggregate output alias,
replaced by its expression | yes |
| HAVING without aggregate | select output → its child output | yes |
| HAVING over aggregate, outside an aggregate function | group by slots →
aggregate output → aggregate child | yes |
| HAVING over aggregate, inside an aggregate function | aggregate child |
yes |
| QUALIFY over project | project child output → project output | yes |
| QUALIFY over aggregate | group by slots → aggregate output → aggregate
child | yes |
| ORDER BY | select output → its child output (skips QUALIFY, HAVING,
DISTINCT project over aggregate); an integer literal is an ordinal | no |
| ORDER BY, key contains an aggregate function | aggregate output without
aggregate functions → child output | no |
| ORDER BY over a set operation | set operation output | yes |
| Lambda body | lambda arguments → **the enclosing clause, by the rules
above** (new) | as the clause |
What changes in priority: for a multipart name, a relation-qualified column
in **any** local layer now wins over a `col.field` reading in a nearer layer. A
single-part name is untouched, `ORDER BY q` still prefers the alias. A lambda
argument keeps lexical priority over a relation of the same name (`array_map(x
-> x.value, x.items) from t x`).
## Behavior changes
- `q.v` prefers column `v` of relation `q` over field `v` of a same-named
struct / map / variant output alias in GROUP BY, HAVING, QUALIFY and ORDER BY.
A query that relied on the alias reading while a same-named relation has a
matching column now binds to the column.
- Lambda bodies:
- an unresolved name reports the error of the enclosing clause (`Unknown
column ... in SORT clause`) instead of `Unknown lambda slot`;
- a lambda body in a correlated subquery can reference an outer column,
which is registered as a correlated slot;
- a lambda body in a join condition no longer reports `Unsupported
correlated subquery with correlated slot in join conjuncts` for a column of the
join itself;
- an ambiguous name follows the exact-match setting of the enclosing
clause (HAVING reports it as ambiguous, as it does outside a lambda);
- analyzers that override name resolution (generated columns, alias
functions) now apply it inside lambda bodies too.
## Test matrix
`U` = FE unit test in `TestDereference` (analysis), `R` = regression case in
`query_p0/test_dereference` (executed, result in `.out`).
| Scenario | U | R |
|---|---|---|
| Reported repro: `select q.v as q from (select 7 as v) q order by q.v` | ✓
| ✓ |
| ORDER BY on a table, scalar alias | ✓ | ✓ |
| ORDER BY with DISTINCT / join / inside an `IN` subquery | ✓ | |
| ORDER BY `db.tbl.col`, alias named like the database | ✓ | |
| ORDER BY with an aggregate function in the key | ✓ | ✓ |
| ORDER BY over aggregate, key is a group by column | ✓ | ✓ |
| ORDER BY over aggregate, key is the group by expression | ✓ | |
| HAVING without aggregate | ✓ | ✓ |
| HAVING over aggregate, group by column / group by expression | ✓ | ✓
(expression) |
| QUALIFY over project / over aggregate with a group by expression | ✓ | ✓
(aggregate) |
| struct alias, ORDER BY — result must follow column `v`, not `s.v` | ✓ (no
`element_at`) | ✓ (returns id 3, the wrong binding returns 1) |
| struct alias, HAVING | ✓ | ✓ (returns id 1, the wrong binding returns
nothing) |
| struct alias, QUALIFY | ✓ | |
| Fallback kept: alias only, `select q.s as a ... order by a.v` | ✓
(`element_at`) | |
| Fallback kept: alias named like a relation that has no such column | ✓ | ✓
|
| Negative: name is only a scalar output alias → still `No such field 'v' in
'q'` | ✓ | |
| Lambda in ORDER BY, column not in the select list, unqualified / qualified
| ✓ | ✓ |
| Lambda in ORDER BY / HAVING with the alias shadow | ✓ | ✓ |
| Lambda inside an aggregate function in HAVING, lambda in QUALIFY | ✓ | |
| Nested lambda referencing a column of the clause | ✓ | |
| Lambda in JOIN ON referencing both sides | ✓ | |
| Lambda in a correlated subquery referencing an outer column (correlated
slot registered) | ✓ | |
| Lambda argument keeps priority over a same-named relation (existing test)
| ✓ | ✓ |
| Negative: unknown name in a lambda body → `Unknown column` | ✓ | |
How it was run:
- The new unit tests fail without the fix (`No such field 'v' in 'q'`, or an
`element_at` binding for the struct alias) and pass with it. All FE unit tests
under `nereids/rules/analysis` plus the lambda unit tests pass (39 classes, 383
tests).
- The new regression cases were executed, and their expected output
generated with `run-regression-test.sh -genOut`, as a standalone copy of the
new section against a local cluster of this FE with a **4.1.3 BE** (no master
BE was available). The generated blocks were appended to
`test_dereference.out`. The whole `test_dereference` suite has not run end to
end locally, because the existing `parse_to_variant` cases need a master BE, so
CI is its first full run.
Not covered, left for a follow-up:
- Execution (not just analysis) of a lambda in a correlated subquery that
references an outer column, for EXISTS / IN / scalar subqueries. This used to
be an analysis error, so there is no previously-correct result to regress.
- A regression case for a variant-typed alias. It takes the same code path
as the struct alias.
- Lambdas in generated columns and alias functions.
## Known and deliberately not changed
An output alias that shadows a same-named **column** (not a relation) for
nested access still prefers the alias in ORDER BY and HAVING:
```sql
select id, var.name as var from t order by cast(var.name as int), id;
-- `var.name` reads field `name` of the alias `var`, i.e. var.name.name,
which is NULL: the sort is a no-op
```
This is the "column vs alias" priority, a separate rule from the "relation
qualifier vs alias" one fixed here (QUALIFY and GROUP BY already prefer the
underlying column, HAVING and ORDER BY prefer the alias). Changing it is a
semantic decision with its own corner cases under aggregation, so it is left
for a separate PR.
--
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]
