Hi Gunther, Thanks for reporting this. It would indeed be a shame if you couldn't upgrade to jOOQ 3.4, given all the new features we've been adding since 2.3 :-)
I've added my comment on the MySQL bug and I've also pinged Morgan Tocker on Twitter (https://twitter.com/lukaseder/status/514352788030361600). In the short run, yes we absolutely do perform these kinds of patches when databases have issues that don't get resolved quickly. We've done so numerous times in the past, as jOOQ is exactly the right place to implement these workarounds. Here's my suggestion: You provide a pull request on GitHub with your working alternative org.jooq.impl.Val implementation, and I'll see if this will still work with our integration test suite. If you're unfamiliar with pull requests, you can also just simply send a .diff file, or the complete Val.java to this thread on the user group. Best Regards, Lukas Am Dienstag, 23. September 2014 11:26:19 UTC+2 schrieb debruyckereg: > > Hi, > > We've been using jOOQ for some time now against a MySQL database. We > recently migrated from version 2.3.2 to 3.4.2, and ran into the very > strange issue of some queries sometimes returning results while at other > times returning nothing (i'm talking exact same queries with the same > parameters here). > > Since I could confirm the issue by testing the generated queries in MySQL > Workbench, I assumed a MySQL bug. Indeed, the following bug has been > reported against MySQL 5.6.10: > > http://bugs.mysql.com/bug.php?id=69233 > > Apparently, when using date literals in the format "{d 'yyyy-MM-dd'}" or > "date 'yyyy-MM-dd'", the optimizer takes one route at one time, while > choosing another at another time for the exact same query, yielding > different results. In our case, the use of timestamp literals produced the > same effect, where the same query sometimes returns the correct results, > and sometimes nothing. > > The issue appears to be a regression bug that found its way into MySQL > 5.6.4, which introduced fractional seconds for the DATETIME data type, and > which no longer ignores the date, time and timestamp literal keywords ( > http://dev.mysql.com/doc/refman/5.6/en/date-and-time-literals.html). > Although it's qualified as a critical issue, it is as yet unresolved (we > use MySQL version 5.6.19). > > But why did we run into this issue all of a sudden? I noticed that the > queries, generated by jOOQ, now used the "date", "time" and "timestamp" > keywords for date/time literals instead of JDBC escape sequences. When I > removed the keywords, the queries functioned normally. > > Searching the jOOQ source code, I found out that the org.jooq.impl.Val > class indeed made the switch from JDBC escape sequences to keywords since > version 2.4.0 (https://github.com/jOOQ/jOOQ/issues/1253). Contrary to > what MySQL bug 69233 would imply, the JDBC escape sequences pose no > problems for us, because the MySQL JDBC driver (we use version 5.1.31) > processes them, and the SQL that actually reaches the DB engine no longer > has them. > > To summarize, we cannot migrate to the latest jOOQ version as long as the > MySQL bug is not fixed. At least not without patching the "Val" class. > Therefore, I wanted to ask if there is any chance of your team issuing a > "fix" for this in an upcoming jOOQ release, even though it's really not > your bug. It would be greatly appreciated. > > Using JDBC escape sequences again would certainly do it, but I understand > that you would rather avoid them (not sure why, though). An alternative > would be to use them only for the MySQL dialect. Perhaps it can even serve > some purpose to make the "escaping" of date/time literals configurable per > dialect. > > I'm looking forward to hearing your thoughts on this issue. Many thanks in > advance. > > With kind regards, > > Gunther > > -- 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.
