List:General Discussion« Previous MessageNext Message »
From:Boyd E. Hemphill Date:May 3 2004 7:22pm
Subject:RE: urban myth?
View as plain text  
To all who answered thank you.  This answer below is the one that I can
use to convince him what he proposes is not necessarily safe.  

Now I just need to decide how to convince him it was his idea :-)



Best Regards,
Boyd E. Hemphill
bhemphill@stripped
Triand, Inc.
www.triand.com
O:  (512) 248-2287
M:  (713) 252-4688

-----Original Message-----
From: Michael Stassen [mailto:Michael.Stassen@stripped] 
Sent: Monday, May 03, 2004 12:57 PM
To: Bob Ramsey
Cc: mysql@stripped
Subject: Re: urban myth?


Bob Ramsey wrote:

> Ah, but the ordering is not random.  As your example has it, the
results 
> are in the order that the entries were inserted into the table.  There

> is an explanation for the order of the returned data.
<snip>

Apparently not random, but not in the order inserted either.  Consider:

create temporary table foo (num int(10));
insert into foo values (1), (2), (3), (4), (5);
select * from foo;
delete from foo where num = 3;
insert into foo values (6);
insert into foo values (3);

mysql> select * from foo;
+------+
| num  |
+------+
|    1 |
|    2 |
|    6 |
|    4 |
|    5 |
|    3 |
+------+
6 rows in set (0.01 sec)

(Same example as before with the "delete...where num=6" removed.)  Note
the 
6 is where the 3 was originally, because the slot where the first 3 was 
inserted/deleted was reused for the 6.

This trivial example yields results which are ordered neither by num nor
by 
the order inserted.  The lesson is clear: The *only* way to be sure your

rows are sorted in a particular way is to explicitly request it with an 
ORDER BY clause, as several others have pointed out.  This is really a 
fundamental principle: It is the data in the row that matters, not how
or 
where it is stored.

Michael



-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/mysql?unsub=1


Thread
Create table results in (errno: 121)Tom Brown22 Apr
  • Re: Create table results in (errno: 121)Martijn Tonies22 Apr
  • Re: Create table results in (errno: 121)Tom Brown22 Apr
  • Re: Create table results in (errno: 121)Adam28 Apr
    • urban myth?Boyd E. Hemphill3 May
      • Re: urban myth?Peter J Milanese3 May
      • Re: urban myth?Daniel Clark3 May
      • Re: urban myth?Garth Webb3 May
        • Re: urban myth?Bob Ramsey3 May
          • Re: urban myth?Josh Trutwin3 May
          • Re: urban myth?Garth Webb3 May
          • Re: urban myth?Michael Stassen3 May
            • RE: urban myth?Boyd E. Hemphill3 May
              • Re: urban myth?Anders Karlsson3 May
              • Re: urban myth?Michael T. Babcock13 May
      • Re: urban myth?Josh Trutwin3 May
      • Re: urban myth?Jeremy Zawodny3 May
      • Re: urban myth?gerald_clark3 May
        • Re: urban myth?Peter J Milanese3 May
Re: Create table results in (errno: 121)beacker22 Apr
RE: urban myth?Mike Johnson3 May
Re: urban myth?Udikarni3 May