Available in versions: Dev (3.18)

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.

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")) {
        // ..
    }
}

Feedback

Do you have any feedback about this page? We'd love to hear it!

The jOOQ Logo