Bug with "limit" clause

[Dirk Hillbrecht]

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: [EMAIL PROTECTED], Web: http://www.chitec.de



---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php