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]

Reply via email to