Re: urban myth?

[Garth Webb]

On Mon, 2004-05-03 at 10:39, Boyd E. Hemphill wrote:
> My boss says that if you do a select statement against a table the
> result set always comes back in the same order.  I say that this is a
> myth and that the result is random, except when some ordering is
> specified in the SQL statement.
> 
> Who is right?  Is this behavior specified by ANSI or ISO?

You are correct.  Ordering takes time.  Why choose a random column on
which to order the results and take additional time when the user didn't
ask for it.  Here's the proof:

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

+------+
| num  |
+------+
|    1 |
|    2 |
|    3 |
|    4 |
|    5 |
+------+
5 rows in set (0.00 sec)

delete from foo where num = 3;
insert into foo values (6);
insert into foo values (3);
delete from foo where num = 6;
select * from foo;
+------+
| num  |
+------+
|    1 |
|    2 |
|    4 |
|    5 |
|    3 |
+------+
5 rows in set (0.00 sec)


Garth