List:General Discussion« Previous MessageNext Message »
From:Steve Edberg Date:November 5 2002 11:31am
Subject:Re: Bug with "limit" clause
View as plain text  
Not a bug or feature...

As you note, you aren't using an ORDER BY clause in these queries. 
Thus, MySQL is free to return the rows in any order it sees fit. 
Usually, they'll appear in the order they have been inserted, but 
there's no guarantee, especially if there have been 
insertions/deletions.

Remember that the result of a SELECT is an unordered set unless you 
explicitly specify an order.

	-steve


At 12:26 AM +0100 11/5/02, Dirk Hillbrecht wrote:
>Hello MySQL AB,
>
>I want to inform you about a strange behaviour I just had with the 
>MySQL server. Probably it's a bug, probably I've not read the docs 
>good enough. Here it goes:
>
>Situation: My application splits huge queries into a bunch of 
>smaller ones using the "limit" clause. One can doubt whether this is 
>good style or not, but years earlier this was the only way to handle 
>large ResultSets (it's a Java app) and preformance is not that bad...
>
>So, I have two tables, "person" and "persontoorg" which can be 
>joined on "person.nr=persontoorg.person". I perform a certain select 
>which reads like this:
>
>select distinct 
>person.name,person.prename,person.street,person.streetnr,person.country,person.postalcode,person.city,person.belongs,person.telnr,
> 
>person.telnr2,person.faxnr,person.faxnr2,person.email,person.misc,persontoorg.nrinorg
> 
>from person,persontoorg where persontoorg.person=person.nr and 
>persontoorg.orgnr=1
>
>When I do this, I get 4251 rows in the result.
>
>Now, I split this query via the way mentioned above. So, my first query is
>
>---
>select distinct 
>person.name,person.prename,person.street,person.streetnr,person.country,person.postalcode,person.city,person.belongs,person.telnr,
> 
>person.telnr2,person.faxnr,person.faxnr2,person.email,person.misc,persontoorg.nrinorg
> 
>from person,persontoorg where persontoorg.person=person.nr and 
>persontoorg.orgnr=1
>limit 16
>---
>
>then I query
>
>---
>select distinct 
>person.name,person.prename,person.street,person.streetnr,person.country,person.postalcode,person.city,person.belongs,person.telnr,
> 
>person.telnr2,person.faxnr,person.faxnr2,person.email,person.misc,persontoorg.nrinorg
> 
>from person,persontoorg where persontoorg.person=person.nr and 
>persontoorg.orgnr=1
>limit 16,15
>---
>
>next is
>
>---
>select distinct 
>person.name,person.prename,person.street,person.streetnr,person.country,person.postalcode,person.city,person.belongs,person.telnr,
> 
>person.telnr2,person.faxnr,person.faxnr2,person.email,person.misc,persontoorg.nrinorg
> 
>from person,persontoorg where persontoorg.person=person.nr and 
>persontoorg.orgnr=1
>limit 31.15
>---
>
>and so on. As I expect, the chunks' contents are equal to the result 
>of the one, large query without "limit". With one exception: The 
>very last query
>
>---
>select distinct 
>person.name,person.prename,person.street,person.streetnr,person.country,person.postalcode,person.city,person.belongs,person.telnr,
> 
>person.telnr2,person.faxnr,person.faxnr2,person.email,person.misc,persontoorg.nrinorg
> 
>from person,persontoorg where persontoorg.person=person.nr and 
>persontoorg.orgnr=1
>limit 4246,15
>---
>
>delivers _not_ the last five entries I expect, but five different 
>entries of the table which have already been delivered earlier. So, 
>I get some doubles, but the last entries are missing. Took me about 
>three hours to find this odd behaviour...
>
>When I do "...limit 4246,X" in the clause, X in (1,2,3,4) gives the 
>"correct" entries from the end of the table while X>=5 just jumps in 
>its middle again.
>
>Workaround: Rewrite the query using some "order" clause. While this 
>is shuffling all entries compared to before, it seems to guarantee 
>that all entries are processed and transmitted.
>
>Now my question: Bug or feature? Server is 3.23.48, SuSE 8.0.
>
>Best regards,
>Dirk
>
>--
>--- Dirk Hillbrecht
>----- chitec OHG, Vahrenwalder Str. 7/TCH, 30165 Hannover
>----- Tel.: +49/511/9357-840, Fax: +49/511/9357-849
>----- eMail: dh@stripped, Web: http://www.chitec.de


-- 
+------------------------------------------------------------------------+
| Steve Edberg                                      sbedberg@stripped |
| University of California, Davis                          (530)754-9127 |
| Programming/Database/SysAdmin               http://pgfsun.ucdavis.edu/ |
+------------------------------------------------------------------------+
| SETI@Home: 1001 Work units on 23 oct 2002                              |
| 3.152 years CPU time, 3.142 years SETI user... and STILL no aliens...  |
+------------------------------------------------------------------------+
Thread
Bug with "limit" clauseDirk Hillbrecht5 Nov
  • Newbie help needed with mysqljohn5 Nov
    • Re: Newbie help needed with mysqlJohn Coder5 Nov
  • Re: Bug with "limit" clauseSteve Edberg5 Nov
  • Re: Newbie help needed with mysqljohn9 Nov
  • Newbie help needed with mysql : part IIjohn9 Nov
    • Re: Newbie help needed with mysql : part IIJohn Coder9 Nov
    • re: Newbie help needed with mysql : part IIVictoria Reznichenko11 Nov
RE: Newbie help needed with mysqlKelly W [PCS] Black5 Nov