How to resolve ambiguous match when chaining generated Jooq classes

Question

I have defined my models in JPA and am writing some queries for my application and I am using JOOQ generated classes to join all the tables together to check if the requested resources actually belong to the requesting user.

However, when I do this I get the following warning:

Ambiguous match found for ID. Both "alias_4548634"."ID" and "alias_47496750"."ID" match.

java.sql.SQLWarning: null                                                                                                                                       
        at org.jooq.impl.Fields.field(Fields.java:132) ~[jooq-3.11.10.jar:?]
... etc

This is my code

db.select(countField)
  .from(thing)
  .where(JThing.THING.thingBucket().bucket().organization().customer().ID.in(idList))
  .orderBy(countField)

This is the SQL it generates

SELECT 
  count(PUBLIC.THING.ID) AS count
FROM (
  PUBLIC.THING
    LEFT OUTER JOIN (
      PUBLIC.THING_BUCKET AS alias_72652126
        LEFT OUTER JOIN (
          PUBLIC.BUCKET AS alias_4548634
            LEFT OUTER JOIN (
              PUBLIC.ORGANISATION AS alias_43016761
                LEFT OUTER JOIN PUBLIC.CUSTOMER AS alias_47496750
                ON alias_43016761.CUSTOMER_ID = alias_47496750.ID
            )
            ON alias_4548634.ORGANISATION_ID = alias_43016761.ID
        )
        ON alias_72652126.ID = alias_4548634.ID
    )
    ON PUBLIC.THING.THING_BUCKET_ID = alias_72652126.ID
  )
WHERE alias_47496750.ID IN (81353)
ORDER BY count

Given that JOOQ is generating the SQL I'd expect it to be able to understand it without throwing an error. What am I missing? How do I do configure/query/whatever to resolve the SQLWarning?

UPDATE

After playing around I've identified the source of the issue.

THING_BUCKET is sub-type of BUCKET so that THING_BUCKET.ID = BUCKET.ID

if I rewrite the query to I get the same results, but without the error

SELECT 
  count(PUBLIC.THING.ID) AS count
FROM (
  PUBLIC.THING
        LEFT OUTER JOIN (
          PUBLIC.BUCKET AS alias_4548634
            LEFT OUTER JOIN (
              PUBLIC.ORGANISATION AS alias_43016761
                LEFT OUTER JOIN PUBLIC.CUSTOMER AS alias_47496750
                ON alias_43016761.CUSTOMER_ID = alias_47496750.ID
            )
            ON alias_4548634.ORGANISATION_ID = alias_43016761.ID
        )
    ON PUBLIC.THING.BUCKET_ID = alias_4548634.ID
  )
WHERE alias_47496750.ID IN (81353)
ORDER BY count

So what I would like to be able to do is go

db.select(countField)
  .from(thing)
  .where(JThing.THING.bucket().organization().customer().ID.in(idList))
  .orderBy(countField)

and join my THING directly to the BUCKET rather then the THING_BUCKET, but I do not know how to accomplish this with the generated classes.

Answer 1

This looks like a bug that has been fixed in jOOQ 3.14, see #8659, #10603