empire-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Rainer Döbele <doeb...@esteam.de>
Subject re: UNION and LIMIT(SKIP) support
Date Tue, 13 Sep 2011 09:47:43 GMT
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

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
Let me know, if you have any more problems with this.


> from: exxos [mailto:hatufr@gmail.com]
> to: empire-db-user@incubator.apache.org
> re: UNION and LIMIT(SKIP) support
> Hi empire-db team,
> A year ago, I posted a message about several issues I experienced with
> [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
> or LIMIT after the last one."
> (SELECT a FROM t1 WHERE a=10 AND B=1)
> (SELECT a FROM t2 WHERE a=11 AND B=2)
> 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
> Best regards,
> exxos.

View raw message