This is an automated email from the ASF dual-hosted git repository.
morrySnow pushed a commit to branch master
in repository https://gitbox.apache.org/repos/asf/doris.git
The following commit(s) were added to refs/heads/master by this push:
new 1cac9af07eb [fix](constraint) Preserve table identity in foreign key
join elimination (#67699)
1cac9af07eb is described below
commit 1cac9af07eb8bdb1d14d433a5811ed3a01ee0f72
Author: morrySnow <[email protected]>
AuthorDate: Fri Sep 11 13:32:45 2026 +0800
[fix](constraint) Preserve table identity in foreign key join elimination
(#67699)
## Problem
Nereids can eliminate an inner join against the wrong primary-key table.
If a child table has a foreign key to table `p1`, and an unrelated table
`p2` has a key column with the same schema as `p1`, a query joining the
child table to `p2` can lose the join. Rows that have no match in `p2`
are then returned incorrectly.
## Root cause
`ForeignKeyContext` represented constraints and slot lineage with
unqualified `Column` objects. `Column.equals()` compares column schema
attributes but does not include catalog, database, or table identity.
Consequently, a constraint from `child.fk` to `p1.id` compared equal to
a candidate mapping from `child.fk` to the same-shaped `p2.id`.
## How to reproduce
1. Create `p1(id)` and `p2(id)` with identical primary-key definitions.
2. Create `child(fk, payload)` with a foreign key from `child.fk` to
`p1.id`.
3. Insert `(1)` into `p1`, `(2)` into `p2`, and `(1, 7)` into `child`.
4. Run:
```sql
SELECT child.payload
FROM child INNER JOIN p2 ON child.fk = p2.id;
```
The correct result is empty because `p2` has no row with `id = 1`.
Before this change, the optimizer removed `p2` and the join and returned
`7`.
## Fix
Qualify every column used by foreign-key proofs with its owning
`TableIdentifier`. Constraint collection, primary-key tracking, slot
lineage through aliases, and final constraint matching now compare both
table identity and column schema. This keeps the existing elimination
for the declared target table while rejecting an unrelated table with an
identical column definition.
## Tests
- Added a negative FE unit test with an unrelated same-schema
primary-key table.
- Kept the existing positive tests for elimination against the actual
referenced table.
- Ran:
```text
./run-fe-ut.sh --run
org.apache.doris.nereids.rules.rewrite.EliminateJoinByFkTest
```
Result: 12 tests passed, 0 failures, 0 errors.
---
.../nereids/rules/rewrite/ForeignKeyContext.java | 61 +++++++++++++++++-----
.../rules/rewrite/EliminateJoinByFkTest.java | 18 +++++++
2 files changed, 67 insertions(+), 12 deletions(-)
diff --git
a/fe/fe-core/src/main/java/org/apache/doris/nereids/rules/rewrite/ForeignKeyContext.java
b/fe/fe-core/src/main/java/org/apache/doris/nereids/rules/rewrite/ForeignKeyContext.java
index bf0c84fbb70..1b6bd72cea7 100644
---
a/fe/fe-core/src/main/java/org/apache/doris/nereids/rules/rewrite/ForeignKeyContext.java
+++
b/fe/fe-core/src/main/java/org/apache/doris/nereids/rules/rewrite/ForeignKeyContext.java
@@ -22,6 +22,7 @@ import org.apache.doris.catalog.Env;
import org.apache.doris.catalog.TableIf;
import org.apache.doris.catalog.constraint.ForeignKeyConstraint;
import org.apache.doris.catalog.constraint.PrimaryKeyConstraint;
+import org.apache.doris.catalog.constraint.TableIdentifier;
import org.apache.doris.catalog.info.TableNameInfo;
import org.apache.doris.info.TableNameInfoUtils;
import org.apache.doris.nereids.trees.expressions.Alias;
@@ -41,6 +42,7 @@ import com.google.common.collect.ImmutableMap;
import java.util.HashMap;
import java.util.HashSet;
import java.util.Map;
+import java.util.Objects;
import java.util.Set;
import java.util.stream.Collectors;
@@ -48,10 +50,10 @@ import java.util.stream.Collectors;
* Record Foreign Key Context
*/
public class ForeignKeyContext {
- Set<Map<Column, Column>> constraints = new HashSet<>();
- Set<Column> foreignKeys = new HashSet<>();
- Set<Column> primaryKeys = new HashSet<>();
- Map<Slot, Column> slotToColumn = new HashMap<>();
+ Set<Map<QualifiedColumn, QualifiedColumn>> constraints = new HashSet<>();
+ Set<QualifiedColumn> foreignKeys = new HashSet<>();
+ Set<QualifiedColumn> primaryKeys = new HashSet<>();
+ Map<Slot, QualifiedColumn> slotToColumn = new HashMap<>();
Map<Slot, Set<Expression>> slotWithPredicates = new HashMap<>();
/**
@@ -71,12 +73,13 @@ public class ForeignKeyContext {
@Override
public Void visitLogicalRelation(LogicalRelation relation,
ForeignKeyContext context) {
if (relation instanceof LogicalCatalogRelation) {
- context.putAllForeignKeys(((LogicalCatalogRelation)
relation).getTable());
- context.putAllPrimaryKeys(((LogicalCatalogRelation)
relation).getTable());
+ TableIf table = ((LogicalCatalogRelation)
relation).getTable();
+ context.putAllForeignKeys(table);
+ context.putAllPrimaryKeys(table);
relation.getOutput().stream()
.filter(SlotReference.class::isInstance)
.map(SlotReference.class::cast)
- .forEach(context::putSlot);
+ .forEach(slot -> context.putSlot(slot, table));
}
return null;
}
@@ -109,7 +112,12 @@ public class ForeignKeyContext {
}
for (ForeignKeyConstraint c :
Env.getCurrentEnv().getConstraintManager()
.getForeignKeyConstraints(tableNameInfo)) {
- Map<Column, Column> constraint = c.getForeignToPrimary(table);
+ TableIf referencedTable = c.getReferencedTable();
+ Map<QualifiedColumn, QualifiedColumn> constraint =
c.getForeignToReference().entrySet().stream()
+ .collect(ImmutableMap.toImmutableMap(
+ entry -> new QualifiedColumn(table,
table.getColumn(entry.getKey())),
+ entry -> new QualifiedColumn(
+ referencedTable,
referencedTable.getColumn(entry.getValue()))));
constraints.add(constraint);
foreignKeys.addAll(constraint.keySet());
}
@@ -122,7 +130,8 @@ public class ForeignKeyContext {
}
for (PrimaryKeyConstraint c :
Env.getCurrentEnv().getConstraintManager()
.getPrimaryKeyConstraints(tableNameInfo)) {
- Set<Column> primaryKey = c.getPrimaryKeys(table);
+ Set<QualifiedColumn> primaryKey = c.getPrimaryKeys(table).stream()
+ .map(column -> new QualifiedColumn(table,
column)).collect(Collectors.toSet());
primaryKeys.addAll(primaryKey);
}
}
@@ -137,12 +146,12 @@ public class ForeignKeyContext {
key.stream().map(s ->
slotToColumn.get(s)).collect(Collectors.toSet()));
}
- void putSlot(SlotReference slot) {
+ void putSlot(SlotReference slot, TableIf table) {
if (!slot.getOriginalColumn().isPresent()) {
return;
}
Column c = slot.getOriginalColumn().get();
- slotToColumn.put(slot, c);
+ slotToColumn.put(slot, new QualifiedColumn(table, c));
}
void putAlias(Slot newSlot, Slot originSlot) {
@@ -186,7 +195,7 @@ public class ForeignKeyContext {
* Check whether the given mapping relation satisfies any constraints
*/
public boolean satisfyConstraint(Map<Slot, Slot> primaryToForeign) {
- Map<Column, Column> foreignToPrimary =
primaryToForeign.entrySet().stream()
+ Map<QualifiedColumn, QualifiedColumn> foreignToPrimary =
primaryToForeign.entrySet().stream()
.collect(ImmutableMap.toImmutableMap(
e -> slotToColumn.get(e.getValue()),
e -> slotToColumn.get(e.getKey())));
@@ -218,4 +227,32 @@ public class ForeignKeyContext {
return
slotWithPredicates.get(pf.getValue()).containsAll(primaryPredicates);
});
}
+
+ /** A column identity qualified by its owning table. */
+ private static final class QualifiedColumn {
+ private final TableIdentifier tableIdentifier;
+ private final Column column;
+
+ private QualifiedColumn(TableIf table, Column column) {
+ this.tableIdentifier = new TableIdentifier(table);
+ this.column = column;
+ }
+
+ @Override
+ public boolean equals(Object obj) {
+ if (this == obj) {
+ return true;
+ }
+ if (!(obj instanceof QualifiedColumn)) {
+ return false;
+ }
+ QualifiedColumn other = (QualifiedColumn) obj;
+ return tableIdentifier.equals(other.tableIdentifier) &&
column.equals(other.column);
+ }
+
+ @Override
+ public int hashCode() {
+ return Objects.hash(tableIdentifier, column);
+ }
+ }
}
diff --git
a/fe/fe-core/src/test/java/org/apache/doris/nereids/rules/rewrite/EliminateJoinByFkTest.java
b/fe/fe-core/src/test/java/org/apache/doris/nereids/rules/rewrite/EliminateJoinByFkTest.java
index 63f1b650bbc..8d7505ccefd 100644
---
a/fe/fe-core/src/test/java/org/apache/doris/nereids/rules/rewrite/EliminateJoinByFkTest.java
+++
b/fe/fe-core/src/test/java/org/apache/doris/nereids/rules/rewrite/EliminateJoinByFkTest.java
@@ -68,9 +68,16 @@ class EliminateJoinByFkTest extends TestWithFeService
implements MemoPatternMatc
+ ")\n"
+ "UNIQUE KEY(id3)\n"
+ "DISTRIBUTED BY HASH(id3) BUCKETS 10\n"
+ + "PROPERTIES (\"replication_num\" = \"1\")\n",
+ "CREATE TABLE IF NOT EXISTS unrelated_pri (\n"
+ + " id1 int not null\n"
+ + ")\n"
+ + "UNIQUE KEY(id1)\n"
+ + "DISTRIBUTED BY HASH(id1) BUCKETS 10\n"
+ "PROPERTIES (\"replication_num\" = \"1\")\n"
);
addConstraint("Alter table pri add constraint pk primary key (id1)");
+ addConstraint("Alter table unrelated_pri add constraint unrelated_pk
primary key (id1)");
addConstraint("Alter table foreign_not_null add constraint f_not_null
foreign key (id2)\n"
+ "references pri(id1)");
addConstraint("Alter table foreign_null add constraint f_not_null
foreign key (id3)\n"
@@ -106,6 +113,17 @@ class EliminateJoinByFkTest extends TestWithFeService
implements MemoPatternMatc
.printlnTree();
}
+ @Test
+ void testDoNotEliminateJoinWithUnrelatedPrimaryTable() {
+ String sql = "select foreign_not_null.id2 from unrelated_pri "
+ + "inner join foreign_not_null on unrelated_pri.id1 =
foreign_not_null.id2";
+ PlanChecker.from(connectContext)
+ .analyze(sql)
+ .rewrite()
+ .matches(logicalJoin())
+ .printlnTree();
+ }
+
@Test
void testNotNullWithPredicate() {
String sql = "select pri.id1 from pri inner join foreign_not_null on
pri.id1 = foreign_not_null.id2\n"
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
