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.
