MySQL Bugs: #2816: Sorting Incorrect

Bug #2816	Sorting Incorrect
Submitted:	15 Feb 2004 21:56	Modified:	16 Feb 2004 0:50
Reporter:	Wahid Sabbir	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server	Severity:	S1 (Critical)
Version:	4.0.15-standard	OS:	Linux (Linux)
Assigned to:		CPU Architecture:	Any

Description:
We have created a database for our online newspaper 'The Independent' http://www.independent.com.bd In our database we created a table for storing the news. The sturcture of the table is given below:

CREATE TABLE news (
  news_id mediumint(6) unsigned NOT NULL auto_increment,
  issue_id mediumint(5) unsigned NOT NULL default '0',
  news_topic varchar(30) default NULL,
  news_title varchar(255) default NULL,
  news_writer varchar(50) default NULL,
  news_summary blob,
  news_full_story blob,
  news_story_priority tinyint(1) unsigned NOT NULL default '1',
  news_date timestamp(8) NOT NULL,
  news_sub_title varchar(255) default NULL,
  PRIMARY KEY  (news_id),
  KEY issue (issue_id)
)

The database is working fine, but when we ran the query 
SELECT news_id, issue_id, news_topic FROM news WHERE issue_id = '1948'
result like the following is shown

news_id  issue_id  news_topic
9900     1948      metropolitan
9899     1948      top_story
9901     1948      metropolitan
9902     1948      metropolitan

If you look at the second row of the result our primary key field 'news_id' is sorted incorrectly. In our website scripts sets some orders based on this 'news_id' field sorting and incorrect results are shown due to this problem. You may recomend using a 'ORDER BY' clause, but consider the following case:
SELECT DISTINCT news_topic FROM news WHERE issue_id = '1948'
and we want the 'top_story' to be on top of 'metropolitan'

How to repeat:
Try creating the table and store some data. After delete some inbetween arbitrary rows and insert some records again. Then run the query.

You forgot a "ORDER BY" in your query.

If you have read the description well then you would have noticed that I mentioned about the 'ORDER BY' clause. Ok it's true that if I add a 'ORDER BY news_id' to the SQL statement the ordering is ok, then how does the rows are ordered when a 'SELECT' SQL query is run without the 'ORDER BY' clause and why does the row jumped up/down.

Thank you for taking the time to write to us, but this is not
a bug. Please double-check the documentation available at
http://www.mysql.com/documentation/ and the instructions on
how to report a bug at http://bugs.mysql.com/how-to-report.php

Additional info:

Due to basic requirements of Relational Model tables in *any* relational database have no internal order. When ORDER BY is not specified server can return rows in any order and even decide it different every time you run the query.

You wrote:
"... then how does the rows are ordered when a 'SELECT' SQL query is run without the 'ORDER BY' ..."

This is wrong from client perspective. When client sends query without ORDER BY the is instructed that client don't care about the order.