Confusing error message when reading a non-existing Field from a Record containing accidentally nested row types

[lukaseder]

Since jOOQ 3.17, users may be accidentally nesting records in their queries:

• Let Table<R> extend SelectField<R> #4727
• https://stackoverflow.com/q/76258061/521799

An example:

ctx.select(CONFIGURATION)
   .from(CONFIGURATION)
   .where(CONFIGURATION.DEVICE_ID.eq(id))
   .fetch();

When trying to read a CONFIGURATION column from the resulting nested record, this error message appears:

Exception in thread "" java.lang.IllegalArgumentException: Field ("public"."configuration"."minimum_temperature_threshold") is not contained in Row (row (
  "public"."configuration"."configuration_id",
  "public"."configuration"."minimum_temperature_threshold",
  "public"."configuration"."maximum_temperature_threshold",
  "public"."configuration"."ideal_temperature",
  "public"."configuration"."minimum_ph_threshold",
  "public"."configuration"."maximum_ph_threshold",
  "public"."configuration"."ideal_ph",
  "public"."configuration"."light_on_time",
  "public"."configuration"."light_off_time",
  "public"."configuration"."update_time",
  "public"."configuration"."device_id"
))

The fact that Row (row ( means the row is nested is too subtle to debug this. It appears that the requested column is right there!

Probably, the error message can be improved to help users here.

[lukaseder]

I'll try this error message:

Field "ID1" is not contained at the top level of row type containing nested row types (row ("TABLE1"."ID1", "TABLE1"."NAME1", "TABLE1"."DATE1"))

The problem is really specific to the fact that the row type is Record1<NestedRecordType> in this particular case. If multiple nested row types are available (e.g. Record3<Integer, String, NestedRecordType>), then I think the API usage and error messages are less confusing.

As a side note, it might be possible to allow such access of nested row attributes via top level column references, pretending the record is flat. I'm not sure yet if this is a good idea, but here's a feature request for this:

• Support accessing nested row type attributes from top level row type with their Field reference #16721

[lukaseder]

Without actually implementing #16721, some of its logic can already be implemented to help with the error message, though. I.e. we can look up the ID1 column in all nested rows and check if we would have found it if #16721 was implemented, and then hint at the mistake.

[lukaseder]

This message improvement should also apply to index based field access

[lukaseder]

Fixed in versions:

• 3.20.0
• 3.19.9 (Confusing error message when reading a non-existing Field from a Record containing accidentally nested row types - Merge [#15085] #16722)
• 3.18.16 (Confusing error message when reading a non-existing Field from a Record containing accidentally nested row types - Merge [#15085] #16723)
• 3.17.25 (Confusing error message when reading a non-existing Field from a Record containing accidentally nested row types - Merge [#15085] #16724)