Hi Lukas, Thank you for this explanation, I didn't suspect this solution may corrupt the database.
However, as you pointed out in Stack Overflow, the MySQL limitation is only here because the query optimizer is lazy :) So it might be nice if jOOQ would do the query transformation. But so you know, for me this feature is not urgent at all. Anyway, thank you for your time, it is nice to see that jOOQ is that much active ;) Cheers, Aurélien On Sunday, May 25, 2014 10:36:41 AM UTC+2, Lukas Eder wrote: > > Hi Aurélien, > > 2014-05-24 1:17 GMT+02:00 Aurélien Manteaux <[email protected] > <javascript:>>: > >> Hello Lukas, >> >> Yes it finally worked, I have tested it this time ! >> I think it is reliable since the solution is 6 years old ( >> http://stackoverflow.com/questions/45494/mysql-error-1093-cant-specify-target-table-for-update-in-from-clause/8620162#8620162) >> >> and still nothing has changed. >> > > Well, what I'd be worried about: The MySQL folks must have had a reason > for adding this limitation, but they obviously didn't implement it > thoroughly - i.e. preventing reuse of the same table regardless of the SQL > transformations that are applied to it. Probably, they just implemented a > couple of simple checks on the AST, rather than actually using locks on the > table that would fail the query, regardless of syntax. Or maybe, the > alternative syntax really *is* different, which would be equally > frightening :-) > > So your workaround is a hack to work around a limitation for which there > probably is a reason. I suspect the reason to be related to guaranteeing > ACID-ness. I.e. I wouldn't find it surprising, if by working around this > limitation, you might produce race conditions or corrupted data in > situations with significant concurrency. > > I'm raising the issue on Stack Overflow. I think that clarifying this will > help all MySQL users: > http://stackoverflow.com/q/23853453/521799 > > However, I don't know if because of a MySQL limitation, a (big) hack >> should be added to jOOQ. >> Maybe we can wait to see if there are other MySQL users interested in >> this feature before starting to think of an implementation into jOOQ. >> > > It wouldn't be the first hack :-) just consider how jOOQ emulates OFFSET > .. FETCH (aka. LIMIT .. OFFSET) for Oracle 11g and less, SQL Server 2008 > and less, and for DB2. We've done our share of SQL transformations to > standardise such common things, e.g. > > http://blog.jooq.org/2013/10/09/sql-trick-row_number-is-to-select-what-dense_rank-is-to-select-distinct/ > -- 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.
