A Condition is a Field – Java, SQL and jOOQ.

Starting with jOOQ 3.17, the Condition type extends the Field<Boolean> type. Because, that’s what the SQL standard thinks it is, in sorts:

<boolean value expression> ::=
  <predicate>

The exact definition contains intermediate rules, but you get the idea. A <predicate> (which is a Condition in jOOQ) can be used wherever a <boolean value expression> can be used, which again can be used in projections, predicates, and elsewhere.

Not all SQL dialects work this way, and in fact, before SQL:1999 standardised on the BOOLEAN data type, SQL itself didn’t work this way. SQL-92, for example, listed <predicate> as a possible substitute for the <search condition> only, which is used for example in <where clause>, but not in any ordinary <value expression>.

Hence, while this works in PostgreSQL, which supports standard SQL BOOLEAN types:

SELECT id, id > 2 AS big_id
FROM book
ORDER BY id

Producing:

|id |big_id|
|---|------|
|1  |false |
|2  |false |
|3  |true  |
|4  |true  |

It doesn’t work in Oracle, for example, which delights us with the usual useful error message:

SQL Error [923] [42000]: ORA-00923: FROM keyword not found where expected

How this used to work in jOOQ 3.16 or less

jOOQ has always supported a way to use Condition and Field<Boolean> exchangeably. There are the two wrapper methods:

• DSL.field(Condition) returns Field<Boolean>
• DSL.condition(Field<Boolean>) returns Condition

This is documented here. As such, the previous query could have been written as follows:

Result<Record2<Integer, Boolean>> result =
ctx.select(BOOK.ID, field(BOOK.ID.gt(2)).as("big_id"))
//                  ^^^^^^^^^^^^^^^^^^^^ wrapping condition with field()
   .from(BOOK)
   .orderBy(BOOK.ID)
   .fetch();

The generated SQL looks like this, for PostgreSQL:

SELECT
  book.id,
  (book.id > 2) AS big_id
FROM book
ORDER BY book.id

And for Oracle, this is the emulation of the feature:

SELECT
  book.id,
  CASE
    WHEN book.id > 2 THEN 1
    WHEN NOT (book.id > 2) THEN 0
  END big_id
FROM book
ORDER BY book.id

The emulation preserves our beloved three valued logic, i.e. the BOOLEAN value is NULL in case BOOK.ID is NULL.

How this works in jOOQ 3.17, now

Starting from jOOQ 3.17 and #11969, this manual wrapping of field(Condition) is no longer necessary, and you can just project the Condition directly:

Result<Record2<Integer, Boolean>> result =
ctx.select(BOOK.ID, BOOK.ID.gt(2).as("big_id"))
   //               ^^^^^^^^^^^^^ no more wrapping necessary
   .from(BOOK)
   .orderBy(BOOK.ID)
   .fetch();

The behaviour is exactly the same as if you had wrapped the condition (including the result type), and the emulation still kicks in also for Oracle and other dialects that don’t support BOOLEAN value expressions. This means you can also use Condition in other clauses that take Field types, including, e.g.:

• GROUP BY or PARTITION BY
• ORDER BY

Time to upgrade your jOOQ version!