2014-10-20 20:27 GMT+02:00 Alok Menghrajani <[email protected]>: > On Wed, Oct 15, 2014 at 10:13 AM, Lukas Eder <[email protected]> wrote: > >> I have committed: >> >> - >> https://github.com/jOOQ/jOOQ/commit/d77e77696b10f1c6fe95c99f357330cb369eb077 >> - >> https://github.com/jOOQ/jOOQ/commit/cf00bd9d6d71ec71f8a0912fc077766858971f68 >> >> ... and some other changes. This fix will take care of the type of query >> that you were writing. It doesn't work for most databases yet, in >> particular not for Oracle, SQL Server, Sybase, and DB2. >> >> > I can confirm that this is now fixed, thanks! >
Great, that's good news > > Some caveats that I encountered: >> >> - Some databases don't allow any ORDER BY clause in UNION subselects. In >> those cases, the subselect must be transformed into a nested table >> - The nested LIMIT clauses are not yet correctly emulated in Oracle, SQL >> Server 2008, Sybase and DB2 when used in a UNION subselect >> - Derived tables are currently enforced for UNION subselects also for >> MySQL in situations where they wouldn't be strictly necessary >> > > Which might explain why the resulting query has "select * from (select * > from ...)": > > select * from (select * from jooqtest2 where name = 'abcdef' order by ts > asc limit 1) x union all select * from (select * from jooqtest2 where name > = 'foobar' order by ts asc limit 1) x; > > There might be some performance improvements you could do down the road, > but at least the query returns the expected result. > Yes, I'm aware of the performance implications that may incur due to derived tables. I'm sure that this will be fixed by 3.5.0 GA This whole thing will be an interesting blog post subject. How to correctly >> transform the following query into valid SQL for all 17 supported RDBMS: >> > > Totally! Let me know if you need help with anything. > Thanks. I guess I'll just run a loop over SQLDialect.families() to let jOOQ generate the SQL for all 17 RDBMS and dump the output in a blog post with some explanations :-) -- 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.
