2013/2/19 <[email protected]>: > I maybe missing something but would it not be possible to handle this by > turning the original SQL select into a sub select so > > select * from table > > ends up as > > select count(*) > from ( select * from table ) > > This way all the edge cases caused by things like DISTINCT and UNION operate > correctly as long as the DB engine can handle them within a sub-select. It > also deals with the issue of DBs that do not have the window functions. > > I am saying this without any understanding of how you hold a query within > JOOQ so do not know if you can defind such a wrapping at will.
I must've had tomatoes on my eyes. Trying to patch rendering and binding and all that... Sheesh ;-) This will indeed "get rid" of most of the corner cases, as it isn't really a complex query transformation. Here are some corner-cases that would persist: - The FOR UPDATE clauses. But that can be left as the user's responsibility, not to pass a SELECT .. FOR UPDATE to this new Executor.fetchCount(Select<?>) And here is a new corner-case: - "SELECT 1 FROM ..." Some databases cannot handle unnamed columns in subqueries. E.g. SQL Server can't run SELECT count(*) FROM (SELECT 1). It would have to be changed to SELECT count(*) FROM (SELECT 1 [dummy]) I wonder, though, if all optimisers can handle the transformation efficiently. I'm not afraid for Oracle, but are these the same queries in MySQL? > select * from table > > select count(*) > from ( select * from table ) Anyway, thanks a lot for the input :-) so, this will still make it easily into jOOQ 3.0-RC2 and 2.7 Cheers Lukas -- 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/groups/opt_out.
