The jOOQ User Manual SQL execution Diagnostics Null condition

previous : next

Null condition

Applies to ❌ Open Source Edition   ✅ Express Edition   ✅ Professional Edition   ✅ Enterprise Edition

The SPI methods handling these events are nullCondition(). This diagnostic depends on the transform patterns feature.

This problem appears with JDBC, jOOQ or with any ORM. A predicate of arbitrary complexity can sometimes be reduced to a simple NULL condition, which is almost always a mistake.

Why is it bad?

A NULL condition is bad for your application for multiple reasons:

• SQL implements three valued logic, and as such you should never directly compare T.A = NULL, but use the NULL predicate instead, e.g. T.A IS NULL.
• There are more subtle kinds of effectively NULL conditions, such as e.g. NOT IN predicates containing at least one NULL value in the IN list.

An example is given here:

// A custom DiagnosticsListener SPI implementation
class NullCondition extends DefaultDiagnosticsListener {
    @Override
    public void nullCondition(DiagnosticsContext ctx) {
        System.out.println("Null condition: " + ctx.part());
    }
}

// Configuration is configured with the target DataSource, SQLDialect, etc. for instance Oracle.
try (Connection c = DSL.using(configuration.derive(new NullCondition()))
                       .diagnosticsConnection();
     Statement s = c.createStatement()) {
     
    try (ResultSet rs = s.executeQuery("SELECT * FROM author WHERE id = null")) {
        // ..
    }
}

The jOOQ User Manual SQL execution Diagnostics Null condition

previous : next