NullPointerException thrown instead of DetachedException, when executing detached query

[jgh]

Expected behavior

I'm trying to Jooq and CTE's to do a 'move' operation as described here

I can delete rows from one table and insert those rows into another table in the one operation.

Actual behavior

I'm getting a null pointer exception

java.lang.NullPointerException
at org.jooq.impl.DefaultExecuteContext.(DefaultExecuteContext.java:311)
at org.jooq.impl.DefaultExecuteContext.(DefaultExecuteContext.java:299)
at org.jooq.impl.AbstractQuery.execute(AbstractQuery.java:263)
at org.jooq.impl.AbstractDelegatingQuery.execute(AbstractDelegatingQuery.java:111)

Steps to reproduce the problem

Create two tables

create table tmp1 (name varchar);
create table tmp2 (name varchar);

Run jooq code generation

Test kotlin code:

val create = DSL.using(config)
create.insertInto(Tables.TMP1).values("ONE").execute()
create.insertInto(Tables.TMP1).values("TWO").execute()

//the delete statement I want to put in my cte
val deleteResultStep = create.delete(TMP1).returningResult(TMP1.NAME)

println(deleteResultStep.getSQL(ParamType.INLINED))
//when running delete by itself it works
deleteResultStep.fetch().forEach {
    //this prints the 2 rows returned from the delete
    println(it)
}

//add the rows back into table
create.insertInto(Tables.TMP1).values("ONE").execute()
create.insertInto(Tables.TMP1).values("TWO").execute()

//now put the same statement into a cte
val deleteCte = DSL.name("a").fields("name").`as`(deleteResultStep)

//use cte to move data from tmp1 to tmp2
val move = DSL.with(deleteCte).insertInto(Tables.TMP2).columns(Tables.TMP2.NAME)
	.select(create.select(deleteCte.field("name", Tables.TMP1.NAME.dataType)).from(deleteCte))

//this sql is missing the returning clause that was present when executed standalone
println(move.getSQL(ParamType.INLINED))

//npe here
val execute = move.execute()
println(execute)

When I print out the deleteResultStep above I get:

delete from "public"."tmp1" returning "public"."tmp1"."name"

note the 'returning' clause.

Now when I println out the deleteCte above I get:

with "a"("name") as (delete from "public"."tmp1") insert into "public"."tmp2" ("name") select "a"."name" from "a"

note the returning clause has gone missing.

executing that statement returns:

[42P10] ERROR: WITH query "a" has 0 columns available but 1 columns specified
Position: 6

I change this to

with "a"("name") as (delete from "public"."tmp1" returning name) insert into "public"."tmp2" ("name") select "a"."name" from "a";

It executes correctly and data is moved from table 1 to 2.

jOOQ Version

Community Edition 3.15.1

Database product and version

PostgreSQL 14.2 on aarch64-unknown-linux-musl, compiled by gcc (Alpine 10.3.1_git20211027) 10.3.1 20211027, 64-bit

Java Version

openjdk version "11.0.13" 2021-10-19 LTS OpenJDK Runtime Environment Zulu11.52+13-CA (build 11.0.13+8-LTS) OpenJDK 64-Bit Server VM Zulu11.52+13-CA (build 11.0.13+8-LTS, mixed mode)

OS Version

MacOS, uname: Darwin Kernel Version 21.5.0: Tue Apr 26 21:08:37 PDT 2022; root:xnu-8020.121.3~4/RELEASE_ARM64_T6000

JDBC driver name and version (include name if unofficial driver)

org.postgresql:postgresql:42.4.0

[lukaseder]

Thanks a lot for your report. I will look into this soon

[lukaseder]

Just to be sure, you're using an older version of jOOQ. Can you reproduce this with 3.17.3?

[lukaseder]

Ah, I get it. The NPE is obviously confusing. I can reproduce it, though I get a different stack trace on jOOQ 3.17. Instead, a DetachedException should be thrown.

You're doing:

val move = DSL.with(deleteCte)

When you should be doing:

val move = create.with(deleteCte)

Your CTE query isn't attached to your configuration.

[lukaseder]

It's not related to using DELETE .. RETURNING. This also reproduces the NPE:

DSL.with(name("t").as(selectOne())).selectOne().execute()

[lukaseder]

Fixed in jOOQ 3.18.0, 3.17.4 (#13929), and 3.16.10 (#13930).

Thanks again for your report!

[jgh]

I tested the change you suggested and it is working now. Thanks for the help.