You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@empire-db.apache.org by Rainer Döbele <do...@esteam.de> on 2011/09/13 11:47:43 UTC
re: UNION and LIMIT(SKIP) support
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.
Re: UNION and LIMIT(SKIP) support
Posted by Francis De Brabandere <fr...@gmail.com>.
Since slf4j is compatible with most logging api's this should not be a problem.
If it's provided by your app container you could indeed put it
provided in dependency management. If you really don't want it you can
have an exclusion section for slf4j in your empire-db dependency.
Cheers,
Francis
On Thu, Sep 22, 2011 at 2:49 PM, exxos <ha...@gmail.com> wrote:
> Hi,
>
> I'm glad to confirm that the version 2.0.20 solves the issue with the limit
> and skip.
>
> By the way, I noticed that empire-db now depends on slf4j api. This is a
> good thing, but this makes my project dependent as well.
> Currently I added this dependency with Maven and maked it as provided (by my
> container where my application runs)
> According to you, how am I supposed to deal with it?
>
> Regards,
> exxos.
>
>
>
> On Mon, Sep 19, 2011 at 10:26 PM, exxos <ha...@gmail.com> wrote:
>>
>> Hi Rainer,
>>
>> Thank you for your support on this. Your prompt reactivity is really
>> appreciated.
>>
>> Following your advise, I downloaded the trunk view and build the snapshot
>> 2.0.20 locally. Now, I'm testing, and I will tell you the result by the week
>> (time to me, necessary to integrate it in my projet).
>>
>> Regards,
>> exxos.
>>
>>
>>
>> On Tue, Sep 13, 2011 at 11:47 AM, Rainer Döbele <do...@esteam.de> wrote:
>>>
>>> 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.
>>>
>>
>
>
--
http://www.somatik.be
Microsoft gives you windows, Linux gives you the whole house.
Re: UNION and LIMIT(SKIP) support
Posted by exxos <ha...@gmail.com>.
Hi,
I'm glad to confirm that the version 2.0.20 solves the issue with the limit
and skip.
By the way, I noticed that empire-db now depends on slf4j api. This is a
good thing, but this makes my project dependent as well.
Currently I added this dependency with Maven and maked it as provided (by my
container where my application runs)
According to you, how am I supposed to deal with it?
Regards,
exxos.
On Mon, Sep 19, 2011 at 10:26 PM, exxos <ha...@gmail.com> wrote:
> Hi Rainer,
>
> Thank you for your support on this. Your prompt reactivity is really
> appreciated.
>
> Following your advise, I downloaded the trunk view and build the snapshot
> 2.0.20 locally. Now, I'm testing, and I will tell you the result by the week
> (time to me, necessary to integrate it in my projet).
>
> Regards,
> exxos.
>
>
>
>
> On Tue, Sep 13, 2011 at 11:47 AM, Rainer Döbele <do...@esteam.de> wrote:
>
>> 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.
>>
>>
>
Re: UNION and LIMIT(SKIP) support
Posted by exxos <ha...@gmail.com>.
Hi Rainer,
Thank you for your support on this. Your prompt reactivity is really
appreciated.
Following your advise, I downloaded the trunk view and build the snapshot
2.0.20 locally. Now, I'm testing, and I will tell you the result by the week
(time to me, necessary to integrate it in my projet).
Regards,
exxos.
On Tue, Sep 13, 2011 at 11:47 AM, Rainer Döbele <do...@esteam.de> wrote:
> 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.
>
>