Possible bug in JOOQ's data record to column mapping functionality

[Max Kremer]

I recently ran into a strange issue around retrieving data from a JOOQ record. I wanted to post it here before logging a bug officially in GitHub.

The issue is basically an inconsistency around how you need to access the result of a select depending on how that query is executed. The following code illustrates:

String someFieldName = "foo"
Field<?> groupBreakdownField = field("P."+someFieldName);  //P.foo

return dsl.select(  groupBreakdownField, countDistinct(field("P.id")).as("thecount")  )
           .from( table("person").as("P"))
           .groupBy(groupBreakdownField)
           .fetch().toStream()
         .map( r- > new Group(r.getValue(groupBreakdownField, String.class), r.getValue("thecount", Integer.class)))
                          .collect( ....

The query constructed above is goes something like this

SELECT P.foo, count(distinct P.id) as thecount
FROM person P
GROUP BY P.foo

Also note that I'm using groupBreakdownField to map the rows of the result set to my pojo. In the above example everything works fine. 

Now if we change the query to execute a SQL created by JOOQ then something breaks. 

String someFieldName = "foo"
Field<?> groupBreakdownField = field("P."+someFieldName);  //P.foo

return dsl.fetch(select(  groupBreakdownField, countDistinct(field("P.id")).as("thecount")  )
           .from( table("person").as("P"))
           .groupBy(groupBreakdownField)
           ).toStream()
          .map( r- > new Group(r.getValue(groupBreakdownField, String.class), r.getValue("thecount", Integer.class)))
                          .collect( ....

The above code is similar but instead of calling fetch on the JOOQ query I'm calling fetch and passing it String SQL created by jooq DSL. The query run by the DB is exactly the same, however we get the following error when trying to pull the values out of the result

Caused by: java.lang.IllegalArgumentException: Field (P.foo) is not contained in Row (foo, thecount)

at org.jooq.impl.Utils.indexOrFail(Utils.java:1059) ~[jooq-3.6.2.jar:na]

at org.jooq.impl.AbstractRecord.getValue(AbstractRecord.java:284) ~[jooq-3.6.2.jar:na]

at org.jooq.impl.AbstractRecord.getValue(AbstractRecord.java:295) ~[jooq-3.6.2.jar:na]

Now JOOQ doesn't understand P.foo. It seems it's not respecting my alias. If change r.getValue(groupBreakdownField, String.class) to r.getValue("foo", String.class) then everything works. 

Why does it not complain in the first case that it can't find P.foo? 

[Lukas Eder]

Hi Max,

I cannot reproduce the issue the way you've presented things. Both execution modes are equivalent.

Are we seeing the whole picture here, or did you perhaps omit a detail that might be interesting for reproduction?

A couple of general thoughts:

- If you can avoid it, you shouldn't use the "plain SQL" field() constructor to produce qualified field names such as "P.foo". Prefer using the more formal version: field(name("P", "foo")). This is explained here: http://www.jooq.org/doc/latest/manual/sql-building/names

- Case sensitivity may be an issue down the line. If you're mixing lower case column names with upper case table names, at some point, the reason for something not working may just be case sensitivity (this wouldn't be an issue with the field(name("P", "foo")) method)

- jOOQ doesn't have a toStream() method. I suspect this is just a typo, not some implementation of your own?

Cheers,

Lukas

--
You received this message because you are subscribed to the Google Groups "jOOQ User Group" group.
To unsubscribe from this group and stop receiving emails from it, send an email to jooq-user+...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[Max Kremer]

Hi Lukas,

  You're right, there's a typo in my code sample. It should be stream() not toStream().

Another thing I should mention is that I'm using RenderNameStyle.AS_IS in my org.jooq.conf.Settings

I made the change you prescribed by doing field(name("P", "foo")) and that fixes the second case (no longer throwing an exception)... But it still doesn't explain why the first case worked with out adding the "P" qualifier using the name function

[Lukas Eder]

Hi Max,

Thanks for your additional info

2015-11-10 17:47 GMT+01:00 Max Kremer <mkr...@trialfire.com>:

Hi Lukas,

  You're right, there's a typo in my code sample. It should be stream() not toStream().

Another thing I should mention is that I'm using RenderNameStyle.AS_IS in my org.jooq.conf.Settings

Yes, I've noticed that. Otherwise, you might have had additional issues, as renaming in jOOQ via as() always creates case sensitive names.

 

I made the change you prescribed by doing field(name("P", "foo")) and that fixes the second case (no longer throwing an exception)... But it still doesn't explain why the first case worked with out adding the "P" qualifier using the name function

I don't see why this should happen either, but as I've mentioned in my previous E-Mail, I couldn't reproduce it. Both of your versions worked fine for me...

Is there anything else you might share to help reproduce the issue? Did you also try things with the latest jOOQ version(s)? E.g. 3.6.4, or 3.7.0?