Bug #2810 subselect with order by

• Submitted: 15 Feb 2004 11:57
• Modified: 16 Feb 2004 1:02
• Reporter: Manu -
• Status: Can't repeat
• Category: MySQL Server: InnoDB storage engine
• Severity: S3 (Non-critical)
• Version: 4.1.1-alpha
• OS: Linux (linux (slackware))
• Assigned to: Heikki Tuuri
• CPU Architecture: Any

[15 Feb 2004 11:57] Manu -

Description:
While:

SELECT * FROM comuni WHERE provincia_id = (SELECT provincia_id FROM comuni where id = '1');

works fine (3 results),
instead:

SELECT * FROM comuni WHERE provincia_id = (SELECT provincia_id FROM comuni where id = '1') order by citta asc;

DOES NOT WORK!
Subselects with "order by" return 0 rows. Vary strange. Bug?

How to repeat:
any subselect with order by,

[16 Feb 2004 1:02] Alexander Keremidarski

Not enough information was provided for us to be able
to handle this bug. Please re-read the instructions at
http://bugs.mysql.com/how-to-report.php

If you can provide more information, feel free to add it
to this bug and change the status back to 'Open'.

Thank you for your interest in MySQL.

Additional info:

mysql> select * from t;
+------+------+------+
| i1   | i2   | i3   |
+------+------+------+
|    1 |    1 |    6 |
|    1 |    2 |    7 |
|    1 |    3 |    6 |
|    2 |    1 |    1 |
|    2 |    2 |    7 |
+------+------+------+

mysql> select * from t where i1 = (select i1 from t where i2 = 3);
+------+------+------+
| i1   | i2   | i3   |
+------+------+------+
|    1 |    1 |    6 |
|    1 |    2 |    7 |
|    1 |    3 |    6 |
+------+------+------+
3 rows in set (0.00 sec)
 
mysql> select * from t where i1 = (select i1 from t where i2 = 3) order by i3 asc;
+------+------+------+
| i1   | i2   | i3   |
+------+------+------+
|    1 |    1 |    6 |
|    1 |    3 |    6 |
|    1 |    2 |    7 |
+------+------+------+
3 rows in set (0.00 sec)

mysql> select version();
+-----------------------+
| version()             |
+-----------------------+
| 4.1.2-alpha-debug-log |
+-----------------------+