Hi there, Are you using jOOQ 3.5.0-SNAPSHOT? We might still have 1-2 regressions as we're implementing more complete SQL standard UNION support. I don't think this issue should appear with jOOQ 3.4.2...
Best Regards, Lukas 2014-10-05 0:02 GMT+02:00 Alok Menghrajani <[email protected]>: > Hi, > > I have a piece of code which is resulting in incorrect MySQL queries. > I read https://github.com/jOOQ/jOOQ/issues/288, and I'm not sure if my > case is a bug or falls into a limitation when using MySQL. > > Besides dumping the database, is there a way to fix such queries? > > DSLContext create = DSL.using(conn, SQLDialect.MYSQL); > Select<Record> s1 = create.select() > .from("jooqtest2") > .where(field("name").eq("abcdef")) > .orderBy(field("ts")) > .limit(1); > Select<Record> s2 = create.select() > .from("jooqtest2") > .where(field("name").eq("foobar")) > .orderBy(field("ts")) > .limit(1); > Select<Record> s3 = s1.unionAll(s2); > System.out.println(s3.getSQL()); > > Results in: > "select * from jooqtest2 where name = ? union all select * from > jooqtest2 where name = ? order by ts asc limit ? order by ts asc limit > ?" > > Which is invalid MySQL. A valid query would probably be something like: > "(select * from jooqtest2 where name = ? order by ts asc limit 1) > union all (select * from jooqtest2 where name = ? order by ts asc > limit ?)" > > The differences are: > 1. the "order by ts asc" and "limit ?" of the first query appear at > the end of the first select, not at the end of the whole query. > 2. parenthesis around each select. > > > Thanks, > Alok > > -- > 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.
