Is it possible to remove parentheses of SELECT statement

[Ohad Pinchevsky]

I noticed that parentheses added to select statement inside a query (for example between union statement), is it possible to control it?

removing specific select statement parentheses? or to remove them completely?

via flag or method or post processor ?

for example:

(select x from y)

union

(select x from y)

to be:

select x from y

union

select x from y

Thanks,

Ohad

[Knut Wannheden]

Hi Ohad,

Thank you for your message.

On Wed, Aug 7, 2019 at 9:07 AM Ohad Pinchevsky <ohad.pi...@gmail.com> 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

[Ohad Pinchevsky]

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

[Lukas Eder]

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

--
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.
To view this discussion on the web visit https://groups.google.com/d/msgid/jooq-user/50be1d2e-d4f9-4051-974c-b75fa4ffc21a%40googlegroups.com.

[Ohad Pinchevsky]

Thanks for your reply! 

I'll check the testcontainers alternative...

Thanks,

Ohad 

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.