You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@empire-db.apache.org by exxos <ha...@gmail.com> on 2010/08/03 15:18:17 UTC
UNION and the parentheses
Hello,
According to the MySQL's documentation (5.0), if the ORDER BY or the LIMIT
clause, are used with UNION, the "( )" 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."
But empire-db version 2.0.6 produces "<DBCommand> UNION (<DBCommand>) ORDER
BY <DBColumn>" and there is no "( )" to the first SELECT.
But by chance it continues to work with the ORDER BY, but with the keyword
LIMIT it stops!
The class in charge to build the UNION command seems to be DBCombinedCmd
(line 102 - public boolean getSelect(StringBuilder buf) )
Could you please advise about a workarround?
/Cheers.
re: UNION and the parentheses
Posted by Rainer Döbele <do...@esteam.de>.
Hi exxos,
still working on it.
Will probably take until after the weekend.
Regards
Rainer
from: exxos [mailto:hatufr@gmail.com]
to: empire-db-user@incubator.apache.org
re: Re: UNION and the parentheses
Hi,
Anynews on this subjet? Is this issue has been tracked?
Currently I fixed it myself but I would (it would be great) this issue fixed in the next releases of db-empire...
Thank you very much for your support.
Regards,
exxos.
On Tue, Aug 10, 2010 at 11:33 PM, exxos <ha...@gmail.com> wrote:
Hi,
Here is a candidate fix:
public boolean getSelect(StringBuilder buf)
{
StringBuilder leftBuffer = new StringBuilder();
// the left part
left.clearOrderBy();
if (!left.getSelect(leftBuffer))
return error(left);
if(leftBuffer.indexOf(keyWord) == -1) {
buf.append( "(" );
buf.append(leftBuffer);
buf.append( ")" );
} else {
buf.append(leftBuffer);
}
[...]
I do not like the "indexOf(...)" because it is not really elegante, but this has the advantage to work...
Regards,
exxos.
On Tue, Aug 10, 2010 at 10:53 PM, exxos <ha...@gmail.com> wrote:
Sorry, I confused between StringBuffer and StringBuilder...
But the use case is still valid.
Please accept my appologize.
Regards,
exxos.
On Tue, Aug 10, 2010 at 10:49 PM, exxos <ha...@gmail.com> wrote:
Hi,
Thank you for your advise.
The issue is considering multiple UNION.
[sta1] UNON [sta2] UNION [sta3] UNION etc...
DBCommandExpr cmd = cmd1.union(cmd2);
cmd = cmd.union(cmd3);
When is invoked the method "getSelect()", the StringBuffer given in parametre is used in cascading. The StringBuffer is shared by all sub getSelect() of the commands. This makes the solution a little bit hard to elaborate. I'm thinking about to stop to share the same StringBuffer. This is because I base my logic on the length of the StringBuffer:
getSelect(...) {
boolean isFirst = buf.length() < 1;
if(isFirst) {
buf.append( "(" );
}
This comment is only to share my experience on that.
Regards,
exxos.
On Wed, Aug 4, 2010 at 10:11 AM, Rainer Döbele <do...@esteam.de> wrote:
Hi exxos,
I need a bit more time to investigate this.
In the meantime I suggest to derive a class from DBCombinedCmd and to override the getSelect(StringBuilder buf) method.
Then copy the code from the base-class and add the parenthesis.
Afterwards in your client-code you write:
DBCommandExpr myExpr = new DBMyCombinedCmd(cmdLeft, "UNION", cmdRight);
Regards
Rainer
Von: exxos [mailto:hatufr@gmail.com]
Gesendet: Dienstag, 3. August 2010 15:18
An: empire-db-user@incubator.apache.org
Betreff: UNION and the parentheses
Hello,
According to the MySQL's documentation (5.0), if the ORDER BY or the LIMIT clause, are used with UNION, the "( )" 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."
But empire-db version 2.0.6 produces "<DBCommand> UNION (<DBCommand>) ORDER BY <DBColumn>" and there is no "( )" to the first SELECT.
But by chance it continues to work with the ORDER BY, but with the keyword LIMIT it stops!
The class in charge to build the UNION command seems to be DBCombinedCmd (line 102 - public boolean getSelect(StringBuilder buf) )
Could you please advise about a workarround?
/Cheers.
Re: UNION and the parentheses
Posted by exxos <ha...@gmail.com>.
Hi,
Anynews on this subjet? Is this issue has been tracked?
Currently I fixed it myself but I would (it would be great) this issue fixed
in the next releases of db-empire...
Thank you very much for your support.
Regards,
exxos.
On Tue, Aug 10, 2010 at 11:33 PM, exxos <ha...@gmail.com> wrote:
> Hi,
>
> Here is a candidate fix:
>
> public boolean getSelect(StringBuilder buf)
> {
>
> StringBuilder leftBuffer = new StringBuilder();
>
> // the left part
> left.clearOrderBy();
> if (!left.getSelect(leftBuffer))
> return error(left);
>
> if(leftBuffer.indexOf(keyWord) == -1) {
> buf.append( "(" );
> buf.append(leftBuffer);
> buf.append( ")" );
> } else {
> buf.append(leftBuffer);
> }
>
> [...]
>
> I do not like the "indexOf(...)" because it is not really elegante, but
> this has the advantage to work...
>
> Regards,
> exxos.
>
>
> On Tue, Aug 10, 2010 at 10:53 PM, exxos <ha...@gmail.com> wrote:
>
>> Sorry, I confused between StringBuffer and StringBuilder...
>> But the use case is still valid.
>>
>> Please accept my appologize.
>> Regards,
>> exxos.
>>
>> On Tue, Aug 10, 2010 at 10:49 PM, exxos <ha...@gmail.com> wrote:
>>
>>> Hi,
>>>
>>> Thank you for your advise.
>>>
>>> The issue is considering multiple UNION.
>>>
>>> [sta1] UNON [sta2] UNION [sta3] UNION etc...
>>>
>>> DBCommandExpr cmd = cmd1.union(cmd2);
>>> cmd = cmd.union(cmd3);
>>>
>>> When is invoked the method "getSelect()", the StringBuffer given in
>>> parametre is used in cascading. The StringBuffer is shared by all sub
>>> getSelect() of the commands. This makes the solution a little bit hard to
>>> elaborate. I'm thinking about to stop to share the same StringBuffer. This
>>> is because I base my logic on the length of the StringBuffer:
>>>
>>> getSelect(...) {
>>> boolean isFirst = buf.length() < 1;
>>> if(isFirst) {
>>> buf.append( "(" );
>>> }
>>>
>>> This comment is only to share my experience on that.
>>>
>>> Regards,
>>> exxos.
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>> On Wed, Aug 4, 2010 at 10:11 AM, Rainer Döbele <do...@esteam.de>wrote:
>>>
>>>> Hi exxos,
>>>>
>>>>
>>>>
>>>> I need a bit more time to investigate this.
>>>>
>>>> In the meantime I suggest to derive a class from DBCombinedCmd and to
>>>> override the getSelect(StringBuilder buf) method.
>>>>
>>>> Then copy the code from the base-class and add the parenthesis.
>>>>
>>>>
>>>>
>>>> Afterwards in your client-code you write:
>>>>
>>>> DBCommandExpr myExpr = new DBMyCombinedCmd(cmdLeft, “UNION”, cmdRight);
>>>>
>>>>
>>>>
>>>> Regards
>>>>
>>>> Rainer
>>>>
>>>>
>>>>
>>>> *Von:* exxos [mailto:hatufr@gmail.com]
>>>> *Gesendet:* Dienstag, 3. August 2010 15:18
>>>> *An:* empire-db-user@incubator.apache.org
>>>> *Betreff:* UNION and the parentheses
>>>>
>>>>
>>>>
>>>>
>>>> Hello,
>>>>
>>>> According to the MySQL's documentation (5.0), if the ORDER BY or the
>>>> LIMIT clause, are used with UNION, the "( )" 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."
>>>>
>>>> But empire-db version 2.0.6 produces "<DBCommand> UNION (<DBCommand>)
>>>> ORDER BY <DBColumn>" and there is no "( )" to the first SELECT.
>>>>
>>>> But by chance it continues to work with the ORDER BY, but with the
>>>> keyword LIMIT it stops!
>>>>
>>>> The class in charge to build the UNION command seems to be DBCombinedCmd
>>>> (line 102 - public boolean getSelect(StringBuilder buf) )
>>>>
>>>> Could you please advise about a workarround?
>>>>
>>>> /Cheers.
>>>>
>>>>
>>>
>>
>
Re: UNION and the parentheses
Posted by exxos <ha...@gmail.com>.
Hi,
Here is a candidate fix:
public boolean getSelect(StringBuilder buf)
{
StringBuilder leftBuffer = new StringBuilder();
// the left part
left.clearOrderBy();
if (!left.getSelect(leftBuffer))
return error(left);
if(leftBuffer.indexOf(keyWord) == -1) {
buf.append( "(" );
buf.append(leftBuffer);
buf.append( ")" );
} else {
buf.append(leftBuffer);
}
[...]
I do not like the "indexOf(...)" because it is not really elegante, but this
has the advantage to work...
Regards,
exxos.
On Tue, Aug 10, 2010 at 10:53 PM, exxos <ha...@gmail.com> wrote:
> Sorry, I confused between StringBuffer and StringBuilder...
> But the use case is still valid.
>
> Please accept my appologize.
> Regards,
> exxos.
>
> On Tue, Aug 10, 2010 at 10:49 PM, exxos <ha...@gmail.com> wrote:
>
>> Hi,
>>
>> Thank you for your advise.
>>
>> The issue is considering multiple UNION.
>>
>> [sta1] UNON [sta2] UNION [sta3] UNION etc...
>>
>> DBCommandExpr cmd = cmd1.union(cmd2);
>> cmd = cmd.union(cmd3);
>>
>> When is invoked the method "getSelect()", the StringBuffer given in
>> parametre is used in cascading. The StringBuffer is shared by all sub
>> getSelect() of the commands. This makes the solution a little bit hard to
>> elaborate. I'm thinking about to stop to share the same StringBuffer. This
>> is because I base my logic on the length of the StringBuffer:
>>
>> getSelect(...) {
>> boolean isFirst = buf.length() < 1;
>> if(isFirst) {
>> buf.append( "(" );
>> }
>>
>> This comment is only to share my experience on that.
>>
>> Regards,
>> exxos.
>>
>>
>>
>>
>>
>>
>>
>> On Wed, Aug 4, 2010 at 10:11 AM, Rainer Döbele <do...@esteam.de> wrote:
>>
>>> Hi exxos,
>>>
>>>
>>>
>>> I need a bit more time to investigate this.
>>>
>>> In the meantime I suggest to derive a class from DBCombinedCmd and to
>>> override the getSelect(StringBuilder buf) method.
>>>
>>> Then copy the code from the base-class and add the parenthesis.
>>>
>>>
>>>
>>> Afterwards in your client-code you write:
>>>
>>> DBCommandExpr myExpr = new DBMyCombinedCmd(cmdLeft, “UNION”, cmdRight);
>>>
>>>
>>>
>>> Regards
>>>
>>> Rainer
>>>
>>>
>>>
>>> *Von:* exxos [mailto:hatufr@gmail.com]
>>> *Gesendet:* Dienstag, 3. August 2010 15:18
>>> *An:* empire-db-user@incubator.apache.org
>>> *Betreff:* UNION and the parentheses
>>>
>>>
>>>
>>>
>>> Hello,
>>>
>>> According to the MySQL's documentation (5.0), if the ORDER BY or the
>>> LIMIT clause, are used with UNION, the "( )" 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."
>>>
>>> But empire-db version 2.0.6 produces "<DBCommand> UNION (<DBCommand>)
>>> ORDER BY <DBColumn>" and there is no "( )" to the first SELECT.
>>>
>>> But by chance it continues to work with the ORDER BY, but with the
>>> keyword LIMIT it stops!
>>>
>>> The class in charge to build the UNION command seems to be DBCombinedCmd
>>> (line 102 - public boolean getSelect(StringBuilder buf) )
>>>
>>> Could you please advise about a workarround?
>>>
>>> /Cheers.
>>>
>>>
>>
>
Re: UNION and the parentheses
Posted by exxos <ha...@gmail.com>.
Sorry, I confused between StringBuffer and StringBuilder...
But the use case is still valid.
Please accept my appologize.
Regards,
exxos.
On Tue, Aug 10, 2010 at 10:49 PM, exxos <ha...@gmail.com> wrote:
> Hi,
>
> Thank you for your advise.
>
> The issue is considering multiple UNION.
>
> [sta1] UNON [sta2] UNION [sta3] UNION etc...
>
> DBCommandExpr cmd = cmd1.union(cmd2);
> cmd = cmd.union(cmd3);
>
> When is invoked the method "getSelect()", the StringBuffer given in
> parametre is used in cascading. The StringBuffer is shared by all sub
> getSelect() of the commands. This makes the solution a little bit hard to
> elaborate. I'm thinking about to stop to share the same StringBuffer. This
> is because I base my logic on the length of the StringBuffer:
>
> getSelect(...) {
> boolean isFirst = buf.length() < 1;
> if(isFirst) {
> buf.append( "(" );
> }
>
> This comment is only to share my experience on that.
>
> Regards,
> exxos.
>
>
>
>
>
>
>
> On Wed, Aug 4, 2010 at 10:11 AM, Rainer Döbele <do...@esteam.de> wrote:
>
>> Hi exxos,
>>
>>
>>
>> I need a bit more time to investigate this.
>>
>> In the meantime I suggest to derive a class from DBCombinedCmd and to
>> override the getSelect(StringBuilder buf) method.
>>
>> Then copy the code from the base-class and add the parenthesis.
>>
>>
>>
>> Afterwards in your client-code you write:
>>
>> DBCommandExpr myExpr = new DBMyCombinedCmd(cmdLeft, “UNION”, cmdRight);
>>
>>
>>
>> Regards
>>
>> Rainer
>>
>>
>>
>> *Von:* exxos [mailto:hatufr@gmail.com]
>> *Gesendet:* Dienstag, 3. August 2010 15:18
>> *An:* empire-db-user@incubator.apache.org
>> *Betreff:* UNION and the parentheses
>>
>>
>>
>>
>> Hello,
>>
>> According to the MySQL's documentation (5.0), if the ORDER BY or the LIMIT
>> clause, are used with UNION, the "( )" 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."
>>
>> But empire-db version 2.0.6 produces "<DBCommand> UNION (<DBCommand>)
>> ORDER BY <DBColumn>" and there is no "( )" to the first SELECT.
>>
>> But by chance it continues to work with the ORDER BY, but with the keyword
>> LIMIT it stops!
>>
>> The class in charge to build the UNION command seems to be DBCombinedCmd
>> (line 102 - public boolean getSelect(StringBuilder buf) )
>>
>> Could you please advise about a workarround?
>>
>> /Cheers.
>>
>>
>
Re: UNION and the parentheses
Posted by exxos <ha...@gmail.com>.
Hi,
Thank you for your advise.
The issue is considering multiple UNION.
[sta1] UNON [sta2] UNION [sta3] UNION etc...
DBCommandExpr cmd = cmd1.union(cmd2);
cmd = cmd.union(cmd3);
When is invoked the method "getSelect()", the StringBuffer given in
parametre is used in cascading. The StringBuffer is shared by all sub
getSelect() of the commands. This makes the solution a little bit hard to
elaborate. I'm thinking about to stop to share the same StringBuffer. This
is because I base my logic on the length of the StringBuffer:
getSelect(...) {
boolean isFirst = buf.length() < 1;
if(isFirst) {
buf.append( "(" );
}
This comment is only to share my experience on that.
Regards,
exxos.
On Wed, Aug 4, 2010 at 10:11 AM, Rainer Döbele <do...@esteam.de> wrote:
> Hi exxos,
>
>
>
> I need a bit more time to investigate this.
>
> In the meantime I suggest to derive a class from DBCombinedCmd and to
> override the getSelect(StringBuilder buf) method.
>
> Then copy the code from the base-class and add the parenthesis.
>
>
>
> Afterwards in your client-code you write:
>
> DBCommandExpr myExpr = new DBMyCombinedCmd(cmdLeft, “UNION”, cmdRight);
>
>
>
> Regards
>
> Rainer
>
>
>
> *Von:* exxos [mailto:hatufr@gmail.com]
> *Gesendet:* Dienstag, 3. August 2010 15:18
> *An:* empire-db-user@incubator.apache.org
> *Betreff:* UNION and the parentheses
>
>
>
>
> Hello,
>
> According to the MySQL's documentation (5.0), if the ORDER BY or the LIMIT
> clause, are used with UNION, the "( )" 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."
>
> But empire-db version 2.0.6 produces "<DBCommand> UNION (<DBCommand>) ORDER
> BY <DBColumn>" and there is no "( )" to the first SELECT.
>
> But by chance it continues to work with the ORDER BY, but with the keyword
> LIMIT it stops!
>
> The class in charge to build the UNION command seems to be DBCombinedCmd
> (line 102 - public boolean getSelect(StringBuilder buf) )
>
> Could you please advise about a workarround?
>
> /Cheers.
>
>
re: UNION and the parentheses
Posted by Rainer Döbele <do...@esteam.de>.
Hi exxos,
I need a bit more time to investigate this.
In the meantime I suggest to derive a class from DBCombinedCmd and to override the getSelect(StringBuilder buf) method.
Then copy the code from the base-class and add the parenthesis.
Afterwards in your client-code you write:
DBCommandExpr myExpr = new DBMyCombinedCmd(cmdLeft, "UNION", cmdRight);
Regards
Rainer
Von: exxos [mailto:hatufr@gmail.com]
Gesendet: Dienstag, 3. August 2010 15:18
An: empire-db-user@incubator.apache.org
Betreff: UNION and the parentheses
Hello,
According to the MySQL's documentation (5.0), if the ORDER BY or the LIMIT clause, are used with UNION, the "( )" 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."
But empire-db version 2.0.6 produces "<DBCommand> UNION (<DBCommand>) ORDER BY <DBColumn>" and there is no "( )" to the first SELECT.
But by chance it continues to work with the ORDER BY, but with the keyword LIMIT it stops!
The class in charge to build the UNION command seems to be DBCombinedCmd (line 102 - public boolean getSelect(StringBuilder buf) )
Could you please advise about a workarround?
/Cheers.