jooq, postgres and update and returning data from multible tables fails

Question

I'm stumped on formulating a jooq query to return data from two tables when updating a field in one of those tables. (Postgres 9.6, jooq 3.11)

Tables DEVICE and CUSTOMER are joined on a foreign key constraint DEVICE.CUSTOMERID = CUSTOMER.ID.

I want to return a single eligible device.id and the customer.secret of the devices customer and update device.state to IN_PROGRESS. Eligibility is assessed by various clauses.

I started with

  final Record task = db
      .update(DEVICE)
      .set(DEVICE.STATE, StateEnum.IN_PROGRESS)
      .from(CUSTOMER)
         .where(DEVICE.CUSTOMERID.eq(CUSTOMER.ID))
         .and(DEVICE.STATE.eq(StateEnum.NEW))
         .and(CUSTOMER.SECRETCONFIRMED.eq(true))
      .returning(DEVICE.ID, CUSTOMER.SECRET)
      .fetchOne();

This resulted in ERROR: Field ("public"."customer"."secret") is not contained in Row

I modeled another query based on this Postgres RETURNING clause with join but ended up with the same error.

  final Record task = db
      .update(DEVICE)
      .set(DEVICE.STATE, StateEnum.IN_PROGRESS)
      .from(CUSTOMER, db
         .select()
         .from(CUSTOMER
            .join(DEVICE).on(CUSTOMER.ID.eq(DEVICE.CUSTOMERID)))
         .forUpdate()
      )
      .where(DEVICE.STATE.eq(StateEnum.NEW))
      .and(CUSTOMER.SECRETCONFIRMED.eq(true))
      .returning(DEVICE.ID, CUSTOMER.SECRET)
      .fetchOne();

 ERROR:  Field ("public"."customer"."secret") is not contained in Row

I tried several permutations and ended up with error some error variations

table name "<customer/device>" specified more than once
missing FROM-clause entry for table "<customer/device>"

I'm confident, that it is possible to make such a query, but I've run out of ideas. Any suggestions?

A representative list of variants I tried

  final Record task = db
      .update(DEVICE)
      .set(DEVICE.STATE, StateEnum.IN_PROGRESS)
      .from(
          DEVICE.join(CUSTOMER).on(DEVICE.CUSTOMERID.eq(CUSTOMER.ID)))
      .where(DEVICE.STATE.eq(StateEnum.NEW))
      .and(CUSTOMER.SECRETCONFIRMED.eq(true))
      .returning(DEVICE.ID, CUSTOMER.SECRET)
      .fetchOne();

  ERROR: table name "device" specified more than once

  final Record task = dbContext
  .update(DEVICE.join(CUSTOMER).on(DEVICE.CUSTOMERID.eq(CUSTOMER.ID)))
      .set(DEVICE.STATE, StateEnum.IN_PROGRESS)
      .where(DEVICE.STATE.eq(StateEnum.NEW))
      .and(CUSTOMER.SECRETCONFIRMED.eq(true))
      .returning(DEVICE.ID, CUSTOMER.SECRET)
      .fetchOne();

  ERROR: syntax error at or near "join"

  final Record task = db
      .update(DEVICE)
      .set(DEVICE.STATE, StateEnum.IN_PROGRESS)
      .from(db.select().from(CUSTOMER,
          DEVICE.join(CUSTOMER).on(DEVICE.CUSTOMERID.eq(CUSTOMER.ID))).forUpdate()
      )
      .where(DEVICE.STATE.eq(StateEnum.NEW))
      .and(CUSTOMER.SECRETCONFIRMED.eq(true))
      .returning(DEVICE.ID, CUSTOMER.SECRET)
      .fetchOne();

  ERROR: table name "customer" specified more than once

  final Record task = db
      .update(DEVICE)
      .set(DEVICE.STATE, StateEnum.IN_PROGRESS)
      .from(db.select().from(
          DEVICE.join(CUSTOMER).on(DEVICE.CUSTOMERID.eq(CUSTOMER.ID))).forUpdate()
      )
      .where(DEVICE.STATE.eq(StateEnum.NEW))
      .and(CUSTOMER.SECRETCONFIRMED.eq(true))
      .returning(DEVICE.ID, CUSTOMER.SECRET)
      .fetchOne();

  ERROR: missing FROM-clause entry for table "customer"


  final Record task = db
      .update(DEVICE)
      .set(DEVICE.STATE, StateEnum.IN_PROGRESS)          

    .from(CUSTOMER.join(DEVICE).on(CUSTOMER.ID.eq(DEVICE.CUSTOMERID)))
      .where(DEVICE.STATE.eq(StateEnum.NEW))
      .and(CUSTOMER.SECRETCONFIRMED.eq(true))
      .returning(DEVICE.ID, CUSTOMER.SECRET)
      .fetchOne();

  ERROR: table name "device" specified more than once

score 3 · Accepted Answer · answered Aug 20 '18 at 06:45

This is because of an old design error in the jOOQ API, where the returning() clause results in a return type of Result<R> or R, rather than the actual columns you're fetching. So, you can only return columns from the table being updated, not from any other tables, or arbitrary expressions. Because of backwards-compatibility, this could not be changed easily.

However, since jOOQ 3.11, you can now call returningResult() as a workaround to get the exact row type you're specifying: https://github.com/jOOQ/jOOQ/issues/7475

Thanks, works like a charm. May I suggest an update to the otherwise very fine manual page for the update..returning feature, to point out this discrepancy? — leisurelarry, Aug 21 '18 at 12:53

jooq, postgres and update and returning data from multible tables fails

1 Answers1