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!