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