jOOQ Converter for NOT NULL column: Why is it necessary to handle null if the column cannot be null?

Question

In jOOQ code generation, it is possible to assign a converter to a NOT NULL field like so:

<forcedType>
    <includeTypes>(?i)^varchar\(\d+\)$</includeTypes>
    <userType>String</userType>
    <nullability>NOT_NULL</nullability><!-- Converter applies only to NOT NULL columns! -->
    <converter>StringCaseConverter</converter>
</forcedType>

And then a Converter may be implemented like so:

public class StringCaseConverter extends org.jooq.impl.AbstractConverter<String, String> {
    public StringCaseConverter() {
        super(String.class, String.class);
    }

    @Override
    public String from(String databaseObject) {
        return databaseObject.toLowerCase(); // FIXME: this throws NPE if argument is ever null!
    }

    @Override
    public String to(String userObject) {
        return userObject.toUpperCase(); // FIXME: this throws NPE if argument is ever null!
    }
}

In typical scenarios, where one is simply performing standard CRUD on a table with such a column, then the databaseObject can never be null, so such an implementation would appear to suffice.

However the Javadoc for the Converter API (now) says:

Irrespective of the Converter's encoding of null values above, an implementation must be able to handle null values.

Such a Converter may be made null-safe by simply checking and returning null in each method like so:

public class StringCaseConverter extends org.jooq.impl.AbstractConverter<String, String> {
    public StringCaseConverter() {
        super(String.class, String.class);
    }

    @Override
    public String from(String databaseObject) {
        return databaseObject == null ? null : databaseObject.toLowerCase();
    }

    @Override
    public String to(String userObject) {
        return userObject == null ? null : userObject.toUpperCase();
    }
}

Alternatively one can use Converter.ofNullable(String.class, String.class, String::toLowerCase, String::toUpperCase) to perform the null checks.

But why is this necessary? Under what scenarios might the Converter.from(databaseObject) method receive and be expected to handle null?

score 5 · Answer 1 · answered Mar 23 '23 at 08:18

This question isn't really conceptually different from why can't nullability be guaranteed by jOOQ via the type system, a question that has seen much interest by the kotlin community ever since jOOQ supported a KotlinGenerator. However, it does illustrate the problem from an interesting new angle.

For a discussion about the typing question, see issue #13999.

Simple cases where `NOT NULL` columns become null

Assuming:

CREATE TABLE a (i INT NOT NULL PRIMARY KEY);
CREATE TABLE b (i INT REFERENCES a); -- Optional foreign key

The simple case of querying

// Explicit left join
ctx.select(B.I, A.I)
   .from(B)
   .leftJoin(A).on(B.I.eq(A.I))
   .fetch();

// Implicit left join
ctx.select(B.I, B.a().I)
   .from(B)
   .fetch();

In both of these cases, A.I turns out to be nullable in the query result, despite it being declared NOT NULL in the table definition.

Likewise, when using explicit UNION or implicit ones, e.g. via GROUPING SETS (including the ROLLUP or CUBE syntax sugars), we get the same behaviour:

ctx.select(A.I, count())
   .from(A)
   .groupBy(rollup(A.I))
   .fetch();

This is just syntax sugar for:

ctx.select(A.I, count())
   .from(A)
   .groupBy(A.I)
   .unionAll(
    select(inline(null, A.I.getDataType()), count())
   .from(A)
   .groupBy())
   .fetch();

When the resulting row appears, it's impossible to know whether the first UNION ALL subquery or the second one produced it (we could implement NULL checks in this particular case to identify the subquery, but the projection might not be available, or there may be other reasons why this isn't viable).

In short, in SQL, an expression that can be annotated as NOT NULL in one context suddenly cannot in another. This is why nullability information cannot be assumed trustworthy, at least not via Java's type system.

Making nullability information available at runtime

Of course, it would be possible to propagate nullability throughout the runtime type representation, even if the Java compiler (or kotlin / scala compilers) cannot enforce it. There has been some work in that area, and there will be more: #11070. In a way, that's what you're asking. You're attaching that Converter instance to a specific column, and you would like jOOQ to propagate SQL's algebra to your Converter, avoiding ever passing NULL to it when it appears to be the Right Thing™ to do.

But what is this "Right Thing?" We've seen before that the same expression that is originally NOT NULL can suddenly become NULL. In the jOOQ query case, A.I is still NOT NULL in a trivial query, but the presence of LEFT JOIN, UNION, GROUPING SETS, and a few other operators will change that within the query.

Even if jOOQ did implement clever logic to somehow remember this (at least, when it is possible), it would not be what half (?) of jOOQ's users want, and it wouldn't always work. The above ROLLUP query produces a: Result<Record2<Integer, Integer>>. And when you attach a Converter<Integer, MyType> to your A.ID column, it will become a Result<Record2<MyType, Integer>>.

You can attach that Converter to A.I using code generation, or you can attach the same Converter to A.I within the query using ad-hoc converters:

Result<Record2<MyType, Integer>> result =
ctx.select(A.I.convertFrom(new MyConverter()), count())
   .from(A.I)
   .fetch();

Or, you could use ResultQuery::coerce to attach that same Converter to a query whose contents aren't even type safe (e.g. a plain SQL template), it's all the same to jOOQ.

At the time when jOOQ fetches jOOQ Record values from the underlying JDBC ResultSet, the information about how the Record may have come to be is "lost." In particular, the UNION case shows that it is impossible to know whether that column is now nullable or not.

As a side-note, the UNION case also shows that only the first UNION subquery converters can be used for fetching. A "fun" caveat.

Conclusion

So, since:

Nullability is hard (but not impossible) to propagate throughout a query
Nullability is impossible to derive from arbitrary result sets (thanks to UNION etc.)
Converter is a generic SPI for generic T <-> U data type conversions, irrespective of context.
A Converter may decide to use a non-null "NULL object" representation for its U type.

You simply have to handle the NULL case in each and every Converter implementation. jOOQ can't make any assumptions on your behalf, here.

It's a tradeoff jOOQ made in favour of predictability, logic, simplicity, against the occasional clever "improvement" (which would inevitably turn out to produce very weird caveats and edge cases)

jOOQ Converter for NOT NULL column: Why is it necessary to handle null if the column cannot be null?

1 Answers1

Simple cases where NOT NULL columns become null

Making nullability information available at runtime

Conclusion

Simple cases where `NOT NULL` columns become null