Thank you for the patch. I see, that might work. The main reason why we removed the JDBC / ODBC escape syntax from inlined bind variable rendering is the fact that we want SQL output to be copy-pastable into any tool, also those not built on Java. E.g. into MySQL Workbench.
According to the documentation, it seems that the MySQL parser itself also supports this escape syntax, so reverting this only for MySQL will be fine: http://dev.mysql.com/doc/refman/5.7/en/date-and-time-literals.html I have committed your patch for jOOQ 3.5.0: https://github.com/jOOQ/jOOQ/issues/3648 We'll also merge the fix to 3.4.3 and other maintenance releases. There are no release dates yet, but very probably by the end of October. Thanks again for letting us know and for the very detailed bug report. Cheers, Lukas 2014-09-23 12:52 GMT+02:00 debruyckereg <[email protected]>: > Hi Lukas, > > Thanks for your swift reply. I'm not familiar with Git and pull requests, > so I'm sending you a diff (made against /tags/version-3.4.2/jOOQ). The > patch reverts to JDBC escape sequences for the MySQL dialect only, so as to > not completely undo the changes made for issue #1253 ( > https://github.com/jOOQ/jOOQ/issues/1253). It includes changes for > BasicTest and DataTypeTest because the tests are using the MySQL dialect. > > Let me know if there's anything else I can do. Thanks again. > > With kind regards, > > Gunther > > > > On Tuesday, September 23, 2014 12:02:27 PM UTC+2, Lukas Eder wrote: >> >> 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. > -- 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.
