Bug #3118 Subquery and order by

• Submitted: 9 Mar 2004 8:43
• Modified: 17 Mar 2004 7:26
• Reporter: Donny Simonton
• Status: Closed
• 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 [e-mail] +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