LIMIT 0,-1 broken in 5.0

[Brian Wakem]

I have a number of scripts that generate queries where the number of rows I need is dynamic ( LIMIT 0,$rows ).

Using MySQL 4.0 I can get all the rows by setting $rows to -1. This produces a syntax error in 5.0. Is there something else I can set $rows to to get all rows (other than some arbitrary high number)?



--
Brian Wakem
Email: [url]http://homepage.ntlworld.com/b.wakem/myemail.png[/url]

[Axel Schwenke]

Brian Wakem <no@email.com> wrote:

> I have a number of scripts that generate queries where the number of
> rows I need is dynamic ( LIMIT 0,$rows ).
>
> Using MySQL 4.0 I can get all the rows by setting $rows to -1. This
> produces a syntax error in 5.0.

According to the manual, both parameters to the LIMIT clause must be
nonnegative (in all MySQL versions). So in fact pre-5.0 was broken
(probably casting the argument to unsigned after parsing).

The correct solution would be to remove the LIMIT clause from the
statement if one does not need it.


XL
--
Axel Schwenke, Senior Software Developer, MySQL AB

Online User Manual: [url]http://dev.mysql.com/doc/refman/5.0/en/[/url]
MySQL User Forums: [url]http://forums.mysql.com/[/url]

[Brian Wakem]

Axel Schwenke wrote:

> Brian Wakem <no@email.com> wrote:

>> I have a number of scripts that generate queries where the number of
>> rows I need is dynamic ( LIMIT 0,$rows ).
>>
>> Using MySQL 4.0 I can get all the rows by setting $rows to -1. This
>> produces a syntax error in 5.0.

>
> According to the manual, both parameters to the LIMIT clause must be
> nonnegative (in all MySQL versions). So in fact pre-5.0 was broken
> (probably casting the argument to unsigned after parsing).
>
> The correct solution would be to remove the LIMIT clause from the
> statement if one does not need it.

I was afraid that would be the answer. This is not going to straight
forward to fix as the number -1 does not even appear in the scripts
themselves but is a result of a calculation, making it impossible to find
them until they produce an error.

I will need to have a little think.



--
Brian Wakem
Email: [url]http://homepage.ntlworld.com/b.wakem/myemail.png[/url]

[Snef]

Brian Wakem wrote:

> Axel Schwenke wrote:

>> Brian Wakem <no@email.com> wrote:

>>> I have a number of scripts that generate queries where the number of
>>> rows I need is dynamic ( LIMIT 0,$rows ).
>>>
>>> Using MySQL 4.0 I can get all the rows by setting $rows to -1. This
>>> produces a syntax error in 5.0.

>>
>> According to the manual, both parameters to the LIMIT clause must be
>> nonnegative (in all MySQL versions). So in fact pre-5.0 was broken
>> (probably casting the argument to unsigned after parsing).
>>
>> The correct solution would be to remove the LIMIT clause from the
>> statement if one does not need it.

>
>
>
> I was afraid that would be the answer. This is not going to straight
> forward to fix as the number -1 does not even appear in the scripts
> themselves but is a result of a calculation, making it impossible to
> find them until they produce an error.
>
> I will need to have a little think.
>
>
>

$sql = "bla bla without limit";

if ($row != -1) {
$sql .= ' LIMIT 0,' . $row;
}

Something like htis?

[Brian Wakem]

Snef wrote:

> Brian Wakem wrote:

>> Axel Schwenke wrote:

>>> Brian Wakem <no@email.com> wrote:
>>>> I have a number of scripts that generate queries where the number of
>>>> rows I need is dynamic ( LIMIT 0,$rows ).
>>>>
>>>> Using MySQL 4.0 I can get all the rows by setting $rows to -1. This
>>>> produces a syntax error in 5.0.
>>>
>>> According to the manual, both parameters to the LIMIT clause must be
>>> nonnegative (in all MySQL versions). So in fact pre-5.0 was broken
>>> (probably casting the argument to unsigned after parsing).
>>>
>>> The correct solution would be to remove the LIMIT clause from the
>>> statement if one does not need it.

>>
>>
>>
>> I was afraid that would be the answer. This is not going to straight
>> forward to fix as the number -1 does not even appear in the scripts
>> themselves but is a result of a calculation, making it impossible to
>> find them until they produce an error.
>>
>> I will need to have a little think.
>>
>>
>>

> $sql = "bla bla without limit";
>
> if ($row != -1) {
> $sql .= ' LIMIT 0,' . $row;
> }
>
> Something like htis?

Yes it's a rather messy hack really, but the only alternative I can think of
is to set $rows to a very high number, which would be just as messy. Maybe
time to toss a coin.



--
Brian Wakem
Email: [url]http://homepage.ntlworld.com/b.wakem/myemail.png[/url]

[Brian Wakem]

Brian Wakem wrote:

> Snef wrote:

>> Brian Wakem wrote:

>>> Axel Schwenke wrote:
>>>> Brian Wakem <no@email.com> wrote:
>>>>> I have a number of scripts that generate queries where the number of
>>>>> rows I need is dynamic ( LIMIT 0,$rows ).
>>>>>
>>>>> Using MySQL 4.0 I can get all the rows by setting $rows to -1. This
>>>>> produces a syntax error in 5.0.
>>>>
>>>> According to the manual, both parameters to the LIMIT clause must be
>>>> nonnegative (in all MySQL versions). So in fact pre-5.0 was broken
>>>> (probably casting the argument to unsigned after parsing).
>>>>
>>>> The correct solution would be to remove the LIMIT clause from the
>>>> statement if one does not need it.
>>>
>>>
>>>
>>> I was afraid that would be the answer. This is not going to straight
>>> forward to fix as the number -1 does not even appear in the scripts
>>> themselves but is a result of a calculation, making it impossible to
>>> find them until they produce an error.
>>>
>>> I will need to have a little think.
>>>
>>>
>>>

>> $sql = "bla bla without limit";
>>
>> if ($row != -1) {
>> $sql .= ' LIMIT 0,' . $row;
>> }
>>
>> Something like htis?

>
>
>
> Yes it's a rather messy hack really, but the only alternative I can think
> of
> is to set $rows to a very high number, which would be just as messy.
> Maybe time to toss a coin.

I decided to go for:

$rows = 1_000_000_000 if $rows == -1;


--
Brian Wakem
Email: [url]http://homepage.ntlworld.com/b.wakem/myemail.png[/url]

[Snef]

Brian Wakem wrote:

> Brian Wakem wrote:
>

>> Snef wrote:

>>> Brian Wakem wrote:
>>>> Axel Schwenke wrote:
>>>>> Brian Wakem <no@email.com> wrote:
>>>>>> I have a number of scripts that generate queries where the number of
>>>>>> rows I need is dynamic ( LIMIT 0,$rows ).
>>>>>>
>>>>>> Using MySQL 4.0 I can get all the rows by setting $rows to -1. This
>>>>>> produces a syntax error in 5.0.
>>>>> According to the manual, both parameters to the LIMIT clause must be
>>>>> nonnegative (in all MySQL versions). So in fact pre-5.0 was broken
>>>>> (probably casting the argument to unsigned after parsing).
>>>>>
>>>>> The correct solution would be to remove the LIMIT clause from the
>>>>> statement if one does not need it.
>>>>
>>>>
>>>> I was afraid that would be the answer. This is not going to straight
>>>> forward to fix as the number -1 does not even appear in the scripts
>>>> themselves but is a result of a calculation, making it impossible to
>>>> find them until they produce an error.
>>>>
>>>> I will need to have a little think.
>>>>
>>>>
>>>>
>>> $sql = "bla bla without limit";
>>>
>>> if ($row != -1) {
>>> $sql .= ' LIMIT 0,' . $row;
>>> }
>>>
>>> Something like htis?

>>
>>
>> Yes it's a rather messy hack really, but the only alternative I can think
>> of
>> is to set $rows to a very high number, which would be just as messy.
>> Maybe time to toss a coin.

>
>
> I decided to go for:
>
> $rows = 1_000_000_000 if $rows == -1;
>
>

How about performance? I only use LIMIT when it us meaningfull.
LIMIT 0,1000000000 seems a bit strange to me.

Nevertheless, when it works, it works ;)

[Brian Wakem]

Snef wrote:

>
>
> Brian Wakem wrote:

>> Brian Wakem wrote:
>>

>>> Snef wrote:
>>>> Brian Wakem wrote:
>>>>> Axel Schwenke wrote:
>>>>>> Brian Wakem <no@email.com> wrote:
>>>>>>> I have a number of scripts that generate queries where the number of
>>>>>>> rows I need is dynamic ( LIMIT 0,$rows ).
>>>>>>>
>>>>>>> Using MySQL 4.0 I can get all the rows by setting $rows to -1. This
>>>>>>> produces a syntax error in 5.0.
>>>>>> According to the manual, both parameters to the LIMIT clause must be
>>>>>> nonnegative (in all MySQL versions). So in fact pre-5.0 was broken
>>>>>> (probably casting the argument to unsigned after parsing).
>>>>>>
>>>>>> The correct solution would be to remove the LIMIT clause from the
>>>>>> statement if one does not need it.
>>>>>
>>>>>
>>>>> I was afraid that would be the answer. This is not going to straight
>>>>> forward to fix as the number -1 does not even appear in the scripts
>>>>> themselves but is a result of a calculation, making it impossible to
>>>>> find them until they produce an error.
>>>>>
>>>>> I will need to have a little think.
>>>>>
>>>>>
>>>>>
>>>> $sql = "bla bla without limit";
>>>>
>>>> if ($row != -1) {
>>>> $sql .= ' LIMIT 0,' . $row;
>>>> }
>>>>
>>>> Something like htis?
>>>
>>>
>>> Yes it's a rather messy hack really, but the only alternative I can
>>> think of
>>> is to set $rows to a very high number, which would be just as messy.
>>> Maybe time to toss a coin.

>>
>>
>> I decided to go for:
>>
>> $rows = 1_000_000_000 if $rows == -1;
>>
>>

> How about performance? I only use LIMIT when it us meaningfull.
> LIMIT 0,1000000000 seems a bit strange to me.
>
> Nevertheless, when it works, it works ;)

No performance hit apparently:-


mysql> SELECT * FROM refmap;

<snip results>

418536 rows in set (1.17 sec)



mysql> SELECT * FROM refmap LIMIT 0,1000000000;

<snip results>

418536 rows in set (1.17 sec)



--
Brian Wakem
Email: [url]http://homepage.ntlworld.com/b.wakem/myemail.png[/url]

[Murdoc]

Brian Wakem wrote:

> Brian Wakem wrote:
>

> > Snef wrote:

> >> Brian Wakem wrote:
> >>> Axel Schwenke wrote:
> >>>> Brian Wakem <no@email.com> wrote:
> >>>>> I have a number of scripts that generate queries where the number of
> >>>>> rows I need is dynamic ( LIMIT 0,$rows ).
> > > > > >
> >>>>> Using MySQL 4.0 I can get all the rows by setting $rows to -1. This
> >>>>> produces a syntax error in 5.0.
> > > > >
> >>>> According to the manual, both parameters to the LIMIT clause must be
> >>>> nonnegative (in all MySQL versions). So in fact pre-5.0 was broken
> >>>> (probably casting the argument to unsigned after parsing).
> > > > >
> >>>> The correct solution would be to remove the LIMIT clause from the
> >>>> statement if one does not need it.
> > > >
> > > >
> > > >
> >>> I was afraid that would be the answer. This is not going to straight
> >>> forward to fix as the number -1 does not even appear in the scripts
> >>> themselves but is a result of a calculation, making it impossible to
> >>> find them until they produce an error.
> > > >
> >>> I will need to have a little think.
> > > >
> > > >
> > > >
> >> $sql = "bla bla without limit";
> >>
> >> if ($row != -1) {
> >> $sql .= ' LIMIT 0,' . $row;
> >> }
> >>
> >> Something like htis?

> >
> >
> >
> > Yes it's a rather messy hack really, but the only alternative I can think
> > of
> > is to set $rows to a very high number, which would be just as messy.
> > Maybe time to toss a coin.

>
>
> I decided to go for:
>
> $rows = 1_000_000_000 if $rows == -1;

And in the instance where there will be more than 1,000,000,000 rows? There is a difference between returning all rows, and returning the minimum number of all rows and a billion rows.

--

[Dikkie Dik]

>> I decided to go for:

>>
>> $rows = 1_000_000_000 if $rows == -1;

>
> And in the instance where there will be more than 1,000,000,000 rows? There is a difference between returning all rows, and returning the minimum number of all rows and a billion rows.
>

Do you have any idea how long it would take to put 1,000,000,000 rows
over the network?. I think you will find that there is absolutely _no_
difference between a time-out error and a time-out error.

Best regards

[Murdoc]

Dikkie Dik wrote:

> > > I decided to go for:
> > >
> > > $rows = 1_000_000_000 if $rows == -1;

> >
> > And in the instance where there will be more than 1,000,000,000 rows? There is a difference between returning all rows, and returning the minimum number of all rows and a billion rows.
> >

> Do you have any idea how long it would take to put 1,000,000,000 rows over the network?. I think you will find that there is absolutely no difference between a time-out error and a time-out error.
>
> Best regards

Who said anything about pulling the resultset over the network? What if the resultset is used in a stored procedure? The above solution, as previously stated, does not solve the problem.

--

[Jerry Stuckle]

Murdoc wrote:

> Dikkie Dik wrote:
>
>

>>>>I decided to go for:
>>>>
>>>>$rows = 1_000_000_000 if $rows == -1;
>>>
>>>And in the instance where there will be more than 1,000,000,000 rows? There is a difference between returning all rows, and returning the minimum number of all rows and a billion rows.
>>>

>>
>>Do you have any idea how long it would take to put 1,000,000,000 rows over the network?. I think you will find that there is absolutely no difference between a time-out error and a time-out error.
>>
>>Best regards

>
>
> Who said anything about pulling the resultset over the network? What if the resultset is used in a stored procedure? The above solution, as previously stated, does not solve the problem.
>

I think for all practical purposes it will fix the problem for this
user. No, it won't fix the problem for all users. But no one suggested
it for all users.

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
[email]jstucklex@attglobal.net[/email]
==================

[Brian Wakem]

Murdoc wrote:

> Brian Wakem wrote:

>> I decided to go for:
>>
>> $rows = 1_000_000_000 if $rows == -1;

>
> And in the instance where there will be more than 1,000,000,000 rows?
> There is a difference between returning all rows, and returning the
> minimum number of all rows and a billion rows.

If the tables in question ever have more than a billion rows then that will
be the at least of my problems.



--
Brian Wakem
Email: [url]http://homepage.ntlworld.com/b.wakem/myemail.png[/url]