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

[29 Oct 2005 20:14] MySQL-Front Team
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;
[29 Oct 2005 20:35] MySQL Verification Team
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)
[1 Nov 2005 0:42] Jim Winstead
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.
[1 Nov 2005 7:10] MySQL-Front Team
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.
[1 Nov 2005 13:29] Sergei Golubchik
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)
[1 Nov 2005 16:59] MySQL-Front Team
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...
[3 Jun 2008 17:12] Ivan Vujisic
select 	TABLE_NAME
from 	information_schema.TABLE_CONSTRAINTS
order by TABLE_NAME;

Works for me!