Dandandan opened a new pull request, #24793:
URL: https://github.com/apache/datafusion/pull/24793
## Which issue does this PR close?
- Closes #.
## Rationale for this change
A fact table often joins a dimension purely to check that a row exists:
```sql
SELECT ss_item_sk FROM store_sales, promotion WHERE ss_promo_sk = p_promo_sk
```
No column of `promotion` is used and nothing filters it. `eliminate_join`
already turns this into a semi join, because `p_promo_sk` is a primary key.
But
if a foreign key says every `ss_promo_sk` exists in `promotion`, the check is
guaranteed to succeed and the join can be dropped entirely.
DataFusion had no way to say that. `Constraint` had only `PrimaryKey` and
`Unique`, and the SQL planner rejected foreign keys outright with "Foreign
key
constraints are not currently supported".
## What changes are included in this PR?
- `Constraint::ForeignKey { columns, referenced_table, referenced_columns }`,
taken on trust like the existing constraints, round-tripping through proto.
- The SQL planner accepts `FOREIGN KEY (..) REFERENCES t(..)` and the inline
`col INT REFERENCES t(c)` form.
- `eliminate_join` drops a join the foreign key makes redundant.
- `dfbench` declares the TPC-DS foreign keys. Its comment previously read
"TPC-DS also defines foreign keys, but those are currently unsupported".
Two details worth review:
The join is replaced by its left input under a `col IS NOT NULL` filter, not
dropped outright. A foreign key column may be nullable, and the semi join it
replaces does drop rows whose key is NULL, so the filter preserves that. It
also covers the case where an outer join further down padded the column.
The rewrite only fires when the referenced side is an unfiltered scan of the
referenced table. A foreign key promises the value exists in the table, not
that it survives a predicate, so a filtered dimension keeps its join.
## Benchmarks
TPC-DS SF1, primary keys declared in both runs, median of 3, plus the movers
re-checked at 9 iterations:
| query | without foreign keys | with | |
|---|---|---|---|
| q64 | 347 ms | 239 ms | **1.45x** |
| q18 | 92 ms | 76 ms | **1.22x** |
| suite | 9551 ms | 9440 ms | -1.2% |
q64 loses 6 joins: `promotion` twice and `income_band` four times. q23 and
q24
looked like regressions at 3 iterations but their plans are unchanged, and
at 9
iterations they are flat.
TPC-H is unaffected: all of its dimension joins carry filters.
## Are these changes tested?
Yes. `functional_dependencies.slt` covers the rewrite, the results it
produces,
and the two cases that must keep the join: a predicate on the referenced
side,
and a query that uses one of its columns. Three cases in `group_by.slt` that
asserted foreign keys were rejected now assert they are accepted.
All 99 TPC-DS queries were checked to return identical results with and
without
the foreign keys declared. The full sqllogictest suite (504 files) and the
workspace test suite pass.
## Are there any user-facing changes?
`FOREIGN KEY` in `CREATE TABLE` is accepted instead of erroring, and joins it
makes redundant are removed. `Constraint` gains a variant, so exhaustive
matches
on it need a new arm; this is an API change.
--
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]
