incubator-empire-db-user 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
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: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
> 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.


Mime
View raw message