What is the 'natural order' of a select?

#1

I was just prototyping a select on a single table, when I noticed that
the order of what came out, was rather weird.

There is an autoincrement on the primary key, and I expected that in the
absence of an 'order by' clause the order would be ascending in terms of
insertion, hence ID, but it appeared that one late entry was at the top
of the list.

Possibly an older deleteed entry had been 'reused'? with a high number key?

This is a curiousity question only: obviously if I cared about the order
there would be an order by statement in there.

I just wondered whether the behaviour was defined, or really
'implementation dependent' and might vary according to the storage
engine and indeed OS platform used?

#2

On 2011-02-24 13:57, The Natural Philosopher wrote:

Quote:

I was just prototyping a select on a single table, when I noticed that
the order of what came out, was rather weird.

There is an autoincrement on the primary key, and I expected that in the
absence of an 'order by' clause the order would be ascending in terms of
insertion, hence ID, but it appeared that one late entry was at the top
of the list.

Possibly an older deleteed entry had been 'reused'? with a high number key?

This is a curiousity question only: obviously if I cared about the order
there would be an order by statement in there.

I just wondered whether the behaviour was defined, or really
'implementation dependent' and might vary according to the storage
engine and indeed OS platform used?

If you don't specify an order by clause the dbms is free to return the
rows in whatever order it find most appropiate at the moment.

/Lennart

#3

On Thu, 24 Feb 2011 14:10:41 +0100, Lennart Jonsson wrote:

Quote:

On 2011-02-24 13:57, The Natural Philosopher wrote:

I was just prototyping a select on a single table, when I noticed that
the order of what came out, was rather weird.

There is an autoincrement on the primary key, and I expected that in the
absence of an 'order by' clause the order would be ascending in terms of
insertion, hence ID, but it appeared that one late entry was at the top
of the list.

Possibly an older deleteed entry had been 'reused'? with a high number key?

This is a curiousity question only: obviously if I cared about the order
there would be an order by statement in there.

I just wondered whether the behaviour was defined, or really
'implementation dependent' and might vary according to the storage
engine and indeed OS platform used?

If you don't specify an order by clause the dbms is free to return the
rows in whatever order it find most appropiate at the moment.

Which might be in oder written, which might be tempered by reuse of
rows, which might be altered by whatever results are lying around in a
cache.

In short, there is no "natural order".

--
_ o
> /)

#4

Peter H. Coffin wrote:

Quote:

On Thu, 24 Feb 2011 14:10:41 +0100, Lennart Jonsson wrote:
On 2011-02-24 13:57, The Natural Philosopher wrote:
I was just prototyping a select on a single table, when I noticed that
the order of what came out, was rather weird.

There is an autoincrement on the primary key, and I expected that in the
absence of an 'order by' clause the order would be ascending in terms of
insertion, hence ID, but it appeared that one late entry was at the top
of the list.

Possibly an older deleteed entry had been 'reused'? with a high number key?

This is a curiousity question only: obviously if I cared about the order
there would be an order by statement in there.

I just wondered whether the behaviour was defined, or really
'implementation dependent' and might vary according to the storage
engine and indeed OS platform used?

If you don't specify an order by clause the dbms is free to return the
rows in whatever order it find most appropiate at the moment.

Which might be in oder written, which might be tempered by reuse of
rows, which might be altered by whatever results are lying around in a
cache.

In short, there is no "natural order".

well obviously there IS, but it is not something that is reliably the

same ..

Either by accident or by design..

Thanks for the response..I hadn't thought about caching at all.

#5

El 24/02/2011 13:57, The Natural Philosopher escribió/wrote:

Quote:

I was just prototyping a select on a single table, when I noticed that
the order of what came out, was rather weird.

There is an autoincrement on the primary key, and I expected that in the
absence of an 'order by' clause the order would be ascending in terms of
insertion, hence ID, but it appeared that one late entry was at the top
of the list.

Possibly an older deleteed entry had been 'reused'? with a high number key?

This is a curiousity question only: obviously if I cared about the order
there would be an order by statement in there.

I just wondered whether the behaviour was defined, or really
'implementation dependent' and might vary according to the storage
engine and indeed OS platform used?

The default order in SQL explicitly undefined so DBMS are free to do
what best suits them. The obvious approach is to send data in the same
order it's retrieved from disc/memory/cache (any non-requested sorting
would just increase query time for no reason). Such order probably
depends on both the physical location of data and intermediate
operations required to comply with the query terms (imagine a GROUP BY
clause or aggregate functions). I'm sure that even a mere server restart
will alter the order of some queries.

So the no-order order is not really random but it's pretty arbitrary.

--
-- http://alvaro.es - Álvaro G. Vicario - Burgos, Spain
-- Mi sitio sobre programación web: http://borrame.com
-- Mi web de humor satinado: http://www.demogracia.com
--

#6

On 2/24/2011 10:06 AM, The Natural Philosopher wrote:

Quote:

Peter H. Coffin wrote:
On Thu, 24 Feb 2011 14:10:41 +0100, Lennart Jonsson wrote:
On 2011-02-24 13:57, The Natural Philosopher wrote:
I was just prototyping a select on a single table, when I noticed
that the order of what came out, was rather weird.

There is an autoincrement on the primary key, and I expected that in
the absence of an 'order by' clause the order would be ascending in
terms of insertion, hence ID, but it appeared that one late entry
was at the top of the list.

Possibly an older deleteed entry had been 'reused'? with a high
number key?

This is a curiousity question only: obviously if I cared about the
order there would be an order by statement in there.

I just wondered whether the behaviour was defined, or really
'implementation dependent' and might vary according to the storage
engine and indeed OS platform used?

If you don't specify an order by clause the dbms is free to return the
rows in whatever order it find most appropiate at the moment.

Which might be in oder written, which might be tempered by reuse of
rows, which might be altered by whatever results are lying around in a
cache.

In short, there is no "natural order".

well obviously there IS, but it is not something that is reliably the
same ..

Either by accident or by design..

Thanks for the response..I hadn't thought about caching at all.

No, there IS NO NATURAL ORDER. By specification, SELECT statements
without an ORDER BY clause are unordered.

If you were correct, two SELECT statements for the same data would
always return the same order (assuming no changes to the database).
That is NOT necessarily the case.

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex (AT) attglobal (DOT) net
==================

#7

Álvaro G. Vicario wrote:

Quote:

El 24/02/2011 13:57, The Natural Philosopher escribió/wrote:
I was just prototyping a select on a single table, when I noticed that
the order of what came out, was rather weird.

There is an autoincrement on the primary key, and I expected that in the
absence of an 'order by' clause the order would be ascending in terms of
insertion, hence ID, but it appeared that one late entry was at the top
of the list.

Possibly an older deleteed entry had been 'reused'? with a high number
key?

This is a curiousity question only: obviously if I cared about the order
there would be an order by statement in there.

I just wondered whether the behaviour was defined, or really
'implementation dependent' and might vary according to the storage
engine and indeed OS platform used?

The default order in SQL explicitly undefined so DBMS are free to do
what best suits them. The obvious approach is to send data in the same
order it's retrieved from disc/memory/cache (any non-requested sorting
would just increase query time for no reason). Such order probably
depends on both the physical location of data and intermediate
operations required to comply with the query terms (imagine a GROUP BY
clause or aggregate functions). I'm sure that even a mere server restart
will alter the order of some queries.

So the no-order order is not really random but it's pretty arbitrary.

Yup. you say 'explicitly' undefined..where is that made explicit in mysql?

#8

On 24-02-11 18:14, The Natural Philosopher wrote:

Quote:

Álvaro G. Vicario wrote:
El 24/02/2011 13:57, The Natural Philosopher escribió/wrote:
I was just prototyping a select on a single table, when I noticed that
the order of what came out, was rather weird.

There is an autoincrement on the primary key, and I expected that in the
absence of an 'order by' clause the order would be ascending in terms of
insertion, hence ID, but it appeared that one late entry was at the top
of the list.

Possibly an older deleteed entry had been 'reused'? with a high
number key?

This is a curiousity question only: obviously if I cared about the order
there would be an order by statement in there.

I just wondered whether the behaviour was defined, or really
'implementation dependent' and might vary according to the storage
engine and indeed OS platform used?

The default order in SQL explicitly undefined so DBMS are free to do
what best suits them. The obvious approach is to send data in the same
order it's retrieved from disc/memory/cache (any non-requested sorting
would just increase query time for no reason). Such order probably
depends on both the physical location of data and intermediate
operations required to comply with the query terms (imagine a GROUP BY
clause or aggregate functions). I'm sure that even a mere server
restart will alter the order of some queries.

So the no-order order is not really random but it's pretty arbitrary.

Yup. you say 'explicitly' undefined..where is that made explicit in mysql?

Its in the definition of SQL,
i'm pretty sure the link is somewhere on the wikipedia-link

http://en.wikipedia.org/wiki/SQL
The ORDER BY clause identifies which columns are used to sort the
resulting data, and in which direction they should be sorted (options
are ascending or descending). Without an ORDER BY clause, the order of
rows returned by an SQL query is undefined.

--
Luuk

#9

Luuk wrote:

Quote:

On 24-02-11 18:14, The Natural Philosopher wrote:
Álvaro G. Vicario wrote:
El 24/02/2011 13:57, The Natural Philosopher escribió/wrote:
I was just prototyping a select on a single table, when I noticed that
the order of what came out, was rather weird.

There is an autoincrement on the primary key, and I expected that in the
absence of an 'order by' clause the order would be ascending in terms of
insertion, hence ID, but it appeared that one late entry was at the top
of the list.

Possibly an older deleteed entry had been 'reused'? with a high
number key?

This is a curiousity question only: obviously if I cared about the order
there would be an order by statement in there.

I just wondered whether the behaviour was defined, or really
'implementation dependent' and might vary according to the storage
engine and indeed OS platform used?
The default order in SQL explicitly undefined so DBMS are free to do
what best suits them. The obvious approach is to send data in the same
order it's retrieved from disc/memory/cache (any non-requested sorting
would just increase query time for no reason). Such order probably
depends on both the physical location of data and intermediate
operations required to comply with the query terms (imagine a GROUP BY
clause or aggregate functions). I'm sure that even a mere server
restart will alter the order of some queries.

So the no-order order is not really random but it's pretty arbitrary.

Yup. you say 'explicitly' undefined..where is that made explicit in mysql?

Its in the definition of SQL,
i'm pretty sure the link is somewhere on the wikipedia-link

http://en.wikipedia.org/wiki/SQL
The ORDER BY clause identifies which columns are used to sort the
resulting data, and in which direction they should be sorted (options
are ascending or descending). Without an ORDER BY clause, the order of
rows returned by an SQL query is undefined.

Excellent Luuk, I knew if anyone had the definitive answer, it would be you.

#10

On Thu, 24 Feb 2011 15:06:12 +0000, The Natural Philosopher wrote:

Quote:

Peter H. Coffin wrote:
Which might be in oder written, which might be tempered by reuse of
rows, which might be altered by whatever results are lying around in a
cache.

In short, there is no "natural order".

well obviously there IS, but it is not something that is reliably the
same ..

Okay then, "whatever order it comes out". The problem with sticking a
"natural order" on that concept is that it leads people to think that
this order is derivable, predictable, or even repeatable. And that's
unreliable to the point of "IT'S A TRAP! RUN!" levels of unreliable.
It's not an order at all like ORDER BY is an order.

--
36. I will not imprison members of the same party in the same cell
block, let alone the same cell. If they are important prisoners, I
will keep the only key to the cell door on my person instead of
handing out copies to every bottom-rung guard in the prison. --EOList