incubator-empire-db-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From exxos <hat...@gmail.com>
Subject UNION and LIMIT(SKIP) support
Date Mon, 12 Sep 2011 21:30:22 GMT
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