Available in versions: Dev (3.21)
This documentation is for the unreleased development version of jOOQ. Click on the above version links to get this documentation for a supported version of jOOQ.
Incomplete equi join
Supported by ❌ Open Source Edition ✅ Express Edition ✅ Professional Edition ✅ Enterprise Edition
The SPI method handling this event is incompleteEquiJoin()
When working with composite keys, it is easy to forget a column in a JOIN predicate. For this to work, the parser must be configured with meta lookups active, such that tables, columns, and constraints can be resolved in the diagnostic listener's parser.
Why is it bad?
A missing predicate in a JOIN predicate can lead to cartesian products and thus wrong results. Unlike absent join predicates, which can be detected due to jOOQ's DSL API not compiling, incomplete join predicates are very subtle and hard to detect. In the worst case, they start out being complete, but as the schema evolves, a constraint becomes composite and queries are not updated accordingly, leading to an incomplete predicate.
An example is given here:
// A custom DiagnosticsListener SPI implementation
class IncompleteJoinPredicate implements DiagnosticsListener {
@Override
public void incompleteJoinPredicate(DiagnosticsContext ctx) {
// The statement that is being executed and which has a incomplete join predicate.
System.out.println("Actual statement: " + ctx.actualStatement());
// The incomplete join predicate.
System.out.println("Predicate : " + ctx.part());
}
}
And then:
// Configuration is configured with the target DataSource, SQLDialect, etc. for instance Oracle.
try (
Connection c = DSL.using(configuration.derive(new IncompleteJoinPredicate()))
.diagnosticsConnection();
Statement s = c.createStatement()
) {
try (ResultSet a = s.executeQuery("SELECT * FROM child JOIN parent ON child.key1 = parent.key1")) {
while (a.next())
println(a.getInt(1));
}
}
Implicit JOIN will never suffer from this problem, as the join predicate is generated by jOOQ always based on the complete available constraint meta data.
Feedback
Do you have any feedback about this page? We'd love to hear it!