Unions and parentheses are a huge can of worms across all the 25 currently supported dialects in jOOQ. By default, we better play safe and add some additional syntax that mostly doesn't hurt, rather than leaving it away, and then run into tons of edge cases.
The H2 issues you've linked are edge cases already in H2, and there's probably a workaround (we might help with that, if we can see the actual query). We have a lot of integration tests testing a variety of combinations of operators against all of those 25 dialects, and the syntax that is currently being generated by jOOQ is the one that fails the least amount of integration tests. As you can see, I've been involved with one of the issues you've linked ( https://github.com/h2database/h2database/issues/918) as our integration tests test the weirdest kind of SQL that might even help discover bugs in various database products. Having said so, in the future, we want to be more sophisticated with the SQL we generate, and avoid generating unnecessary parentheses (this also affects boolean value expressions, for example). But for that, we need a lot more integration tests and a lot better internal query model that allows for better transformations / introspections, etc. In the case of parenthesised union subqueries, at least these things need to be considered: - Are there only unions (single operator type, no precedence issues), or also union all? - Are there only unions or also exceptions / intersections? - Do union subqueries contain order by / limit / offset - Do union subqueries contain with? - Do union subqueries contain other set operators like union? - Is the query a top level query or a derived table or a correlated subquery or something else? "Just" removing the parentheses will definitely break some tests in some databases, so we'd have to figure out when exactly they can be removed on which dialect. And that's currently not a priority, I'm afraid. Related to your use-case, have you tried using https://www.testcontainers.org/ instead? Thanks, Lukas On Wed, Aug 7, 2019 at 10:44 AM Ohad Pinchevsky <[email protected]> wrote: > Hi Knut, > > I am using PostgreSQL dialect - in which the parentheses are working, but > not required; > > Also, I'm using H2 in memory database for unit testing and because of > issue/limitation on their side in that use case, I was thinking maybe I can > solve it from JOOQ side, > > The H2 issues: > https://github.com/h2database/h2database/issues/2050 > https://github.com/h2database/h2database/issues/918 > > So, I hope you can think on a workaround for me... > > Thanks, > Ohad > > > On Wednesday, August 7, 2019 at 10:23:28 AM UTC+3, Knut Wannheden wrote: >> >> Hi Ohad, >> >> Thank you for your message. >> >> On Wed, Aug 7, 2019 at 9:07 AM Ohad Pinchevsky <[email protected]> >> wrote: >> >>> I noticed that parentheses added to select statement inside a query (for >>> example between union statement), is it possible to control it? >>> >> >> No, this isn't easily possible. Can you tell us a little bit more about >> why you would like to do this? >> >> Please also note that depending on the actual dialect the parentheses are >> actually required in some context, as for example the UNION clause you >> mention. >> >> Possibly you have found a case (for a certain dialect) where the >> parentheses are indeed not required. In that case I suggest you report this >> as an issue (https://github.com/jOOQ/jOOQ/issues/new/choose), so that we >> can look into improving that. >> >> Regards, >> Knut >> > -- > 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]. > To view this discussion on the web visit > https://groups.google.com/d/msgid/jooq-user/50be1d2e-d4f9-4051-974c-b75fa4ffc21a%40googlegroups.com > <https://groups.google.com/d/msgid/jooq-user/50be1d2e-d4f9-4051-974c-b75fa4ffc21a%40googlegroups.com?utm_medium=email&utm_source=footer> > . > -- 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]. To view this discussion on the web visit https://groups.google.com/d/msgid/jooq-user/CAB4ELO6TP7X5ohohkd%2BvJZxp1kH2N%2Byybmn5Oo4GxCz8%3D%2B%2BoMw%40mail.gmail.com.
