Hi exxos, yes, you are right- this issue got lost somewhere on the way. As far as I remember I gave you a solution how to solve this in your code - but of course a general solution is preferable.
I have now created an issue in Jira (EMPIREDB-115) and I have even already checked in the solution. Union and intersect statements will now generally have parenthesis around their first select as well and I have added support for limit() and skip() to the MySQL driver. I have tested the parenthesis issue with hsqldb, sql-server and oracle and theoretically it should work with other databases too. However in order to use this feature, you will have to use the current development branch (2.2.0-SNAPSHOT). Let me know, if you have any more problems with this. Regards Rainer > from: exxos [mailto:[email protected]] > to: [email protected] > re: UNION and LIMIT(SKIP) support > > Hi empire-db team, > > A year ago, I posted a message about several issues I experienced with > UNION. > > [sta1] UNION [sta2] UNION [sta3] UNION ... > > 1st issue - DBCommandExpr does not support LIMIT (and SKIP) > 2nd issue - Some parentheses are missing in the SQL produced for UNION? > > According to the MySQL documentation, if the ORDER BY or the > LIMIT/SKIP clause is used with UNION, the parentheses have to be added > to each SELECT > statements. > > http://dev.mysql.com/doc/refman/5.0/en/union.html > > "To use an ORDER BY or LIMIT clause to sort or limit the entire UNION > result, parenthesize the individual SELECT statements and place the > ORDER BY > or LIMIT after the last one." > > (SELECT a FROM t1 WHERE a=10 AND B=1) > UNION > (SELECT a FROM t2 WHERE a=11 AND B=2) > ORDER BY a LIMIT 10; > > But the empire-db version 2.0.6 produces > > [sta1] UNION ( [sta2] ) ORDER BY <DBColumn> > > As you can see, there is no "( )" for the first instruction. > > By chance it works with ORDER BY because MySQL is not strict, but with > the clause LIMIT no way! > > The class in charge to build the UNION is DBCombinedCmd > public boolean getSelect(StringBuilder buf) > > Now, one year after what is the statuation for LIMIT (and SKIP) with > UNION? > > Best regards, > exxos.
