MySQL Bugs: #10507: Table contents are not sorted on primary key if additional columns exist.

Bug #10507	Table contents are not sorted on primary key if additional columns exist.
Submitted:	10 May 2005 12:17	Modified:	12 Jul 2005 12:20
Reporter:	Disha	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	5.0.5 Beta	OS:	Windows (Windows 2003)
Assigned to:		CPU Architecture:	Any

Description:
If a table contains columns other than the once used for primary key then the table contents are not sorted according to the primary key.

How to repeat:
1. Start the MySQL client and connect to the database with valid user and password.
2. Set the delimiter to //
3. Create a table with single int column with primary key as follows:
   create table t1(f1 int, primary key(f1))//
   insert into t1 values(10),(1),(5)//
   select * from t1//
4. The result of the select statement shows sorted output as follows:

   mysql> select * from t1//
   +----+
   | f1 |
   +----+
   |  1 |
   |  5 |
   | 10 |
   +----+
   3 rows in set (0.00 sec)

5. Now drop the table and recreate the same table with additional integer column as follows:
   drop table if exists t1//
   create table t1(f1 int, f2 int, primary key(f1))//
   insert into t1 values(10,2),(1,7),(5,4)//
   select * from t1//
   
Expected Results: 
1. The select statement should display the records in sorted order on f1 column.

Actual Results: 
1. The sorting is not done on f1 column and the result is as follows:
   mysql> select * from t1//
   +----+------+
   | f1 | f2   |
   +----+------+
   | 10 |    2 |
   |  1 |    7 |
   |  5 |    4 |
   +----+------+
   3 rows in set (0.00 sec)

For to have a column sorted you should use the clasue order by:

mysql>    select * from t1 order by f1//
+----+------+
| f1 | f2   |
+----+------+
|  1 |    7 |
|  5 |    4 |
| 10 |    2 |
+----+------+
3 rows in set (0.02 sec)

This bug is that when we have just one field in a table and set it as primary key, all the data inserted is sorted. However, if more than one field exist, then the data is not sorted.

Addtionally, by default the contents should be sorted on the primary key, which is what is observed when we have only one column in the table. Hence inconsistent behaviour is observed when we have more than one field.

FYI, in SQL Server, primary key constraint creates clustered index on primary key, which sorts the rows physically in database. Is there any documentation that has any such information for MySQL.

MySQL is a relational database system and relational database systems require only that the database be perceived by the user as tables. Tables are the logical structure in a relational database system, not the physical structure. Similarly in rows. At the physical level, in fact, the system is free to store the data any way it likes - using sequential files, indexing, hashing, pointer chains, compression, and so on - provided only that it can map that stored representation to tables at the logical level. Thus, there is no need to store rows in primary key order or attributes in the tables in the same order as they are listed in create table clause.
If user wants to have rows and/or attributes in certain order he/she has to request this using e.g. SQL. In InnoDB a cluster index is used for primary key meaning that rows indeed are ordered at the physical structure. But this is not required by the SQL standard. As a conclusion, observed behavior is not a bug it a feature.