[development] My first post: path_set_alias() and multiple aliases

[David Metzler]

Short answer:
Yep don't count on the order of anything that doesn't have an order  
by clause.

Longer answer just cause some seem interested:
Most DB's (mysql included) will return unordered expressions in the  
order that they are retrieved, which sometimes equates to order of  
insertion but not always.  All kinds of things can change that.   
Indexes get use because of data in the where clause. Maintenance  
activities in some databases will change the order of storage. Some  
databases will reuse of deleted rows causing the order to be  
different.  Some databases store multiple tables data in the same  
file (oracle) and this can really get crazy when space gets reused.

The biggest real world example of this is that in one of my larger  
databases, the order of things changed when we exported and re- 
imported data (to facilitate a character set conversion), which  
exposed a whole bunch of queries where the unfiltered list seemed to  
be broken.  These were just queries that didn't have order by clauses  
in the expression.  But I've seen query plans change in upgrades of  
database software also.

So while it may seem to work, even across databases, you really  
shouldn't count on it, not even within a database architecture.

Dave



On Feb 20, 2009, at 10:41 AM, Peter Droogmans wrote:

> My 2c
>
> If I remember correctly from my DB classes, all tables in a  
> relational databases are unordered, you cannot assume an order. My  
> experience is that all databases use table (or insertion) order for  
> simple select statements (like select x from y), but you might get  
> "strange" ordering while using grouping / having clauses. So the  
> safest is to specify the order, you never know if mysql is going to  
> behave differently.
>
> Peter
>
> -----Original Message-----
> From: development-bounces at drupal.org [mailto:development- 
> bounces at drupal.org] On Behalf Of Andrew Berry
> Sent: donderdag 19 februari 2009 15:37
> To: development at drupal.org
> Subject: Re: [development] My first post: path_set_alias() and  
> multiple aliases
>
> On 19-Feb-09, at 3:59 AM, Dipen wrote:
>
>> As an API, I think it should return all aliases of a path or should
>> take an additional parameter like 'all','latest','oldest' depending
>> on how you want it. But yeah it returns the oldest alias and just
>> one entry.
>
> I seem to remember that unless you do an ORDER BY that the order of
> returned results are implementation dependent. I can't seem to find
> anything to back that up other than a few old notes though. So while
> MySQL returns the first matched result in order of insertion, I think
> that could change when using some other RDBMS.
>
> Anyone have any more info?
>
> --Andrew