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
"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)
(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?