Dear group, There is an issue that has been appearing a couple of times on this user group...
- https://groups.google.com/d/msg/jooq-user/zibf1EmQdp8/8bDkIrLiRvQJ - https://groups.google.com/d/msg/jooq-user/BNWthTVXH7c/EBeWh3zJYAIJ ... and on GitHub... - https://github.com/jOOQ/jOOQ/issues/3780 ... etc. The issue is essentially the confusion that happens when people run: SELECT FOO.ID, BAR.ID FROM FOO, BAR SELECT FOO.ID FROM FOO Both results can be mapped correctly onto a FooRecord as generated by jOOQ, containing only FOO.ID. The first result can also be mapped correctly onto a BarRecord, containing only BAR.ID. *Now, the confusion* ...originates from this feature of jOOQ where the second result can also be mapped onto a BarRecord, which will contain the value of FOO.ID in its " BAR.ID" column, by virtue of "equal enough" column name. There are several reasons for this behaviour in jOOQ, most importantly the fact, that SQL really doesn't know any fully qualified column names as projection output. Specifically, only top-level SELECT statements are allowed to have this ambiguity of having two distinct ID columns, which are both named ID. You cannot use this in a subquery / derived table: -- wrong SELECT * FROM ( SELECT FOO.ID, BAR.ID FROM FOO, BAR ) In other words, once a SQL statement has finished with FROM .. WHERE .. GROUP BY .. HAVING and SELECT, there isn't really any such notion as fully qualified column anymore. All that's left is a column with a name. Another example for this are UNIONs and ORDER BY: WITH foo(id) AS (SELECT 1 FROM DUAL), bar(id) AS (SELECT 2 FROM DUAL) SELECT foo.id, bar.id FROM foo, bar UNION ALL SELECT foo.id, bar.id FROM foo, bar ORDER BY 1 -- There is really no other way to order by foo.id, here If you want to map this onto FooRecord, or BarRecord, bpth ID values will be mapped (resulting in the latter to persist) *How to add features to jOOQ to make your lives easier?* >From a mere SQL perspective, I do believe that jOOQ behaves correctly / optimally. But from a Java mapping perspective, the above may prove to be confusing, specifically if people have an ORM / Hibernate background, where such situations cannot happen because queries are not expressed in SQL, but in JPQL, which doesn't support UNIONs or derived tables, and you now understand why. Nonetheless, I'm more than willing to hear ideas that contribute to the improvement jOOQ and to the prevention of this confusion. For instance: - It would probably be useful to be able to prefix all column names in a table to FOO_ID, BAR_ID with a special Table.as() method - It would probably be useful to improve the existing mapping APIs - but how? - What else could we improve? *Your feedback, as always, is very welcome!* 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 [email protected]. For more options, visit https://groups.google.com/d/optout.
