Bug #3118 Subquery and order by
Submitted: 9 Mar 2004 8:43 Modified: 17 Mar 2004 7:26
Reporter: Donny Simonton Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server Severity:S2 (Serious)
Version:4.1.1 OS:Linux (Linux)
Assigned to: Ramil Kalimullin CPU Architecture:Any

[9 Mar 2004 8:43] Donny Simonton
Description:
I saw this on the mysql mailing list.

> mysql> SELECT host,facility,priority,date,time FROM logs WHERE
> host=(select
> ip from hostip where host='ams602.avctr.gxs.com');
> +---------------+----------+----------+------------+----------+
> | host          | facility | priority | date       | time     |
> +---------------+----------+----------+------------+----------+
> | 204.90.248.18 | auth     | info     | 2004-03-06 | 02:00:03 |
> | 204.90.248.18 | auth     | info     | 2004-03-09 | 02:00:03 |
> | 204.90.248.18 | mail     | info     | 2004-03-09 | 04:15:08 |
> | 204.90.248.18 | mail     | info     | 2004-03-09 | 04:15:09 |
> | 204.90.248.18 | auth     | info     | 2004-03-09 | 09:17:26 |
> +---------------+----------+----------+------------+----------+
> 5 rows in set (0.01 sec)
> 
> mysql> SELECT host,facility,priority,date,time FROM logs WHERE
> host=(select
> ip from hostip where host='ams602.avctr.gxs.com') order by date;
> Empty set (0.00 sec)
> 
> mysql> SELECT host,facility,priority,date,time FROM logs WHERE
> host=(select
> ip from hostip where host='ams602.avctr.gxs.com') and facility='auth'
> order
> by date;
> +---------------+----------+----------+------------+----------+
> | host          | facility | priority | date       | time     |
> +---------------+----------+----------+------------+----------+
> | 204.90.248.18 | auth     | info     | 2004-03-06 | 02:00:03 |
> | 204.90.248.18 | auth     | info     | 2004-03-09 | 02:00:03 |
> | 204.90.248.18 | auth     | info     | 2004-03-09 | 09:17:26 |
> +---------------+----------+----------+------------+----------+
> 3 rows in set (0.01 sec)

I then verified with one of my own queries that I through together.

This works:
SELECT  * 
FROM WordScoreTemp
WHERE word = ( 
SELECT word
FROM Word
WHERE word =  'mysql'  )

This does not:
SELECT  * 
FROM WordScoreTemp
WHERE word = ( 
SELECT word
FROM Word
WHERE word =  'mysql'  ) order by score;

How to repeat:
See above, it is repeatable.
[10 Mar 2004 7:57] Dean Ellis
Verified against 4.1.2.  Thank you.

CREATE TABLE test ( a int, b int, INDEX (a,b) );
INSERT INTO test VALUES (1,1),(1,2),(1,3);
SELECT * FROM test WHERE a = ( SELECT MAX(a) FROM test WHERE a = 1 ) ORDER BY a;
SELECT * FROM test WHERE a = ( SELECT MAX(a) FROM test WHERE a = 1 ) ORDER BY b;
ALTER TABLE test DROP KEY a, ADD KEY (a);
SELECT * FROM test WHERE a = ( SELECT MAX(a) FROM test WHERE a = 1 ) ORDER BY a;
SELECT * FROM test WHERE a = ( SELECT MAX(a) FROM test WHERE a = 1 ) ORDER BY b;
ALTER TABLE test DROP KEY a;
SELECT * FROM test WHERE a = ( SELECT MAX(a) FROM test WHERE a = 1 ) ORDER BY a;
SELECT * FROM test WHERE a = ( SELECT MAX(a) FROM test WHERE a = 1 ) ORDER BY b;
DROP TABLE test;
[16 Mar 2004 4:33] Ramil Kalimullin
ChangeSet
  1.1779 04/03/16 16:28:50 ram@gw.mysql.r18.ru +4 -0
  Fix for the bug #3118: Subquery and order by
[17 Mar 2004 7:26] Ramil Kalimullin
Thank you for your bug report. This issue has been committed to our
source repository of that product and will be incorporated into the
next release.

If necessary, you can access the source repository and build the latest
available version, including the bugfix, yourself. More information 
about accessing the source trees is available at
    http://www.mysql.com/doc/en/Installing_source_tree.html