Repository navigation
Commit b6c3a63
authored
[fix](dereference) Resolve relation-qualified columns before output alias fields in ORDER BY / HAVING / QUALIFY (apache#68260)
Follow-up of apache#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. apache#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)
```
Letting a lambda body follow its clause made one more failure reachable,
and it turned out to exist on master independently of this PR: a nested
loop join whose condition has a lambda that references **both sides**
fails in the translator.
```sql
SELECT q.id, p.id FROM t q, t p WHERE array_sum(array_map(x -> x + p.v + q.v, [0])) > 40;
-- java.lang.NullPointerException: Cannot read field "type" because "e" is null
```
`ProjectOtherJoinConditionForNestedLoopJoin` moves a sub-expression of
the join condition into a Project under the join when all of its input
slots come from one side. A lambda argument is not an input slot (no
child outputs it), so `x + p.v` was taken as a right-only expression and
moved out of the lambda body into a Project where `x` does not exist.
Before this PR a lambda in JOIN ON or in a correlated EXISTS failed
during analysis, so only the WHERE-over-join shape could reach it; with
the lambda fix those two shapes reach it too, so it is fixed here.
## The fix
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.
The variant schema auto cast (`enable_variant_schema_auto_cast`) casts
only the outermost `element_at` of a chain, and tracks the chain in a
counter of the analyzer instance. The chain around a lambda body name is
visited by the lambda analyzer, so the enclosing analyzer binds the
delegated name under the counter of the lambda analyzer and restores its
own afterwards (`visitUnboundSlotOfLambdaBody`). This keeps the casts of
a lambda body exactly as they were before the delegation, in both
directions: a chain inside the lambda body, and a chain around the
higher order function.
3. `ProjectOtherJoinConditionForNestedLoopJoin`: do not descend into a
lambda. A higher order function whose inputs all come from one side is
still projected as a whole.
## 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 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. (GROUP BY looks
at the child output first, its result does not change.)
- The relation reading wins even when it then fails: with a relation `q`
that has a scalar column `v` and a struct output alias `q` that has the
path `v.b`, `ORDER BY q.v.b` used to read the alias and now reports `No
such field 'b' in 'v'`. MySQL agrees on the priority: with an output
alias `s` and a table `s`, it reads `s.a` as column `a` of the table.
- 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. The lambda analyzer used to pick the exact match on its own, so
`select q.id as id, p.id from ... having array_sum(array_map(x -> x +
id, q.arr)) > 0` used to bind `id` to the alias and now reports `id is
ambiguous`, exactly as `having id > 0` 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 (`TestDereference` unless another class is named;
analysis only), `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'` | ✓ | ✓ |
| Negative: `q.v.b`, relation column `v` is scalar, alias `q` has the
path `v.b` → `No such field 'b' in 'v'` | ✓ | ✓ |
| 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 | ✓ | ✓ (NPE before the rule
fix) |
| Lambda in a correlated EXISTS referencing the outer and the inner
relation | ✓ (correlated slot registered) | ✓ (NPE before the rule fix)
|
| Lambda in a correlated IN referencing an outer column | | ✓ |
| Nested loop join rule: a lambda body is not projected, a one-sided
higher order function still is | ✓
(`ProjectOtherJoinConditionForNestedLoopJoinTest`) | |
| Negative: ambiguous name in HAVING, outside and inside a lambda → `id
is ambiguous` | ✓ | |
| Variant auto cast: `array_map(x -> data.num_nested['l1']['l2'], arr)`,
the dotted prefix in a chain inside the lambda body is not cast, same as
outside a lambda | ✓ (`ExpressionAnalyzerVariantAutoCastTest`) | |
| Variant auto cast: `array_map(x -> array(data.num_a), arr)[1][1]`, a
chain around the higher order function does not suppress the cast in the
lambda body | ✓ (`ExpressionAnalyzerVariantAutoCastTest`) | |
| 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 unit tests of the broken shapes fail without the fix (`No such
field 'v' in 'q'`, an `element_at` binding for the struct alias, a
projected lambda body for the join rule) and pass with it. A few cases
in the same tests pass either way and only pin the behavior down (HAVING
on a group by column, QUALIFY over a project, the negatives).
- FE unit tests under `nereids/rules/analysis`, the lambda tests and
`nereids/rules/rewrite/*Join*` were run locally before the variant cast
commit: 68 of 70 classes pass. `PushDownLimitDistinctThroughJoinTest`
and `PushDownTopNThroughJoinTest` fail locally in `runBeforeAll` while
creating their tables (`available backend num is 0`), before any test
method runs; they passed in CI on an earlier head. After that commit
`TestDereference` (18), `ExpressionAnalyzerVariantAutoCastTest` (18) and
`ProjectOtherJoinConditionForNestedLoopJoinTest` (3) pass, and the two
variant cast tests fail without the fix.
- 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 `test { sql; exception }` cases were
executed on the same cluster and report the expected messages. The whole
`test_dereference` suite cannot run end to end locally, because the
existing `parse_to_variant` cases need a master BE; it passed in CI (P0)
on an earlier head of this PR, before the negative and the join /
correlated lambda cases were added.
Not covered, left for a follow-up:
- A lambda in a correlated **scalar** subquery with a non-equal
correlated predicate reports `Unsupported correlated subquery with
correlated predicate`, which is the existing limitation for such
predicates and not specific to lambdas.
- A regression case for the variant auto cast inside a lambda body, and
a unit test for passing the cast counter through a nested lambda (traced
in the code only).
- 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.1 parent d657d62 commit b6c3a63
7 files changed
Lines changed: 493 additions & 17 deletions
File tree
- fe/fe-core/src
- main/java/org/apache/doris/nereids/rules
- analysis
- rewrite
- test/java/org/apache/doris/nereids/rules
- analysis
- rewrite
- regression-test
- data/query_p0
- suites/query_p0
| Original file line number | Diff line number | Diff line change | |
|---|---|---|---|
| |||
326 | 326 | | |
327 | 327 | | |
328 | 328 | | |
329 | | - | |
330 | | - | |
331 | | - | |
332 | | - | |
| 329 | + | |
| 330 | + | |
| 331 | + | |
| 332 | + | |
| 333 | + | |
| 334 | + | |
333 | 335 | | |
334 | 336 | | |
335 | 337 | | |
336 | | - | |
| 338 | + | |
337 | 339 | | |
338 | | - | |
339 | | - | |
340 | | - | |
| 340 | + | |
| 341 | + | |
| 342 | + | |
341 | 343 | | |
342 | 344 | | |
343 | 345 | | |
| |||
474 | 476 | | |
475 | 477 | | |
476 | 478 | | |
477 | | - | |
478 | | - | |
| 479 | + | |
479 | 480 | | |
480 | 481 | | |
481 | 482 | | |
482 | 483 | | |
483 | 484 | | |
484 | 485 | | |
485 | | - | |
| 486 | + | |
486 | 487 | | |
| 488 | + | |
487 | 489 | | |
488 | 490 | | |
489 | 491 | | |
490 | 492 | | |
491 | | - | |
492 | | - | |
| 493 | + | |
| 494 | + | |
| 495 | + | |
| 496 | + | |
| 497 | + | |
| 498 | + | |
| 499 | + | |
| 500 | + | |
| 501 | + | |
493 | 502 | | |
494 | 503 | | |
495 | 504 | | |
496 | | - | |
497 | | - | |
498 | | - | |
499 | | - | |
| 505 | + | |
| 506 | + | |
| 507 | + | |
| 508 | + | |
500 | 509 | | |
501 | 510 | | |
502 | 511 | | |
503 | 512 | | |
504 | 513 | | |
| 514 | + | |
| 515 | + | |
| 516 | + | |
| 517 | + | |
| 518 | + | |
| 519 | + | |
| 520 | + | |
| 521 | + | |
| 522 | + | |
| 523 | + | |
| 524 | + | |
| 525 | + | |
| 526 | + | |
| 527 | + | |
| 528 | + | |
| 529 | + | |
505 | 530 | | |
506 | 531 | | |
507 | 532 | | |
| |||
| Original file line number | Diff line number | Diff line change | |
|---|---|---|---|
| |||
23 | 23 | | |
24 | 24 | | |
25 | 25 | | |
| 26 | + | |
26 | 27 | | |
27 | 28 | | |
28 | 29 | | |
| |||
103 | 104 | | |
104 | 105 | | |
105 | 106 | | |
| 107 | + | |
| 108 | + | |
| 109 | + | |
| 110 | + | |
| 111 | + | |
| 112 | + | |
| 113 | + | |
| 114 | + | |
| 115 | + | |
106 | 116 | | |
107 | 117 | | |
108 | 118 | | |
| |||
| Original file line number | Diff line number | Diff line change | |
|---|---|---|---|
| |||
19 | 19 | | |
20 | 20 | | |
21 | 21 | | |
| 22 | + | |
22 | 23 | | |
23 | 24 | | |
24 | 25 | | |
| |||
34 | 35 | | |
35 | 36 | | |
36 | 37 | | |
| 38 | + | |
37 | 39 | | |
38 | 40 | | |
39 | 41 | | |
| 42 | + | |
40 | 43 | | |
41 | 44 | | |
42 | 45 | | |
| |||
49 | 52 | | |
50 | 53 | | |
51 | 54 | | |
| 55 | + | |
| 56 | + | |
52 | 57 | | |
53 | 58 | | |
54 | 59 | | |
| |||
320 | 325 | | |
321 | 326 | | |
322 | 327 | | |
| 328 | + | |
| 329 | + | |
| 330 | + | |
| 331 | + | |
| 332 | + | |
| 333 | + | |
| 334 | + | |
| 335 | + | |
| 336 | + | |
| 337 | + | |
| 338 | + | |
| 339 | + | |
| 340 | + | |
| 341 | + | |
| 342 | + | |
| 343 | + | |
| 344 | + | |
| 345 | + | |
| 346 | + | |
| 347 | + | |
| 348 | + | |
| 349 | + | |
| 350 | + | |
| 351 | + | |
| 352 | + | |
| 353 | + | |
| 354 | + | |
| 355 | + | |
| 356 | + | |
| 357 | + | |
| 358 | + | |
| 359 | + | |
| 360 | + | |
| 361 | + | |
| 362 | + | |
| 363 | + | |
| 364 | + | |
| 365 | + | |
| 366 | + | |
| 367 | + | |
| 368 | + | |
| 369 | + | |
| 370 | + | |
| 371 | + | |
| 372 | + | |
| 373 | + | |
323 | 374 | | |
324 | 375 | | |
325 | 376 | | |
| |||
0 commit comments