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
2015-11-10 0:48 GMT+01:00 Max Kremer <[email protected]>:
>
> 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?
>
>
>
>
>
>
> --
> 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 [email protected].
> For more options, visit https://groups.google.com/d/optout.
>
--
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 [email protected].
For more options, visit https://groups.google.com/d/optout.
