MySQL Bugs: #21120: Query doesn't execute properly in Query Browser

Bug #21120	Query doesn't execute properly in Query Browser
Submitted:	18 Jul 2006 17:45	Modified:	16 Nov 2006 13:43
Reporter:	Tobias Asplund	Email Updates:
Status:	Won't fix	Impact on me:	None
Category:	MySQL Query Browser	Severity:	S3 (Non-critical)
Version:	1.2.1 beta	OS:	Windows (Windows)
Assigned to:	Mike Lischke	CPU Architecture:	Any
Tags:	Source Editors

Description:
A single query union doesn't seem to execute properly in Query browser.

How to repeat:
( SELECT Name, Population
FROM Country
ORDER BY Population DESC
LIMIT 5 ) ORDER BY Population ASC

(Use the world example database from the MySQL documentation page)

Thank you for the bug report. Below the query log:

PS C:\mysql\data> cat light.log
mysqld-nt, Version: 5.0.23-community-nt-log. started with:
TCP Port: 0, Named Pipe: (null)
Time                 Id Command    Argument
060718 22:21:24       1 Connect     root@localhost on
                      1 Query       SET SESSION interactive_timeout=1000000
                      1 Query       SELECT @@sql_mode
                      1 Query       SET SESSION sql_mode=''
                      1 Query       SET NAMES utf8
                      1 Init DB     world
                      1 Init DB     world
                      1 Query       show databases
                      1 Init DB     world
                      1 Query       SHOW FULL TABLES
                      1 Query       SHOW COLUMNS FROM `city`
                      1 Query       SHOW COLUMNS FROM `country`
                      1 Query       SHOW COLUMNS FROM `countrylanguage`
                      1 Init DB     world
                      1 Init DB     world
                      1 Query       SHOW PROCEDURE STATUS
                      1 Query       SHOW FUNCTION STATUS
                      1 Init DB     world
060718 22:21:30       2 Connect     root@localhost on world
                      2 Query       SET SESSION interactive_timeout=1000000
                      2 Query       SELECT @@sql_mode
                      2 Query       SET SESSION sql_mode=''
                      2 Query       SET NAMES utf8
                      2 Query       SELECT @@SQL_MODE
                      2 Query       ( SELECT Name, Population
FROM Country
ORDER BY Population DESC
LIMIT 5 ) ORDER BY Population ASC
                      2 Quit
060718 22:21:34       1 Quit
060718 22:21:37       3 Connect     root@localhost on
                      3 Shutdown
PS C:\mysql\data>

Indeed QB returns not data how the mysql client does:

PS C:\mysql\bin> ./mysql -uroot world
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 5.0.23-community-nt

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

mysql> ( SELECT Name, Population
    -> FROM Country
    -> ORDER BY Population DESC
    -> LIMIT 5 ) ORDER BY Population ASC;
+---------------+------------+
| Name          | Population |
+---------------+------------+
| Brazil        |  170115000 |
| Indonesia     |  212107000 |
| United States |  278357000 |
| India         | 1013662000 |
| China         | 1277558000 |
+---------------+------------+
5 rows in set (0.00 sec)

mysql>

This is actually a wrong (incomplete) query, even if the server accepts it. This is rather a bug in the server. QB behaves correctly. This problem is actually a bit larger, e.g.

select * from ( select name, population from country order by population desc limit 5 ) order by population asc;

(which would be the complete query) does not work as we need an alias here after ). However adding this alias (which also is correct syntax) in the original query also throws an error that the alias is wrong. 

( select name, population from country order by population desc limit 5 ) as c order by population asc;

So this is clearly a server problem.

Look at the explain output between the different queries, one is a union and one is a subquery.