emanuele-em opened a new pull request, #27460:
URL: https://github.com/apache/datafusion/pull/27460
## Which issue does this PR close?
- Closes #6012.
## Rationale for this change
When two relations in a query share a table name, a column reference that
only uses the table name silently picks one of them, so the result depends on
the join order:
```sql
CREATE SCHEMA s1;
CREATE SCHEMA s2;
CREATE TABLE s1.t AS SELECT 1 AS a, 2 AS b;
CREATE TABLE s2.t AS SELECT 1 AS a, 3 AS b;
SELECT t.b FROM s1.t JOIN s2.t USING (a); -- 2
SELECT t.b FROM s2.t JOIN s1.t USING (a); -- 3
```
An unqualified `b` already fails with an ambiguity error, but `t.b` does
not. A related case returned the wrong column: with tables `t` and `s1.t`,
`SELECT t.b FROM s1.t JOIN t USING (a)` returned `s1.t.b`.
## What changes are included in this PR?
The partial qualifier was resolved in three places, and each one took the
first match:
- `DFSchema::index_of_column_by_name` stopped at the first field whose
qualifier matched. A qualified lookup now looks at all matches. A field whose
qualifier is exactly the requested one wins, because columns keep the qualifier
as written in the query, so `t` still finds a relation named `t`. Otherwise
more than one match returns the new `SchemaError::AmbiguousQualifiedReference`.
- `search_dfschema` in the SQL planner turned every lookup error into "not
found" and went on to read `t.b` as a field `b` of a struct column `t`, which
hid the ambiguity. It now returns the ambiguity error. I think this is why the
earlier attempt mentioned in the issue was not enough.
- `rewrite_partial_qualifier` matched qualifiers by string suffix
(`ends_with(".t")`). It now uses the same `DFSchema` lookup and only replaces
the qualifier, so the column keeps its spans.
This does not centralize column resolution as discussed in the issue. It
only fixes the places that picked the first match.
## What is the testing strategy for this PR?
- `identifiers.slt`: ambiguous references in the select list, `WHERE`,
`ORDER BY` and on a `USING` column, in both join orders, plus the cases that
must keep working (full qualifier, alias, a relation named exactly `t`).
- `select_ambiguous_qualified_column` in `core/tests/dataframe`, because
`col("t.b")` in the DataFrame API does not go through the SQL planner.
The new tests fail without the fix.
## Are there any user-facing changes?
Yes, this needs the `api change` label.
- SQL: a query where `t.b` matches more than one relation now fails with
`Ambiguous reference to t.b, it matches s1.t.b and s2.t.b` instead of picking
one. A full qualifier or an alias resolves it.
- Rust API: new variant `SchemaError::AmbiguousQualifiedReference`, which
breaks exhaustive matches on `SchemaError`. `DFSchema::index_of_column_by_name`
and `DFSchema::maybe_index_of_column` return `None` for an ambiguous qualified
reference instead of the first match.
Both are described in the 57.0.0 upgrade guide.
@alamb you discussed this in the issue, could you take a look when you have
time?
--
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]