asolimando opened a new pull request, #27620:
URL: https://github.com/apache/datafusion/pull/27620

   ## Which issue does this PR close?
   
   - Closes #27619.
   
   ## Rationale for this change
   
   A query on a table with a UNIQUE constraint on a nullable column and a 
declared sort order (`WITH ORDER`) can return rows in the wrong order. See the 
issue for the reproducer.
   
   `EquivalenceProperties::satisfied_by_constraints` and 
`satisfied_by_constraints_ordering` decide whether a PRIMARY KEY or UNIQUE 
constraint lets the input ordering satisfy a sort requirement, so that the sort 
can be removed. For a UNIQUE constraint, they accept a nullable key column when 
it is neither the first nor the last column of the ordering. This is not 
correct at any position: SQL `UNIQUE` allows several rows with NULL in a key 
column, so these rows are not unique on the key.
   
   In the issue, the input is ordered by `[a, b, c]` with `UNIQUE (a, b)` and 
`b` nullable. `b` is in the middle of the ordering, so the requirement `[a, b, 
c, v]` is considered satisfied and the `SortExec` is removed. The two rows `(1, 
NULL, 0, 2)` and `(1, NULL, 0, 1)` tie on `[a, b, c]`, so only the sort on `v` 
can order them.
   
   ## What changes are included in this PR?
   
   - New `Constraint::is_valid_across_nulls`: `true` for a PRIMARY KEY, and for 
a UNIQUE constraint only if none of its key columns is nullable. It applies to 
a `Constraint` the same rule that `FunctionalDependence::is_valid_across_nulls` 
applies to a functional dependency.
   - `satisfied_by_constraints` and `satisfied_by_constraints_ordering` call it 
once per constraint, before the orderings are checked, instead of checking 
nullability by position in each ordering.
   - A new private helper `ordering_contains_columns`, used by both functions, 
checks that all key columns are in an ordering. It replaces the map of column 
positions and nullability that was built for each ordering.
   
   ## What is the testing strategy for this PR?
   
   - A new `sqllogictest` case in `order.slt` (table `unique_nullable`) checks 
the query result and that the `SortExec` stays in the plan. On `main`, the 
query returns the rows in the wrong order and the plan has no `SortExec`. No 
existing test changes.
   - A unit test for `Constraint::is_valid_across_nulls`.
   
   ## Are there any user-facing changes?
   
   Queries on tables with a UNIQUE constraint on a nullable column and a 
declared sort order return correctly sorted results. New public method 
`Constraint::is_valid_across_nulls` in `datafusion-common`.
   
   ----
   
   Disclaimer: I used AI to assist in the code generation, I have manually 
reviewed the output and it matches my intention and understanding.
   


-- 
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