Professional Web Applications Themes

LIMIT 0,-1 broken in 5.0 - MySQL

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]...

  1. #1

    Default LIMIT 0,-1 broken in 5.0

    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]
    Brian Wakem Guest

  2. #2

    Default Re: LIMIT 0,-1 broken in 5.0

    Brian Wakem <noemail.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]
    Axel Schwenke Guest

  3. #3

    Default Re: LIMIT 0,-1 broken in 5.0

    Axel Schwenke wrote:
    > Brian Wakem <noemail.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]
    Brian Wakem Guest

  4. #4

    Default Re: LIMIT 0,-1 broken in 5.0

    Brian Wakem wrote:
    > Axel Schwenke wrote:
    >> Brian Wakem <noemail.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?
    Snef Guest

  5. #5

    Default Re: LIMIT 0,-1 broken in 5.0

    Snef wrote:
    > Brian Wakem wrote:
    >> Axel Schwenke wrote:
    >>> Brian Wakem <noemail.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 Guest

  6. #6

    Default Re: LIMIT 0,-1 broken in 5.0

    Brian Wakem wrote:
    > Snef wrote:
    >> Brian Wakem wrote:
    >>> Axel Schwenke wrote:
    >>>> Brian Wakem <noemail.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]
    Brian Wakem Guest

  7. #7

    Default Re: LIMIT 0,-1 broken in 5.0



    Brian Wakem wrote:
    > Brian Wakem wrote:
    >
    >> Snef wrote:
    >>> Brian Wakem wrote:
    >>>> Axel Schwenke wrote:
    >>>>> Brian Wakem <noemail.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 ;)
    Snef Guest

  8. #8

    Default Re: LIMIT 0,-1 broken in 5.0

    Snef wrote:
    >
    >
    > Brian Wakem wrote:
    >> Brian Wakem wrote:
    >>
    >>> Snef wrote:
    >>>> Brian Wakem wrote:
    >>>>> Axel Schwenke wrote:
    >>>>>> Brian Wakem <noemail.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]
    Brian Wakem Guest

  9. #9

    Default Re: LIMIT 0,-1 broken in 5.0

    Brian Wakem wrote:
    > Brian Wakem wrote:
    >
    > > Snef wrote:
    > >> Brian Wakem wrote:
    > >>> Axel Schwenke wrote:
    > >>>> Brian Wakem <noemail.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.

    --

    Murdoc Guest

  10. #10

    Default Re: LIMIT 0,-1 broken in 5.0

    >> 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
    Dikkie Dik Guest

  11. #11

    Default Re: LIMIT 0,-1 broken in 5.0

    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.

    --

    Murdoc Guest

  12. #12

    Default Re: LIMIT 0,-1 broken in 5.0

    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]jstucklexattglobal.net[/email]
    ==================
    Jerry Stuckle Guest

  13. #13

    Default Re: LIMIT 0,-1 broken in 5.0

    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]
    Brian Wakem Guest

  14. #14

    Default Re: LIMIT 0,-1 broken in 5.0

    On Sun, 3 Sep 2006 12:26:03 +0000 (UTC), Murdoc wrote:
     
    >>
    >> 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[/ref]
    >
    > 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.[/ref]

    My day job is working on CRM databases for a Fortune 50 company. We have
    no tables with that many rows in them. Tens of millions, sure, but not
    billions.

    And, no, it's not running on MySQL. Though, I think it would be fun to
    try...

    --
    33. I won't require high-ranking female members of my organization to wear
    a stainless-steel bustier. Morale is better with a more casual dress-code.
    Similarly, outfits made entirely from black leather will be reserved for
    formal occasions. --Peter Anspach's Evil Overlord List
    Peter Guest

Similar Threads

  1. broken
    By mankybar21 in forum Macromedia Flash Player
    Replies: 1
    Last Post: March 8th, 04:47 AM
  2. Image Limit
    By sincere_shari in forum Macromedia Contribute Connection Administrtion
    Replies: 1
    Last Post: August 15th, 06:19 PM
  3. QBF Limit?
    By Con Giacomini in forum Microsoft Access
    Replies: 3
    Last Post: July 17th, 11:55 AM
  4. Broken key
    By Anders Gjendemsjø in forum Linux Setup, Configuration & Administration
    Replies: 9
    Last Post: July 8th, 07:47 AM
  5. Broken CD
    By Jim in forum Windows XP/2000/ME
    Replies: 1
    Last Post: July 7th, 08:48 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139