Erroneous Field in jOOQ Record

Question

I have 3 tables A, B and C which are part of an inheritance chain:

  A
 / \
B   C

and look like:

A(row_id)
B(row_id, a_row_id REFERENCES A(row_id))
C(row_id, a_row_id REFERENCES A(row_id))

Note that the column names are the same (a_row_id)

Now, I fetch Records of B and C by doing:

ctx.select()
    .from(
        Tables.A
        .leftOuterJoin(Tables.B).onKey()
        .leftOuterJoin(Tables.C).onKey()
    )
    .where(someCondition)
    .fetch()

I join over both B and C because I don't (at this point) know which one I'm looking for.

But, when I know I need to fetch B and do this:

ctx.select()
    .from(
        Tables.A
        .leftOuterJoin(Tables.B).onKey()
    )
    .where(someCondition)
    .fetch()

I get a Record on which if I do record.field(Tables.B.A_ROW_ID).toString(), I get "b.a_row_id", and if I do record.getValue(Tables.B.A_ROW_ID) I get the expected value.

However, If I do record.field(Tables.C.A_ROW_ID).toString(), I get "b.a_row_id" and record.getValue(Tables.C.A_ROW_ID) gives me the expected value of record.getValue(Tables.B.A_ROW_ID).

I think this is happening because the column names referring to the parent table are the same.

Is jOOQ using only the field_name of the table (and not the fully qualified "table.field_name") if that is the only table in the join?

Any help on this would be greatly appreciated.

Answer 1

The semantics of Record.field(Field), Record.field(Name), and Record.field(String) all follow the same, consistent logic:

In SQL, a record's column has a name. That name can be qualified if it originates from a table / view (in a schema (in a catalog)), but that is optional. Unqualified columns are perfectly fine too, e.g. when:

• Aliasing the column
• Creating expressions, such as col + 1
• Using JOIN .. USING, in case of which columns are not qualified
• Using derived tables (which may have a table name, but certainly not a schema / catalog)
• Using SELECT * with vendor-specific, column-emitting operators like PIVOT, MODEL, MATCH_RECOGNIZE.
• Etc.

As you can see, from a syntax perspective, having qualified column names are the exception, not the rule. Thus, the most reasonable and generally useful implementation of Record.field(Field) is to:

• Find the exact match inside of the record (fully qualified column name)
• If that fails, find the approximate match inside of the record (unqualified column name)
• If that is "ambiguous" (because top-level selects allow for such ambiguity), then:
  • jOOQ 3.8+ logs a warning
  • jOOQ 4.0+ will throw an exception

So, the behaviour you've observed is correct.

Answer 2

Lukas already answered the main question but I just wanted to note to you that in the end jOOQ is running a SQL statement. It's bounded for security and caching but it's still a SQL statement. You can see the SQL itself if you turn up logging.
If you like the returning a dynamic record and you want column to be X you can always do .as("X") to make it easier to read the data back out.