List:General Discussion« Previous MessageNext Message »
From:Udikarni Date:May 3 2004 6:16pm
Subject:Re: urban myth?
View as plain text  
As everyone has mentioned - you should always assume the data comes back randomly - even
if the table is completely static and there have been no inserts or updates, but it's
even more subtle than that.

When you port your application to a database than allows your queries to run
multi-threaded (Oracle, DB2, SQL Server) - every query process will return a chunk of the
data and one process will mastermind and glue the chunks together. There is no guarantee
that the chunks will always be glued in the same order - unless ORDER BY has been
specified.

People who get sloppy in single threaded databases and only use a GROUP BY without an
ORDER BY, relying on the fact that the GROUP BY does a sort and makes the ORDER BY
unnecessary - get a rude awakening when they move to a multi-threaded database where
every query process returns its chunk sorted but the different chunks are glued out of
sequence unless you explicitly specified the ORDER BY.

So - certain errors will never become apparent in single-threaded MySQL or Microsot
Access, but will sting you when upgrading to a multi-threaded database. Therefore -
beware - and always specify ORDER BY if you want your data sorted.




In a message dated 5/3/2004 1:39:48 PM Eastern Daylight Time, bhemphill@stripped writes:

> 
> 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?
> 
> Best Regards,
> Boyd E. Hemphill
> bhemphill@stripped
> Triand, Inc.
> www.triand.com
> O:  (512) 248-2287
> M:  (713) 252-4688
> 
> 
> 
> -- 
> 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