MySQL Bugs: #14473: SHOW DATABASES does not order infomation

Bug #14473	SHOW DATABASES does not order infomation_schema correct
Submitted:	29 Oct 2005 20:14	Modified:	1 Nov 2005 0:42
Reporter:	MySQL-Front Team	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	5.0.15/BK source 5.0	OS:	Windows (Windows XP/Linux)
Assigned to:		CPU Architecture:	Any

Description:
SHOW DATABASES;

returns the information_schema database allways at first. Since I expect the databases in alphanumerical order this is not correct.

How to repeat:
SHOW DATABASES;

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 5.0.16-debug

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| test               |
+--------------------+
3 rows in set (0.03 sec)

mysql> create database a;
Query OK, 1 row affected (0.00 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| a                  |
| mysql              |
| test               |
+--------------------+
4 rows in set (0.01 sec)

There is no guarantee of order from 'SHOW DATABASES'. If order is important, one should use a select against INFORMATION_SCHEMA.SCHEMATA with an ORDER BY clause.

Hey guy!

You are joking!

ALL other SHOW commands sorts the result. All of them doing it without a documentation of the sorting.

Sorry, but if all SHOW queries sorts the result - only one doesn't: This is a BUG!!!

If you are not able to accept this you are a very poor guy!

Indeed I know it's not easy to fix this bug since information_schema is't a physical database and you have to sort it into the list of physical databases. But this cannot be a reason not to fix this bug.

Actually, no SHOW command sorts the result.

SHOW DATABASES does not always returns names in the alphabetic order, on a different filesystem and/or kernel version sorting order may broke.

(search for bugs where "DROP DATABASE does not work" for details)

We don't need to discuss:

SHOW DATABASES shows the databases in alphabetical order - without information_schema.

SHOW TABLES shows the tables in alphabetical order.

SELECT * FROM information_schema.schemata shows the databases in alphabetical order - without information_schema.

... so there must be any kind of ordering - but you don't want to insert information_schema on the right pleace.

Ok, you don't want to investigate your time - but it's still a bug, if you are able to see this bug or not doesn't relate about the existing of this bug...

select 	TABLE_NAME
from 	information_schema.TABLE_CONSTRAINTS
order by TABLE_NAME;

Works for me!