This is experimental functionality, and as such subject to change. Use at your own risk!
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.
Concatenation in predicates
Applies to ❌ Open Source Edition ✅ Express Edition ✅ Professional Edition ✅ Enterprise Edition
The SPI method handling this event is consecutiveAggregation()
Using CONCAT inside of predicate is often a sign of potentially slow and/or wrong queries.
Why is it bad?
Ordinary indexes cannot be used this way. Even if it's possible to define function based indexes that cover such a predicate, it's usually better not to use concatenation in predicates because:
- Ordinary indexes on concatenated columns are more likely to be reusable by other queries.
- Unless there's a separation token that is known not to be present in actual data, these concatenations produce the same value:
'John Taylor' || 'Doe'and'John' || 'Taylor Doe'. But they might be different first and last names.
An example is given here:
// A custom DiagnosticsListener SPI implementation
class ConcatenationInPredicate implements DiagnosticsListener {
@Override
public void concatenationInPredicate(DiagnosticsContext ctx) {
// The statement that is being executed and which has a concatenation in a predicate.
System.out.println("Actual statement: " + ctx.actualStatement());
// The predicate containing the concatenation.
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 ConcatenationInPredicate()))
.diagnosticsConnection();
Statement s = c.createStatement()
) {
try (ResultSet a = s.executeQuery("SELECT id FROM author WHERE first_name || last_name = ?")) {
while (a.next())
println(a.getInt(1));
}
}
Feedback
Do you have any feedback about this page? We'd love to hear it!