Thread: Re: Use of the LIMIT clause ?

Re: Use of the LIMIT clause ?

From
Tom Lane
Date:
Spy <spy@joystick.fr> writes:
> Tom Lane a �crit :
>> Is that actually how MySQL interprets two parameters?  We treat them
>> as count and offset respectively, which definition I thought was the
>> same as MySQL's.

> But MySQL's syntax is different, as found on
> http://www.mysql.com/doc/S/E/SELECT.html :
> "SELECT [STRAIGHT_JOIN] [SQL_SMALL_RESULT] [SQL_BIG_RESULT]
> [SQL_BUFFER_RESULT]
> [...]
>         [LIMIT [offset,] rows]"

That's annoying; looks like we do it backwards from MySQL.  Can anyone
confirm that this is how MySQL behaves (maybe it's a typo on this
documentation page)?

Should we consider changing ours if it is different?  Are there any
other RDBMSes that implement two-parameter LIMIT?

            regards, tom lane

Re: Re: Use of the LIMIT clause ?

From
Richard Poole
Date:
On Mon, Mar 12, 2001 at 09:21:58PM -0500, Tom Lane wrote:
> Spy <spy@joystick.fr> writes:
> > Tom Lane a écrit :
> >> Is that actually how MySQL interprets two parameters?  We treat them
> >> as count and offset respectively, which definition I thought was the
> >> same as MySQL's.
> 
> > But MySQL's syntax is different, as found on
> > http://www.mysql.com/doc/S/E/SELECT.html : 
> > "SELECT [STRAIGHT_JOIN] [SQL_SMALL_RESULT] [SQL_BIG_RESULT]
> > [SQL_BUFFER_RESULT]
> > [...]   
> >         [LIMIT [offset,] rows]"
> 
> That's annoying; looks like we do it backwards from MySQL.  Can anyone
> confirm that this is how MySQL behaves (maybe it's a typo on this
> documentation page)?

Yes, it does behave as documented.

> Should we consider changing ours if it is different?

I don't know that it's worth it... it seems to inconvenience some
people either way. I may soon be moving a moderately complex system
from MySQL to Postgres and it wouldn't be the end of my world if
I had to reverse all the LIMITs.

Richard


Re: Re: Use of the LIMIT clause ?

From
Bruce Momjian
Date:
Is this a TODO item?  Seems we should match MySQL, seeing as we took the
feature syntax from them.

[ Charset ISO-8859-1 unsupported, converting... ]
> On Mon, Mar 12, 2001 at 09:21:58PM -0500, Tom Lane wrote:
> > Spy <spy@joystick.fr> writes:
> > > Tom Lane a ?crit :
> > >> Is that actually how MySQL interprets two parameters?  We treat them
> > >> as count and offset respectively, which definition I thought was the
> > >> same as MySQL's.
> > 
> > > But MySQL's syntax is different, as found on
> > > http://www.mysql.com/doc/S/E/SELECT.html : 
> > > "SELECT [STRAIGHT_JOIN] [SQL_SMALL_RESULT] [SQL_BIG_RESULT]
> > > [SQL_BUFFER_RESULT]
> > > [...]   
> > >         [LIMIT [offset,] rows]"
> > 
> > That's annoying; looks like we do it backwards from MySQL.  Can anyone
> > confirm that this is how MySQL behaves (maybe it's a typo on this
> > documentation page)?
> 
> Yes, it does behave as documented.
> 
> > Should we consider changing ours if it is different?
> 
> I don't know that it's worth it... it seems to inconvenience some
> people either way. I may soon be moving a moderately complex system
> from MySQL to Postgres and it wouldn't be the end of my world if
> I had to reverse all the LIMITs.
> 
> Richard
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
> 
> http://www.postgresql.org/users-lounge/docs/faq.html
> 


--  Bruce Momjian                        |  http://candle.pha.pa.us pgman@candle.pha.pa.us               |  (610)
853-3000+  If your life is a hard drive,     |  830 Blythe Avenue +  Christ can be your backup.        |  Drexel Hill,
Pennsylvania19026
 


Re: Re: Use of the LIMIT clause ?

From
Tom Lane
Date:
Bruce Momjian <pgman@candle.pha.pa.us> writes:
> Is this a TODO item?  Seems we should match MySQL, seeing as we took the
> feature syntax from them.

Question is, will more people be inconvenienced if we change it than if
we leave it alone?  Seems like someone gets screwed either way ...
        regards, tom lane


Re: Re: Use of the LIMIT clause ?

From
Bruce Momjian
Date:
> Bruce Momjian <pgman@candle.pha.pa.us> writes:
> > Is this a TODO item?  Seems we should match MySQL, seeing as we took the
> > feature syntax from them.
> 
> Question is, will more people be inconvenienced if we change it than if
> we leave it alone?  Seems like someone gets screwed either way ...

No question about it.  One solution would be to disable this syntax for
one release and then fix it in the one after that.  We already offer
LIMIT/OFFSET, which is what they should be using anyway.  If we can't
remember, and no one reported the bug for many releases, odds are no one
was using that more limited form anyway.

--  Bruce Momjian                        |  http://candle.pha.pa.us pgman@candle.pha.pa.us               |  (610)
853-3000+  If your life is a hard drive,     |  830 Blythe Avenue +  Christ can be your backup.        |  Drexel Hill,
Pennsylvania19026
 


Re: Re: Use of the LIMIT clause ?

From
Bruce Momjian
Date:
[ Charset ISO-8859-1 unsupported, converting... ]
> On Mon, Mar 12, 2001 at 09:21:58PM -0500, Tom Lane wrote:
> > Spy <spy@joystick.fr> writes:
> > > Tom Lane a ?crit :
> > >> Is that actually how MySQL interprets two parameters?  We treat them
> > >> as count and offset respectively, which definition I thought was the
> > >> same as MySQL's.
> > 
> > > But MySQL's syntax is different, as found on
> > > http://www.mysql.com/doc/S/E/SELECT.html : 
> > > "SELECT [STRAIGHT_JOIN] [SQL_SMALL_RESULT] [SQL_BIG_RESULT]
> > > [SQL_BUFFER_RESULT]
> > > [...]   
> > >         [LIMIT [offset,] rows]"
> > 
> > That's annoying; looks like we do it backwards from MySQL.  Can anyone
> > confirm that this is how MySQL behaves (maybe it's a typo on this
> > documentation page)?
> 
> Yes, it does behave as documented.
> 
> > Should we consider changing ours if it is different?
> 
> I don't know that it's worth it... it seems to inconvenience some
> people either way. I may soon be moving a moderately complex system
> from MySQL to Postgres and it wouldn't be the end of my world if
> I had to reverse all the LIMITs.
> 

Added to TODO.  If we took the feature from MySQL, seems we should match
it.  This will require a clear notice in the release notes:
* Change LIMIT val,val to be offset,limit to match MySQL   

--  Bruce Momjian                        |  http://candle.pha.pa.us pgman@candle.pha.pa.us               |  (610)
853-3000+  If your life is a hard drive,     |  830 Blythe Avenue +  Christ can be your backup.        |  Drexel Hill,
Pennsylvania19026