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

   ## Which issue does this PR close?
   
   - Closes #27596.
   
   ## Rationale for this change
   
   A table with a PRIMARY KEY and a declared sort order (`WITH ORDER`) can lose 
a required sort when the scan reads only some of the table columns, so the 
query returns rows in the wrong order. See the issue for the reproducer.
   
   The table in the issue has the columns `a0, a, b, c, d` (indices 0 to 4), so 
the key `c` has table index 3. The scan reads `a, b, c, d`, so in the scan 
output `c` has index 2 and `d` has index 3.
   
   
[`EquivalenceProperties::projected_constraints`](https://github.com/apache/datafusion/blob/c6c1cc31d2f433930c9b4f3ca9fea023846ac276/datafusion/physical-expr/src/equivalence/properties/mod.rs#L1579)
 passes the output column indices of the projection (`[0, 1, 2, 3]`) to 
[`Constraints::project`](https://github.com/apache/datafusion/blob/c6c1cc31d2f433930c9b4f3ca9fea023846ac276/datafusion/common/src/functional_dependencies.rs#L51).
 `Constraints::project` expects, at each output position, the index of the 
source column (here `[1, 2, 3, 4]`). It looks up the key index 3 in `[0, 1, 2, 
3]`, finds it at position 3, and remaps the key to output column 3, which is 
`d`. 
[`satisfied_by_constraints`](https://github.com/apache/datafusion/blob/c6c1cc31d2f433930c9b4f3ca9fea023846ac276/datafusion/physical-expr/src/equivalence/properties/mod.rs#L1198)
 then treats `d` as unique: the input is sorted on `d`, so the requirement `d, 
a` is considered satisfied and the sort is removed.
   
   With other projections the same error can also drop a valid key (a missed 
optimization) instead of moving it to the wrong column.
   
   ## What changes are included in this PR?
   
   `EquivalenceProperties::projected_constraints` now passes to 
`Constraints::project`, at each output column index, the index of its source 
column. Output columns that are not a plain source column get `usize::MAX`, 
which matches no constraint column.
   
   ## What is the testing strategy for this PR?
   
   A new `sqllogictest` case in `order.slt` (table `pk_dropped_columns`) checks 
the query result and that the `SortExec` stays in the plan. Without the fix, 
the case fails: the rows are not sorted and the plan has no `SortExec`.
   
   ## Are there any user-facing changes?
   
   Queries on tables with constraints and a declared sort order return 
correctly sorted results. No public API changes.
   
   ----
   
   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