You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@empire-db.apache.org by "Rainer Döbele (JIRA)" <em...@incubator.apache.org> on 2011/09/13 11:18:08 UTC
[jira] [Created] (EMPIREDB-115) Add parenthesis to the first select
of a union/ intersect and allow driver to implement limit() and skip()
Add parenthesis to the first select of a union/ intersect and allow driver to implement limit() and skip()
----------------------------------------------------------------------------------------------------------
Key: EMPIREDB-115
URL: https://issues.apache.org/jira/browse/EMPIREDB-115
Project: Empire-DB
Issue Type: Improvement
Components: Core
Affects Versions: empire-db-2.2.0-incubating
Reporter: Rainer Döbele
Assignee: Rainer Döbele
Fix For: empire-db-2.2.0-incubating
This issue tackles the following problem reported by exxos:
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!
--
This message is automatically generated by JIRA.
For more information on JIRA, see: http://www.atlassian.com/software/jira
[jira] [Resolved] (EMPIREDB-115) Add parenthesis to the first
select of a union/ intersect and allow driver to implement limit() and
skip()
Posted by "Rainer Döbele (JIRA)" <em...@incubator.apache.org>.
[ https://issues.apache.org/jira/browse/EMPIREDB-115?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Rainer Döbele resolved EMPIREDB-115.
------------------------------------
Resolution: Fixed
- parenthesis added for all drivers
- limit() and skip() support for MySQL
> Add parenthesis to the first select of a union/ intersect and allow driver to implement limit() and skip()
> ----------------------------------------------------------------------------------------------------------
>
> Key: EMPIREDB-115
> URL: https://issues.apache.org/jira/browse/EMPIREDB-115
> Project: Empire-DB
> Issue Type: Improvement
> Components: Core
> Affects Versions: empire-db-2.2.0-incubating
> Reporter: Rainer Döbele
> Assignee: Rainer Döbele
> Fix For: empire-db-2.2.0-incubating
>
>
> This issue tackles the following problem reported by exxos:
> 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!
--
This message is automatically generated by JIRA.
For more information on JIRA, see: http://www.atlassian.com/software/jira