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.
