JOOQ “IN” Query throws null pointer exception

When we try to fetch data with Null values

field(TABLE_NAME.COLUMN_NAME.in(null))

with IN clause getting null pointer exception.

Maybe because of this.

  @Override
    public final Condition in(Collection<?> values) {
        Field<?>[] fields = new Field[values.size()];

        Iterator<?> it = values.iterator();
        for (int i = 0; it.hasNext(); i++)
            fields[i] = Tools.field(it.next(), this);

        return in(fields);
    }

In the database, we can provide null in IN clause.

There is an existing “won’t fix” issue in jooq https://github.com/jOOQ/jOOQ/issues/3867

There are some alternatives:

• check null before IN(Cant do in my case its a really big select statement)

So if I want to make this possible is there any other workaround.

PS: On a similar note “eq” works perfectly fine:

@Override
    public final Condition equal(Field<T> field) {
        return compare(EQUALS, nullSafe(field, getDataType()));
    }

Edit: ‘field(TABLE_NAME.COLUMN_NAME.in(null))’ here null is a collection.

Answer

Your example code doesn’t compile:

TABLE_NAME.COLUMN_NAME.in(null)

There are 5 overloads of this in() method in jOOQ 3.14, and as such, you cannot pass the null literal to the in() method. Your real client code may be using a local variable like this:

Collection<?> collection = null;
TABLE_NAME.COLUMN_NAME.in(collection)

There might be a case for when this should behave the same as passing an empty collection, such as Collections.emptyList(), but this isn’t what you seem to want. You probably want to pass actual null values inside of that collection, which you can do:

TABLE_NAME.COLUMN_NAME.in(1, null, 2)

But why would you do it? SQL implements three valued logic, meaning that NULL values have no effect in IN predicates, while they have an unintuitive, hardly desired effect in NOT IN predicates (the entire predicate becomes NULL)